One of my columns returning null when it should provide rank (1,2,3,4,5, etc...) WHAT AM I DOING WRONG

• Question

• 

  

  0

  Sign in to vote

  Ok so I cannot figure out why my rank column is returning null when I run this query. It should be basing its ranking (1,2,3,4,5, etc..) of of the 'rankedMetric' calculations. Do I have a syntax problem or are my brackets/parens off somewhere? Below is code and screen shot.

  WITH
  MEMBER [Measures].[Metric] AS [Measures].[Overtime hours as a % of paid hours]
  MEMBER [Measures].[RankedMetric] AS SUM([Measures].[Metric])
  MEMBER [Measures].[SupportingMetric1] AS 'SUM([Measures].[Non productive hours %])' 
  MEMBER [Measures].[SupportingMetric2] AS 'SUM([Measures].[Absent hours as a % of paid hours])' 
  SET [TopSet] AS 
  TopCount (NonEmpty(({[Labor Levels].[LBRACCT4 DES].CHILDREN},STRTOSET(@LaborLevelsLBRACCTDES)), [Measures].[RankedMetric]), 10,[Measures].[RankedMetric])
  MEMBER [Measures].[Rank] AS 
  'Rank  ([Labor Levels].[LBRACCT4 DES].CurrentMember, [TopSet])', FORMAT_STRING = '#;#;-'
  SELECT 
  { [Measures].[Rank], [Measures].[RankedMetric], [Measures].[SupportingMetric1], [Measures].[SupportingMetric2]} on columns,
  { [TopSet] } on rows
  FROM [Scorecard Daily] 
  WHERE 
  PERIODSTODATE([Date].[Calendar].[Quarter],
  ANCESTOR(strtomember("[Date].[Calendar].[Date].
  &["+format(dateadd("d",-1,now()),"yyy-MM-dd")+"T00:00:00]"),1))

  below is the result it gives

  

  Wednesday, April 25, 2012 6:02 PM