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. 
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. 

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