Creating Conditional Running Totals
There may be times when you have a list of ungrouped values, and you only want to subtotal some of the values in the list.
Suppose that:
- You have a list that contains both Canadian and U.S. customers.
- You want to keep customer records sorted alphabetically based on customer name.
- You do not want to break the data into groups based on the country.
- You want a total of the values from just the Canadian records.
- You also want a total of the values from just the U.S. records.
To accomplish this, create two running totals, one to keep a running total of the U.S. records, and one to keep a running total of the Canadian records.
To create a conditional running total
- In the Field Explorer, right-click Running Total Fields and click New.
- In the Create Running Total Field dialog box, enter a name for the running total object in the Running Total Name field.
- In the Available Tables and Fields area, select the first field you want to summarize.
- Click the first arrow button to add the field to the Field to summarize box.
- In the Type of summary list, click sum
- In the Evaluate section of the dialog box, click Use a formula and then click the Formula button (labelled x+2).
- In the Running Total Condition Formula dialog box, enter the formula in the formula box.
For example, if using Crystal syntax to create a running total of U.S. sales, you would enter:
{Customer.Country} = "USA"
If using Basic syntax, you would enter:
Formula = {Customer.Country} = "USA"
This tells the program to evaluate the running total each time it comes to a record where {Customer.COUNTRY} is equal to "USA." The running total will ignore all other records.
- Click the Save and close button.
- In the Reset section of the Create Running Total Field dialog box, click Never.
- Click OK to save the running total field.
- Create another running total using the above steps. The only difference will be the value in the formula.
- Return to the Field Explorer and locate your Running Total Fields.
- Either drag both of your running total fields onto the Details section of the report, or, if you want to view a grand total, drag them onto the Report Footer section of your report.
Note A running total that is placed on a report is indicated by the # prefix (for instance, #Total1).
See Also
Placement of Running Total Fields | Creating Running Totals