SQL Server Reporting Services (SSRS) – How to Check If a Parameter Contains a Certain Value

Here is how you can determine in parameter one’s expression if a certain value was selected in parameter two’s multiple select parameter, and depending on the condition, set the value of parameter one.

Let’s say one of the values in parameter two has the value of  ” ” and it was NOT selected. Both expressions below will work and set the value of parameter one to “N”.

I have been using the first expression for a long time until after a year or so I came across a scenario where it wasn’t working anymore. I expected parameter one’s value to be set to “N”, but kept getting set as “Y”.

One of the values selected in parameter two was two words separated with a blank space, like this:  “RACE CAR”. In this instance, the first expression was setting the value of parameter one to “Y” instead of “N”. The problem was that I thought the first expression was actually looking for a value of blank when it was really looking for blank ANYWHERE in the value. Duh, on my part!

I then came across expression #2 that works for what I intended expression #1 to do.

So, in the end, if you are looking for a value of just blank, then use expression #2.

If you are looking for a blank contained in the value, such as “RACE CAR”, then use expression #1.

Expression #1:

=iif(instr(Join(Parameters!someparameter.Value,",")," ") > 0,"Y","N")

Expression #2:

=Array.IndexOf(Parameters!someparameter.Value, " ") > -1
Please follow and like us: