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

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

  1. Hi,
    I still get error message :

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

    any sugesstion ?

    Thanks

  2. 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 ?

    1. 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

  3. 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

    1. Adela, for multi-select parameters try converting the parameter array into string tuples in your sub-report.
      For e.g “{” & Join(Parameters!Dim.Value,”,”) & “}”

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

  5. 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

  6. 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

    1. 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s