O'Reilly logo

MDX with Microsoft SQL Server 2016 Analysis Services Cookbook - Third Edition by Sherry Li, Tomislav Piasevoli

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required