CODEWITHSUNDEEP
sql-serverreporting-services
Testtest11
Testtest11

Reputation: 367

Pass many values for one label as parameter

How I can pass many values for one label as parameter in SSRS2016?

My dataset looks like (I can modify VALUE column as needed):

LABEL VALUE
XXX   10, 12
YYY   11
AAA   90, 80, 70

so each row contains more than one value (with the same number of digits). I use it to filter dataset on report level. I don't want to show each value in separate row, because LABELs will be duplicated.

Here is one of my trial:

enter image description here

Upvotes: 0

Views: 1196

Answers (1)

alejandro zuleta
alejandro zuleta

Reputation: 14108

You can set your parameter to use the dataset you posted in the OP. Just be sure you remove each white space between the comma and the number.

LABEL VALUE
XXX   10,12
YYY   11
AAA   90,80,70

Then in the filter you just need to use SPLIT function in the tablix filter:

enter image description here

For Value use this expression:

=Split(Parameters!ParamX.Value,",")

Where ParamX is your Text parameter and [Code] is the column you want to filter Fields!Code.Value.

Also if your Fields!Code.Value is an Integer field you must convert it to a string in order to this works, so you can use the below expression in the Expression textbox:

=Cstr(Fields!Code.Value)

UPDATE Note you can filter a column with ONLY one value since Split() function takes 10,12 and lets the report evaluates the filter first using 10 then 12 and so on.

So this approach will filter a dataset like this:

Code    Row
 10      1
 11      2
 80      3
 70      4
103      5

So if you select in your parameter the label AAA it will filter the rows 3 and 4.

UPDATE 2 Support for multi valued parameters.

If your parameter is set to Allow multiple values you have to use a mix of SPLIT and JOIN functions:

Replace the Value expression by the below:

=split(join(Parameters!ParamX.Value,","),",")

Upvotes: 3

Related Questions