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:
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]
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!
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.
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 🙂
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?
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()
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
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
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.
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.
Hi Chris,
I am facing an issue in ascedants function in MDX which return “NULL” for only one member in the attribute and ascendants are displaying correctly for other members.
Please find the below query.
WITH SET MYROWS AS ‘{[CO].[Level06].[All].CHILDREN}’
MEMBER [Measures].[AncestorNames]
AS Generate (Ascendants([CO].[Level06]),[CO].[Level06].CurrentMember.NAME,’ ->’)
MEMBER [Measures].[AncestorUniqueNames]
AS Generate (Ascendants([CO].[Level06]),[CO].[Level06].CurrentMember.UNIQUENAME,’ ->’)
SELECT { [Measures].[AncestorNames],[Measures].[AncestorUniqueNames] } ON COLUMNS, {SubSet(MyRows,0,500)} ON ROWS
Could you please suggest what could the issue, checked the data there are no null values in database.
To be clear, you are seeing the text “NULL” somewhere in the text returned by your two calculated measures?
Hi Chris,
Yes,exactly.
For particular member it returns ‘NULL’ value in these two calculated measures.
Please find the below example
AncestorNames AncestorUniqueNames
6X Blocked 6X Blocked ->All [CO].[Level06].&[6X Blocked]&[6 Det] ->[CO].[Level06].[All]
6Z Statistical (null) (null)