Home > MDX > MDX – Rank

MDX – Rank

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

Advertisements
Categories: MDX Tags:
  1. philip
    February 22, 2012 at 11:19 pm

    you could replace lines 23:33 by

    ,[EmployeeRankSet]ON 1

    • February 23, 2012 at 7:07 pm

      Thanks Philip,
      Yes, We could use the same dataset and updated the post with alternate/better idea

  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: