SSRS: Parameter Expression for MDX Datasets Having Role Playing Dimension

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.

Screen Capture 1 - SSRS Report with role playing dimension as parameter — Screen Capture 1 – SSRS Report with role playing dimension as parameter

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.

Benny Austin

RT @bennyaustin: Updated post wp.me/pxNuz-uR for multiple value parameters #SSRS #MDX
—
Aneal Roney (@anealroney) May 21, 2013

12 thoughts on “SSRS: Parameter Expression for MDX Datasets Having Role Playing Dimension”

Elizabeth says:

November 4, 2015 at 19:44

Hi,
I still get error message :

the restrictions imposed by the CONSTRAINED flag in the STRTOSET function were violated.

any sugesstion ?

Thanks

Reply
Elizabeth says:

November 4, 2015 at 19:10

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 ?

Reply
Pingback: Dimension Modeling: Creating Business Friendly Dashboards with CURRENT attributes in Date Dimension | Benny Austin
1. Elizabeth says:
  
  November 4, 2015 at 20:33
  
  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
  
  Reply
  1. Benny Austin says:
    
    November 5, 2015 at 09:44
    
    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
adelasmith2014 says:

March 11, 2015 at 08:46

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

Reply
1. Benny Austin says:
  
  March 12, 2015 at 09:09
  
  Adela, for multi-select parameters try converting the parameter array into string tuples in your sub-report.
  For e.g “{” & Join(Parameters!Dim.Value,”,”) & “}”
  
  Reply
Priyanka says:

November 13, 2014 at 08:21

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”)) + “]”

Reply
Amadeo says:

July 12, 2013 at 21:23

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

Reply
Pingback: Dimensional Modelling: Excluding Unused Dimension Attribute Members | Benny Austin
Mauro Cavosi says:

May 17, 2013 at 17:34

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

Reply
1. Benny Austin says:
  
  May 19, 2013 at 23:55
  
  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.
  
  Reply