Placing the Running Total Field
You might recall from earlier in the chapter that when printing a summary on a group, you can put that field in either the group footer or group header and it still prints the same result. The location of a summary field doesn’t affect its value. This isn’t true for running totals because they are calculated while printing records. Thus, they are only accurate to the point that they get printed. Where you place the running total is very important. If you put a running total in the group header, then it shows the calculation as of the first record in the group. Since the other records haven’t been printed yet, they aren’t calculated. An example report showing this behavior is shown in Figure 3-27.
Figure 3-27. The results of a running total field with a grouping report.
The report in figure 3-27 shows a running total column and there are three identical running total fields. The running total field calculates the sum of each order amount, and it is reset when a group changes. There is a copy of it in the header, the detail, and the footer. You can see that the field in the detail section changes for every record and that the footer matches the value of the last record printed. But the header record doesn’t match the footer value. Instead, it is equal to the first record printed in the group.
Question: I currently have a running total field displayed in the page footer. I want to carry that same value forward to the next page in the header section. Unfortunately, it doesn’t work because the header section often shows a different total than what was in the previous page footer.
Answer: This is because the running total field always includes the current record being printed. When it appears in the page header of the following page, it is also adding the first record that is getting printed on that page. Even though the first record on that page hasn’t printed yet, it still gets included in the running total balance. That’s why the two values are different. To fix this, you have to create a manual running total formula and place it in the details section. This synchronizes the running total balance with the actual printing of the detail data. To get the balance to appear in the report footer and header sections, the formula has to use a shared variable and create a second formula which simply prints the value of that shared variable. Put this second formula in the report header and footer.
