Archive

Posts Tagged ‘Rank’

MDX – Rank

December 20, 2011 2 comments

Recently while developing a dashboard report, I have got a requirement to show the TOP N most selling employee list.
I am always curious about the report rendering on rowNumber/rowCount while retrieving the TOP N employee list using MDX.
However, I am trying something different using Rank function to generate the rank which intern becomes the rowNumber in report.
 

WITH 
  SET [EmployeeRankSet] AS 
    Order
    (
      NonEmpty
      (
        [Employee].[Employee Department].[Employee].MEMBERS
       ,[Measures].[Reseller Sales Amount]
      )
     ,[Measures].[Reseller Sales Amount]
     ,desc
    ) 
  MEMBER [Measures].[EmployeeRank] AS 
    Rank
    (
      [Employee].[Employee Department].CurrentMember
     ,EmployeeRankSet
    ) 
SELECT 
  {
    [Measures].[EmployeeRank]
   ,[Measures].[Reseller Sales Amount]
  } ON 0
 ,Order
  (
    NonEmpty
    (
      [Employee].[Employee Department].[Employee].MEMBERS
     ,[Measures].[Reseller Sales Amount]
    )
   ,[Measures].[Reseller Sales Amount]
   ,desc
  ) ON 1
FROM [Adventure Works];

We could also use the [EmployeeRankSet] on rows as, here eventually we have used the same set twice so just replaced with existing dataset

WITH 
  SET [EmployeeRankSet] AS 
    Order
    (
      NonEmpty
      (
        [Employee].[Employee Department].[Employee].MEMBERS
       ,[Measures].[Reseller Sales Amount]
      )
     ,[Measures].[Reseller Sales Amount]
     ,desc
    ) 
  MEMBER [Measures].[EmployeeRank] AS 
    Rank
    (
      [Employee].[Employee Department].CurrentMember
     ,EmployeeRankSet
    ) 
SELECT 
  {
    [Measures].[EmployeeRank]
   ,[Measures].[Reseller Sales Amount]
  } ON 0
 ,{[EmployeeRankSet]} ON 1
FROM [Adventure Works];


 
Points to consider:
 
1. I tried this method to avoid rowNumber in SSRS report layer
2. We could also use TopCount function to have only the required ranks
3. After getting the Rank, just drag and drop in report layer to change the header name to a corresponding business name i.e. rowNumber

Categories: MDX Tags: