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

Get MDX with Microsoft SQL Server 2016 Analysis Services Cookbook - Third Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.