Parameterize MDX in SSRS

Question

Relatively new to MDX but familiar with SSRS and I have a query that works just as I want it to in the query designer in SSMS however I'm not quite sure how to parameterize this in SSRS.

Query returns department and shift as dimensions and Hours and Period as Measures with the query being filtered by a fiscal year and the Period measure as a specific period for that year.

Query works as expected but do not know how to convert to SSRS by implementing parameters for Fiscal Period and Fiscal Year.

Query:

WITH MEMBER [measures].[Period] AS 
SUM([Date].[Fiscal Period].[P9],[Measures].[Hours])

SELECT NON EMPTY [Department].[Department].[Department] * [Associate].[Current Shift].[Current Shift] on Rows,
{[Measures].[Hours], [Measures].[Period]} ON Columns
FROM Personnel
WHERE [Date].[Fiscal Year].[FY2015]

Yes. However these are utilizing the parameter in a where clause for filtering. I need to be able to access the parameter in the WITH MEMBER statement dynamically, so that I have one column for total year and another (from the with member) for just the specified period. — Bigstik55
I should have stated, what I'm basically trying to do is replace the "P9" with a Period parameter. I have the report parameterized for fiscal year, that's not the problem. — Bigstik55
Seems like it should be easy to move your Fiscal Period slicer to the WHERE clause, but try STRTOMEMBER(): mssqltips.com/sqlservertip/2367/… — Tab Alleman
Right but it's not though. I have two measures; Hours utilizes the year parameter and returns basically a ytd figure and the other measure is only a particular period within that year. If I move the period to the where clause it filters my entire report for a specific period, not just the one measure. Hopefully this makes sense. — Bigstik55

SouravA SouravA · Accepted Answer · 2015-10-19T11:03:47

Create two parameters for Fiscal year and Fiscal Period. Then use the below query:

SELECT NON EMPTY [Department].[Department].[Department] * [Associate].[Current Shift].[Current Shift] on Rows,
[Measures].[Hours] ON Columns
FROM Personnel
WHERE 

(
 StrToMember(@FiscalYear, CONSTRAINED), 
 StrToMember(@FiscalPeriod, CONSTRAINED)
)

Parameterize MDX in SSRS

1 Answers