Running Totals

Running totals are built-in fields that accumulate the total of another field. These fields save you the trouble of creating and maintaining a set of formulas that do the same thing. Since they are part of Crystal Reports, you have to do very little effort to use them in a report.

A running total takes a field on a report, performs a calculation on it, and adds the result to a report-wide variable that keeps track of the total amount so far. There are various calculations that can be used with running totals so that they can be customized to your exact needs. For example, you can calculate a running total that sums all the amounts, calculates the current average of all the amounts, or calculates the maximum amount. You can also set the interval for when to perform the calculation. For example, you can do it on every field or whenever the field changes values.

To add a running total field to your report, right click on a numeric field that you want to track and select Insert Running Total. This presents you with the dialog box in Figure 6-16.

Figure 6-16. The Create Running Total dialog box.

When the dialog box opens, a default name and the field to summarize is already filled in. You should immediately change the name to something descriptive. There are also three options that can be set with a running total field: what type of calculation to perform, when to perform the calculation and when to reset the total back to zero.

Calling the field a running total is deceiving because the calculation doesn’t have to calculate a running total. There are over a dozen different calculations available. It could calculate the average of all the numbers printed so far, or it can print the largest of all the numbers. The most simple, and the default, is the Sum operation. It sums a value as it is printed. Don’t think that just because the field is called Running Total, that you are limited calculations that sums numbers.

The value of the running total field is affected by when it is evaluated. You can have a value recalculated every time a detail record is printed, or if you have a group level field, then you can calculate it on every new group. You could also calculate it only when the summary field changes value.

Just as important as setting when to calculate the field is setting when to reset its value back to zero. If you want a running total to accumulate throughout the life of the report so that the last record shows the grand total, then you don’t want the value to ever get reset. But if you are tracking the running total for individual groups, then you want it to reset every time the group changes. The dialog box also gives you the option to reset the running total when a field’s value changes or by using a formula. Clicking the formula button brings up the Formula Editor dialog box so that you can write more advanced formulas for determining when the running total is reset.

Where you place the running total is very important. It is only as accurate as the most recent record printed. If you want to keep a running total of the detail records, then you would put the field in the detail section. If you put the field in the group summary section, then it will calculate the total as of the last record in the group.

You might recall from Chapter 4 that when printing a subtotal on a group, you can put that field in either the group footer or group header and it still prints the same result. The location of a subtotal doesn’t affect its value. This isn’t the case with running totals. If you put a running total in the group header, then it will only show the calculation for the first record. Since the other records haven’t been printed yet, they aren’t calculated. An example report showing this behavior is shown in Figure 6-17.

Figure 6-17. The results of a running total field with a grouping report.

This is a Top N report that shows a running total column and there are three identical running totals fields. The running total field calculates the sum of each order amount, and it is reset when a group changes. There is a copy of it in the header, the detail, and the footer. You can see that the field in the detail section changes for every record and that the footer matches the value of the last record printed. But the header record doesn’t match the footer value. Instead, it is equal to the first record printed in the group.