Running Totals Compared to Summary Fields

Running totals are interesting because they have similar functionality to summary fields, and they can also be duplicated with formula fields. This can cause some confusion as to when you should use a running total, a summary field, or a custom formula field. Each of these options has unique characteristics that you need to be familiar with to make the best choice.

Summary fields are an easy way to summarize data outside of the Details section. For example, you can put subtotals in the Group Footer and put a grand total in the Report Footer. But you can’t put a summary field in the Details section.

Running Totals have more flexibility because they can be evaluated at different times. They can summarize data in the Details section, across groups, or only when a field’s value changes. They can even use a formula to summarize on specific records only.

Both summary fields and running totals have a limitation that if the report group suppresses data, then the calculations will include the records that don’t get printed. It will show an incorrect value. If you recall from Chapter 1’s coverage of the two-pass report process, even though a record is suppressed, the data was still read into memory during Pass 1 of the reporting process and its summaries fomulas were calculated. The grouping is done in pass #2 and this is after the formulas have been calculated.

To get around this limitation, you can create a running total field that evaluates based on a formula. This formula uses the same logic as the conditional formatting formula for the Suppress property of the Details section. By doing this, the running total is calculated as the records are being printed and the data matches the report grouping. For example, assume the report only wants to show records where the Region field is equal to “West”. The Suppress property for the Details section would have the following conditional formula:

{Customer.Region} <> "West";

Any records where the Region isn’t “West” are suppressed. To make the running total field calculate properly you have to modify it so that it only gets evaluated for the records that are displayed. In the Evaluate area, you would set the option Use a Formula and enter the following formula:

{Customer.Region} = "West";

Thus, if the Region is “West” then the value is included in the running total field. As an example of this, the report in Figure 3-28 is the same report as the last example, but it suppresses some of the records from printing. The grand total field in the Order Amount column is a summary field that was added by right-clicking on the Order Amount field and selecting Insert Grand Total. This doesn’t have any special logic associated with it and it will include values that aren’t printed. The grand total in the right-most column is a running total field that also sums the order amount. But this running total field is evaluated using a formula that matches the suppress formula. It is only evaluated for records that get printed.

Figure 3-28. Summary field incorrectly calculates the grand total.

You can see that the grand total that is calculated with the summary field is much larger than the running total that uses a formula. The summary field is including records that aren’t printed on the report.