Add more values to expression using IIF

Question

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.

For future reference, you can add other criteria with an AND or OR in the IIF (though SteveOs use of SWITCH might be better in this instance) `IIF(Fields!Type.Value = "TKT1" OR Fields!Type.Value = "TKT2", ...` Unfortunately, it's not as easy as the IN. — Hannover Fist, Mar 21 '19 at 17:10

score 2 · Accepted Answer · answered Mar 21 '19 at 13:40

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")

I was starting to write the expression using the OR operator, but Let me give the SWITCH function a try. Thank you! — erasmo carlos, Mar 21 '19 at 13:51
@erasmocarlos Let me know if either of my solutions fixes it for you or if you need more help with it. Always happy to help. — Steve-o169, Mar 21 '19 at 14:56
Thank you @Steve-o169, the first recommendation worked perfectly. — erasmo carlos, Mar 21 '19 at 16:25

Add more values to expression using IIF

1 Answers1