Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Handling SSRS Multi-Valued Parameters in DAX Queries

with 24 comments

Last year I blogged about using DAX queries inside SSRS reports but one topic I didn’t touch on was how to handle parameters. Marco wrote a post on using parameters in DAX earlier this year but didn’t cover how to handle multi-valued parameters in SSRS, so I thought I’d write a post to fill that gap.

Let’s start by assuming you’ve built a simple SSAS 2012 Tabular model that contains the DimDate table from Adventure Works, that you want to build an SSRS report that shows data from that table and you want to parameterise that report by the values in the EnglishDayNameOfWeek column, ie by day.

If you were writing the query from scratch, probably the best way of doing it would be to use the CalculateTable() function like so:

evaluate
calculatetable(
DimDate
, DimDate[EnglishDayNameOfWeek] = "Monday"
)

This query returns the whole of the DimDate table filtered where EnglishDayNameOfWeek is Monday.

image

To filter by Monday or Tuesday, you’d need to do something like this:

evaluate
calculatetable(
DimDate
, DimDate[EnglishDayNameOfWeek] = "Monday"
|| DimDate[EnglishDayNameOfWeek] = "Tuesday"
)

Since there is no way of doing an IN in DAX, you might be able to guess what the big problem that needs to solved when handling multi-value parameters in DAX is: with this style of query, for each parameter value that is selected you need to add a new OR condition and the only way of doing that is to generate the query dynamically. While that’s perfectly possible in SSRS it’s something you may want to avoid because it makes report development awkward.

As an alternative to dynamic query generation you can make use of the DAX PathContains() function. Although it’s intended for use when flattening out parent/child hierarchies, it’s useful here because it allows you to see whether a particular string value appears in a pipe-delimited list. You can use it with the Filter() function to get all the Mondays and Tuesdays like so:

evaluate
filter(
DimDate
, pathcontains("Monday|Tuesday", DimDate[EnglishDayNameOfWeek])
)

There’s a problem with using it this way, unfortunately – it’s not very efficient. Looking in Profiler at the Vertipaq SE Query Begin/End events, you can see that Storage Engine has to call back to the Formula Engine to use the PathContains() function when filtering:

image

…and as Jeffrey Wang describes here, this can be very expensive indeed. However, its impact can be lessened if you have a small number of potential parameter values by doing this instead:

evaluate
calculatetable(
DimDate
, filter(
values(DimDate[EnglishDayNameOfWeek])
, pathcontains("Monday|Tuesday", DimDate[EnglishDayNameOfWeek])
)
)

Here, I’m only filtering the table of distinct values in the EnglishDayNameOfWeek column (which has only got 7 rows), and then taking that table and using it as a filter with the calculatetable() function on the main DimDate table. In this case there’s still a callback but only on a filter on a relatively small table, and the filter on the larger table, DimDate, is performed in the most efficient way possible.

How, then, can you take this query pattern and use in in your SSRS report? The first thing to do is to create a dataset that returns all the days of the week, using the following query:

evaluate values(DimDate[EnglishDayNameOfWeek])

image

This, then, can be used to provide the available values for an SSRS multi-valued parameter. This should all be straightforward for anyone who has used SSRS before.

image

image

You can then add a new query parameter to our main dataset, the one that queries the DimDate table:

image 

The next problem to solve is the fact that when using the Analysis Services connection type in SSRS, when you have a multi-valued parameter SSRS assumes you’re using MDX to query a cube. Therefore, if you have selected Monday and Tuesday in your parameter, SSRS will pass a string containing an MDX set expression like the one below through to the query parameter:

{ Monday,Tuesday }

You therefore need to do some string manipulation to turn this into a pipe-delimited string of the type that PathContains() can use. Here’s the complete DAX query that shows how this can be done:

evaluate
calculatetable(
DimDate
, filter(
values(DimDate[EnglishDayNameOfWeek])
, pathcontains(
substitute(
substitute(
substitute(
@DayNames
, "{ ", "")
, " }", "")
, ",", "|")
, DimDate[EnglishDayNameOfWeek])
)
)

It’s pretty simple: I’m using Substitute() to remove the opening and closing braces, and to turn the commas into pipes.  With that done, you now have an SSRS report using a DAX query that can handle multi-valued parameters!

image

Written by Chris Webb

June 1, 2012 at 6:23 pm

Posted in DAX, Tabular

24 Responses

Subscribe to comments with RSS.

  1. This is great stuff. Thanks for sharing Chris!

    javierguillen

    June 1, 2012 at 6:50 pm

  2. Hey Chris,

    have you thought of using FIND instead of PATHCONTAINS?
    doing so makes it unnecessary to use SUBSTITUTE to replace characters that are added by SSRS automatically and you can use the parameter directly:

    EVALUATE
    CALCULATETABLE(
    DimDate,
    FILTER(
    VALUES(DimDate[EnglishDayNameOfWeek]),
    FIND(DimDate[EnglishDayNameOfWeek], “{Monday,Tuesday}”, , -1)>0
    )
    )

    for some reason you have to specify the 3rd and 4th parameter of the FIND function otherwise you get an error eventhough those parameters are optional

    I have not checked the performance yet but I’d assume that both approaches perform similar

    Gerhard

    gbrueckl

    June 4, 2012 at 9:25 am

    • Yes, that’s an option but it’s less robust I suppose: what happens when you have one parameter value that appears as part of another parameter value, for example “web” and “Webb”?

      Chris Webb

      June 4, 2012 at 8:56 pm

  3. Thanks for sharing! worth following your blogs! :)

    Nikhil

    June 4, 2012 at 3:46 pm

    • Hi Chris,

      I was able to get the same result without using the FILTER function inside Calculatetable. I used this query:

      evaluate
      calculatetable(
      DimDate
      , pathcontains(
      substitute(
      substitute(
      substitute(
      @DayNames
      , “{ “, “”)
      , ” }”, “”)
      , “,”, “|”)
      , DimDate[EnglishDayNameOfWeek])
      )

      Both approaches have same result and response time.

      Ayad

      Ayad Shammout

      December 31, 2012 at 5:36 pm

  4. [...] a justement abordé le sujet dans son excellent blog ainsi que Chris Webb dans son non moins fameux blog mais je me permet de le reprendre car le principe est très « éclairant » sur les principes [...]

  5. Is this a way to set “all” member selected by default? Ty

    dimitri

    June 25, 2013 at 11:13 am

    • I don’t know what you mean, sorry – can you explain what you want to do in more detail?

      Chris Webb

      June 25, 2013 at 12:52 pm

  6. Hello Chris, been reading about your posts for SSRS and DAX and I can’t thank you enough. They have helped me sooo much in one of our projects.

    I have a question, referring to your example mentioned above, is there a way to include a member “All” that would basically get all the values for the Weekdays (Like we have in MDX).

    Omar Sultan

    June 28, 2013 at 8:09 am

  7. Hello Chris, I implemented the multi value parameter as you mentioned in the blog and my reports are now running as expected. However I’m trying to implement an additional feature and wanted to see if you came across something like that before.

    If a user selects the (All) member in the filter I am trying to find a way of not having to pass all the possible values in a string to improve the performance, I was thinking of using this formula as one of the arguments in my calculatetable function but i guess it’s not allowed by DAX

    Any idea if this can be done?

    EVALUATE(

    SUMMARIZE(
    CALCULATETABLE(‘Loyalty Transactions’,
    CALCULATETABLE(‘Date’,DATESBETWEEN(‘Date’[ActualDate],”01/01/2013″,”01/31/2013″)),
    IF(@Param = “All”,FILTER(VALUES(‘Loyalty Entry Type’[Type]),’Loyalty Entry Type’),,pathcontains(substitute( substitute( substitute( @LoyaltyTransactionTypeType , “{ “, “”) , ” }”, “”) , “,”, “|”),’Loyalty Entry Type’[Type])))),
    [TransactionNo],
    ‘Date’[ActualDate])

    The idea here is that if the user selected ALL then I’m using a filter that basically selects all values instead of having to go through the pathcontains function.

    Omar Sultan

    July 2, 2013 at 8:17 am

    • Hmm, I’m not sure. I guess the problem here is that you can’t return a table from IF()?

      Chris Webb

      July 2, 2013 at 9:21 am

      • Yeah that seems to be the issue, IF doesn’t accept returning tables or DAX doesn’t accept the IF as a possible type for Tables.

        I was trying to improve the performance of my query as one of my filters contains around 150 Items and if the user decides to check All performance goes down a little

        Omar Sultan

        July 2, 2013 at 9:30 am

      • Just wanted to share a little finding, I found that removing the Substitute function in DAX and handling it in SSRS with Replace improved performance for reports that have large number of filters/parameters.

        Omar Sultan

        July 2, 2013 at 1:21 pm

      • Thanks Omar, that makes sense. In fact, you could probably get much better performance by dynamically generating the DAX query using SSRS expressions, instead of writing the kind of complex DAX I use here.

        Chris Webb

        July 2, 2013 at 1:30 pm

      • I’m actually looking into generating the Query dynamically to only apply the filter when All is not selected.

        Do you have any posts regarding dynamic DAX queries in SSRS?

        Omar Sultan

        July 2, 2013 at 1:34 pm

      • No, I don’t. Sorry! It should be fairly easy to do though if you know SSRS well.

        Chris Webb

        July 2, 2013 at 1:44 pm

      • Thanks anyways :)

        Yeah it shouldn’t be a problem I was just wondering if you had any special posts about it, your posts usually cover stuff I haven’t seen before

        Omar Sultan

        July 2, 2013 at 2:04 pm

  8. Hi Chris,

    Thanks for all your blog posts! they’re incredibly helpful.
    I have one question about regarding the parameter usage in this though, why is it that you’re using the @ symbol for the parameters instead of using string concatenation?

    Would that work the same way using string concatenation?

    Thanks!

    Ed

    July 18, 2013 at 6:36 pm

    • The @ denotes a parameter – both DAX and MDX support parameters. They’re much easier to work with than using string concatenation.

      Chris Webb

      July 18, 2013 at 6:38 pm

  9. Hi Chris,

    I would like to do something quite similar: pass a multivalue parameter in a SSAS Tabular Action (type Rowset). How can you pass a list of products for example to filter your rowset ?
    I manage to pass a single value filter but not a multi value….

    Regards

    RNDDRND

    October 21, 2013 at 9:21 pm

  10. Hi Guys,

    One option is to use the JOIN function to generate a delimited list from a multi-valued parameter.

    Expression: =Join(Parameters!Days.Value, “|”)

    DAX query:
    EVALUEATE
    CALCULATETABLE(
    ‘Order Date’,
    FILTER(
    VALUES(‘Order Date’[EnglishDayNameOfWeek])
    , PathContains(@Days, ‘Order Date’[EnglishDayNameOfWeek]
    )
    )
    )

    Cheers,
    Ivan

    Ivan

    June 27, 2014 at 8:17 am


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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,298 other followers

%d bloggers like this: