Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Videos          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

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


By:   |   Read Comments (1)   |   Related Tips: > Reporting Services Filtering

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


Last Update:






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.

View all my tips


 









Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



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

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


Learn more about SQL Server tools