views:

355

answers:

0

Starting with the MDX below I'd like to add two ranking columns that evaluate the member "Close Ratio". The first rank would be based on the sales rep population. Let's say there are 100 reps then if no close ratios repeat the rep with the highest ratio would be number 1 and the rep with the lowest ratio would be number 100. The second rank is within the "Goal Plan" teams (there are 4 total with sub-populations of 30,40,20,10), so within each team there would be rankings of 1-30,1-40,1-20,1-10. All of these rankings would be based on all months, i.e., a year-to-date rank. Hopefully this makes sense and the MDX below helps.

  WITH MEMBER [Measures].[Close Ratio] 
       AS IIf([Measures].[Lead Quantity] = 0,null
       , [Measures].[Opened Quantity] / [Measures].[Lead Quantity])
SELECT NON EMPTY 
       CrossJoin
       (
          {[Lead Dates].[Month].members}
        , {
            [Measures].[Lead Quantity]
           ,[Measures].[Opened Quantity]
           ,[Measures].[Close Ratio]
          }
       ) ON COLUMNS 
     , NON EMPTY 
       CrossJoin
       (           
          {[Sales Reps].[Goal Plan].children}
        , 
          {[Sales Reps].[Sales Rep Name].members}              
       ) ON ROWS  
  FROM [cube]
 WHERE 
     (
       [Lead Dates].[Year].&[2009]
     , [Sales Reps].[Sales Channel].&[USA]
     )