Handling SSRS Multi-Valued Parameters in DAX Queries

Reposted from Chris Webb's blog with the author's permission.

 

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.

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:

…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])

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.

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

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!

 

---

Chris has been working with Microsoft BI tools since he started using beta 3 of OLAP Services back in the late 90s. Since then he has worked with Analysis Services in a number of roles (including three years spent with Microsoft Consulting Services) and he is now an independent consultant specialising in complex MDX, Analysis Services cube design and Analysis Services query performance problems. His company website can be found at http://www.crossjoin.co.uk and his blog can be found at http://cwebbbi.wordpress.com .

---