Impromptu - Subtotal on same field in different Footers

[oxl]

I am trying to get subtotals on the same Field in two different footers. First, I need to sub-total sales for all package sizes of a given product, for each customer. Then I need a second sub-total for each product for ALL customers.

It should look like this:

Invoice
Cust 1
Prod A
10 Cnt $100 (values in table, not shown in report)
50 Cnt $250 (values in table, not shown in report)
Total $350 (first roll up - report detail level)
Prod B
5 Cnt $75 (values in table, not shown in report)
30 Cnt $350 (values in table, not shown in report)
Total $425 (first roll up - report detail level)
Total Cust 1 $775

Cust 2
Prod A
10 Cnt $150 (values in table, not shown in report)
50 Cnt $150 (values in table, not shown in report)
Total $300 (first roll up - report detail level)
Prod B
5 Cnt $175 (values in table, not shown in report)
30 Cnt $150 (values in table, not shown in report)
Total $325 (first roll up - report detail level)
Total Cust 2 $625

Prod A Total $650(second roll up)
Prod B Total $750{second roll-up)

Invoice Total $1400

The part I cannot get is the Prod A and Prod B Totals (the second roll-up using the Prod field)

Thanks

 

[griffindm]

oxl,

One normally does this by creating a separate calculation for each product, grouped at whatever level you desire, and calculated with either If-Then-Else or Decode (Oracle only), as follows:

Non-Oracle:
Total (If (Prod = 'A') then (Inv_amt) else (0)) For grouping...

Oracle:
Total (Decode(Prod,'A',Inv_amt,0)) For grouping...

Hope this helps,

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com