Home > MDX Challenges > MDX #8 – Why the employee parent-child hierarchy is not sorted

MDX #8 – Why the employee parent-child hierarchy is not sorted

Dimension attributes are always pre-sorted

In my previous post, MDX #7 – Get only 10 elements from a MDX query, I mentioned that dimension attributes are always pre-sorted.

This is obvious with the following query.

clip_image001

Parent-child hierarchy is sorted from top to bottom

However, this result does not seem to be sorted in any noticeable order.

clip_image002

Until you realize that the Employees hierarchy in the Employee dimension is a parent-child hierarchy (you can follow this link to see how a typical parent-child hierarchy is constructed:  http://technet.microsoft.com/en-us/library/ms167115.aspx).

clip_image003

The sorting is done from top to bottom. So by default, Jae B. Pak will appear before David R. Campbell, because manager Amy E. Alberts appears before Stephen Y. Jiang.

Now we know that the sorting order is working as expected. But you still want to sort the result alphabetically by employee names.

Use MemberValue function to sort by dimension

The Order function is normally used to sort results by measures. For sorting by dimensions, we can use the Order function and the MemberValue function (or the Member_name) (you can follow this link to see a very good comparison between MemberValue and Member_name and member_key: http://msdn.microsoft.com/en-us/library/ms146082.aspx). Use it as the 2nd parameter for the Order function. 

clip_image004

Change the 3rd parameter from ASC to BASC to break the parent-child hierarchy

The above result is the same as before. Nothing changes. That is because we forgot to tell the MDX engine to break the parent-child hierarchy, and stop sorting from top to bottom.

Changing the 3rd parameter from ASC to BASC will do the trick.

clip_image005

Now the result is sorted alphabetically by employee names, but many of them have no sales.

Let’s add a Non Empty command to the Y axis.

clip_image006

Now we know that a parent-child hierarchy is indeed also pre-sorted, and we also know at least one way now to sort it differently.

  1. No comments yet.
  1. No trackbacks yet.

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

%d bloggers like this: