How to Create SSRS Report with Dynamic Query and Parameters

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)