Crystal Reports: Convert Null to Default

For instance, if a report returns fewer records than expected when you select for less than a particular value, you could suspect that Null data are present.

See if your report behaves differently when you tick "Convert NULL Field Value to Default" on the File / Report Options menu. If it does, then Null data need to be processed properly.

Do not leave "Convert NULL Field Value to Default" as a permanent setting (and we recommend that you do not tick it in File / Options / Reporting, either).

Null data are there for a reason. If you assume a null is a blank or zero, you run a high risk of having your report produce incorrect summaries, totals, counts, and averages.

This brings up another important concern. Do you really know what is in all of your reports and how they are structured, or are your reports producing incorrect information while you are completely unaware of that problem? You really need a copy of Report Analyzer. It's an excellent investment for your company, and it will help you keep your career on track--especially at performance appraisal time.

Report Analyzer is the ultimate documentation and analysis tool for Crystal Reports administrators, server administrators, and others who need to maintain electronic filing systems.

Originally developed as a Crystal Reports tool only, with its version 5 release Report Analyzer became a powerful tool for analyzing any data repository.

Some key points:

• Generate summary and detailed documentation.
• Analyze report complexity metrics to assist with test plan development and maintenance planning.
• Easy to use documentation explorer to browse and compare report design settings.
  Print report design diagrams.
• Print report data dictionaries.
• Extensive formula printouts including call trees, object cross references, formatted listing, and conditional formulas.
• All documentation can be exported in a variety of formats including PDF, HTML and Word.
• Uses Crystal Reports to produce the documentation, enabling complete customization.
   

You'll want to use Report Analyzer to track down those nulls and look at the structure that causes them to exist. You can get nulls in formulas, and sometimes that results in nonsense instead of a value.

If you have a formula like this:

If {table.field}<value then "ResultA" else "ResultB"

and the field is Null ,you get neither result.

The only solution is to use the IsNull function as follows:

If IsNull({table.field}) then "NullResult" else

If {table.field}<value then "ResultA" else "ResultB"

Do you think maybe you have nulls or null-generating formulas you can't find, and worry they'll bite you some day? At the worst possible time (the day before your performance appraisal, for example)?

This article is copyrighted by Crystalkeen, Mindconnection, and Chelsea Technologies Ltd. It may be freely copied and distributed as long as the original copyright is displayed and no modifications are made to this material. Extracts are permitted. The names Crystal Reports and Seagate Info are trademarks owned by Business Objects.