switch statement in ssrs

Question

I have an SSRS report in which I am trying to hide columns based on the select value from a multi-select dropdown. I tried using multiple IIF statements as well as SWITCH CASE but it always throws an error.

Following is my IIF and CASE statements for 2nd and 3rd columns.

=IIF(Parameters!columns.Count >= 1, IIF(Parameters!columns.Value(0) = "3", true, false),
 IIF(Parameters!columns.Count >= 2, IIF(Parameters!columns.Value(1) = "3", true, false), 
 IIF(Parameters!columns.Count >= 3, IIF(Parameters!columns.Value(2) = "3", true, false), 
 false)))

 =IIF(Parameters!columns.Count >= 1, IIF(Parameters!columns.Value(0) = "2", true, false),
 IIF(Parameters!columns.Count >= 2, IIF(Parameters!columns.Value(1) = "2", true, false), 
 false))


=SWITCH(Parameters!columns.Count = 1, IIF(Parameters!columns.Value(0) = "2", false, true),
Parameters!columns.Count = 2, IIF(Parameters!columns.Value(1) = "2", false, true))

=SWITCH(Parameters!columns.Count = 1, IIF(Parameters!columns.Value(0) = "3", false, true),
Parameters!columns.Count = 2, IIF(Parameters!columns.Value(1) = "3", false, true),
Parameters!columns.Count = 3, IIF(Parameters!columns.Value(2) = "3", false, true)) 

Please help me what I am doing wrong in here

Answer 1

Based on the comments, it appears you are getting an Array Out Of Bounds error which will occur when you attempt to reference a position in an array that does not exist. In this case, your multi-value parameter is considered an array and you are referencing that in the syntax .Value(0). The problem occurs because SSRS will evaluate the entire IIF or SWITCH statement at execution regardless of which value is ultimately returned. This means that when your report has less than 3 values selected, these expression will always be trying to call an array position that does not exist, resulting in the error you are seeing.

So, in order to fix this and check the values as you intend, there is actually a fairly simple workaround that should get you the correct results. The following expression uses JOIN to put all of the parameter's values into a single result with a comma delimiter. You can then use the InStr function which will return a numeric value indicating the position in a string where the search value is first found. In this case, we are searching for the value 3. If 3 is not found, the InStr function will return a value of 0 and any other value will indicate that 3 is present in the joined parameter list.

=IIF(InStr(Join(Parameters!columns.Value, ","), "3") > 0, true, false)