WEBI Crosstab - Insert a row with a custom subtotal

[gordon_jack]

Hello all,

I am attempting to add a custom subtotal in the middle of a cross tab report which is being used as an income statement. The purpose is to show profitability as Gl accounts are included.

I am using the crosstsb with breaks for different GL groupings. The crosstab calculates the subtotal for each break correctly. However, I need to be able to do a calculation accross multiple breaks as demonstrated by the rows with **** in the example below. I am able to inert the row but the calulation seems to only include the values from the individual break.

Advice would be appreciated.

GL Accounts	Jan	Feb
Sales acct 1	100
Sales acct 2	110
Subtotal Sales	210
Direct Cost Acct 1	20
Direct Cost Acct 2	30
Subtotal Direct costs	50
**Sales Minus Direct Costs**	160
Indirect Costs Acct 1	15
Indirect Costs Acct 2	25
Subtotal Indirect Costs	40
**Sales Minus Costs**	120

[tanveer1 · Accepted solution]

Hi,

you can use the variable on which the break is applied lets say the break is on [G/L Account]

1. Insert row at the end of break., that inserts after every break.

2. Create formula to populate the custom text

= If ([G/L Account]="Sales") Then "" Else If ([G/L Account]="Direct Cost") Then "** Sales Minus Direct Costs**" else "**Sales Minus Costs**"

this will populate the text based on your requirement in the newly inserted cell

Similarly create formula to get the totals

= If ([G/L Account]="Sales") Then "" Else If ([G/L Account]="Direct Cost") Then [Amount] where ([G/L Account]="Sales") - [Amount]  else [Amount] where([G/L Account]="Sales") - [Amount]

Thanks,

Tanveer.

[gordon_jack · Accepted solution]

Thank You Tanveer for the response.

I have something similar to what you proposed for the text and it works properly.

For the Subtotal it does not work. I tried the formula you provided but I am still getting the same result. It only seems to use the values within the break. I need a way to include all values from the row.

Direct Cost Acct 1	20	20
Direct Cost Acct 2	30	30
Subtotal Direct costs	50	50
**Sales Minus Direct Costs**	160	50
	Expected	Actual

[gordon_jack · Accepted solution]

Actually, Meant to say I need to include all of the values from the column. Also the Actual result was a negative 50.

[gordon_jack]

I have resolved my issue....I had a higher level break on my report. I removed that which allowed the formula to work. However, It would have been nice to get the formula to work without having to remove the higher level break.