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.

Parent-child hierarchy is sorted from top to bottom

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

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).

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. 

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.

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.

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.