I was working with a customer the other day and was told that the company had implemented a Crystal Report standard that ALL reports would have the option “Convert Database Null Values to Default” checked. I almost never check that as a global default in a report. One of the main reasons has to do with WHEN that conversion takes place. It happens after the query has been run in the database but before Crystal reads in the records. That makes it very difficult to select null values. Consider a report using the Xtreme Customer table that lists all customers with Null postal codes. My selection formula is:

IsNull ({Customer.Postal Code})

which becomes the following WHERE clause:

WHERE `Customer`.`Postal Code` IS NULL

The SQL query sends back the 10 records that have null values. Then Crystal converts all of these NULL values into their default value (“”). And then Crystal applies the selection formula to “double check” the records returned by the query. The selection formula will now reject all of 10 records because they are no longer null. I get zero records in the report. And if I change my selection formula to be:

{Customer.Postal Code} = ''

I have the opposite problem. The SQL now says:

WHERE `Customer`.`Postal Code`=''

Now none of the records qualify for the query and, again, nothing is returned in the report. One of the nice features of versions 11 and 12 (XI and 2008) is the ability to convert nulls to defaults in individual formulas.