Totaling Problem

[petperson]

Hi - I have a report that goes down to the lowest level in the heirarchy (SKU). It has facts of QTY and COST. A metric (TRANS_AMT) with the calculation of QTY * COST is created. If I display at the site/sku level for example, I would have QTY = 1 ; COST = 10.00 ; TRANS_AMT = 10.00
and another row might read QTY = 2 ; COST = 15.00 ; TRANS_AMT = 30.00. So far so good. The problem is when I total DOWN on the report it seems to be using averages. This leaves me with QTY = 1.5 ( 3/2), COST = 12.50 (25.00/2) and TRANS_AMT = 20.00 (40/2). This scenario seems to work OK at the lowest level (SKU) but when try to roll it up to a site level - it starts skewing the totals. Any suggestions? thanks!

 

[chael]

Do you have smart totals enabled on the report? Check this.

 

[petperson]

Yes, smart totals are being used.

 

[JRO061601]

Can you create a simple report - i.e. few columns and few rows - that displays this problem? If so, send a screen shot of the grid and a copy of the SQL to mstrtemp@hotmail.com and I'll take a look at it if you want.

 

[chael]

Stupid question, but what happens if you turn off smart totals? Have you tweaked the VLDB properties on this report too? One last thing, the SQL may be going to different tables during each pass...doesn't sound like the problem, but worth thinking about.

 

[nlim]

there are really 2 issues here

1. totalling :
- it really does not make sense to subtotal the cost column so I would get rid of it.
- you really want to total(trans amt) so I would follow chael's suggestion and turn smart totals OFF.

2. if you want to roll up to the site level, really what you want is get all the transamt at the SKU level first, then sum that by site. To do this, you need to create 2 metrics:
- M1 sum(cost x qty) dimensional at the SKU level
- M2 = sum(M1)

now put M2 on your report and you should be able to drill up and down with no problems...this is what they term "nested aggregation"

let us know if it works for you.