SQLTeam.com | Weblogs | Forums

SSRS multiple values of a parameter


#1

Hello,

Anyone knows what is the SSRS expression to take multiple values of a parameter separated by comma? I tried many different expressions but it did not work.

=split(replace(Parameters!param1.Value," ",""),",")
=split(Parameters!param1.Value,",")
=Parameters!param1.value + ","
=Split(Join(Parameters!param1.Value,”,”),”,”)

Below is my sql query and when I executed it, sql returned multiple values of my @param1 parameter but my SSRS does not. So, it works in SQL but not when I use the same query in SSRS.

DECLARE @param1 varchar(max)
SET @param1 = 'A,B,C,D'

SELECT tbl.column1
,tbl.column2
,tbl.column3
,tbl.column4
,tbl.column5
,tbl.column6
,tbl.column7
,tbl.column8
FROM TableTbl tbl
WHERE @param1 IS NULL OR Exists ( SELECT 1 FROM dbo.splitparameter(@param1,',') b
WHERE b.values = tbl.column1
OR tbl.column2
OR tbl.column3)))


#2

SSRS handles that for you. You just specify "tbl.column = param" and since SSRS "knows" there could be multiple values, it generates the appropriate IN clause or OR conditions.


#3

Hi Scott,

Correct, I thought the samething but when I tested in SSRS, placing these values = 'A,B,C,D', SSRS returned only the first value (A) only.


#4

WHERE (@param1 IS NULL OR tbl.column1 = @param1)


#5

Thank you Scott.