SSRS - Multi Value Parameter Expression

Question

I have the following Expression which should appear on the title of the report. However, When I run this for a single entity which should fall under the else statement, It displays an error instead of the Label.

The Parameter is a multi value Parameter. And If I select 3 Parameters, it works fine, hence the else statement is working. But when I only select 1 value it returns an error. What am I doing wrong.

=IIF(
(Parameters!Parameter1.Count = 2 AND ((Parameters!Parameter1.Value(0)= 4589 AND Parameters!Parameter1.Value(1) = 4324) OR (Parameters!Parameter1.Value(1)= 4589 AND Parameters!Parameter1.Value(0)) = 4324)), "HLITE LLC",
JOIN(Parameters!Parameter1.Label," & ")
)

I also tried the following:

= SWITCH(
    Parameters!Parameter1.Count = 1 , lookup(Parameters!Parameter1.Value(0), Fields!Legal_Entity_ID.Value, Fields!Legal_Entity.Value, "DataSet2"),
    (Parameters!Parameter1.Count = 2 AND ((Parameters!Parameter1.Value(0)= 4589 AND Parameters!Parameter1.Value(1) = 4324) OR (Parameters!Parameter1.Value(1)= 4589 AND Parameters!Parameter1.Value(0)) = 4324)), "HHLITE"
)

This fails as well. But the look up expression by itself works AND COUNT IS 1

Hannover Fist Hannover Fist · Accepted Answer · 2016-09-01T18:06:19

I think the issue is that there is NOT a Value(1) when there's only 1 selection.

Maybe nesting IIFs would work:

=IIF(Parameters!Parameter1.Count = 2,
     IIF((Parameters!Parameter1.Value(0)= 4589 AND Parameters!Parameter1.Value(1) = 4324) 
            OR (Parameters!Parameter1.Value(1)= 4589 AND Parameters!Parameter1.Value(0)) = 4324), 
        "HLITE LLC",
        JOIN(Parameters!Parameter1.Label," & "), 
    JOIN(Parameters!Parameter1.Label," & "))

This way, it only checks for value(1) if there are 2.

If all else fails,

=IIF(JOIN(Parameters!Parameter1.Label," & ") = "4324 & 4589", "HLITE LLC", JOIN(Parameters!Parameter1.Label," & "))

SSRS - Multi Value Parameter Expression

1 Answers