Almost everyone that picks a report in the first time wants to see the big numbers first, and only later see the details. And this happens due to technical reasons.
Most reporting engines use running formulas to do the calculations and present the totals in the end. If you ever tried to display the totals in the header of the report, you'll know it's a nightmare; It's either by using complicated sql inner queries or if you do it on the reporting engine side you'll have a complicated logic to maintain.
On some conditions, though, there's a very easy way to do this. The answer is MDX (am I becoming repetitive? :) ) Just try this mdx query in the steelwheels schema:
select NON EMPTY {[Time].[All Years].[2003], [Time].[All Years].[2004], [Time].[All Years].[2005]} ON COLUMNS,
NON EMPTY Descendants([Product].[All Products], [Product].[Vendor], SELF_AND_BEFORE) ON ROWS
from [SteelWheelsSales]
This query, that uses the very useful Descendants function with the SELF_AND_BEFORE attribute, returns the following result:
If we just use this query in the report designer, we'll get a nice report with the totals in the front of the details.
Ah, and notice that with mdx we also get crosstabs for free
7 comments:
Believe it or not, our monthly sales reports have the grand totals at the top, and the bottom! :)
Check this about totals in Jpivot:
http://212.170.170.186/Daspivot/economicas.jsp?query=economicas
Emilio
Yeah, it shouldn't matter what technology you use, it should be easy to build reports where the totals come first. When I get my phone bill, I certainly don't want to have to scroll through all the call records to get to the total. I only read the call records if the total is higher than normal.
I'm obviously a huge fan of using MDX to build formatted reports. I need to work with Thomas to expose every last shred of metadata in the result set - gleaned from the underlying DB columns if that's where the user defined it.
Strangely, MDX makes it a bit hard to put the totals last, because of how the Hierarchize function works.
Julian
@tom: And how was that achieved?
@Emilio: We can certainly do it in jpivot - my topic was more about transporting it to reporting.
@Julian: Indeed, in this approach we couldn't use the hierarchize function in the same way; at least to get human readable results, we'd probably have to play with the Generate function.
Anyway, what I really like is that even currently we can - indirectly - get metadata info out of the mdx query. I've used the Level function to get the level name and use it with style expressions in PRD to get different indentations, font sizes, whatever_I_want. Basically, with a single query get a unlimited number of groups in a much maintainable way
Hi!
ok - so if I understand correctly you are using this form of the DESCENDENTS function:
Descendants(
Member_Expression
, Level_Expression
, Desc_Flag
)
with
Member expression: [Product].[All Products]
Level expression: [Product].[Vendor]
Desc_Flag: SELF_AND_BEFORE
And in English, it would translate to something like "From the [Product] hierarchy, from all descendants of the [All Products] member, those members that reside at the [Vendor] level, and additionally, those members that occur at any level including and between the level of [All Products] and the [Vendor] level".
Great example of the power of MDX. All hail MDX!!
@pedro Oops, I just remembered that Hierarchize has a POST flag:
HIERARCHIZE(Product.MEMBERS, POST)
will display the grand total, [All Products], last.
You can use GENERATE if you like. It's a fantastic function - you might call it MDX's very own Map/Reduce - and it's always fun to use it to solve problems.
Julian
Post a Comment