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