Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Displaying totals at the bottom

with 8 comments

For someone like me without a financial background, the default behaviour of AS when displaying a hierarchy seems natural: when you ask for a set like MyHierarchy.Members, you’d get all the parent members appearing in the list before their child members. But for financial reports you want the opposite, you want to see all child members displayed before the parent members. The other day I was asked how to do this in turn and asked Andrew Wiles (who has a lot more financial OLAP experience than me), and he pointed me to the simple answer – the HIERARCHIZE function.

Here’s an example from Adventure Works:

SELECT [Measures].[Amount] ON 0,
DESCENDANTS([Account].[Accounts].&[95])
ON 1
FROM [Adventure Works]

Run the query and you’ll see the following returned, with the parent member Statistical Accounts displayed above its children:

pre

However, if you want to display it in the financial style, all you need to do is wrap your set with HIERARCHIZE( <<set>>, POST) as follows:

SELECT [Measures].[Amount] ON 0,
HIERARCHIZE(
DESCENDANTS([Account].[Accounts].&[95])
, POST)
ON 1
FROM [Adventure Works]

post

I have to admit, I’d always wondered what the point of Hierarchize() was – this is the first time I’ve actually needed to use it!

Written by Chris Webb

September 16, 2008 at 1:03 pm

Posted in MDX

8 Responses

Subscribe to comments with RSS.

  1. Although I learned about this whilst working with financial applications I think It is more a "printed report" issue and stems from our days at school where we taught how to "do sums". Sums always have their total at the bottom and so do 99% of all paper reports. I think that the BI/software notation of putting totals at the top of the group is a result of usability when interacting with data. It is a little disconcerting to click on a total to expand its children and have the total diassapear off the screen. My advice is that any report which is to be printed (financial or not) should use Hierarchize(<<set>>, POST). For online reports if the content of the row and column axes is "dynamic/drillable"  then use the normal BI notation, if they are static it is probably best to have the totals at the bottom.

    Andrew

    September 16, 2008 at 4:31 pm

  2. Good to see POST flag being put to use finally. Curiously, this was one of the first things that we put into MDX, even before SELECT statement existed ! My experience with Panorama customers was that they very often wanted totals at the bottom, so I thought that putting this capability into MDX was one of the highest priorities :) Well, I\’m glad to see that it didn\’t turn out completely useless :)

    Mosha

    September 16, 2008 at 5:35 pm

  3. Hi,
     
    this is very good news but I faced an other problem. How can I take this change to table? And change the order of the rows?

    Eeva

    September 17, 2008 at 12:27 pm

  4. It depends on what you mean by \’table\’. Are you using Reporting Services? If so, then you need to modify the underlying MDX query that\’s populating the table to use Hierarchize()

    Chris

    September 17, 2008 at 1:37 pm

  5. Thanks for your prompt answer.
    I\’m using Reporting Services and trying to create Profit&Loss statement. Actually I managed to get row order change to report. But now there is no parent grouping in table details properties and therefore no level() number for the row. I\’d like to add more padding for the account text box but I dont know how to do it.
     
    Br. Eeva

    Eeva

    September 17, 2008 at 1:43 pm

  6. You\’d need to do this in MDX, using an approach similar to the one described here:
    http://www.databasejournal.com/features/mssql/article.php/10894_3523996_3

    Chris

    September 17, 2008 at 4:36 pm

  7. It might be a little of topic but what bugs me more is that you show headcount, units etc. in $ (and in real life I would also question the headcount down to 2 decimals). BI should never forget to present meaningfull numbers to the end user, whether totals on top or bottom.

    Thomas

    September 19, 2008 at 6:28 pm

  8. Hierarchize can be used to reverse the results from Ascendants(), which curiously is the only function I can think of to return children before parents. For example:Hierarchize (  Generate (    [set],    Ascendants (<dim>.CurrentMember)  ))will add in totals for some picked-out set in the right way. 

    Unknown

    September 19, 2008 at 10:01 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,081 other followers

%d bloggers like this: