How to create MDX parameters for SQL Server Reporting Services (SSRS)?

Question

In SQL Server Reporting Service, when I connect to my cube to create a dataset, in Query Designer I create my query with a filter. It creates the following MDX for me:

  SELECT NON EMPTY { KPIValue("KPI1"), KPIGoal("KPI1"), KPIStatus("KPI1") } 
ON COLUMNS, NON EMPTY { ([Create Date].[Month Num].[Month Num].ALLMEMBERS * [Create Date].[Hierarchy].[Month].ALLMEMBERS ) } 
DIMENSION PROPERTIES MEMBER_CAPTION, 
MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT 
( STRTOSET(@CreateDateYear, CONSTRAINED) ) 
ON COLUMNS FROM [ERP]) 
WHERE ( IIF( STRTOSET(@CreateDateYear, CONSTRAINED).Count = 1, 
STRTOSET(@CreateDateYear, CONSTRAINED), 
[Create Date].[Year].currentmember ) ) 
CELL PROPERTIES VALUE, BACK_COLOR, 
FORE_COLOR, FORMATTED_VALUE, 
FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

When I add this dataset, Reporting Services creates a parameter named CreateDateYear. When I pass a value like "2014" to this parameter I get nothing and I have to pass a value like [Create Date].[Year].&[2014].

How can I change my report to change this parameter for passing the value like "2014" instead of ugly and not user-friendly string [Create Date].[Year].&[2014]?

Answer 1

I did not change the MDX. In the Dataset Properties of my report I Changed the Parameter Value to:

="[Create Date].[Year].&[" + Parameters!Year.Value + "]"

And the problem solved