Share via

SSRS - Easy way to split multi parameter values in SQL

  • Article

In SSRS ,  in multi parameter selection all the selected values are concatenated as comma separated . We need to split the comma separated values to use it in the IN clause of the report query or cascading parameter query.

For example, let us consider DimProduct from Adventureworks and ProductAlternateKey as the parameter. Now we need to fetch the data from DimProduct for the selected ProductAlternateKeys. When a multi selection is made , the sample data would be as below

"AR-5381,BA-8327,BE-2349,BE-2908"

The data needs to be retrieved by using this values in the WHERE clause IN condition

SELECT * FROM DimProduct WHERE ProductAlternateKey IN ('AR-5381,BA-8327,BE-2349,BE-2908')

To achieve this easily , we can use the below Technic of converting the parameter value itself into a SQL statement and insert the split values into a table variable. In turn which can be used in the IN clause

USE AdventureWorksDW2012
Go
DECLARE  @ProductParam VARCHAR(5000)
SELECT  @ProductParam = 'AR-5381,BA-8327,BE-2349,BE-2908'

DECLARE @ProductParamTable TABLE
(
ParamValues VARCHAR(100)
)

DECLARE @SQL VARCHAR(3000)
SELECT  @SQL = 'SELECT ''' + REPLACE  (@ProductParam,',',''' UNION SELECT ''') + ''''

INSERT INTO @ProductParamTable
(ParamValues)
EXEC (@SQL)

SELECT * FROM DimProduct WHERE ProductAlternateKey IN (SELECT ParamValues FROM @ProductParamTable)

In the above sample code , by using the REPLACE statements the comma separated parameter values is converted into a SQL UNION statement as below and stored in a variable. By using EXEC command the SQL statement is executed and inserted into a table variable which will be used in WHERE clause IN Condition. Also this can be used as JOIN itself instead of IN condition as the data is in a table.

SELECT 'AR-5381'  UNION SELECT  'BA-8327' UNION  SELECT 'BE-2349'  UNION SELECT  'BE-2908'

Hope this tip is helpful.