SSRS supports multi-value parameters, which is nice, but there are a few issues with them. This is how I deal with them.
Two of the problems with SSRS multi-value parameters are:
- You have to jump through a few hoops to get them to work with stored procedures
- The (Select All) option, as shown above
The reason the (Select All) option is a problem is that it is a really inelegant way of saying 'this parameter does not matter to me'. If you have a list with hundreds of values, passing all of them as a default option just seems wrong. Also, if your report shows the user which items they selected, printing the whole list when they choose (Select All) is excessive.
So in this post I'm going to show my particular way of:
- Jumping through the hoops to get Multi-Value params in a stored procedure
- Adding a single '--All--' value that the report interprets as meaning all the options.
Getting Multi-Value params to work with Stored Procedures
This is no great secret. But anyway, here it is again:
1. The stored procedure for the report should declare any multi-value parameters as type nvarchar(max).
2. In the Report Designer, your parameter should have the 'Allow multiple values' option ticked (obviously). Also, it helps to set the Data type to 'Text' even if its actually an integer or something, because we're going to have to treat it as text later on in the Stored Procedure.
3. In the DataSet that calls the stored procedure, go to the Parameters tab:
4. For each parameter that needs to be a multi-value one, press the Fx button and change the default:
\=Parameters!TerritoryMulti.Value
to
\=Join(Parameters!TerritoryMulti.Value, ",")
This will just string the multi-values together into a comma separated string.
5. In the database, you need a function that can split comma-separated values back out into a table format. There are lots of variations of this sort of function around, here's the one I use:
CREATE FUNCTION \[dbo\].\[ufnSplit\]
(@RepParam nvarchar(max), @Delim char(1)= ',')
RETURNS @Values TABLE (Item nvarchar(100))AS
\-- based on John Sansoms StackOverflow answer:
\-- http://stackoverflow.com/a/512300/22194
BEGIN
DECLARE @chrind INT
DECLARE @Piece nvarchar(100)
SELECT @chrind = 1
WHILE @chrind > 0
BEGIN
SELECT @chrind = CHARINDEX(@Delim,@RepParam)
IF @chrind > 0
SELECT @Piece = LEFT(@RepParam,@chrind - 1)
ELSE
SELECT @Piece = @RepParam
INSERT @Values(Item) VALUES(@Piece)
SELECT @RepParam = RIGHT(@RepParam,LEN(@RepParam) - @chrind)
IF LEN(@RepParam) = 0 BREAK
END
RETURN
END
6. The query inside the stored procedure that uses the multi-value parameter should use it like this:
WHERE
sometable.TerritoryID in (select Item from dbo.ufnSplit(@TerritoryMulti,','))
... that is, it uses ufnSplit to split the passed values out into a table, and then selects from that table and uses 'in' to check for membership.
Adding a single '--All--' value
As mentioned above, the (Select All) option on SSRS multi-picks leads down the path of madness. We can't turn (Select All) off (at least not in the default Report Manager) but we can add our own 'magic value' that the report will then interpret as 'any value is fine'.
7. Choose your magic value that the report should interpret as 'any value is fine'. Often '-1' is a good magic value to use.
8. The 'Available values' for your multi-value parameters are probably coming from a dataset, like this:
Change the query for the dataset so that it inserts the magic value at the top. Here's one way to do that:
select t.TerritoryID,
t.\[Name\] AS \[TerritoryDesc\],
1 as SortFix
from \[Sales\].\[SalesTerritory\] t
union
select -1 as TerritoryID,
'--All--' as TerritoryDesc,
0 as SortFix
order by SortFix, TerritoryDesc
... so here we use a union to add on the magic value, and the extra SortFix column is there to make sure the magic value appears at the top.
9. On the Default Values tab for the parameters, set your chosen 'magic value' as the single default value:
10. In the stored procedure, add a check before the main query that looks for the default value, and then resets the parameter to null if it finds it:
if (exists(select \* from dbo.ufnSplit(@TerritoryMulti,',') where Item = -1))
set @TerritoryMulti = null
Note that here, if the user selects the magic '--All--' value and some other values, it will be interpreted as if they just selected '--All--'.
11. Adapt the where clause of the query in the stored procedure to check for a null parameter:
WHERE
(@TerritoryMulti is null
or sometable.TerritoryID in (select Item from dbo.ufnSplit(@TerritoryMulti,',')))
12. And thats it.
The parameter should now show up with the '--All--' value like this:
There are still a few possible problems; users will need to be helped to understand the difference between (Select All) and '--All--', and why they should use the 'magic value'. And users who select other values and forget to uncheck the '--All--' option may get confusing results - one way out of that is to get the stored procedure to throw an error if it detects that '--All--' has been used in conjunction with other values.
edit: Another way to make selection of multi-value parameters easier is to stop using the default 'Report Manager' ui and use something else instead. See CrissCross, an open-source UI for running SSRS reports that works well with the techniques described above.