Skip to Content
avatar image
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.

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

  • Follow
  • Get RSS Feed

2 Answers

  • Best Answer
    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.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    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.

    Add comment
    10|10000 characters needed characters exceeded