For some reason whenever I get to working on a report I am really irritated at how I “forget” that SSRS doesn’t handle multi-select parameters at all. I am irritated at myself for pushing this horrid fact from my brain, my reluctance to remember or maybe that the painful truth is a coping mechanism or I am just in denial–maybe it’s been fixed.. Then I am reminded how Microsoft failed us by not making this an actual feature. I feel the writing has been on the wall for a while now SSRS is a fading technology. I do however know that there will be SSRS servers running for years to come.
When creating a report and setting up your parameters, you are left to believe that everything is taken care of. In the properties of a parameter you have the option to select Allow Multiple Values. (Image below)
The sad truth is this really just gives users the opportunity to click and select on more than one item. And if you forget to fix this with code you will find yourself not getting the results you are looking for and off in the weeds trouble shooting your query. I am sure there is always more than one way to skin a cat, especially when it comes to a Microsoft product. But my weapon of choice is creating a function and using for everything.
There is 0 reason in this world to spend hours on end recreating the wheel, I found code online and I just reuse it. (Disclaimer, it’s been so long I am not sure where I got the code or I would give proper credit.) This code will create a function and and you can use it for all your reports that have multi-value parameters.
CREATE FUNCTION [dbo].[SplitParameter]
–Define parameters
(@pString VARCHAR(8000), @pDelimiter CHAR(1))
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), –10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), –10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), –10E+4 or 10,000 rows max
cteTally(N) AS (–==== This provides the “base” CTE and limits the number of rows right up front
— for both a performance gain and prevention of accidental “overruns”
SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
cteStart(N1) AS (–==== This returns N+1 (starting position of each “element” just once for each delimiter)
SELECT 1 UNION ALL
SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
),
cteLen(N1,L1) AS(–==== Return start and length (for use in substring)
SELECT s.N1,
ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
FROM cteStart s
)
–===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
Item = SUBSTRING(@pString, l.N1, l.L1)
FROM cteLen l
Now to complete this task you need to update your code, which is hopefully a stored procedure. First step is to set the parameter to NULL if all items are passed in and then call the function to separate the list and dump it into a temp table. To make things simple I usually use a naming convention of naming the parameter, temp tables and anything related to it after the column being searched.
Now there WHERE clause should look like below. NULL means all were selected and we are not filtering or there is a list.
AND ((@ReportParameter1 IS NULL) OR (I.Item in (SELECT ITEM FROM #ReportParameter1)))
This seems like extra work that you shouldn’t have to do, but with this info being easy to find online it makes this task simple to complete. So if you are new to report writing or you are like me and forget, hopefully this help you.
Leave a Reply