How to Work with Nulls in Crystal Reports
Crystal Reports, SAP Crystal Reports
Crystal Reports handles nulls, or completely unfilled fields, in a particular way. And it’s very important that anyone using the program understands the details clearly.
A null, in Crystal’s view, is a database field which never had anything in it, and doesn’t now. A real-life example of this is in some people-related databases; folks sometimes don’t have an official home phone anymore. They do 99.9% of their stuff via smartphone, and don’t need a hard line. So they’ll have a number in the Cell Phone field, and nothing in the Home Phone field. But this means a technically incomplete record, and if Crystal doesn’t know about this, it will actually skip those records. Meaning, it won’t bring them into the report. And this could be a bad thing.
So before we deal with the database, we go into the program options (File->Options) and click the Formula Editor tab. Near the bottom left, we look at the Null Treatment dropdown.
Basically, if Exceptions For Nulls is chosen, the record will be skipped—that is, not brought into the report. The database can have a rule to forbid nulls, but this isn’t always the case. So more often than not, it’s worth considering the other choice: Default Values For Nulls. This means any record containing a null will come in anyway, and Crystal will fill it in. String (text) fields get blank spaces, and number fields get zeroes. But the important thing is, partly-empty records will not be skipped.
There are situations where either the database forces a null to fill in, or they aren’t important. But the key thing is to understand that Crystal won’t know about this situation unless we “tell” it what’s going on. There’s actually no right or wrong answer to the question here. Do we need to include the records with nulls, for whatever reason, or not? If not, Exceptions For Nulls is okay to use. If we don’t want to skip records regardless, and can deal with nulls in the report, Default Values For Nulls would be the way to go.
For more on this and other useful stuff, check out our Crystal Reports courses.