MDX Solutions: With Microsoft® SQL Server™ Analysis Services 2005 and Hyperion® Essbase, Second Edition

Chapter 6. Sorting and Ranking in MDX

A very common requirement for analytical applications is providing sorted and ranked views of data. Users are always interested in Top−10 lists, and often in seeing who or what was #1 last month or year. We'll cover the basics of sorting and ranking in this chapter, and also look at some ins and outs for more advanced kinds of sorting and ranking calculations.

Functions used in this chapter are Order(), Hierarchize(), Top-Count(), BottomCount(), TopSum(), BottomSum(), TopPercent(), BottomPercent(), YTD(), CoalesceEmpty(), Rank(), ParallelPeriod(), Generate(), Ancestor(), Descendants(),.Properties(), DrillDownLevelTop().

The Function Building Blocks

We took a brief look at the Order() function in Chapter 1, so we've seen one building block already. The full set of functions provided for sorting and ranking purposes are as follows:

FUNCTION	PURPOSE
Order()	Sort a set of tuples
Hierarchize()	Sort a set of tuples into hierarchical order.
TopCount()	Select the top N tuples of a set.
BottomCount()	Select the bottom N tuples of a set.
TopSum()	Select the top tuples of a set whose sum meets a threshold.
BottomSum()	Select the bottom N tuples of a set whose sum meets a threshold.
TopPercent()	Select the tuples of a set corresponding to the top N% of all in set.
BottomPercent()	Select the tuples of a set corresponding to the bottom N% of all in set.
Rank()	Find the ordinal position of a tuple in a set.

Note that all of these functions operate on sets of tuples, not just one-dimensional ...