Skip to Main Content
MDX Solutions: With Microsoft® SQL Server™ Analysis Services 2005 and Hyperion® Essbase, Second Edition
book

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

by George Spofford, Sivakumar Harinath, Christopher Webb, Dylan Hai Huang, Francesco Civardi
March 2006
Intermediate to advanced content levelIntermediate to advanced
744 pages
16h 7m
English
Wiley
Content preview from 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 ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Professional SQL Server™ Analysis Services 2005 with MDX

Professional SQL Server™ Analysis Services 2005 with MDX

Sivakumar Harinath, Stephen R. Quinn
Professional Microsoft® SQL Server® Analysis Services 2008 with MDX

Professional Microsoft® SQL Server® Analysis Services 2008 with MDX

Sivakumar Harinath, Matt Carroll, Sethu Meenakshisundaram, Robert Zare, Denny Guang-Yeu Lee
Microsoft® SQL Server™ 2008 Analysis Services Unleashed

Microsoft® SQL Server™ 2008 Analysis Services Unleashed

Irina Gorbach, Alexander Berger, Edward Melomed

Publisher Resources

ISBN: 9780471748083Purchase book