Crystal Reports – Display running total as 0 when null value

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.

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. Give the formula field a name, e.g. percent_fixed
  3. Use the following formula, replacing {Sheet1_.percent} with your own field
  4. if ISNULL( {Sheet1_.percent} ) then 0 else {Sheet1_.percent}
  5. Save and close the formula editor
  6. Add the new formula field to your report
  7. The NULL value will be presented as a 0

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. Tick ‘Convert Database NULL Values to Default’ and click ‘OK to save the changes.

Related Articles

Leave a Reply

Your email address will not be published.