reporting-servicesswitch-statementiif-function

Report Builder 3.0 SWITCH and nested IIF both throw the same error [BC32017]

I'm using report builder 3.0 trying to get a dynamic title based on a user chosen parameter.

Error

The Value expression for the textrun ‘Textbox29.Paragraphs[2].TextRuns[0]’ contains an error: [BC32017] Comma, ')', or a valid expression continuation expected.

I get this error when I use this SWITCH statement

=SWITCH(
Parameters!LineCalled.Count = 3, "All Lines",
Parameters!LineCalled.Count = 2, "Both Notts Lines",
Parameters!LineCalled.Count = 1 AND
Parameters!LineCalled.Value = "01156842190", "Order Line",
Parameters!LineCalled.Count = 1 AND
Parameters!LineCalled.Value = "01156842191", "Overflow Line",
Parameters!LineCalled.Count = 1 AND
Parameters!LineCalled.Value = "393607", "Belfast Line"

)

Or this IIF

=IIF(Parameters!LineCalled.Count = 3, "All Lines",
IIF(Parameters!LineCalled.Count = 2, "Both Notts Lines",
IIF(Parameters!LineCalled.Count = 1 AND
Parameters!LineCalled.Value = "01156842190", "Order Line",
IIF(Parameters!LineCalled.Count = 1 AND
Parameters!LineCalled.Value = "01156842191", "Overflow Line",
IIF(Parameters!LineCalled.Count = 1 AND
Parameters!LineCalled.Value = "393607", "Belfast Line","Other"
)))))

What am I missing?

Solution

  • The thing you are missing is that your parameter seems to be a multi-value parameter, so whenever you want to access the only selected value, you should use the expression Parameters!LineCalled.Value(0).

    For example:

    =SWITCH(
  Parameters!LineCalled.Count >= 3, "All Lines",
  Parameters!LineCalled.Count = 2, "Both Notts Lines",
  Parameters!LineCalled.Value(0) = "01156842190", "Order Line",
  Parameters!LineCalled.Value(0) = "01156842191", "Overflow Line",
  Parameters!LineCalled.Value(0) = "393607", "Belfast Line"
)