How to check if a value is selected in a multi-value parameter

Question

In SSRS 2008, I use Multi-value parameters to, well, select multiple values for a particular parameter.

In my report I have conditional formatting: a particular label has to be blue if the item is selected; otherwise it will stay black. My intuition was trying it the SQL way - but I was wrong :-)

Switch(
  Fields!groupType.Value = "firstValue", "#00ffffff",
  Fields!groepType.Value = "secondValue", "Tomato",
  Fields!groepType.Value = "thirdValue", "DimGray",
  Fields!groepType.Value IN Parameters!p_myMultipleValueParameter.Values, "Blue"
)

What is the right way to handle this?

MonkeyPushButton MonkeyPushButton · Accepted Answer · 2013-08-12T14:40:22

I think the cleanest way is probably the following

Array.IndexOf(Parameters!p_myMultipleValueParameter.Value, Fields!groepType.Value) > -1

Running a join each time may be inefficient because of the overhead of allocating extra strings, especially if the function will be run over a large list, or once per row of a grid, say.

How to check if a value is selected in a multi-value parameter

1 Answers