reporting-servicesssrs-2012fetchxml

Switch expression in SSRS + CRM : Argument 'VarExpr' is not a valid value"

I'm using SSRS with Microsoft Dynamics 365 Fetch where my FetchXML contains the attribute having Option Set datatype - where as option set values are like below:

   Value =====> Label
   1     =====> "State 1"
   2     =====> "State 2"
   3     =====> "State 3"

What I want to do here is get the label for Value attribute.

When I try to add expression to convert this all of them throws the same error:

Switch expression

=Switch(
  Fields!State.Value = 1, "State 1",
  Fields!State.Value = 2, "State 2",
  Fields!State.Value = 3, "State 3",
  Space(1)
)

IIf Expression

=IIf(Fields!State.Value = 1, "State 1",
   IIf(Fields!State.Value = 2, "State 2", 
     IIf(Fields!State.Value = 3, "State 3", 
       " "
      )
    )   
  )

I have referred some answers where it says that this can be the data type issue - and to try that I have tried converting Fields!State.Value to Int & String too but not helpful.

Can you pls share that what am I missing here?

Solution

  • Your SWITCH statement seems to be missing the first argument of the last pair of values (expression and value). It doesn't work like an ELSE, you need to supply an expression to evaluate. SSRS is trying to evaluate the Space(1) as a Boolean expression.

    =Switch(
  Fields!State.Value = 1, "State 1",
  Fields!State.Value = 2, "State 2",
  Fields!State.Value = 3, "State 3",
  True, Space(1)
)

    Not sure why the IIF isn't working though.