Search code examples
reporting-servicesiifmultiple-value

Add more values to expression using IIF

I am looking for help updating an expression used in a SSRS report.

The current expression is something like this:

=IIF(Fields!Type.Value = "TKT", "Ticket No.", 
     IIF(Fields!Type.Value = "CUN", "Customer Number", 
         IIF(Fields!Type.Value = "ANM", "Account Number", 
             IIF(Fields!Type.Value = "CID", "Client ID", ""))))

We have added a few more "Types"

So for Type "TKT" now we have: "TKT1", "TKT2". For Type "CUN", now we have "CUN1", "CUN2, and so on for the last 2.

I am not familiar with how when using an IIF function, multiple values can be specified (similar to an IN operator).

If anyone could share some light regarding how this is done, that would be awesome.

Thank you for your help in advance.

Solution

  • You would probably be better off converting this expression to a SWITCH statement before this thing gets out of hand. A SWITCH accepts as many conditional and result pairings as you need to add. The following expression allows you to add as many checks as you need and the final pairing true,"" simply sets any that don't match the switch statement to a blank value.

    =SWITCH(Fields!Type.Value = "TKT1", "Ticket No.",
        Fields!Type.Value = "TKT2", "Ticket No.",
        Fields!Type.Value = "CUN1", "Customer Number",
        Fields!Type.Value = "CUN2", "Customer Number",
        Fields!Type.Value = "ANM", "Account Number",
        Fields!Type.Value = "CID", "Client ID",
        [add additional pairings here],
        true, "")

    An additional solution would be to use the Contains keyword in SSRS. This would search the string to find a particular substring. You could simply modify each conditional to the following which would return true if the field contains that substring.

    Fields!Type.Value.Contains("TKT")