code-u-like

C#, .Net, Sql Server, Salesforce, Dynamics CRM etc,
Charity and Not-for-Profit sector

Saturday, March 24, 2012

SSRS multi-value parameters with less fail

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.

Labels:

15 Comments:

At April 8, 2012 at 12:10 AM , Anonymous Adrian Huang said...

I've been using the method introduced in this post for a while and the only inconvenience is that when we want to select only some of the values from the list we need remember to clear the "--All--" first. Users sometimes forget and the report will still include all records.

My new approach is to add a "--Empty--" selection (value as -1) instead of the "--All--". The "--Empty--" selection is to cover the records with NULL value in this field. The parameter is set to select all items including the "--Empty--" by default. This complies with SSRS's original "(Select All)" convention.

I understand it is crazy to have a where clause condition "value in (...)" putting hundreds of items in the "in", mainly performance issue. To avoid this I still adopt a bypassing technique similar to that is introduced in this post, like below:
if (-1 in select value from ufnSplit(@TerritoryMulti) and not exists(select value from TheListTable where value not in (select value from ufnSplit(@TerritoryMulti))))
@TerritoryMulti = null


The rest are the same.

 
At September 4, 2012 at 5:47 AM , Blogger Lolo said...

Thank u!
After lots of googling, I have found the light!

 
At January 15, 2013 at 8:25 AM , Anonymous Anonymous said...

where am i going wrong? When i preview the report all the parameteres default to --All-- but then when i deploy it they are all blank? My datasets look like this;

use Activity
select distinct t.PatiTypeDes,
1 as SortFix

from exptosql201213 t
union select

'--All--' as PatiTypeDes,

0 as SortFix


order by sortfix, PatiTypeDes

great walk through btw

 
At January 15, 2013 at 8:29 AM , Blogger Ian said...

re: where am i going wrong?

Hmm, not sure but it might be the default settings on the Report Manager - sometimes they end up different to the defaults in the report defn.

In Report Manager (the SSRS server) go to the report properties (show details, click edit, etc) then go to the Parameters tab and see what defaults are specified for each parameter.

 
At January 16, 2013 at 2:46 AM , Anonymous Anonymous said...

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

I think the problem lies in this piece of code, i only have the one description field (no ID) which i was selecting distinct for the parameter, how do I apply this?

John W

 
At April 4, 2013 at 1:34 PM , Blogger Nilesh said...

can i use same function for each multi-value parameter ??

 
At April 4, 2013 at 3:01 PM , Blogger Ian said...

@Nilesh - yes dbo.ufnSplit() can be re-used for any parameter

 
At May 17, 2013 at 7:09 AM , Anonymous Anonymous said...

Thank you very much for this quick and efficient solution. I found in my scenario, SSRS wasn't appending the last ',' and I had to use the function like so : (select Item from dbo.ufnSplit(@ClientName +',',',')). Anyways, works well now :-)

 
At April 3, 2014 at 8:08 AM , Blogger Regin Varghese said...

"1. The stored procedure for the report should declare any multi-value parameters as type nvarchar(max)."

Thank you, Thank you, Thank you! I have been looking at a number of sites all over and implemented all the steps but was still getting only 1 row returned. You spelled it out for me, my parameter was a char(2)! Thanks again...

Now, I feel like dancing...pa..pa..ra..pa..pa!

 
At April 30, 2015 at 2:45 PM , Blogger Harvey French said...

If the user selects "---ALL---" and also ticks a few other values they can get unexpected results.

This can be prevented by getting the parameter expression to determine whether the user has done this and getting the same expression to prevent all records being returned and just showing the ones the user ticked.

(Although this is not ideal, it is preferable to "ALL" rows being returned, as it is quicker and more likely to be what the user meant to do!)

Simply change the expression used in the main dataset parameter from:

=Join(Parameters!prmClientID.Value,",")


To be

=iif( (Parameters!prmClientID.Value(0)="-1"

-- -1 was used a the value associated with "---All---"
-- (0) refers to the very first parameter ticked, and relies on the "---ALL---"
-- option being at the top of the list
And
Parameters!prmClientID.Count>1
)
,Join( filter( Parameters!prmClientID.Value, "-1", False, comparemethod.Binary) , ",")
-- The fitler removes the -1 value, in effect stopping "All rows" being returned

,Join(Parameters!prmClientID.Value,",")
)


PS. Thanks for the very useful post.

Harvey

 
At April 30, 2015 at 3:14 PM , Blogger Harvey French said...

I'm interested in your thoughts on this technique.

How to get around the performance issues discussed above due to the user selecting the SSRS "(Select All)" choice:

You can change the SQL of the reports main dataset to detect whether all values were ticked.




-- This SELECT deals with the user NOT ticking all choices
SELECT *
FROM dbo.rpt0002_CallDisposition ( @prmClientID )
WHERE
(
@prmClientID_COUNTOFVALUES
<
(SELECT COUNT(*) FROM Actor WHERE (ExternalLevel = 50)
-- This SELECT is identical to the SELECT used to provide the user with a list of values in the @prmClientID
-- parameter. (Except it does not include the SELECT to add the "---All---" option.
)
)

UNION ALL

-- This SELECT is for when the user has ticked all choices
SELECT *
FROM dbo.rpt0002_CallDisposition ( NULL )
-- When the inline function received NULL it has logic that stops fn_SplitStringIntoATable
-- from being called again.
WHERE
(
@prmClientID_COUNTOFVALUES - 1
>=
(SELECT COUNT(*) FROM Actor WHERE (ExternalLevel = 50)
)
)


Note that @prmClientID_COUNTOFVALUES is a parameter set up only on the DATASET which counts the number of parameters that the user has ticked (not including "---ALL---"), by using the expression:
= Parameters!prmClientID.Count - Iif( Parameters!prmClientID.Value(0) = "-1", 1, 0)


PS. The above logic could be put into the stored procedure or the inline function that is called, but then the inline function is not so easily re-used...

 
At April 30, 2015 at 3:16 PM , Blogger Harvey French said...

PS error in code of my last post

remove the -1 in the code below

WHERE
(
@prmClientID_COUNTOFVALUES - 1
>=

 
At April 30, 2015 at 3:22 PM , Blogger Harvey French said...

Sorry Mr Moderator.

One last thought, the technique using the parameter @prmClientID_COUNTOFVALUES to determine whether all values were ticked actually means that there is no need for the "---ALL---" values option.

Good eh!

 
At April 30, 2015 at 4:12 PM , Blogger Harvey French said...

So just to clarify, here's the code that I now use that stops the function dbo.ufnSplit being called if the user selects the standard "(Select All)". There is no need to give the user the "----ALL----" option.

(I've only just come up with this, it seems to work, let me know what you think)


I hope you find this useful.

Harvey

============================================================================================================

Main Dataset has parameters

@prmClientID with expression =Join(Parameters!prmClientID.Value, ",")

@prmClientID_COUNTOFVALUES with expression =Parameters!prmClientID.Count




============================================================================================================

Here is the main dataset sql (Using the inline function listed below)
===============================

-- This SELECT deals with the user NOT ticking all choices

SELECT *
FROM dbo.rpt0002_CallDisposition ( @prmClientID )
WHERE
(
@prmClientID_COUNTOFVALUES
<
(SELECT COUNT(*) FROM dbo.rpt_ShdDs_SelectAllActorsWhoAreClients() )
)
UNION ALL

-- This SELECT is for when the user has ticked all choices
SELECT *
FROM dbo.rpt0002_CallDisposition ( NULL ) ---
WHERE
(
@prmClientID_COUNTOFVALUES
>=
(SELECT COUNT(*) FROM dbo.rpt_ShdDs_SelectAllActorsWhoAreClients() )
)



============================================================================================================
Here is the inline function
============================

CREATE FUNCTION [dbo].rpt_ShdDs_SelectAllActorsWhoAreClients
(
)
RETURNS @returntable TABLE
(
ID int,
Name nvarchar(200)
)
AS
BEGIN


INSERT @returntable
SELECT ID, Name
FROM Actor
WHERE ExternalLevel = 50;

RETURN;
END;




============================================================================================================
Here is the key lines from the main dat getting function
============================



CREATE FUNCTION [dbo].[rpt0002_CallDisposition]
(
@prmClientID as varchar(max) = null
)
--
--
--
RETURNS TABLE AS
RETURN
(
SELECT t2.*
...
FROM
...
WHERE ((( Call.CallType = N'out'
AND
Call.Flow = 'out'
)))

AND ( (@prmClientID IS NULL)
OR
Call.[ActorID-OfClient] IN ( SELECT Param -- Number
FROM [dbo].[fn_SplitStringIntoATable]
( @prmClientID -- @RepParam
, ',' -- @Delim
)
)
)

...
)

 
At May 13, 2015 at 9:33 PM , Anonymous Anonymous said...

Very helpfull, saved a lot of time

 

Post a Comment

Subscribe to Post Comments [Atom]

<< Home