Archive

Posts Tagged ‘Nested Rank’

MDX – Nested Ranking

February 24, 2012 Leave a comment

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

Categories: MDX Tags: