Performing complex sorts
Sorting is one of those often-requested operations. To sort a hierarchy by a measure is not a problem. Neither is to sort a hierarchy using its member properties. The MDX language has a designated function for that operation and a straightforward one too. Yes, we're talking about the Order() function.
Difficulties appear when we need to sort two or more hierarchies, one inside the other, or when we need to use two or more criteria. Not to mention the confusion when one of the members on the columns is supposed to be the criteria for sorting a related hierarchy on the rows. These are complex sort operations, operations we will cover in this recipe.
Let us build a case and see how it should be solved.
Getting ready
Start SSMS ...
Get MDX with Microsoft SQL Server 2016 Analysis Services Cookbook - Third Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
