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

  • Question

  • 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

All replies

  • I think you should post this question to SSAS Forum, not SSRS.


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    • Proposed as answer by Lola Wang Thursday, April 26, 2012 6:58 AM
    • Marked as answer by Lola Wang Wednesday, May 2, 2012 8:53 AM
    • Unmarked as answer by Lola Wang Wednesday, May 2, 2012 8:53 AM
    Wednesday, April 25, 2012 6:03 PM