Running Totals

Running totals are like an advanced version of summary fields. They give you more ways of summing data and give you more flexibility for determining which data to summarize. The primary difference is that summary fields are calculated by group and running totals can be calculated many ways (by group, record by record, etc.)

Running totals are built-in fields that accumulate the total of another field. It is so common to summarize numerical data that Crystal Reports has this functionality built-in. Otherwise, you would have to create formulas for summing these values and zeroing them out when appropriate.

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

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 simplest calculation, and the default, is the Sum operation. It sums a value as it is printed.

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

Figure 3-26. The Edit Running Total dialog box.

When this dialog box opens, a default field name is already filled in for you. You should immediately change the name to something descriptive. The dialog box has three areas where you can set the field’s properties. They are the Summary area, Evaluate area, and Reset area. They control how and when the running total is calculated. Each area is described next.

It should be immediately obvious that this dialog box is much more advanced than the simplistic Insert Summary dialog box you worked with in the previous section. A few things that stand out are that in the middle of the dialog box is an evaluate area which sets when the running total should be calculated (for each record, on change of group, etc.) and at the bottom is a reset area that determines when the running total gets set back to zero. These options alone make it obvious that you can do a lot more with running totals than you can with summary fields.

The Summary Area

The Summary area is very simplistic and is similar to the Insert Summary dialog box discussed in the last section because. It lets you set the field to base the running total on and which summary calculation to perform (sum, average, count, etc.).

The Evaluate Area

The Evaluate area determines when the field’s value gets added to the running total calculation. You can have a value get recalculated every time a detail record is printed, or if it is based on a group level field, then you can calculate it for every new group. You could also calculate it only when the summary field changes value. There are four options for when to evaluate the field and they each have a different purpose. They are described next.

For Each Record

Evaluating the running total field as each record is printed is the most common way of using running total fields. As each row is printed, the selected field’s value is used to update the running total field. You can use this option when you want to show the current running total in the Details section beside the field’s current value. Even if you don’t show the running total field in the Details section, you should use this option when every row in the report is going to be included in the running total.

On Change of Field

This option only includes the field’s value in the calculation when the field’s value changes from one record to the next. If the current record has the same value as the previous record, then it won’t be counted. Evaluating the running total when a field changes is useful when there is repeated data in a field and you only want to count each value once. This frequently happens when the data source links two tables in a parent-child relationship. When this is the case, the fields from the parent table are repeated each time that the fields from the child table are listed. If you are summarizing a value from the parent table, then the totals will be wrong because they will be incremented each time a child record is printed. To correct this problem, select the On Change of Field option and select a field from the parent table. This insures that the running total is only evaluated once for each record in the parent table.

When using this option, the way to select the field isn’t completely intuitive. There is a text box to the right of the option, but it won’t let you type anything into it. Instead, you have to select the field from the field list on the left and click on the arrow button beside the option. Once you click on the arrow button, the field name is entered into the text box. If you want to change the field, you have to use the arrow button again. Although you can click on the text box and move the cursor around with the arrow keys, no typing is allowed.

When selecting the field from the parent table, it’s best to choose the primary key from the parent table. If you aren’t careful, you might think that you can choose any field in the parent table to determine when to evaluate the running total. But this isn’t always true because some records in the parent table will have the same value over consecutive records. For example, if the parent table is the Employee table, you might select the Employee Name field. But it is very common for some employees to have the same name and selecting this field will result in the second employee’s data not being evaluated. Thus, in this example, you should use the Employee ID field or the Social Security Number so that uniqueness is guaranteed.

On Change of Group

This option is pretty self explanatory. The running total field is evaluated whenever the group changes. It is only calculated one time for each group in the report. This is similar to the On Change of Field option except that this option can only be used when the report has groups. When selecting this option, the dropdown box only shows you the available group names.

Use a Formula

Evaluating the running total field based on a formula lets you create more advanced running total calculations. There are times when the other options are too simplistic to give you the running total result you need. You could have a running total which is only calculated when a field is a certain value or is within a range of values. For example, this can be used to create multiple running totals and each one only summarizes data for a particular country. One field could just summarize data when the country is USA and another field could do so only when the country is Canada. Another example is a running total field that only summarizes data for values that exceed a certain limit. For example, a sales report that prints sales details could print a grand total of all the deals that exceeded a million dollars.

The Reset Area

Just as important as setting when to evaluate the running total is determining when to reset its value back to zero. If you are tracking the running total for individual groups, then you want it to reset every time the group changes. There are also times when you don’t want the value to get reset at all. 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 to reset the value.

The Reset area gives you the option to never reset the running total, reset it when a field’s value changes, reset it when the group 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. Just like the Evaluate area, using the formula gives you the ability to reset the value using additional functionality not provided in the other options.