If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

How to use the correct granularity when aggregating summary rows?

Started by thubble, 01 Feb 2008 11:05:33 AM

Previous topic - Next topic

thubble

I'm designing a model for sales transactions which has 2 levels: Invoice level (highest) and item level (lowest).

A simplified version of the data looks something like this:


Invoice# InvoiceTotal ItemName ItemPrice
0001 50.00 Item A 20.00
0001 50.00 Item A 20.00
0001 50.00 Item B 10.00


I've set up determinants and this works for the detail rows (i.e. in query studio, if I drag out Invoice# and InvoiceTotal, the detail row shows $50 for the InvoiceTotal instead of $150).

However, if I drag out Invoice#, InvoiceTotal and ItemName, all 3 detail rows still correctly show $50 for the InvoiceTotal, but the summary row shows $150 (total of all 3 rows).

I can get around this in Report Studio by doing TOTAL (MINIMUM ([InvoiceTotal] FOR [Invoice#]) FOR REPORT) but this is not an ideal solution and does not work in Query Studio.

Is there anything I can do at the model level to get these values to aggregate correctly?

Note that just summing up ItemPrice to get the InvoiceTotal will not work, as the actual model is far more complicated than the simplified one in my example (there is actually another, lower level - Option - but hopefully if I can get invoice/item to work I can apply the same method to Option).

Thanks in advance.