SSRS Multi-value Parameter (all), (blank), Value

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

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

USE [master];

IF (DB_ID('PL') IS NOT NULL)

BEGIN

ALTER DATABASE PL SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

DROP DATABASE PL;

END

CREATE DATABASE PL;

USE PL;

Create test table

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

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';

SELECT

'>' + FirstName + '<' AS FirstName

, '>' + LastName + '<' AS LastName

FROM TestTable;

Create ‘Split’ function to split the multi-value strings to array (table)

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 FUNCTION udf_SplitString

(

@String VARCHAR(MAX)

, @Delimiter VARCHAR(255)

)

RETURNS

@Result TABLE

(

ID INT IDENTITY PRIMARY KEY

, [Value] VARCHAR(1000)

)

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

RETURN;

END

Create SSRS datasets for the parameters

-- @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];

-- @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];

-- @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];

-- @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))

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))
		);

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

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

USE [master];

IF (DB_ID('PL') IS NOT NULL)

BEGIN

ALTER DATABASE PL SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

DROP DATABASE PL;

END

Keep it simple :-)

Peter Lalovsky

Keep it simple

Keep it simple

SSRS: Multi-value Parameter – (all), (blank), Value