Running totals 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 its unique characteristics.
Summary fields are useful for summarizing data outside of the detail section. For example, you can put subtotals in a group footer and put a grand total in the report footer. But you wouldn’t want to put either of these fields in the Details section. Summary fields also have a limitation that if your report suppresses data, then the summary calculation will include the records that don’t get printed. This is because the summary fields are calculated during the first-pass, and this occurs before the fields are suppressed. They can’t take into account which fields don’t get printed. But the running total fields are calculated as each record is printed. So if a record is suppressed, it doesn’t get included in the calculation. As an example of this, the report in Figure 6-18 is the same Top-N report as the last example, but this report has two grand total fields added. The grand total in the right-most column a running total field and it is a copy of the fields above it. The grand total field in the left column is a summary field that was added by right-clicking on the Order Amount field and selecting Insert Grand Total.
Figure 6-18. 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 it should be. It is including records that weren’t printed on the report (they were filtered out). The running total field is correct because it only totals fields that were printed.
A formula should only be used when there isn’t any other alternative. Not only does it incur more overhead when the report runs, but it requires more work on your part. If the previous two options are sufficient for your needs, then you should use them first. Having said that, there are times when a running total is limited and using a formula will give you the results you are looking for. An example is a report that tracks how many records are printed on each page. It shows the row number next to each record and it resets the counter to zero for every new page. Since there isn’t an option with running totals to reset at the top of a page, a formula is required. To implement this report you need two formulas. The first increments a global variable by 1 for each record. This formula is put in the detail section.
WhilePrintingRecords
Global LineNumber As Number
LineNumber = LineNumber + 1
Formula = LineNumber
The second formula resets the counter back to zero. It goes in the report footer so that it only gets called when a page is finished. It also has its Suppress format set so that the user can’t see it in on the report.
WhilePrintingRecords
Global LineNumber As Number
LineNumber = 0
Formula = LineNumber
When this report is run the first record will always show a line number of 1 at the top of each page. The last record will show how many total records were printed on the page.
