Return to Newsletter Contents...

Running Total or Summary

by: Tom Hinkle, Consultant, CRCP

Often when a total is needed we place a summary in the group, page or report footer and it does what we want.  However there are times when we want that total to be dependent on a condition or accumulate record by record.  That’s when a running total is needed.

Running total fields are similar to summary fields but allow more control over how the total is calculated and when it is reset. Running total fields are specifically suited to perform the following totaling functions:

• Show values of a total accumulate as it is calculated record by record

• Total a value independent of the report's grouping

• Total a value conditionally

• Total a value after a group selection formula has been applied

• Total a value from the driving table in a one-to-many linking relationship

How running totals work

A running total field is created with the Running Total Expert. The Running Total Expert creates a running total field by asking you to select a field to summarize, the summary operation to use, a condition upon which to base the evaluation, and a condition upon which to reset the evaluation.

Note:    A running total field can be used on database fields and first-pass formulas, but cannot be used on second-pass formulas or formulas that reference other second-pass formulas.

Placement of running total fields

The calculation of a Running Total field is determined by the settings selected in the Running Total Expert. However, where you place the running total affects the value that appears on the report.

The following list summarizes the records that are included in the calculation when a running total is placed in various report sections. This list assumes the running total is not reset.

The following steps explain how to create a running total:

1. On the View menu, click Field Explorer

2.  Select Running Total Fields and click New

3. Enter the running total name in the Running Total Name box
4. Highlight the field to be used in the Available Tables and Fields box, and use the first arrow button to move it over to the Field to summarize box
5. Select sum from the Type of summary list
6. In the Evaluate section of the dialog box, click On change of field, and select the desired field as the On change of field
   1. The running total will execute each time this field changes
7. In the Reset section of the dialog box, click Never (this gives you a running total that never resets; that is, the running total continues throughout the report)
8. Click OK to save the running total field

To display a running total that accumulates, insert the running total field in the Details section of the report. On your report, each row in the running total column displays the current record value added to the previous values. This total continues, unbroken, through the report.

To create a running total for a group follow the same steps as above with the following changes:

1. In the Evaluate section of the dialog box, click For each record
2. In the Reset section of the dialog box, click On change of group and accept the default group name

3. Place the running total field in the Details section of your report

Note:    If you want to view a grand total of each group, place the running total field in the Group Footer section of your report

To create a conditional running total make the following changes when creating the running total:

1. In the Evaluate section of the dialog box, click Use a formula and then click the Formula button
   1. The Formula Workshop appears with Running Total Condition Formula active
2. Enter a formula in the Formula box:
   1. This tells the program to evaluate the running total each time it comes to a record where the selected field is equal to a value. The running total will ignore all other records
3. When the formula has the correct syntax, click Save and close
4. In the Reset section of the dialog box, click Never
5. When you are finished, place the running total field in the Details section of your report

Note:    If you only want to see a grand total of the selected field, place the running total field you created in the Report Footer section of your report.

Now you have an idea how running totals work and how to create them.  Experiment with the options when creating running totals and you will soon use them on a regular basis.

Go to Top  |  Return to Newsletter Contents