Crystal Reports: Recognizing Nulls in Formulas

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)?

To see where these are, we have a great tool called Report Analyzer.

This 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.
   

Jason Dove says that  formulas saved as functions on the Report Server do not handle NULLS. This can make some things just impossible to save in this manner. His comments:

How to Setup Functions

Write and save a formula, preferably something useful!

Within the formula editor, select "Custom Function" from the New File dropdown.

Choose to "Use Extractor" and then select the previously written formula.

Crystal Reports will create a Function based on the formula and replace any database fields with variables to allow it’s re-use with other values.

Rename the computer created variable names to something meaningful. I advise using ctrl F and Find & Replace to ensure all references to the variable names are changed.

Upload to the Report Server, where the Function be saved in the Repository Custom Functions folder.

Note the consistent naming pattern to the Functions in the illustration above. This is important for later use and makes finding the correct Function a lot easier.

Using Existing Functions

To use an existing Function is simple:

Open a new formula and expand the Repository Custom Functions.

You may need to log on to the Report Server, then, highlight the Function you want.

Click the cog icon with the red arrow pointing to it.

Go back to the formula you just opened and notice that the Functions Panel now has an additional folder “Custom Functions” which contains the Function just created.

Just double click the Function you want.

Ok, that may sound a little convoluted when approached as an end to end process, but once a library of Functions builds up, accessing it as and when required becomes second nature and a genuine time saver.

Jason Dove is a top Business Intelligence consultant and author who has shared his expertise with some of the most respected businesses in the world.  Crystal Reports is his reporting software of choice and the subject of his book "Crystal Reports Formulas Explained" which is the most advanced book on the market.  Currently available with a free 70 page Crystal Reports XI tutorial: http://www.scry-business-intelligence.com/products/bookoffer.php

And since we're talking about formulas, here's some info on the Formula Workshop. The Formula Workshop makes formula development easier. In Crystal Reports 9.0, all formulas are managed in the Formula Workshop. This is where you create and edit your calculations--including Record and Group Selection, SQL expressions, and conditional formatting of sections and fields.

One major improvement in version 9 is the ability to process string fields that are longer than 254 characters. The new limit is 64,000 characters in length.

In a similar way, Memo fields can now be processed in formulas.

There are some new functions available in version 9.0 including ProperCase, and formatting numbers in Roman numerals. (So perhaps we should refer to Crystal Reports IX).

Another useful function is DrillDownGroup Level which we see adding a whole new level of improved presentation to Drill Down Reports. If you know what level of Drilldown is currently being displayed, then conditional formulas and formatting can be used to change the appearance of the report. 

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.