How do I use a multi-value parameter in an iif expression in SSRS (SQL Server Reporting Services)?

Question

I am trying to write a hidden column expression in SSRS. I need to involve a multi-value parameter. If multi-value parameter has a certain value in it, I want a column to show - else have it be hidden.

Something like this:

=IIF(Parameters!RecsIncl.Value(0) = 3, false, true)

Hannover Fist Hannover Fist · Accepted Answer · 2021-01-11T21:52:44

Unfortunately, there's no built-in way to do this.

I usually concatenate the Parameter values with JOIN. I use PIPES as the separator in case the text has commas. You also want to add PIPES to the start and end of the string so when you search for 3, it doesn't find 13.

"|" + JOIN(Parameters!RecsIncl.Value, "|") + "|"

|1|2|3|5|8|13|

and then search for the value as a string using the INSTR function. INSTR returns 0 if not found or the position if found.

INSTR( "|" + JOIN(Parameters!RecsIncl.Value, "|") + "|", "|3|") > 0

Your expression would be something like

=IIF(INSTR( "|" + JOIN(Parameters!RecsIncl.Value, "|") + "|", "|3|") > 0, false, true)

How do I use a multi-value parameter in an iif expression in SSRS (SQL Server Reporting Services)?

1 Answers