In SSRS report, when a report parameter is role playing dimension then the parameter value passed to each dataset has to be customized depending on the context in which the dimension is being used. In an earlier post, I explained how this is done in PerformancePoint dashboard. In this post an equivalent solution for reporting services is provided.
The following AdventureWorks SSRS report screen capture has two MDX data sets – sales by country and delivery by country. Order date is the report parameter. The date slicer used in sales by country is order date and the one used in delivery by country is delivery date.
The dataset for date parameter is provided by the following MDX query that fetches all Fiscal Year of Order Date that has a sale. The dataset is sorted with latest date listed first.
--Report Parameter MDX dataset WITH MEMBER [Measures].[Date Display] AS [Date].[Fiscal].CurrentMember.Name MEMBER [Measures].[Date UniqueName] AS [Date].[Fiscal].CurrentMember.UniqueName SELECT {[Measures].[Date Display],[Measures].[Date UniqueName]} ON COLUMNS, ORDER( Filter([Date].[Fiscal].[Fiscal Year],[Measures].[Order Count]>0) ,[Date].[Fiscal].CurrentMember.Properties('key') ,DESC) ON ROWS FROM [Adventure Works]
The following MDX query returns the current fiscal year as default value for the order date dataset.
--Report Parameter MDX dataset - Default Value (OPTIONAL): WITH MEMBER [Measures].[Date UniqueName] AS [Date].[Fiscal].CurrentMember.UniqueName SELECT [Measures].[Date UniqueName] ON COLUMNS, HEAD( ORDER( FILTER([Date].[Fiscal].[Fiscal Year].Members ,[Measures].[Order Count]>0) ,[Date].[Fiscal].CurrentMember.Properties('key'),DESC ),1) ON ROWS FROM [Adventure Works]
The MDX query for Sales by Country dataset is:
--Internet Sales By Country SELECT [Measures].[Internet Sales Amount] ON COLUMNS, NON EMPTY ( CROSSJOIN(STRTOSET(@OrderDate,CONSTRAINED) ,[Date].[Fiscal Quarter of Year].Children ,[Date].[Month of Year].Children ,[Sales Territory].[Sales Territory Country].Children ) ) ON ROWS FROM [Adventure Works]
Here the dataset parameter @OrderDate expects an order date member in the form of [Date].[Fiscal].[Fiscal Year].&[YYYY] . The SSRS expression Parameters!OrderDate.Value passes this date member from the report date parameter.
The MDX query for Delivery by Country dataset is:
--Delivery By Country SELECT [Measures].[Order Count] ON COLUMNS, NON EMPTY (CROSSJOIN( STRTOSET(@DeliveryDate,CONSTRAINED) ,[Delivery Date].[Fiscal Quarter of Year].Children ,[Delivery Date].[Month of Year].Children ,[Sales Territory].[Sales Territory Country].Children ) ) ON ROWS FROM [Adventure Works]
Here the dataset parameter @DeliveryDate expects a delivery date member in the form of [Delivery Date].[Fiscal].[Fiscal Year].&[YYYY], however the report parameter can only pass an order date member in the form of [Date].[Fiscal].[Fiscal Year].&[YYYY] . Because date is a role playing dimension, order date and delivery date conform to the same structure and differ only in their names by which they are addressed. So effectively using a simple find and replace SSRS string expression would provide the delivery date member that this dataset requires.
So effectively @DeliveryDate is provided a delivery date member by this expression REPLACE(Parameters!OrderDate.Value,”[Date]”,”[Delivery Date]”) as shown in Screen Capture 2

Screen Capture 2 – SSRS Expression for Role Playing Dimension Report Parameter
UPDATE: What if the parameters allow multiple values ?
Mutli-select parameters are passed as arrays to SSRS datasets, so they must be first converted to string. You could use the SSRS Join function to convert array to string. In the above example, the parameter expression would be
"{" & Replace(Join(Parameters!OrderDate.Value,",") ,"[Date]","[Delivery Date]") & "}"
Notice that multi-value SSAS parameters are MDX tuples and would require the open and close braces. Thanks to Mauro Cavosi for bringing this up.
Hi,
I still get error message :
the restrictions imposed by the CONSTRAINED flag in the STRTOSET function were violated.
any sugesstion ?
Thanks
Hi,
I want to ask, I use this query :
“{” & Replace(Join(Parameters!Branch.Value,”,”),”[From Branch]”,”[To Branch]”) & “}”
for aarameter ToBranch
and in dataset Query I use this syntax below :
SELECT NON EMPTY {[Measures].[SellOut],
[Measures].[SellAmount]
} ON COLUMNS,
{ ([Time].[Month].[Month].ALLMEMBERS * [Time].[Calendar].[Quarter].ALLMEMBERS *[Sector].[Sector].[All]
)} DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM (SELECT ( STRTOSET(@ToBranch, CONSTRAINED)) ON COLUMNS
but when I run this syntax, I got this error message :
The restrictions imposed by the CONSTRAINED flag in the STRTOSET function were violated.
Any suggestion ?
Hi Benny,
I’m already set parameter expression like this :
“{” & Replace(Join(Parameters!Branch.Value,”,”),”[From Branch]”,”[To Branch]”) & “}”
but when I set MDX query like this:
SELECT NON EMPTY {[Measures].[SellOut Qty],
[Measures].[SellOut Amount]
} ON COLUMNS,
{ ([Time].[Month].[Month].ALLMEMBERS * [Time].[Calendar].[Quarter].ALLMEMBERS * STRTOSET(@BrandCode, CONSTRAINED)*[Sector].[Sector].[All]*STRTOSET(@ToBranch,CONSTRAINED)
)} DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
I got this error message :
“The restrictions imposed by the CONSTRAINED flag in the STRTOSET function were violated.”
Any sugesstion ?
Thanks
Elizabeth, if you haven’t already figured out, check whether the member you are deriving exists in the dimension. It probably doesn’t that’s why you are getting this error. More info here https://msdn.microsoft.com/en-AU/library/ms144782.aspx
Hello Benny, Excellent article! I’m having a bit of trouble getting my subreport to accept the value formatted as specified for multi-select parameters. The value is in the format of: {[Milestones].[WorkTypeShortName].&[value]}. I’m not sure what I have wrong … is there something more that needs to be done?
Adela
Adela, for multi-select parameters try converting the parameter array into string tuples in your sub-report.
For e.g “{” & Join(Parameters!Dim.Value,”,”) & “}”
Error: Overload resolution failed because no accessible ‘DatePart’ can be called without a narrowing conversion
Hi,
I want to add a date filter parameter .But I get an error when I use this expression..
Can you please help?
I am trying to get the last sundays date from the current date:
=”[Date].[Date].&[” & Format(CDate(DateAdd(“d”, – DatePart(“w”, Now(), 1)-6, Now()), “yyyyMMdd”)) + “]”
Mr. Austin thanks a lot for this great tip regarding multivalued ssrs parameters… join() function is so beautifully-simple approach. People search for an alternative to a SSRS & stored proc based dataset & multivalued parameter “;” issue… using UDF’s, xml’s and various other complications…
But this/your approach is the only proper way it should be done, you should accent this native ssrs approach in a special post!
Thanks,
Amadeo
Hello Benny, good hint thanks.
In my first implementation I receive an error message “conversion from type ‘object()’ to type ‘string’ is not valid”. I think it is due to the fact that I have a multi-value parameter and that the Replace function acts only on the very first element of the array. (by removing the flag multi valued parameter it works fine). Do you happen to have a smart solution also for this? Bye Mauro
Hi Mauro, thanks for taking time to send me that query. Mutli-select parameters are passed as arrays to SSRS datasets, so recommend converting the array to string before the replace. You could use the Join SSRS function to convert array to string. Something like this expression would work “{” & Replace(Join(Parameters!OrderDate.Value,”,”),”[Date]”,”[Delivery Date]”) & “}”. Notice that multi-value SSAS parameters are tuples and would require the open and close braces.