MDX --ORDER FUNCTION-- Part-8

                                          MDX --ORDER FUNCTION-- Part-8
ORDER Function :-
                                                                               ORDER Function is used to sort the data either in ASCENDING order OR DESCENDING order based on given expression.

-- ORDER FUNCTION

 /*  ORDER Function IS USED to SORT the data either in Ascending OR Descending order based on given expression */

-- Actual MDX Query

SELECT [Measures].[Sales Amount] ON COLUMNS

      , [Dim Customer].[Geography Hierarchy].[English Country Region Name]

       ON ROWS

FROM [MSBI]

 -- EXAMPLE- 1  ASCENDING Order Bases on Sales Amount Measure

SELECT [Measures].[Sales Amount] ON COLUMNS

      , ORDER (

                    [Dim Customer].[Geography Hierarchy].[English Country Region Name]

                   ,[Measures].[Sales Amount]

                   ,ASC

               )

       ON ROWS

FROM [MSBI]

 -- EXAMPLE- 2  ASCENDING Order by Breaking all the Hierarchy Levels

SELECT [Measures].[Sales Amount] ON COLUMNS

      , ORDER (

                    [Dim Customer].[Geography Hierarchy].[State Province Name]

                   ,[Measures].[Sales Amount]

                   ,BASC

              )

       ON ROWS

FROM [MSBI]

 -- EXAMPLE- 3  DESCENDING Order Bases on Sales Amount Measure

  SELECT [Measures].[Sales Amount] ON COLUMNS

      , ORDER (

                    [Dim Customer].[Geography Hierarchy].[English Country Region Name]

                   ,[Measures].[Sales Amount]

                   ,DESC

               )

       ON ROWS

FROM [MSBI]

 -- EXAMPLE- 4  DESCENDING Order by Breaking all the Hierarchy Levels

SELECT [Measures].[Sales Amount] ON COLUMNS

      , ORDER (

                    [Dim Customer].[Geography Hierarchy].[State Province Name]

                   ,[Measures].[Sales Amount]

                   ,BDESC

              )

       ON ROWS

FROM [MSBI]

-- HOW TO CHANGE THE ORDER OF DIMENTION ATTRIBUTES

-- Actual MDX Query

SELECT [Measures].[Sales Amount] ON COLUMNS

    ,  [Order Date].[Calendar Hierachy].[Calendar Year]

      ON ROWS

FROM [MSBI]

 -- EXAMPLE- 5  ASCENDING ORDER

 SELECT [Measures].[Sales Amount] ON COLUMNS

    , ORDER (

                  [Dim Customer].[Geography Hierarchy].[English Country Region Name]

                 ,[Dim Customer].[Geography Hierarchy].CURRENTMEMBER.MEMBER_key

                 ,ASC

             )

       ON ROWS      

FROM [MSBI]

SELECT [Measures].[Sales Amount] ON COLUMNS

    , ORDER (

                  [Order Date].[Calendar Hierachy].[Calendar Year]

                 ,[Order Date].[Calendar Hierachy].CURRENTMEMBER.MEMBER_KEY

                 ,ASC

             )

       ON ROWS      

FROM [MSBI]

-- EXAMPLE- 6 ASCENDING Order by Breaking all the Hierarchy Levels

SELECT [Measures].[Sales Amount] ON COLUMNS

    , ORDER (

                  [Dim Customer].[Geography Hierarchy].[State Province Name]

                 ,[Dim Customer].[Geography Hierarchy].CURRENTMEMBER.MEMBER_key

                 ,BASC

             )

       ON ROWS      

FROM [MSBI]

-- EXAMPLE- 7  DESCENDING ORDER

SELECT [Measures].[Sales Amount] ON COLUMNS

    , ORDER (

                  [Dim Customer].[Geography Hierarchy].[English Country Region Name]

                 ,[Dim Customer].[Geography Hierarchy].CURRENTMEMBER.MEMBER_key

                 ,DESC

             )

       ON ROWS      

FROM [MSBI]

SELECT [Measures].[Sales Amount] ON COLUMNS

    , ORDER (

                  [Order Date].[Calendar Hierachy].[Calendar Year]

                 ,[Order Date].[Calendar Hierachy].CURRENTMEMBER.MEMBER_KEY

                 ,DESC

            )

       ON ROWS      

FROM [MSBI]

-- EXAMPLE- 8 DESCENDING Order by Breaking all the Hierarchy Levels

SELECT [Measures].[Sales Amount] ON COLUMNS

    , ORDER (

                  [Dim Customer].[Geography Hierarchy].[State Province Name]

                 ,[Dim Customer].[Geography Hierarchy].CURRENTMEMBER.MEMBER_key

                 ,BDESC

             )

       ON ROWS      

FROM [MSBI]

--Another EXAMPLE - 9

SELECT [Measures].[Sales Amount] ON COLUMNS

    ,( [Dim Customer].[English Country Region Name].[English Country Region Name]

       ,ORDER (

                  [Dim Customer].[Geography Hierarchy].[City]

                 ,[Dim Customer].[Geography Hierarchy].CURRENTMEMBER.MEMBER_key

                 ,ASC

               )

      )

       ON ROWS   

FROM [MSBI]

-- ORDER FUNCTION WITH MULTIPLE ATTRIBUTES (Multiple Columns)

-- Example-10

SELECT [Measures].[Sales Amount] ON COLUMNS

              , ( ORDER(

                         [Dim Customer].[Geography Hierarchy].[English Country Region Name]

                        ,[Dim Customer].[Geography Hierarchy].CURRENTMEMBER.MEMBER_NAME

                         ,DESC

                        )

                     ,ORDER (

                               [Dim Customer].[State Province Name].[State Province Name]

                              ,[Dim Customer].[State Province Name].CURRENTMEMBER.MEMBER_NAME

                              ,DESC

                           )

              ) ON ROWS    

FROM [MSBI]

-- ALTERNATIVE METHODE

SELECT [Measures].[Sales Amount] ON COLUMNS

    , (

          ORDER ( ORDER(

                         [Dim Customer].[Geography Hierarchy].[English Country Region Name]

                        ,[Dim Customer].[Geography Hierarchy].CURRENTMEMBER.MEMBER_NAME

                        ,BDESC

                       )

                    ,[Dim Customer].[State Province Name].CURRENTMEMBER.MEMBER_NAME

                    ,BDESC

             )

          ) ON ROWS 

FROM [MSBI]

Thanks for Looking into this.....