Crystal Reports Basics: Summary Fields and Statistical Functions

This is based on the book, Crystal Reports: A Beginner’s Guide. For more detail and explanation, plus practice exercises, order the book here.

Crystal Reports provides many statistical functions, all of which are covered in detail in Crystal Reports: A Beginner’s Guide. These differ from the summary fields.

For even more functionality, you can use third-party programs, such as the ones available here.

These various summary fields can come in quite handy. Think about how you can use them to convert data to actionable business intelligence. But don't start with the data. Start by asking users what they need and what will help them meet their business goals.

Make a list of business goal related information descriptions. For example:

• Most profitable customers.
• Least profitable customers.
• Most profitable products.
• Least profitable products.
• Types of jobs that get reworked the most.
• Products with the highest discount to profit ratio.
• Discounts that result in a decrease in net profits per product.

Then, examine your database to see if there's a way to create the needed information from the data you have. If not, consider ways to capture the data and see if that can be done.

When designing the report, make heavy use of graphics. A graphic does a far better job of making ratios instantly make sense than do numeric representations. And that means a far more useful report.

So that you don't get some managers doing end runs around your reporting system so they can waste time (though in their eyes, it's "essential") gazing at the source data make sure you provide drilldown capability. Yes, you can lead the horse to water. But if you try to force it to drink you may just get a hoof in the head.

Let's not forget some inherent problems with statistics, either. Probably the most abused one is the average. An average is often meaningless, but worse it's often misleading. Just to illustrate, suppose you had two people in a room.

1. Person One has unpaid medical bills of $2.6 million and owes another million in back taxes. He lives in a tent and can't find a job.
2. The other person is Bill Gates.

If you look at their average net worth, Person One has no money problems. Get the point?

When you're doing statistics, you'll need to apply some statistical methodology that normally is done only by actual statisticians. It's been said that statistics lie, but the actual problem is the statistics are not properly arrived at.

Be very cautious about having your report show "average" anything. You need to look at the population being averaged and determine which outliers exist that can skew the average. The same is true for any other metric that includes an entire population. For example, mean and median.

Some people think it's OK to present the misleading statistic if you also provide a qualifying statement. It's not OK. First of all, the statistic still makes a miscommunication. Secondly, you are sending the message that you are either too lazy or too incompetent to provide a correct statistic. When in doubt, leave it out.

But what if a manager insists on having an average or other statistic that can't be properly validated? In that case, you can use the Bill Gates example to explain the issue. The solution may require contracting an actual statistician to develop a formula your report can use to provide a meaningful and reliable statistic.

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.