Working with multi-value parameters in SSRS is always fun. We need to pick a good delimiter, add additional values like “ALL” or “BLANKS” and many others. In the following few lines I explain a logics to add a multi-value parameter to SSRS with additional values (all) to not apply the parameter and (blank) to select the blanks and NULLs.
The parameter’s default is (all) to facilitate the subscriptions. When (all) is the only selected option it is treated as (all). When (all) is selected together with other values, (all) it is ignored. The logic is that the default is (all) and the user doesn’t uncheck it while selecting the desired values.
Pick replacement characters that doesn’t exist on the keyboard and most likely in the data.
Delimiter –> CHAR(164) (¤)
Replacement for (all) –> CHAR(160) ( )
Replacement for (blank) –> CHAR(172) (¬)
Create test DB
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
USE [master]; GO IF (DB_ID('PL') IS NOT NULL) BEGIN ALTER DATABASE PL SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE PL; END GO CREATE DATABASE PL; GO USE PL; GO |
Create test table
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT 'FN 1' As FirstName, NULL AS LastName INTO TestTable UNION SELECT 'FN 2', '' UNION SELECT ' ', 'LN 1' UNION SELECT 'FN 3', 'LN 2' UNION SELECT NULL, ' ' UNION SELECT 'FN 5', 'LN 3'; GO SELECT '>' + FirstName + '<' AS FirstName , '>' + LastName + '<' AS LastName FROM TestTable; GO |
Create ‘Split’ function to split the multi-value strings to array (table)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
CREATE FUNCTION udf_SplitString ( @String VARCHAR(MAX) , @Delimiter VARCHAR(255) ) RETURNS @Result TABLE ( ID INT IDENTITY PRIMARY KEY , [Value] VARCHAR(1000) ) AS BEGIN DECLARE @Extract VARCHAR(1000); WHILE LEN(@String) > 0 BEGIN IF PATINDEX('%' + @Delimiter + '%', @String) > 0 BEGIN SET @Extract = SUBSTRING(@String, 0, PATINDEX('%' + @Delimiter + '%', @String)); INSERT INTO @Result([Value]) SELECT LTRIM(RTRIM(@Extract)); SET @String = SUBSTRING(@String, LEN(@Extract + @Delimiter) + 1, LEN(@String)); END ELSE BEGIN SET @Extract = @String; SET @String = NULL; INSERT INTO @Result([Value]) SELECT LTRIM(RTRIM(@Extract)); END END RETURN; END GO |
Create SSRS datasets for the parameters
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
-- @Par_FirstName - (multi-value), (all) and (blank) added to the top SELECT 1 AS [Order] , CHAR(160) AS [Value] , '(all)' AS [Label] UNION SELECT 2 AS [Order] , CHAR(172) AS [Value] , '(blank)' AS [Label] FROM TestTable WHERE ISNULL(LTRIM(RTRIM(FirstName)), '') = '' UNION SELECT 3 AS [Order] , LTRIM(RTRIM(FirstName)) AS [Value] , LTRIM(RTRIM(FirstName)) AS [Label] FROM TestTable WHERE ISNULL(LTRIM(RTRIM(FirstName)), '') != '' ORDER BY [Order], [Label]; |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
-- @Par_LastName - (multi-value), (all) and (blank) added to the top SELECT 1 AS [Order] , CHAR(160) AS [Value] , '(all)' AS [Label] UNION SELECT 2 AS [Order] , CHAR(172) AS [Value] , '(blank)' AS [Label] FROM TestTable WHERE ISNULL(LTRIM(RTRIM(LastName)), '') = '' UNION SELECT 3 AS [Order] , LTRIM(RTRIM(LastName)) AS [Value] , LTRIM(RTRIM(LastName)) AS [Label] FROM TestTable WHERE ISNULL(LTRIM(RTRIM(LastName)), '') != '' ORDER BY [Order], [Label]; |
Create a dataset for the report and pass the parameters with Join(Parameters!Par_FirstName.Value, Chr(164))
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
SELECT * FROM TestTable AS P WHERE -- @Par_FirstName CASE WHEN @Par_FirstName = CHAR(160) THEN CHAR(160) WHEN LTRIM(RTRIM(ISNULL(FirstName, ''))) = '' THEN CHAR(172) ELSE LTRIM(RTRIM(FirstName)) END IN ( SELECT [Value] FROM PL.dbo.udf_SplitString(@Par_FirstName, CHAR(164)) ) -- @Par_LastName AND CASE WHEN @Par_LastName = CHAR(160) THEN CHAR(160) WHEN LTRIM(RTRIM(ISNULL(LastName, ''))) = '' THEN CHAR(172) ELSE LTRIM(RTRIM(LastName)) END IN ( SELECT [Value] FROM PL.dbo.udf_SplitString(@Par_LastName, CHAR(164)) ); |
Delete test DB
1 2 3 4 5 6 7 8 9 |
USE [master]; GO IF (DB_ID('PL') IS NOT NULL) BEGIN ALTER DATABASE PL SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE PL; END GO |
Keep it simple :-)