A customer recently sent me a report with an unusual requirement. The report had 2 groups and they needed a running total that reset at the end of each group 1. The unusual part was that the RT would be displayed only at the top and bottom of each page. The idea was to see where the total ended at the bottom of the page and then show it pick up again at that same point at the top of the next page. It sounded simple, but when we tried putting a normal running total field in both the Page Header and the Page Footer we got inconsistent results.

In most cases the value in the Page Header was not the same as the value in the Page Footer of the prior page. But sometimes the two numbers WERE the same. It took a little testing to uncover the reason which is related to how the Page Header and the Page Footer get their values.

When you put field, like a running total, in the PH, the value displayed is based on the record used in the first section to print on that page. The PF takes its value from the record used in the last section to print on that page. So if the bottom of page 1 is the detail band for record 36 then you would see the running total up through record 36 in the PF. The top of the next page will usually show the next detail record, so the PH will pull it’s value from record 37. That is why the two sections will show different values.

So why did the report sometimes show the SAME value in these 2 places? Take the above example and imagine that record 36 is the last record in Group #2. If detail 36 prints at the bottom of page 1 then Group Footer 2 will print at the top of the next page. The PH on page 2 will still use record 36, because it is being used at the top of page 2. So now the value stays the same.

The solution was to store the running total into a variable at the page footer and display that variable in a separate formula in the page header. A formula in the PH that refers to a variable doesn’t need to “look forward” to the section below it for it’s value, because variables are not tied to the rows of the report. They are stored separately in memory. The value of a variable won’t change from the bottom of one page to the top of the next.

For a better understanding of using variables, including variables in running totals, see my Expert’s Guide to Formulas.