Continuing from the last SSRS tutorial on creating a SQL Server Reporting Services report with dynamic query and parameters, one caveat about the report is that it only works for one record search at a time.
While this may be fine for a customer who needs to search a particular record specific to the person, this is not generally useful for office use, where one person may need to search possibly hundreds or thousands of records that will be exported to an excel spreadsheet for further scrubbing and presentation.
We want to enable users to enter several search keywords and have the report rendered if any matched records.
In order to accomplish this, we need to modify the Parameters to allow multiple values. If we go to the left pane on the Report Designer and open the @SearchString Parameter Properties, under General tab, click on the Allow multiple values check box and click OK.
What this does is to change the Search for: text box to a drop-down list box to allow search based on multiple keywords. However, the report can still only search for one value. If we entered two values as in the example below, no records will be retrieved because the search keyword is actually a concatenated value of those two words separated by comma (i.e., ‘brown,baker’).
The concatenation is done using =Join(Parameters!SearchString.Value) built-in function. SSRS is smart enough to do this automatically when you selected Allow multiple values on the Parameter Properties. While the report is stored enough to concatenate the multiple values, the linked stored procedure still doesn’t know that it’s supposed to split the parameters into several individual values.
To do this, we need to create a user-defined function (UDF) that will split the concatenated parameters as a one column table.
Create a Split Function
CREATE FUNCTION dbo.ufnSplit (
@Input NVARCHAR(MAX),
@Delimiter CHAR(1) = ','
)
RETURNS @Values TABLE (Item NVARCHAR(100))
AS
BEGIN
DECLARE @Pos INT
DECLARE @Single NVARCHAR(100)
SELECT @Pos = 1
WHILE @Pos > 0
BEGIN
SELECT @Pos = CHARINDEX(@Delimiter, @Input)
IF @Pos > 0
SELECT @Single = LEFT(@Input, @Pos - 1)
ELSE
SELECT @Single = @Input
INSERT @Values(Item) VALUES (@Single)
SELECT @Input = RIGHT(@Input, LEN(@Input) - @Pos)
IF LEN(@Input) = 0 BREAK
END
RETURN
END
Implement Split Function
Next, we implement the split function in the dbo.SearchEmployee stored procedure created in previous posting.
Modify stored procedure dbo.SearchEmployee by using ALTER PROCEDURE and replace the last line of the SQL statement from
= (@SearchString)
To
IN (SELECT Item FROM dbo.ufnSplit(@SearchString, ','))
The full SQL statement should look like this:
ALTER PROCEDURE dbo.SearchEmployees
@SearchBy NVARCHAR(MAX),
@SearchString NVARCHAR(MAX)
AS
SELECT NationalIDNumber, FirstName, MiddleName, LastName,
JobTitle, LoginID
FROM HumanResources.Employee e
INNER JOIN Person.Person p
ON e.BusinessEntityID = p.BusinessEntityID
WHERE
CASE
WHEN @SearchBy = 'IDNo' THEN NationalIDNumber
WHEN @SearchBy = 'Title' THEN JobTitle
WHEN @SearchBy = 'First' THEN FirstName
WHEN @SearchBy = 'Middle' THEN MiddleName
WHEN @SearchBy = 'Last' THEN LastName
WHEN @SearchBy = 'Login' THEN LoginID
END
IN (SELECT Item FROM dbo.ufnSplit(@SearchString, ','))
Execute the SQL to save the stored procedure (or press F5) and you’re done.
Once the stored procedure is modified, it will know how to parse the multi-value parameters and pass back the results as shown in the screenshot below.
Please share this post if you find it helpful.
Futher Reading
Microsoft SQL Server 2012 Reporting Services (Developer Reference)
code-u-like: SSRS multi-value parameters with less fail
Passing multiple values for a single parameter in Reporting Services
User-Defined Functions
Using Parameters Collection References in Expressions (Reporting Services)
Single-Value and Multivalue Parameters (Report Builder and SSRS)
