Running Total Fields

In certain situations, the use of formulas with variables (discussed earlier in the chapter) is inevitable. However, many of the examples shown previously can actually be accomplished without even creating a formula. If you need to accumulate, display, and reset running totals, you will probably prefer the running total field. A running total field can be inserted just like a database field. It gives you great flexibility to accumulate or increment values as the report progresses, without the need for formulas or variables.

Figure 5-7 shows a Top N report (discussed in Chapter 3) that shows regional subtotals for the top five regions in the U.S. This particular Top N report does not include Others. As mentioned in Chapter 3, this causes the report grand totals not to agree with the sum of all the group totals. The grand totals are based on all report records, not just those that fall into the top five groups. Using running total fields is the perfect answer to this problem.

Figure 5-7: Grand total problem with Top N report and no Others group

All new running total fields are created from the Field Explorer. First, select the Running Total Fields category in the Field Explorer. Click the New button in the Field Explorer toolbar or right-click the Running Total Fields category and choose New from the pop-up menu. You may also select an existing field in the Details section, right-click, and choose Insert Running Total from the pop-up menu. The Create Running Total Field dialog box appears, as shown in Figure 5-8.

Figure 5-8: Create Running Total Field dialog box

Start by giving the running total field a name (if you don t like the default name given by Crystal Reports). It can contain mixed-case characters and spaces and won t conflict with formula or database field names . Crystal Reports will precede the running total field name with a pound sign (#).

If you select a detail field and use the right-click method to insert the running total field, the field you choose will already appear in the Field to Summarize drop-down list, and a default summary function will appear in the Type of Summary drop-down list. If you re creating a new running total field from the Field Explorer, choose the report, database, or formula field that you want to use to calculate the running total by selecting the field in the Available Tables and Fields list and clicking the right arrow next to the Field to Summarize box. Choose the type of calculation you want to use from the Type of Summary pull-down list. If you just want to increment the running total by one for certain records, use the Count or DistinctCount summaries (depending on how unique the field you are summarizing is), along with any field from the report that won t contain null values. Nulls don t increment counts. Other functions available in running totals are the same as those available for summaries. Look back at Chapter 3 for a detailed description of available summaries.

Choose when you want the running total to increment, by making choices in the Evaluate section. Then, choose when you want the running total to reset, by making choices in the Reset section. If you select a field in the Available Tables and Fields list and then click the arrow next to the On Change of Field radio button, the running total will increment or reset every time a new value appears in that field. If you click the On Change of Group radio button, you can then choose an existing report group in the pull-down list. The running total will increment or reset every time the chosen group changes. If you click the Use a Formula radio button, you can then click the Formula button next to it. The Formula Editor will appear, in which you can enter a Boolean formula that will trigger when the running total field is incremented or reset.

Click OK when you ve completed the Create Running Total Field dialog box. The running total will now appear in the Field Explorer and can be dragged and dropped on the report just like a database field. If you d like to edit, rename, or delete the running total field, you have these choices in the Field Explorer. You can also right-click a running total field in either the Design or Preview tab and choose Edit Field Object from the pop-up menu.

To solve the problem with the Top N report without Others, simply create two running total fields: one to calculate the number of customers:

and one to calculate the sale grand total:

Place these running totals in the report footer instead of grand totaling the fields from the details section. Because running totals evaluate only during the While Printing Records pass, the extra records in the Others group won t be included in the report footer. Figure 5-9 shows the correct totals now displayed on the Top N report.

Figure 5-9: Correct Top N report using running totals

Caution  

Because running total fields are calculated in the While Printing Records pass of the report, you cannot create running total fields based on second-pass formulas.