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!
This is great stuff. Thanks for sharing Chris!
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
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”?
Thanks for sharing! worth following your blogs! 🙂
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
Is this a way to set “all” member selected by default? Ty
I don’t know what you mean, sorry – can you explain what you want to do in more detail?
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).
Hi Omar,
It sounds like you need the ROLLUP function:
http://cwebbbi.wordpress.com/2011/07/15/dax-queries-part-2/
Chris
Thank’s a lot Chris, the RollUp function helped solve my issue.
I have to say your blog is simply amazing
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.
Hmm, I’m not sure. I guess the problem here is that you can’t return a table from IF()?
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
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.
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.
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?
No, I don’t. Sorry! It should be fairly easy to do though if you know SSRS well.
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
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!
The @ denotes a parameter – both DAX and MDX support parameters. They’re much easier to work with than using string concatenation.
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
It would have to be as a delimited list again. Creating the delimited list would be difficult though. This post does it in the opposite direction and might help: http://www.sqljason.com/2013/06/split-delimited-row-into-multiple-rows.html
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
This was posted a while back, and I have found this extremely helpful. However, I am having the following issue:
When I select 2 variables, the result set comes back empty.
When I select 1 or 3 or more variables, the result set returns correct data.
I have two parameters setup against this report using the PathContains pattern. They both have this issue. They work with any selection less than or greater than 2. Is this a common issue that anyone else has experienced/ resolved?
FILTER (
Calendar,
Calendar[YEAR] = value(@YearParam)
&& Calendar[MONTH] = value(@MonthParam)
&& PATHCONTAINS (
SUBSTITUTE (
SUBSTITUTE ( SUBSTITUTE ( @WeekParam, “,”, “|” ), “{“, “” ),
“}”,
“”
),
Calendar[WEEK]
)
),
FILTER (
DivCode,
PATHCONTAINS (
SUBSTITUTE (
SUBSTITUTE ( SUBSTITUTE (@DivParam, “,”, “|” ), “{“, “” ),
“}”,
“”
),
DivCode[DIV_CODE]
)
Thanks
-Brian
I resolved the issue by following Ivan’s recommendation:
I removed all substitute functions from the DAX, and wrote the Paramater to be an expression with Replace().
Now the query works as expected. I am not sure why there was an issue with the DAX Substitute() version.
Hi,
Can you please help me to add the “ALL” parameter in the SSRS report.
evaluate
(
calculatetable
(
summarize
(
‘R_Clndr’,
‘R_Clndr'[Year_WW]
),
pathcontains(@fltr_Month,’R_Clndr'[Month])
)
)
order by ‘R_Clndr'[Year_WW]
The result is below:
R_Clndr[Year_WW]
201606
201607
201608
201609
201610
I need the result below.
R_Clndr[Year_WW]
ALL
201606
201607
201608
201609
201610
Can you please help me asap.
regards,
Reddeppa
I am unable to run ssrs report multivalued parameter, it is throwing an error
I have SSRS report with DAX query multivalued Parameter, CalendarYear, if I select Multiple Years then run the report, it is throwing an error, your help will be much appreciated
my query in SSRS report
EVALUATE
CALCULATETABLE(
SUMMARIZE(FactInternetSales
,’DimProductCategory'[EnglishProductCategoryName]
,’DimProduct'[EnglishProductName]
,’DimProductSubcategory'[EnglishProductSubcategoryName]
,DimDate[CalendarYear]
,”Total Sales Amount”,SUM(FactInternetSales[SalesAmount])
,”Order Quantity”, SUM(‘FactInternetSales'[OrderQuantity])
),PATHCONTAINS(@Year,DimDate[CalendarYear])
)
I am able to resolve the issue with multivalued parameter in SSRS, we have split the values into multiple rows using SUBSTITUTE and PATHCONTAINS function.
EVALUATE
CALCULATETABLE(
SUMMARIZE(FactInternetSales
,’DimProductCategory'[EnglishProductCategoryName]
,’DimProduct'[EnglishProductName]
,’DimProductSubcategory'[EnglishProductSubcategoryName]
,DimDate[CalendarYear]
,”Total Sales Amount”,SUM(FactInternetSales[SalesAmount])
,”Order Quantity”, SUM(‘FactInternetSales'[OrderQuantity])
),PATHCONTAINS(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
@Year
, “{ “, “”)
, “}”, “”)
, “,”, “|”)
, DimDate[CalendarYear])
)
Thank you
Vishwanath
I get error while implementing this.Could you please guide as to where does this code exactly go?