[againey11]

I am creating an ssrs report from MSSQL Analysis services 2008 r2 in Visual Studio 2008. The one thing I am getting hung up on is how to return different results sets in a specific matrix based on what the user selects in the report. Below I will post my code and screen shots of the output it generates. The basic issue is I need the user to have the ability to select between two different labor levels (4 or 6) and according to what the user has selected populate the matrix with the appropriate data. The code change from 4 to 6 is just on two lines but what I can't figure out is how to have both pieces of code ready to execute depending on what the user selects.


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.

Attached image(s)

• 

[e_i_pi]

I use SSRS and BI, so what you're doing is a little different to what I'm used to. My advice might be moot / non-applicable to your situation.

When I need to return data based on a condition, I'll either pass a parameter to the query and use a CASE...WHEN...ELSE...END statement to generate data conditionally, or I'll return both cases and let the report definition dynamically display the data based on the user parameter input. Is it possible you could use either of these methods?

[againey11]

yes I actually figured it out but now i'm running into a new problem. Ok so on this dashboard there are a couple different graphs and tables that calculate different things. One of the requirements is to be able to narrow down by either one manufacturing plant site or by multilple or by potentially all. I created a parameter containing all the appropriate plant sites with the ablility to select multiple, one, or all. What I can't figure out how to do is have those graphs and tables update the data based on what plant site the user selects. I will give example of what I am talking about below.


the code below is for the line graph in the report. I also have the code for the labor level 3(manufacturing plant site) data set I created in order to populate the manufacturing plant site drop down.

In the screen shot i have arrows pointing to parameter drop down and line graph.

WITH
MEMBER [Measures].[OT] AS 

'[Measures].[Overtime hours as a % of worked hours]'

MEMBER [Measures].[Unplanned_OT] AS

'[Measures].[Unscheduled OT hours as a % of total OT hours]'

MEMBER [Measures].[Deficient_Punches] AS

'[Measures].[Deficient punches as % of total punches]'

MEMBER [Measures].[Absenteeisim] AS

'[Measures].[Absent events as a % of worked events]'

 SELECT NON EMPTY
{[Measures].[OT]
	,[Measures].[Unplanned_OT]
		,[Measures].[Deficient_Punches]
			,[Measures].[Absenteeisim]
		} on columns,	
ANCESTOR(strtomember("[Date].[Calendar].[Date].
	&["+format(dateadd("d",-1,now()),"yyy-MM-dd")+"T00:00:00]"),1).Lag(5):
		ANCESTOR(strtomember("[Date].[Calendar].[Date].
		&["+format(dateadd("d",-1,now()),"yyy-MM-dd")+"T00:00:00]"),1) on rows		
FROM [Scorecard Daily] 

Then here is the code for the labor level 3 dataset i created in order to populate parameter drop down with correct plant sites.

 SELECT NON EMPTY { } ON COLUMNS FROM ( SELECT ( STRTOSET(@LaborLevelsLBRACCTDES, CONSTRAINED) ) ON COLUMNS FROM [Scorecard Daily]) WHERE ( IIF( STRTOSET(@LaborLevelsLBRACCTDES, CONSTRAINED).Count = 1, STRTOSET(@LaborLevelsLBRACCTDES, CONSTRAINED), [Labor Levels].[LBRACCT3 DES].currentmember ) ) CELL PROPERTIES VALUE

Attached image(s)

•