How to use a multi valued comma delimited input parameter for an SSRS report

By:   |   Comments (5)   |   Related: > Reporting Services Development


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.

Adding Data Source

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.

Adding Report DataSet

Once you click OK, it will open the Define Query Parameters window. Check the "Pass Null" checkbox and click OK.

Define Query Parameter Value

As you can see from the below image the dataset has been created with one report parameter.

Report Data Pane After Creating Dataset

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.

Report DataSet Properties

Click on the Parameter value expression button, it will open an expression window. Modify the expression as shown below.

Dataset Parameter Value Expression

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.

Adding Tablix in report body

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.

Report Preview for first set of parameter

Report Preview for second set of parameter

As you can see from the above two images the report is working fine for a multi-value user input report parameter.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ghanesh Prasad Ghanesh Prasad leads a team in Microsoft Business Intelligence and has over 5 years of experience working with SQL Server.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Friday, September 9, 2022 - 6:34:37 AM - Simanta Bora Back To Top (90459)
Thank you, this was very helpful. The only change I made is a added an IF condition after the while section ended, as it was not returning the last comma separated value.

IF CHARINDEX(',',@FiscalWeek)=0
BEGIN
INSERT INTO #temp VALUES (@FiscalWeek,NULL)
END

Tuesday, September 10, 2019 - 5:28:52 PM - Jose Back To Top (82343)

The quickest way I have made this work, is just by using WHERE ColumnName IN (@Parameter) inside your Dataset SQL. Setting your @Parameter properties to allow multiple values, and pasting the values in after you run report. It will automatically add comma separations.


Tuesday, July 16, 2019 - 11:29:10 AM - Chris Hauck Back To Top (81768)

change

SELECT Productkey, EnglishProductName FROM DIMPRODUCT WHERE PRODUCTKEY IN(SELECT PRODUCTKEY FROM TEMP)

to

SELECT Productkey, EnglishProductName FROM DIMPRODUCT d INNER JOIN TEMP t on t.PRODUCTKEY = d.PRODUCTKEY

the inner join is a better optimized solution.


Friday, June 8, 2018 - 2:36:31 PM - JP Back To Top (76165)

Beautiful work.

How could this be accomplished with two parameters that allow multi value user input? without using stored procedure?

 


Tuesday, July 28, 2015 - 9:44:20 AM - Tom Back To Top (38297)

tried it in SSRS 2008 but gave me empty result. so what exactly is wrong   =Parameters!gahh.Value+"," it is obvious this will give an error =Parameters!gahh.Value+','

regards

Tom















get free sql tips
agree to terms