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

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)

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

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

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

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