MDX Ranking Formula Does not Work when Dimension Gets Filtered

• Dwight M. Specht

  Old Hand

  Points: 323

  More actions

  January 17, 2012 at 9:58 am

  #150763

  This is a bit of a complex question. I am trying to create a dashboard against SSAS data that displays an employee's rank, based on revenue produced, for a given time period.

  In SSAS 2008R2,I wrote the following calculated member to establish a person's rank by billed revenue in the given period:

  CREATE MEMBER CURRENTCUBE.[Measures].[Consulting Rank - By Revenue]

  AS Rank([Employees].[Employee ID].currentmember, [Consultants-RankedByRevenue] ),

  FORMAT_STRING = "Standard",

  VISIBLE = 1 , DISPLAY_FOLDER = 'Actual' , ASSOCIATED_MEASURE_GROUP = 'Project Transactions' ;

  The Set [Consultants-RankedByRevenue] looks like this:

  CREATE DYNAMIC SET CURRENTCUBE.[Consultants-RankedByRevenue]

  AS order([Employees].[Employee ID].Children,([Measures].[Revenue Billable],[Master Date].[Dates by Billing Period].CurrentMember), DESC);

  If I use the measure in Excel Pivot Tables using the [Employees].[Employee ID] in the page filter limited to a single employee, Excel generates the below MDX and does not correctly display the rank (the calculation returns 0):

  SELECT

  FROM [Projects]

  WHERE ( [Master Date].[Dates by Billing Period].[Year].&[2012].&[Q1].&[1].&[2012-01-05T00:00:00], [Employees].[Employee ID].&[DDUNCAN], [Measures].[Consulting Rank - By Revenue] ) CELL PROPERTIES VALUE

  , FORMAT_STRING

  , LANGUAGE

  , BACK_COLOR

  , FORE_COLOR

  , FONT_FLAGS

  If I put the [Employees].[Employee ID] dimension into a row and filter to single employee, the rank is shown as 1 and generates the following MDX:

  SELECT NON EMPTY Hierarchize (

  {

  DrilldownLevel (

  { [Employees].[Employee ID].[All] }

  ,

  ,

  , INCLUDE_CALC_MEMBERS

  )

  }

  ) DIMENSION PROPERTIES PARENT_UNIQUE_NAME

  , HIERARCHY_UNIQUE_NAME ON COLUMNS

  FROM (

  SELECT ( { [Employees].[Employee ID].&[DDUNCAN] } ) ON COLUMNS

  FROM [Projects]

  )

  WHERE ( [Master Date].[Dates by Billing Period].[Year].&[2012].&[Q1].&[1].&[2012-01-05T00:00:00], [Measures].[Consulting Rank - By Revenue] ) CELL PROPERTIES VALUE

  , FORMAT_STRING

  , LANGUAGE

  , BACK_COLOR

  , FORE_COLOR

  , FONT_FLAGS

  If I don't filter the row, I get the correct ranking and the following MDX:

  SELECT NON EMPTY Hierarchize (

  {

  DrilldownLevel (

  { [Employees].[Employee ID].[All] }

  ,

  ,

  , INCLUDE_CALC_MEMBERS

  )

  }

  ) DIMENSION PROPERTIES PARENT_UNIQUE_NAME

  , HIERARCHY_UNIQUE_NAME ON COLUMNS

  FROM [Projects]

  WHERE ( [Master Date].[Dates by Billing Period].[Year].&[2012].&[Q1].&[1].&[2012-01-05T00:00:00], [Measures].[Consulting Rank - By Revenue] ) CELL PROPERTIES VALUE

  , FORMAT_STRING

  , LANGUAGE

  , BACK_COLOR

  , FORE_COLOR

  , FONT_FLAGS

  When using SSRS to create a gauge based dashboard, I get similar results (ie: a zero for the rank).

  So, my question: How to I get a rank based on revenue and a time dimension (see first formula) that works even when you are displaying only a single member of the dimension you want ranked?

  Thanks,

  Dwight