Friday, January 18, 2013

using mdx order purely based on measure

Wonder how to do an "ORDER by" purely based on a measure value! Well yes, it can simply be done using the ORDER function. 

Any idea what the result of this query would be?

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

Here is the result

I am sure this is not what you wanted...As you see they are not sorted yet.

To solve this all that you need to change in the above query is replace the DESC with BDESC (Break Hierarchy Desc)

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

Here is result


No comments:

Post a Comment

Note: Only a member of this blog may post a comment.

LinkWithin

Related Posts with Thumbnails