Using MDX Hierarchize to Properly Order Data

Back To Blog

Without any ordering being applied, MDX by default will return data in the same order the members exist in the outline.  This would be the same order you see visually when maintaining the outline with children following their parents.

For an example let’s look at a typical Time dimension that starts at Full Year, has 4 quarters each which has 3 months.  The default return order using DESCENDANTS( [Full Year] ) would be as follows:

In other words the values that make up a total precede the total line.  The problem with this is the majority of the time clients want the parent of the member to follow the children – like the following:

Thankfully MDX has a command to fix this called HIERARCHIZE.  The Hierarchize function organizes the members of the specified set into hierarchical order. Which means you can throw any meta-data query into it, with members in any order, and the results will be returned in hierarchy order.  You can see how this could be very useful if the order of your hierarchy has business importance or just familiarity.  Again, by default, even the HIERARCHIZE function will return members in the original order (as in figure 1 above).  When used with the POST parameter however the HIERARCHIZE command flips the return members in our desired order with children preceding their parents!  So using HIERARCHIZE ( DESCENDANTS( [Full Year] ), POST ) will produce figure 2.

This all sounds great – but you’re probably wondering how you can use this within Clarity CPM.  Clarity Studio’s tools for selecting members is pretty good and suitable in most cases.  There are however many times when the basic query constructs available through the graphical piece of Studio are not enough and you just want to write some MDX and have Clarity run it.  This is exactly what the Clarity Snippet is for and what we need to implement the HIERARCHIZE statement.

You`ll find the snippet object available in Studio whenever you are building OLAP dimension member lists.  Instead of inserting a dimension member from the tree, insert the Snippet instead.

Selecting the Snippet button with will then bring up a dialog in which you can enter the HIERARCHIZE MDX statement.

  • Important Note: in Essbase, Snippets in member lists used for Page Options must be in Essbase Calculation Script, not MDX – so you would need to use the more advanced <Script> element in the template XML to implement this solution in this situation.  See my other blog entry titled Ordering Clarity OLAP Page Option Lists for details on how to do this.

You may also be wondering why this is an issue at all when you can just create multiple row ranges for each parent and use Excel formulas for the total.  This is true, but does not create as dynamic of a report.  If your outline changes significantly you report may become out of date and require maintenance – but with a more dynamic report it will just adjust automatically to the new hierarchy!  By creating a report that returns all members, including parents, and using the row member property selection and the block formatting feature you can create a dynamic that both looks good and adjusts to changes in your hierarchy automatically.  Stay tuned for more details on this in a future blog post!

Although I used a Time dimension in this example, I think an Account or Cost Center dimension would actually be the more likely situation where HIERARCHIZE would be the most useful.