Report writers often don’t have the ability to modify the data in the database. We are limited to working with what we have. We spend a lot of time trying to fix the data enough so that our reports can run smoothly.
This is especially true for Nulls.
Nulls refer to the absence of data, as if someone has skipped a cell in the database. The problem is that Nulls cannot be equal to zero. Nulls can’t be equal to any value.
This is because Nulls can cause havoc in other reporting practices. This is most commonly seen when you combine several strings of text. This is basically gluing text pieces together into one long text. A single Null in any part will render the entire result null. Imagine concatenating first, middle, and last names into a field called “fullname”. Not everyone has a middle name, and if there are Nulls in the middle column, some people will get Nulls to the “fullname” field. It’s like saying, “just because you don’t have a mid-initial, you don’t get a fullname.”

In summary, dealing with Nulls in reports is a necessary evil that you will need to conquer.
Crystal Reports offers four ways to deal database nulls.
To convert all Nulls to a default value, change the Report Editor’s Null Handling default setting. This will convert all Nulls in all new reports. Use an expression to convert specific Nulls to a desired value. Filter the report to eliminate Nulls.
1. To convert all Nulls into a default value in an active report, change a report setting
This option is a simple solution to most reporting needs. It will convert any and every raw data Nulls in an active report into a default value based on the data type. For example, numeric data types can be transformed into zero, while string data types can be converted into an empty string.
If empty strings are unfamiliar to you, here’s a brief explanation. They “look blank” to the naked eye just like Nulls. The main difference is that empty strings are actually a value, but it just so happens that the value is zero. It sounds crazy, but bear with me! Normal strings of text are referenced in quotes like “This string of text”. However, empty strings are stored as “”, which is the quote without any inside (hence the term empty strings!). Technically, the quotes are hidden behind the scenes so we can point to them using expressions such as concatenations. This is why empty strings are considered the lesser evil.
This option uses the raw data Nulls from a database to prime your data for whatever expressions you create in the report. It does not have to deal with Nulls within each expression. This is why it is considered a simple solution.
This option is used when a report uses tables that contain the SPARSE datatype, concatenations are used in a report, or calculations using fields with Nulls are performed. It can also be used to clean up raw data that is displayed directly in a report.
To change the default report setting to covert Nulls:
File menuReport optionsFirst check box, “Convert NULL Values to default” This option only affects Nulls that are the result of your report writing and is therefore rarely required.
2. To convert all Nulls in new reports, change the default setting of the Formula Editor’s Null Handling.
Crystal Reports will convert all Nulls into a default value, such as a zero or empty string. This option is similar to the previous one. However, there are two key differences: 1) It does not apply to raw data Nulls (it only applies to Nulls when they’re referenced in an Expression), and 2) This option will affect all new Expressions starting now (don’t worry about your existing expressions).
This first difference is similar to saying “I don’t want to change the raw data, but I also want my calculations to not get broken by Null”.
This option is useful when: Nulls are scattered throughout your database, many tables have SPARSE table datatypes, you need to see Nulls in raw data but also need calculations, or you’re lazy, indecisive, and don’t feel like changing settings one at a time.
To change the Null Handling setting
Choose “Default Values for Nulls” from the File menuOptionsFormula Editor tabNull Therapy dropdown
3. To convert Nulls into desired values, use an expression
Let’s face the facts – there is rarely a single “one-size fits all” approach to reporting writing. Crystal Reports will often need to know what we want with our Nulls, so we have to tell them on a case by case basis. To help with Nulls we can use expressions. These expressions can be used in filters, formula fields, formatting, and many other ways.

There are many variations in steps and syntax, so it can be difficult to learn. Professional training can fill in the gaps. These valuable 2-day courses are offered by New Horizons Computer Learning Center: Crystal Reports Part 1, Part 2

This option is used when: You need control over the value that replaces null.

Enjoy this little gem below, which is a common expression to address any missing information. In a concatenation such as the one below, if any piece of data is null, the entire result would be null. This expression basically says, “if our database