SSRS - Ignore NULL when doing Boolean IFF expression

Question

Hi guys I have a simple IFF expression

=IIF(Fields!MYCOLUMN.Value = true, "Yes", "No")

which is working fine, except other rows that have a blank or NULL in it's place are returning "No". Is there a way to adapt my expression so that it ignores Null values? I have tried incorporating ISNOTHING but having trouble getting the syntax right.

=IIF(ISNOTHING(Fields!MYCOLUMN.Value), "", IIF(Fields!MYCOLUMN.Value, "Yes", "No")) just to illustrate how to do it using ISNOTHING. Also note there is not need to test for = true as that is implicitly what IIF does. — Alan Schofield

Andrew Andrew · Accepted Answer · 2021-03-03T15:43:29

IFF, IIF, or IF? If this is in the SQL query/stored proc and not the report itself, the answer below just changes to a SQL CASE statement.

Use a switch statement to define what you want displayed in the third case.

=Switch(
Fields!MYCOLUMN.Value = true,"Yes",
Fields!MYCOLUMN.Value = false,"No",
true,"")

SSRS - Ignore NULL when doing Boolean IFF expression

1 Answers