Mastering Business Analysis with Crystal Reports 9 (Wordware Applications Library)

In the past, report developers loathed the creation of running totals. In older versions of Crystal Reports, there was no support for the creation of running totals — thus, developers found themselves needing to create custom formulas just to handle this type of calculation. Thankfully, Crystal Reports now includes the functionality to make adding running totals easy.

Running totals are commonly found in financial reports (such as account statements), where a total balance changes as each record is processed. In the case of an account balance report, the running balance changes after each transaction.

You may enter running totals anywhere within a report (unlike subtotals and grand totals, which are restricted to certain report sections). However, please note that where a running total is placed affects the value that returns.

For example, a running total placed in the Group Footer section creates a running total up to and including the last record of a group. A running total placed in the Details section creates a running total for every record.

Table 9-2 displays how running total values are affected depending on their placement within a section.

To add a running total calculation to your report, perform the following steps. Continue using the report you've created in this chapter.

1. Select the Design tab.

2. Right-click on the Order Amount field (in the Details section).

   An Options dialog box displays.

3. Select Insert, Running Total from the Options dialog box.

   The Create Running Total Field dialog box displays, as shown in Figure 9-4.

   Figure 9-4: The Create Running Total Field dialog box

You'll notice the Create Running Total Field dialog box contains the following functionality:

• Available Tables and Fields: Contains all tables and fields within the report. You may use this list box to select the field to base a running total upon.

• Running Total Name: Allows you to type a name for your running total. The name of the field may contain mixed-case characters and/or spaces. By default, Crystal Reports creates a unique field name for you.

• Fields to summarize: Displays the field you select from the Available Tables and Fields list box. This field is the one you'll base a running total upon.

  Use the Add button (>) to copy a field from the Available Tables and Fields list box to this field.

• Type of summary: Allows you to choose the type of calculation the running total shall perform (for example, sum or average).

  These options will change depending on the data type of the field that you select. For example, a field that contains text data will not contain the ability to calculate sums.

• The Evaluate area: Allows you to specify at what point in the report the records are incremented. The most common option (and the default) is to have the report increment "For each record."

• The Reset area: Allows you to specify when the running total accumulation should start over and reset to zero. The most common option (and the default) is to never have the report reset the running total within a report.

Although this dialog box may look complex, it's a lot less painless than creating a running total formula from scratch. Continuing with your report, try the following:

1. In the Create Running Total Field dialog box, leave the Running Total Name as default.

2. In the Evaluate area, check the On change of group check box.

   Make sure the drop-down field is set to Group#1: Customer.Customer Name. This setting defines that a running total will display at the end of each group.

   Figure 9-5 displays this setting within the Create Running Total Field dialog box.

   Figure 9-5: Defining a running total

3. Press the OK button.

   You'll notice your running total field display above the Order Amount subtotal (within the Group Footer #1 section).

4. Move this running total field to the right of the Order Amount subtotal.

5. Create a header for this running total, and place it within the Page Header section.

   Call this header Running Total. Figure 9-6 displays how your screen should look.

   Figure 9-6: A report with a running total