Summary Rows on Different Columns

[JDawg]

[Solution found]
Create query calculation in new column and Total(Rev for Dim) then group span so no repeating values, align bottom...boom!


Hello All,

I have a very specific summary formatting request for a report, please take a look at the attachment. Essentially I need to remove the summary rows but move these to the detailed rows as a separate column....hope I'm saying that correct, the attached image shows it better than I can explain. I'm thinking I need to create running totals for each dimension I need to summarize by? If that's the route I need to go, how do I only display the sum at the end of that row for each dimension?

I can obviously get all the correct totals I need within the same Rev column, but I am reproducing a report without any enhancement from the business and they are used to looking at it this way, so it makes it more difficult on me unfortunately.  :-\

Any help is much appreciate, thanks and have a great day!

-JDawg

[BigChris]

Not sure if this suggestion will do it for you, but could you firstly group on the product line as wel as the product group? The you could calculate Total([Rev] for [Product Group], [Product Line]) and group span the product line. Similarly you could calculate Total([Rev] for [Product Group]) and group span product group.

You'll need to make sure you don't get multiple values adding up in you grand total, but that might at least come close to what you need.

Incidentally, you didn't mention whether you were using relational or dimensional date...I've assumed relational so this approach might not work with dimensional.

[JDawg]

Hi BigChris,

Thanks for the reply, one of my co-workers came back with the same Total(Rev for Dim) and group span then align bottom idea just before I saw this. I guess great minds think alike! :)

I was able to get the report formatted correctly this way, thanks for the feedback.

FYI...it is relational...so it works!

Thanks again,

JDawg