Crystal Reports: Verifying Data

How do I know if the data in a report are correct?

The most beautiful report in the world is of no use or value if you don’t have any faith that the numbers are correct. The strangest events can throw our reports into a questionable underworld of dishonesty and error. We have a responsibility as report designers to test our reports validity before they go into production. 

What data have been included or excluded from the report? Date time fields have a bad habit of missing the last day of a selected range. If the data in the report are from multiple tables, are they joined correctly? The absence of some data, or a customer or product that is new to the database, or is now obsolete, can seriously affect what data appear in the report. 

Build your report up slowly and as you add each table to the report check the record count. Use “Distinct Count” to see how many different values you get for a primary key field. 

If you are developing a customer report, and there are 421 records in your report, then the distinct count of CustomerID should also be 421. Add the transaction table to the report, and your record count might go up to 1247, and the distinct count may drop to 380.

There are now 41 customers missing from the report because they don’t have any transactions. Is that what you want? Continue this process as you design your report, confirming each step along the way. 

Another important test is to verify the results against a different, but trusted source. That is, give them the common sense sniff test.

If you have a particularly critical report, find a subject matter expert who will help you verify a report before a general release. For example, if you are creating a Crystal Report that will provide a financial overview to the company's senior executives, ask someone in accounting to verify--preferably line by line--the key aspects of the report. Part of the report may have production numbers--ask a production department head to review those numbers for accuracy.

Keep in mind that you are testing not just your report design, but also the validity of the data provided by folks who can play a major role in the future of your career (or in future business with that client, if you are a contractor).

There may be some surprises in the report, once data corrections take place. Folks who participate in the review are then able to provide some background information and explanations for these surprises and begin formulating plans for correcting problems.

This is a great value-add for the reporting process, and it can be a real career-booster to you.

If such procedures were part and parcel at every company, we obviously would not have the Enrons of the world. But more importantly, companies would be better able to plan, monitor, and control their costs, processes, and value to customers.

See also: Crystal Reports: Using Alerts to Audit Transactions.

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.