Chapter 6 Quick Reference

To

Do this

Order a set by an expression

Use the Order function to sort the set. For example, the following query orders all products by reseller sales in descending order:

SELECT
  {([Measures].[Reseller Sales Amount])} ON COLUMNS,
  Order(
    {[Product].[Product].[Product].Members},
    ([Measures].[Reseller Sales Amount]),
    DESC
    ) ON ROWS
FROM [Step-by-Step]

Use the ASC or DESC flag to perform a hierarchal sort in ascending or descending order, and use the BASC or BDESC flag to sort across hierarchies in ascending or descending order, respectively.

Restore the order of a set to its natural order

Use the Hierarchize function to sort a set by its natural order. For example:

SELECT {([Measures].[Reseller Sales Amount])} ON COLUMNS, Hierarchize( ...

Get Microsoft® SQL Server® 2008 MDX Step by Step now with O’Reilly online learning.

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