Sorting Data in MDX

We can sort data in MDX by using Order Function as shown below.

Syntax:

ORDER( «Set», {«String Expression» | «Numeric Expression»}[, ASC | DESC | BASC | BDESC] )



Query 1: Orders data in asscending order hounoring default sort defined while cube design.

 SELECT
    [Measures].[Reseller Sales Amount]     ON COLUMNS
    ,ORDER(
            (
                [Geography].[Country].[Country]
                ,[Geography].[Geography].[State-Province]
            )
            ,[Measures].[Reseller Sales Amount]
            ,ASC
          ) ON ROWS
FROM [Adventure Works];

Query 2: Orders data in descending order hounoring default sort defined while cube design.

SELECT
    [Measures].[Reseller Sales Amount]     ON COLUMNS
    ,ORDER(
            (
                [Geography].[Country].[Country]
                ,[Geography].[Geography].[State-Province]
            )
            ,[Measures].[Reseller Sales Amount]
            ,DESC
          ) ON ROWS
FROM [Adventure Works];

Query 3: Orders data in ASCENDING order IGNORING default sort defined while cube design.

SELECT
    [Measures].[Reseller Sales Amount]     ON COLUMNS
    ,ORDER(
            (
                [Geography].[Country].[Country]
                ,[Geography].[Geography].[State-Province]
            )
            ,[Measures].[Reseller Sales Amount]
            ,BASC
          ) ON ROWS
FROM [Adventure Works];

Query 2: Orders data in descending order IGNORING default sort defined while cube design.

SELECT
    [Measures].[Reseller Sales Amount]     ON COLUMNS
    ,ORDER(
            (
                [Geography].[Country].[Country]
                ,[Geography].[Geography].[State-Province]
            )
            ,[Measures].[Reseller Sales Amount]
            ,BDESC
          ) ON ROWS
FROM [Adventure Works];