MDX with Microsoft SQL Server 2016 Analysis Services Cookbook - Third Edition

Basic sorting and ranking

Sorting and ranking are very common requirements in business analysis, and MDX provides several functions for this purpose. They are:

• TopCount and BottomCount
• TopPercent and BottomPercent
• * TopSum and BottomSum
• ORDER
• Hierarchize
• RANK

All of these functions operate on sets of tuples, not just on one-dimensional sets of members. They all, in some way, involve a numeric expression, which is used to evaluate the sorting and the ranking.

Getting ready

We will start with the classic top five (or top-n) example using the TopCount() function. We will then examine how the result is already pre-sorted, followed by using the ORDER() function to sort the result explicitly. Finally, we will see how we can add a ranking number by using the ...