Using Formulas instead of Running Totals

Running totals have two limitations. The first is that you are limited in the types of calculations you can do (Sum, Avg, etc.). The second limitation is that running totals are restricted to using only first pass data. For example, the formulas can’t use summary functions, the Next() or Previous() functions or shared variables. They also can’t use the WhilePrintingRecords keyword. If you want to calculate the running total of this type of formula, you have to do so using another formula. This is often called a Manual Running Total because you are writing the formulas that implement it. Let’s look at manual running totals in more detail.

A formula can perform the same functionality as a summary field and running total. The difference being that you have to do the work of writing the code and debugging it to make sure it works properly. Formulas give you the benefit of being in complete control and you can use advanced functionality not provided with the Edit Running Total Field dialog box. You have to balance how much work you want to do with how much control you need over the calculations.

You might be surprised to learn that using formulas is actually more efficient than using a running total object. In performance tests on reports, replacing a running total field with a manual running total formula resulted in faster page views. If you have a large report that uses running total fields and the performance is slow, you should try replacing them with manual running totals.

When using a formula to calculate a running total, you need to create up to three formulas to do the job. The first formula, the summary formula, replaces the functionality of the Summary area and the Evaluate area. It performs the running total calculation. It has to have a global variable that tracks the current value of the running total so that this can be shared with the other two formulas. If the formula doesn’t reference any database fields, it should also use the WhilePrintingRecords keyword so that it gets calculated while the report runs.

Once you write the formula and save it, you have to put it on the report so that it gets calculated. If the formula isn’t used on the report, then Crystal Reports ignores it. Where you put it on the report determines when it is evaluated. If you want it evaluated for every record, then put it in the Details section. If you want it evaluated once per group, then put it in the Group Header section.

The second formula, the reset formula, resets the total back to zero. Just like the first formula, where you put it determines when it sets the running total back to zero. If you want it reset for every group, then put it in the Group Footer. If you want it reset for every page, then put it in the Page Footer. Since the reset formula always has the same value (zero), you don’t want the user to see this formula on the report. You should open the Format Editor dialog box and check the Suppress property.

For running totals that accumulate their value for every record in the report and don’t need to be reset, then the reset formula isn’t necessary.

Question: How do I sum the values of a column for each page?

Answer: Create a manual running total but put the reset formula in the page footer. This sets the running total to zero at the start of each new page.

After creating the summary formula and reset formula and placing them on the report, you have replaced the functionality of the running total field and you are finished. However, there are times when you will also need a third formula. Let’s look at when a third formula is necessary and what it does.

In most circumstances, you will put the summary formula in the Details section so that the user sees the running total for every record on the report. But what if you want the running total to only be displayed in the group footer or page footer? In this case, you will still keep the summary field in the Details section, but you will suppress it so that the user doesn’t see it. By suppressing the field, it still gets calculated but it won’t be shown to the user. Since the summary formula is suppressed, you need to create a third formula to display the running total value in another report section. This third formula’s purpose is simply to print the value on the report. You could put it in the group header or report header if you wish.