Passing Multi-value Parameters to a MDX Query in SSRS

Let’s say our report needed ProductCategory as a parameter.  The ProductCategoryKey in the relational database for Bikes is 1, and our cube member for Bikes is [Product].[Category].&[1].  So we can transform our parameter value to pass into our mdx query like so:

“[Product].[Category].&[”+Cstr(Parameters!ProductCategory.Value) + “]”

Simple right?

BUT, I needed to be able to pass multiple values, which meant I had to take some extra steps before I could parse my string.  If ProductCategory is a multi-value parameter, then Parameters!ProductCategory.Value is an object – an array of values.  So we need to create a single string out of that array, format that string, and then break it up again back into an array since that is what the MDX query is expecting.  Whew!

Here’s the magic:

Split(“[Product].[Category].&["+Replace(Join(Parameters!ProductCategory.Value,"],”)+”]”,”,”,”,[Product].[Category].&["),",")

Cool eh? but, what’s happening here?

Join() is a handy little function that takes our array and joins the values into a string.  We can delimit the values with a string, in this case “],”, like so:

Join(Parameters!ProductCategory.Value, “],”)

Bikes, Components, and Clothing for our parameter values, our string after using the join function will be“1],2],3″.  We can then add an ending bracket to the end via simple string concatenation so now we have “1],2],3]”.

Applying the Replace() function, we replace “,” with “,[Product].[Category].&[“ and now we have “1],[Product].[Category].&[2],[Product].[Category].&[3]“.  Finish with one final string concatenation in the front and we have “[Product].[Category].&1],[Product].[Category].&[2],[Product].[Category].&[3]“

Unfortunately we can’t just pass this into our MDX query.  It’s expecting an array object, not a string!  But luckily, the Split() function is the counterpart to Join() and breaks up our string into that array object we want via the commas as a delimiter.

9 thoughts on “Passing Multi-value Parameters to a MDX Query in SSRS

  1. Tan says:

    Hi Alex

    I am trying to pass 2 values ‘ABC’ and ‘CDE’ as parameter values through SSRS into my MDX query as you suggested
    Split(“*[Queue].[Client Name].[Client Name].&[” +Replace(Join(Parameters!ClientName.Value, “],”)+”]”,”,”,”,[Queue].[Client Name].[Client Name].&[“),”,”)

    But this is not working it throws me an error in SSRS when I run the report the original expression is “*[Queue].[Client Name].[Client Name].&[” +Parameters!ClientName.Value+ “]”

    Any suggestions?

  2. Tan says:

    FYI original expression works fine when I declare a single default parameter ‘ABC’ but since I need to add another default parameter it gives me an error

  3. Elizabeth says:

    I’m using SQL 2008 R2 and i’m trying to filtering SSRS with multivalue parameter. I have tried to write this query into MainDataSet query designer :
    FROM (Select STRTOSET(Split(“[To Branch].[Branch].&[“+Replace(Join(Parameters!Branch.Label,”], “)+”]”,”,”,”,[To Branch].[Branch].&[“),”,”))) ON COLUMNS

    and I got this error :

    “Errors in the metadata manager. The configuration property updates were not persisted……. The Syntax for ‘Join’ is incorrect.

    Any suggestion ?

    Thanks

    • Alex Gonzalez says:

      Hi Elizabeth,
      Assuming that your MDX code is a string, I would suggest this change:

      “FROM (Select STRTOSET({[To Branch].[Branch].&[“+Replace(Join(Parameters!Branch.Label,”],“)+”]”,”,”,”,[To Branch].[Branch].&[“) + “})) ON COLUMNS”

      Basically, we don’t need to split the string to form an array because the strtoset functions expects a member set that can be generated with the replace and join functions.

      Good luck!

      • Elizabeth says:

        Hi Alex, It’s still not working.
        My MDX Code is not a string, but it is MDX Code from query designer ( Dataset Properties, Query, Query Designer )

        It seem “Replace” is not function in MDX query but it’s work on expression. (Please correct me if I’m wrong).

        I’m also have tried to make a parameter on MainDataSet properties :

        @ToBranch = “{[To Branch].[Branch].&[“+Replace(Join(Parameters!Branch.Label,”],”)+”]”,”,”,”,[To Branch].[Branch].&[“)+”}”

        and modified the Query in DataSet properties with :

        WITH MEMBER ……………………..
        ………………..
        ………………..

        FROM (SELECT (STRTOSET(@ToBranch, CONSTRAINED)) ON COLUMNS

        and I get this error message :

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

        Could you help me?

        Thanks

Leave a Reply