CODEWITHSUNDEEP
reporting-servicesmdxolap-cubereportparameter
Bob
Bob

Reputation: 23000

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

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]?

Upvotes: 1

Views: 5825

Answers (2)

Bob
Bob

Reputation: 23000

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

Upvotes: 2

FrankPl
FrankPl

Reputation: 13315

Use string concatenation to add the "prefix" '[Create Date].[Year].&[' and the "postfix" ']' in MDX:

STRTOSET('{ [Create Date].[Year].&[' + @CreateDateYear + '] }', CONSTRAINED)

if @CreateDateYear contains something like "2014".

Notes: I also added braces around the member unique name, as that is the proper syntax for a set in MDX. And if you would want to allow multiple selection, then probably it would be better to do the string concatenation in Reporting Services instead of MDX.

Upvotes: 0

Related Questions