Crystal Reports – Display running total as 0 when null value

by

The following steps show how to display a NULL value (a blank value) as a 0.

This can be helpful when you need to use the value to perform calculations or need a consistent layout in the report.

In the example used the third row has a NULL value.

crystalreports-displaynullvalue01

Option 1: Use a formula field

This is the typical approach I take – I create a formula field and use a formula to say ‘if value is NULL then 0 else use value’.

  1. Right-click on ‘Formula Fields’ and choose ‘New’
  2. crystalreports-booleantick2
  3. Give the formula field a name, e.g. percent_fixed
  4. Use the following formula, replacing {Sheet1_.percent} with your own field
  5. if ISNULL( {Sheet1_.percent} ) then 0 else {Sheet1_.percent}
  6. Save and close the formula editor
  7. Add the new formula field to your report
  8. The NULL value will be presented as a 0
  9. crystalreports-displaynullvalue02

Option 2: Use report options to convert all NULL values

Whilst this option usually works fine, I have had issues using it with some more complicated reports. For this reason I usually avoid option.

To enable this:

  1. With your report option, click on the ‘File’ menu and then ‘Report Options’
  2. crystalreports-displaynullvalue03
  3. Tick ‘Convert Database NULL Values to Default’ and click ‘OK‘ to save the changes.
  4. crystalreports-displaynullvalue04