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)

Requirement: Produce an SSRS report the is searchable by the values of any fields in the report as shown below:

This type of report can be created using parameters input into a dynamic query in a stored procedure. We’ll use employee records from AdventureWorks database to demonstrate the report setup so it can be searchable by a search key and a search string as its value.

1. Create Stored Procedure.

   CREATE 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 
     = (@SearchString)
   

   

2. Test the stored procedure. Just to be sure the sproc works correctly, let’s do a test by executing it to search based on last name with a value “Brown.”

   EXEC dbo.SearchEmployees 'Last','Brown'
   

   And it should retrieve three records.
   

3. Create a new report in Visual Studio (the assumption is that you already have a Report Server Project created and setup with Data Sources). On the left pane, right-click on Datasets and select Add Dataset…. Click on Stored Procedure on the Query type and select the procedure name created earlier from the drop-down list and click OK to close it.

   

4. Create report parameters. Right-click on Parameters on the Report Data window and click on Add Parameter… and the first parameter as below:

   Name: SearchBy
   Prompt: Search by:

   

5. Go to Available Values and add all values you want end users to see and able to search from the report. These entries should match with the @SearchBy parameter in the stored procedure (SearchEmployees).

   

6. Still on the same window, click on Default Values and check Specify values and pick any value from the drop-down list so we don’t get an error message that the parameter is blank.

   

   Click OK to close.

7. Add a second parameter for the search string:

   Name: SearchString
   Prompt: Search for:

   Now, you should see two report parameters, @SearchBy and @SearchString. These two parameters still need to be linked to the dataset.

8. Click on Datasets (dsEmployeeSearch) Parameters and set the names and values to the following:

   

9. Setup the report as shown:

   

That’s it. Once you’re done, click on the Preview tab and test it.

Caveat

While it’s nice to be able to search by any fields you want, it’s not too helpful because you can only search one record at a time. We will correct and enhance this report to accept multivalue parameters on the next SSRS tutorial, How to Create SSRS Report with Dynamic Query and Multi-Value Parameters.

Further Reading

Business Intelligence with SQL Server Reporting Services
Microsoft SQL Server 2012 Reporting Services (Developer Reference)