SSRS simple nested IIF condition?

Question

Please have a look at the below expression

="Active : " & IIF(Parameters!lsSSelect_Active_Type_s_.Value = "FXNULL","ALL",
 iif(Parameters!lsSSelect_Active_Type_s_.Value=1,"Active",   
 iif(Parameters!lsSSelect_Active_Type_s_.Value=2,"Inactive","N/A")))

Now user have 3 options, 1, 2 or FXNULL. 1 and 2 can display Active and Inactive correctly but when user select "FXNULL", it's throwing error.

The Value expression for the textrun contains an error: Input string was not in a correct format

Now in order to do a testing to make sure the comparison between FXNULL working fine, I tried the below expression and working fine

="Active : " & IIF(Parameters!lsSSelect_Active_Type_s_.Value = "FXNULL","ALL","N/A")

What's wrong with the first expression?

Answer 1

Add quotes on your numeric checks and it will run without errors

="Active : " & IIF(Parameters!lsSSelect_Active_Type_s_.Value = "FXNULL","ALL",
 iif(Parameters!lsSSelect_Active_Type_s_.Value="1","Active",   
 iif(Parameters!lsSSelect_Active_Type_s_.Value="2","Inactive","N/A")))

The reason this happens is because Iif evaluates all expressions and you have both numeric and string comparisons.

Also I would suggest the use of Switch instead of nested Iif to make your code more readable

="Active : " & 
Switch(Parameters!lsSSelect_Active_Type_s_.Value = "FXNULL","ALL",
Parameters!lsSSelect_Active_Type_s_.Value="1","Active",   
Parameters!lsSSelect_Active_Type_s_.Value="2","Inactive",
True,"N/A"
)