Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Totaling Problem

Totaling Problem

Totaling Problem

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!

RE: Totaling Problem

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

RE: Totaling Problem

Yes, smart totals are being used.

RE: Totaling Problem

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.

RE: Totaling Problem

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.

RE: Totaling Problem

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close