Former Member

# WEBI Crosstab - Insert a row with a custom subtotal

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.

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
10|10000 characters needed characters exceeded

May 29, 2015 at 04:10 PM

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.

10|10000 characters needed characters exceeded
• Former Member Former Member

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

• Former Member
May 29, 2015 at 06:57 PM

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.