O'Reilly logo

MDX Solutions: With Microsoft® SQL Server™ Analysis Services 2005 and Hyperion® Essbase, Second Edition by Francesco Civardi, Dylan Hai Huang, Christopher Webb, Sivakumar Harinath, George Spofford

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

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

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