MDX - SQL Server Analysis Services 2012 Cube Development - page 212

Sorting the query output
To make the output easier to decipher, you could use the ORDER function to sort the
returned dimension members based on some criteria. In addition, many reports re-
quire limiting the results only to the best- or worst-performing hierarchy members. You
can exploit the TOPCOUNT and BOTTOMCOUNT functions to meet such requirements.
How to do it...
Let's get started with sorting the query output.
1. The ORDER function accepts a set as the parameter and allows sorting in an
ascending or descending manner, depending on the sorting expression. For
example, the following query returns those cities in which the reseller sales
amount for components exceeded $200000, ordering results based on the re-
seller sales amount:
SELECT ORDER (
FILTER([Geography].[City].members,
[Measures].[Reseller Sales Amount] > 200000
AND
[Geography].[City].CurrentMember.Name<>
'All Geographies'),
[Measures].[Reseller Sales Amount],
DESC)ON 0,
[Measures].[Reseller Sales Amount] ON 1
FROM [Adventure Works]
WHERE [Product].[Product
Categories].[Category].[Components]
2. Another frequent requirement is to sort the output based on the city's name,
rather than the amount of sales. The following query sorts cities based on
their name in the ascending order, once again exploiting the CurrentMember
function:
SELECT ORDER(
FILTER([Geography].[City].members,