Crystal Reports: Running Total Fields

Your bank statement has a good demonstration of a running total. The page starts with an opening balance, deposits add to the balance, and withdrawals reduce the balance. With a Running Total field, we display the balance each day as these transactions get processed.

Crystal Reports Running Total field first appeared in version 7, and we’ve found it to be a very useful object. The key things to specify are:

1) Summary: the Field to Summarize and the Type of Summary operation: Normally you would select a numeric field or formula and SUM that field.

2) Evaluate: How often do you want to calculate this summary? Usually it’s every record, but you also control less frequent calculations by selecting an evaluate on change of group. We don’t recommend the use of "on change of field" as the sequence of your data can cause this to go horribly wrong. Change of group is a lot safer, even if it requires the creation of an extra group in your report.

3) Reset: When do you want to set the total back to zero? Is this a running total that accumulates all the way through the report, or do you go back to zero on the change of a group field?

You should focus on the idea of totals, whenever and wherever you can when designing your report. It's not the granularity that is useful, it's the meta concept. That is, decision makers need to see the forest without the view obscured by the trees.

Are you really answering any business intelligence questions with your report? Where can you use totals, instead of individual data points?

Can a manager or executive use your report to adjust company strategy or better allocate limited resources?

You need to ensure your reports contain business information, not business data. They need to be decision tools, not intermediary data sources.

The data approach

The normal approach is to start with the database and push data to the users. No matter how you dress up data, you still have data. You do not have information. Data are raw materials and information is a finished product.

Suppose you walk into a Toyota dealer and say you need transportation. Do you expect to walk out of there with a container of parts, or to drive out of there in a car? That is the difference between data and information.

When you shove data at people, they try to assemble the data into information. Which is why you have all these folks wasting millions of salary-hours manipulating spreadsheets instead of doing the jobs they are paid to do. Which is why you have information silos. Which is why you have all sorts of other problems, which is why your job is less secure than it should be.

End-users need analysis, trends, conclusions, snapshots, summaries, thumbnails, overviews, projections, comparisons, and other things that are very different from data. When they don't have those things, out come the spreadsheets.

The report approach

Instead of starting with the database, start with the business questions. Talk with the senior executives (who may or may not be on your existing distribution list), and ask each one to tell you what the top three business questions are. If they give you more, that's OK. Compile a list, and see what data you would need for you to answer those questions with your reports.

Next, repeat this process with the people who are already on your distribution list. You are now ready to determine what will be reported.

Why talk with the senior executives, first? Those are the folks who run your company, so figure out what they want. Providing that helps secure your job and future raises in no small way. But it also helps you build the correct framework for your entire process, so that all users are marching to the same tune. You have to start at the beginning, not in the middle. By definition, middle managers can't see the big picture.

This raises another point. Surveys conducted between 2005 and 2008 showed that senior executives rarely have an accurate picture of their organization or the conditions under which it operates. They have a much rosier view, because people generally try to please them. These same people lack the time to dig through the data to see the real picture. In most companies, senior executives also lack the skills to do so. This means the data-oriented reports they get are essentially useless. If you are in charge of those reports, what does this say about your value to the company?

If you provide the senior executives with the business information they need, and you provide middle managers with the business information they need plus the business information the senior executives are working from, how do you think this will affect the effectiveness of management to make good decisions?

Instead of working from the detail level up, work from the information level down. Determine what information people really need. Then, use the power of Crystal Reports to assemble that information from the data you can get. 

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.