How to Cull the Null in Crystal Reports

As report writers, we often do not have the ability to change the data on the database directly; we merely have to work with the data we’ve got. For this reason, we spend much of our time trying to cosmetically “fix” the data just enough so our report can run properly.

This is especially true of Nulls.

Nulls are the absence of data – as if someone skipped that cell in the database. The tricky part is that Nulls are not equal to zero. In fact, Nulls are not equal to any value.

This is a problem because throughout other reporting practices, Nulls wreak havoc! The most common example of this havoc is if you concatenate several strings of text (essentially gluing pieces of text together into one long text), a single Null in any part of the concatenation will make the whole result Null. In this latter case, imagine concatenating first names, middle initials, and last names into one field called “full name”; not everyone has a middle initial, and if the database has Nulls in the middle initial column, then some people will get Nulls for the “full name” field. It’s like saying “just because you don’t have a middle initial, you don’t get a full name.” And that’s just not fair!

In short, dealing with Nulls is a necessary evil – and one you will have to vanquish in most of your reports.

In Crystal Reports, you have four ways to deal with Database Nulls:

1. Change a report setting to convert all Nulls to a default value in the active report
2. Change the Formula Editor’s Null Handling default setting to convert all Nulls in any new reports from now on
3. Use an expression to convert specific Nulls into a desired value
4. Filter the report to avoid Nulls completely

This post will show you how to do each and will explain when to use one method over another.

1. Change a report setting to convert all Nulls to a default value in the active report

A simple solution for most reporting needs, this option will convert any and all raw data Nulls in the active report into a default value depending on the data type; for example, numeric data types will turn into the number zero and string data types will turn into an empty string.

If you are not familiar with empty strings, here’s an explanation: To the naked eye, they “look blank” just like Nulls. However, the major difference is that empty strings actually are a value … it just so happens to be that the value is nothing. Bear with me, I know it sounds crazy! Normal strings of text are referenced between quotes, like “This is a string of text”. But empty strings are stored as “” – that is, the quotes without anything inside (hence the name empty strings!). Technically the quotes are there behind the scenes, so we can point to them from our expressions like concatenations. This is one reason why empty strings are viewed as the lesser evil.

Since this option deals with the raw data Nulls from the database, then it essentially primes your data for however many expressions you will build in that report – rather than having to handle Nulls inside each and every expression. This is ultimately why it is considered a simple solution.

Use this option when: a certain report is using tables containing the SPARSE datatype, a certain report has concatenations, or a certain report has many calculations using fields that have Nulls, or even to clean up the raw data displayed directly in the report.

To change the report setting to covert Nulls to default value:

1. File menu
2. Report Options
3. First check box, “Convert Database NULL Values to Default”
   

Side note: if you’re wondering about the second check box, that option will convert non-database Nulls to 0 or blank. This option affects only Nulls that are a result of your report writing; as such it is rarely needed.

2. Change the Formula Editor’s Null Handling default setting to convert all Nulls in any new reports from now on

This is similar to the first option, where Crystal Reports will convert any and all Nulls into a default value like the number zero or an empty string. There are two main differences, however: 1) it does not apply to the raw data Nulls (it only affect Nulls when they are referenced in an expression), 2) this option will affect all new expressions from now on (don’t worry, your existing expressions will not be affected).

That first difference is kind of like saying “I don’t want to change how the raw data looks, but I also want to make sure my calculations don’t get broken by a Null”.

Use this option when: Most of your database has Nulls sprinkled throughout most tables, several tables have SPARSE datatypes, you need to see when Nulls exist in the raw data but also need calculations to work, or you’re lazy and indecisive (like me) and just don’t feel like changing settings one report at a time.

To change the Null Handling setting:

1. File menu
2. Options
3. Formula Editor tab
4. Null Treatment dropdown, choose “Default Values For Nulls”

3. Use an expression to convert specific Nulls into a desired value

Let’s face it – very rarely is there a “one-size-fits-all” approach to report writing. This means we often have to tell Crystal Reports exactly what we want it to do with our Nulls on a case-by-case basis. We can use expressions to help with Nulls, and those expression can be applied in filters, in formula fields, formatting and more.

There is a lot of variation on steps and syntax here, depending on the situation, so the learning curve can be quite steep. This is where professional training can really help fill the gaps. Consider these valuable 2-day courses from New Horizons Computer Learning Center: Crystal Reports Part 1 and Part 2.

Use this option when: You need to control the value that replaces the null.

Until then, enjoy this little gem below – a common expression to take care of wonky addresses that might be missing information. Remember, in a concatenation like the one below, if even one piece of data is Null the whole result would turn Null. This expression essentially says “if our database is missing a city, I still need the region and zip code – so just plug in “MISSING CITY” instead of leaving it Null” … and it rinses and repeats for missing regions and missing zip codes. All this work just to get around the Nulls!

4. Filter the report to avoid Nulls completely

Another simple option is to remove the records that have Nulls. If there are no Nulls in your report, then they can’t wreak havoc! While this might seem ideal, removing records can mean you don’t see the complete picture your data is trying to paint, so tread carefully.

Use this option when: Removing the records that contain Nulls do not harm the analysis.

To filter out a record based on Nulls:

1. Click the column in your data that contains the Nulls
2. Select Expert
3. From the drop down, choose “formula:”
4. If necessary, click “Show Formula” button to expand the dialog box
   
5. Click “Formula Editor” button
6. Use the NOT and ISNULL functions on the appropriate column. For example: