Running Totals
Running totals are built-in fields that accumulate the total of another field. These fields save you the trouble of creating and maintaining a set of formulas that do the same thing. Since they are part of Crystal Reports, you have to do very little effort to use them in a report.
A running total takes a field on a report, performs a calculation on it, and adds the result to a report-wide variable that keeps track of the total amount so far. There are various calculations that can be used with running totals so that they can be customized to your exact needs. For example, you can calculate a running total that sums all the amounts, calculates the current average of all the amounts, or calculates the maximum amount. You can also set the interval for when to perform the calculation. You can do it on every field or whenever the field changes values.
To add a running total field to your report, right click on a numeric field that you want to track and select Insert Running Total. This presents you with the dialog box in Figure 6-18.
Figure 6-18. The Create Running Total dialog box.
When the dialog box opens, a default name and the field to summarize is already filled in. You should immediately change the name to something descriptive. There are also three options that can be set with a running total field: what type of calculation to perform, when to perform the calculation, and when to reset the total back to zero.
Just because the field is called Running Total doesn’t mean that you are limited to calculations that only sum numbers. There are over a dozen different calculations available. It could calculate the average of all the numbers printed so far, or it can print the largest of all the numbers. The most simple, and the default, is the Sum operation. It sums a value as it is printed.
The value of the running total field is affected by when it is evaluated. You can have a value recalculated every time a detail record is printed, or if you have a group level field, then you can calculate it on every new group. You could also calculate it only when the summary field changes value.
Just as important as setting when to calculate the field is setting when to reset its value back to zero. If you want a running total to accumulate throughout the life of the report so that the last record shows the grand total, you don’t want the value to ever get reset. But if you are tracking the running total for individual groups, you want it to reset every time the group changes. The dialog box also gives you the option to reset the running total when a field’s value changes or by using a formula to trigger the reset. Clicking the formula button brings up the Formula Editor dialog box so that you can write more advanced formulas for determining when the running total is reset.
Where you place the running total is very important. It is only as accurate as the most recent record printed. If you want to keep a running total of the detail records, you would put the field in the detail section. If you put the field in the group summary section, it will calculate the total as of the last record in the group.
You might recall from Chapter 4 that when printing a subtotal 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 subtotal doesn’t affect its value. This isn’t the case with running totals. If you put a running total in the group header, it will only show the calculation for the first record. Since the other records haven’t been printed yet, they aren’t calculated. An example report showing this behavior is shown in Figure 6-19.
Figure 6-19. The results of a running total field with a grouping report.
This is a Top N report that shows a running total column and there are three identical running totals 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.
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, 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. If a record is suppressed, it doesn’t get included in the calculation. As an example of this, the report in Figure 6-20 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 is 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-20. 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, 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 Basic syntax 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 page 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 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.
Question: I have a report that should only print 15 records per page. Any advice on how to do this?
Answer: You can use the MOD function to do this. The MOD function divides two numbers and returns the remainder. When you divide the current record number by 15 and it returns 0, then you know that it should print a new page. Right-click on the Details section and select Section Expert. Add the following formula for the Details’ property New Page After:
