Here is the code and output for the labor level 4 scenario:
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].[color="#FFFF00"][LBRACCT4 DES][/color].CHILDREN, [Measures].[RankedMetric]), 5,[Measures].[RankedMetric])' MEMBER [Measures].[Rank] AS 'Rank ([Labor Levels].[color="#FFFF00"][LBRACCT4 DES][/color].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))]
output for labor level 4 is attached.
In yellow is where the change for labor level 4 and the code would be the exact same for labor level 6 just replacing the 4 with a 6. Is there an if statement I can use that would run based on what the user selects? or a hide function using mdx language that would hide one result set when the other is selected?
ok I messed up on the yellow part disregard the color portion of the above code.