cancel
Showing results for 
Search instead for 
Did you mean: 

Formula with a null value

0 Kudos

Hi in my sub report I have a formula that looks at a number value only three for a status quite simple really so I have created the below code but when the value is null that is no number it displays open as a value. There is no vlaue and it highlights Open as a result and have no idea why this is the case. The only way I have been able to hide it is to have change the font colour to white?

if {number.Complete} = 1 then "Complete" else
 if {number.Complete} = 0 then "Open" else
 if {number.Complete} = 2 then "Skipped" else
if isnull ({number.Complete}) then "66" /for example

Accepted Solutions (1)

Accepted Solutions (1)

DellSC
Active Contributor

You should set the null handling drop-down in the Formula Editor to "Exceptions for Nulls". Then you need to check for Null before checking for any other value, so your formula would change to this:

if IsNull({number.Complete}) then "66" else
  if {number.Complete} = 1 then "Complete" else
  if {number.Complete} = 0 then "Open" else
  if {number.Complete} = 2 then "Skipped"

-Dell

Thank you for your help! I never knew the is null had to be before " need to check for Null before checking for any other value, so your formula would change to this"

DellSC
Active Contributor
0 Kudos

You have to check for null first because if you compare any value with null, the result is null - NOT true or false. By checking for "Is Null" first, you avoid the null result.

-Dell

Answers (1)

Answers (1)

ido_millet
Active Contributor

Go to File, Report Options. The first checkbox is 'Convert Database NULL values to Default'. There is also a similar option as a drop-down in the formula editor toolbar.

If you want to avoid converting numeric Null to zero, make sure those options are turned off.

Then, use IsNull() to check for Null values.

0 Kudos

Thank you for your help!