Crosstab with sub-grouping

Hi there,

I have the following data set:
- caseTypeID
- caseTypeDesc
- paymentDate
- paymentAmount
- paymentType (in/out)

I have created a data cube to be used in the crosstab, with groups:
- caseTypeID
- paymentDate (month)

Now, what I'd like to display is, per month, the totals of payments in, totals of payments out, and ratio (%).

Example:
Type | Jan 2008 | Feb 2008 |...
| In | Out | Ratio | In | Out | Ratio |...

Type 1 | 200 | 400 | 50% | 300 | 300 | 100%|...
Type 2 | 300 | 100 | 300%| 50 | 200 | 25% |...

total | 500 | 500 | 100%|350 | 500 | 70% |...

How do I go about creating sub-grouping for my cube in order to achieve this?
Is it possible?

Thanks,
Cedric

Find more posts tagged with

Comments

mwilliams

Hi Cedric,

I was able to create what I believe you're looking for by creating 3 computed columns. One for the incoming payments, putting 0 when that line item is an outgoing payment. Another the same as that only for outgoing payments. Then the third was a dummy column Ratio filled with 0's to be able to add as a summary field that you can edit. You change the expression for this column in the actual crosstab.

caseType is the row grouping. Month is the column grouping. Incoming Payments, Outgoing payments, and the dummy column ratio are the summary fields. Once the crosstab is set up, double click on Ratio and change the expression to Incoming/Outgoing*100 or however you want it to display. Click the dropdown box next to caseType and select totals and add those. Then, delete the ratio one and add a data element that takes the totalIncoming/totalOutgoing*100. Hope this helps.

cypherdj

Thanks for the reply Michael, 1st attempt at crosstab, a new world for me ;-)

I did manage to display the incoming, outgoing by year/month with just one computed column (paymentInd : boolean = (paymentType=='Out')), but could not figure out how to do the ratio.

I'll try your solution first thing tomorrow,

One further question if I may : purely cosmetic, but how do I get to display the month columns even if there is no payment (in or out) for this month? In my crosstab, I only get the months for which there is an amount.

Cheers,
Cedric

mwilliams

Cedric,

I think the trick to getting the ratio to work is by making 2 computed columns putting the 0's when that row is not incoming or outgoing, depending on which computed column you're doing at the time. And of course the dummy computed column for ratio.

Also, by doing the computed column for incoming and outgoing with values to use in the crosstab. You'll always at least have a value of '0' to display, so the in/out column will show up whether there's been a payment or not. You may have to handle this in the ratio column to check whether there is a 0 in the outgoing column cause in that case you'll probably get an error because it's infinity.

cypherdj

Hi Michael,

yes, there is a problem when outgoing=0, and the ideal solution would be to display N/A in this case, but I've set the ratio value to 0 instead.

As you said, the overall ratio cannot be computed with an aggregation, so a data element is what I'll use instead.

Regarding the year/month, I have changed the Format DateTime settings to use a Custom formatting "MMMM" to display months as text, but this still shows 1, 2, ... I'm not quite sure what I'm doing wrong here.

I guess with regards to some months not appearing in the report (as there is no payment in or out that month), this is purely a testing issue, in the live system, this scenario won't manifest itself.

I didn't get a chance to test your ratio idea, but will try it soon,
Thanks again,
Cedric

cypherdj

Hi Michael, 
 
I believe I followed the steps you recommended, but somehow am getting 
 
Retrieving data binding error 
caused by: javax.olap.OLAPException: Can not convert the value of NaN to BigDecimal type. 
 
As suggested, I have 3 computed columns: inAmount, outAmount, ratio=0 
 
I created the data cube with 2 groups and 3 measures, then inserted this cube into a crosstab. 
 
I then double-clicked the ratio measure, and modified its expression to 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
if (!isNaN(measure["expenseAmount"]) &&
!isNaN(measure["claimsAmount"]) &&
measure["expenseAmount"]!=0)
(measure["claimsAmount"]/measure["expenseAmount"])*100
else
0
</pre>
 
I didn't display any totals yet. 
 
When I do not display the ratio in the crosstab, I can see missing values for some case types, so I added the isNaN checks in the expression, to no avail. 
 
Any idea how to address this? 
Thanks, 
Cedric

cypherdj

Strange like once you describe your problem, you suddenly have the flash to fix! 
 
I got around this data binding problem by using the Finance.percent function instead of doing the calculation myself! 
 
So, when editing the expression for ratio, I now have: 
<pre class='_prettyXprint _lang-auto _linenums:0'>
if (data["expenseAmount"]!=0)
Finance.percent(data["expenseAmount"], data["claimsAmount"])
else
0
</pre>
 
And now I have 0.00% in my ratio column, as my sample data does not have any lines with in and out payments for the same month ;-)

mwilliams

Excellent! So, you got it all working then?