Home > MDX > MDX – Nested Ranking

MDX – Nested Ranking

This is a random thought of applying the Rank function to generate the ranks for dimensions.
While developing a report on hierarchies it is very common to get the top 10 parent with top 10 childs for each parent record along with rank numbers into the report for more useability.
Here end-up with this MDX and would like to use this as reference for further development in BI journey

WITH 
  SET [CountryRankSet] AS 
    Order
    (
      NonEmpty
      (
        [Geography].[Country].[Country].MEMBERS
       ,[Measures].[Internet Sales Amount]
      )
     ,[Measures].[Internet Sales Amount]
     ,desc
    ) 
      MEMBER [Measures].[CountryRank] AS 
    Rank
    (
      [Geography].[Country].CurrentMember
     ,CountryRankSet
    ) 
  
  SET [ProductRankSet] AS 
    Order
    (
      NonEmpty
      (
        [Product].[Product].[Product].MEMBERS
       ,[Measures].[Internet Sales Amount]
      )
     ,[Measures].[Internet Sales Amount]
     ,desc
    ) 
      MEMBER [Measures].[ProductRank] AS 
    Rank
    (
      [Product].[Product].CurrentMember
     ,ProductRankSet
    ) 
SELECT 
  {[Measures].[CountryRank],[Measures].[ProductRank],[Measures].[Internet Sales Amount]} ON 0
 
 ,Generate
  (
    [Geography].[Country].[Country].MEMBERS
   ,TopCount
    (
      Order
      (
          [Geography].[Country].CurrentMember
        * 
          [Product].[Product].[Product].MEMBERS
       ,[Measures].[Internet Sales Amount]
       ,DESC
      )
     ,10
    )
  ) ON 1
FROM [Adventure Works];



Points to consider:
1. Very sure, there are alternate/better ways to write this using the Nested Generate function
2. Will try to apply this for further development of MDX

Advertisements
Categories: MDX Tags:
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: