You have a requirement where users want to filter report data in SQL Server Reporting Services by entering a list of comma delimited values instead of choosing from a drop down list. In this tip I will show how this can be completed with Reporting Services.
In this tip we will show how to pass multiple values from a user input report parameter to a query parameter in SQL Server Reporting Services. I will use the AdventureWorksDW2008R2 sample database for SQL Server and SQL Server 2012 Reporting Services to demonstrate the solution.
Problem
You have a requirement where users want to filter report data in SQL Server Reporting Services by entering a list of comma delimited values instead of choosing from a dropdown list. In this tip we show how this can be completed with Reporting Services.
Solution
In this tip we will show how to pass multiple values from a user input report parameter to a query parameter in SQL Server Reporting Services. I will use the AdventureWorksDW2008R2 sample database for SQL Server and SQL Server 2012 Reporting Services to demonstrate the solution.
This tip assumes that you have previous real world experience building a simple SSRS Report and Stored Procedure in SQL Server.
Steps to Implement Multi Valued User Input Report Parameter in SSRS
Step 1: Add Data Source
I have already created an embedded data source connection to the AdventureworksDW2008R2 database. You can refer to the below image.
Step 2: Add Dataset using a Stored Procedure
Generally when a user wants to filter report data for one or more parameter values, we create a dataset using the "IN" operator in the WHERE clause. This becomes a little complex when you try to pass comma separated values from a user input report parameter into a query parameter. This is because SSRS treats all comma separated values as a single string value and it passes all comma separated values as a single string value into the query parameter.
For example, suppose I enter 1,2 in the user input report parameter and view the report. SSRS passes '1,2' into the query parameter to be used in the WHERE clause. If the column data type is set as a string then the report will execute successfully, but it will not return data. This is because the user is expecting data for two different values '1'and '2', but in the query SSRS is passing '1,2' as a single string value and '1,2' will not match the data in the table. In the WHERE clause, if the column data type is integer then the report will fail, because SSRS will be passing string data into an integer data type column.
I created a Stored Procedure as shown below. The Stored Procedure has one input parameter, where we will be passing the user input report parameter value into the Stored Procedure. Each comma separated value will then be extracted from the stored procedure input parameter and will be stored in a temp table that will be used to narrow down the results.
Let's walk through an example to understand this. Suppose I have entered 1,2,3 in the report parameter. The stored procedure will extract each comma separated value and insert it into temp table, therefore the temp table will have three records one for each extracted value. The temp tables first record will have a value of 1, the second record will have value of 2 and third record will have value of 3. Then at the end all the records in the temp table will be passed into the query which will return the requested data for our report.
Here is the stored procedure. I am using a table called TEMP to hold the extracted values, but you could also use a temporary table (#temp or some other name). Another approach is to use XML to parse the data and join to your table. Take a look at this tip for some ideas on how to do that.
/*CREATING A STORED PROCEDURE, WHICH HAS ONE INPUT PARAMETER*/
CREATE PROCEDURE MULTI_VALUED_USER_INPUT_PARAMETER(@PRODUCTKEY VARCHAR(MAX))
AS
BEGIN
/*CHECKING IF TABLE EXISTS IN THE DATABASE*/
IF OBJECT_ID('TEMP', 'U') IS NOT NULL
/*IF TABLE EXISTS THEN DROPPING AND RECREATING TABLE*/
DROP TABLE TEMP
CREATE TABLE TEMP (PRODUCTKEY VARCHAR(MAX))
/*INSERTING EACH COMMA SEPERATED VALUE INTO TEMP TABLE*/
WHILE CHARINDEX(',',@PRODUCTKEY)<>0
BEGIN
INSERT INTO TEMP VALUES((SELECT LEFT(@PRODUCTKEY, CHARINDEX(',',@PRODUCTKEY)-1)))
SET @PRODUCTKEY=(SELECT RIGHT(@PRODUCTKEY,LEN(@PRODUCTKEY)-CHARINDEX(',',@PRODUCTKEY)))
END
/*QUERY RETURNS PRODUCTKEY AND ENGLISHPRODUCTNAME BASED ON VALUE PROVIDED IN REPORT PARAMETER IN SSRS*/
SELECT Productkey, EnglishProductName FROM DIMPRODUCT WHERE PRODUCTKEY IN(SELECT PRODUCTKEY FROM TEMP)
/*DROPPING THE TEMP TABLE*/
DROP TABLE TEMP
END
I am creating a new dataset using a MULTI_VALUED_USER_INPUT_PARAMETER stored procedure; it returns two data fields Productkey and EnglishProductName. This dataset has one @PRODUCTKEY Query Parameter which accepts multiple values. You can refer to the below image.
Once you click OK, it will open the Define Query Parameters window. Check the "Pass Null" checkbox and click OK.
As you can see from the below image the dataset has been created with one report parameter.
Step 3: Modify Dataset Parameter Value Expression
Right click on the Dataset and click on Dataset Properties, It will open a Dataset Properties window and then click on the Parameters tab. You can refer to the below image.
Click on the Parameter value expression button, it will open an expression window. Modify the expression as shown below.
Step 4: Add Tablix
For data viewing purpose I am adding a Tablix into my report. This Tablix will show Productkey and EnglishProductName. You can refer to the below image.
Step 5: Preview Report
We have made all the necessary changes, now let’s preview the report. I have run the report for two different parameter values. You can refer to the below images.
As you can see from the above two images the report is working fine for a multi-value user input report parameter.
