Crystal Reports: Conditional running totals

Instead of just adding up a field, we can control under what conditions we wish to add it. The "Evaluate" option on the running total field also includes "Use a Formula." This allows us to calculate a conditional running total.

For the conditional formula, specify when the total should be calculated. To add up sales above $1000 use a formula of:

{Order.Amount} > 1000

A conditional COUNT can be very useful to tell you how many records meet a specific condition. Select "Count" as the Type of Summary instead of Sum. You can use any non-NULL field as the field to summarize.

Another place we used this recently was in a Profit and Loss Report, where the entire report was in the report footer section with conditional running total fields. Each conditional formula selected one or more General Ledger account codes for the report.

You can also have a running total in a crosstab.

Our example uses the Xtreme 9 sample database and a crosstab with Country as a row, Order Date (by year) as the column, and the order amount as the summarized field. Our running total is summing the order amount across the crosstab and resetting on each row.

1) Create the Running total to sum the Order Amount, evaluate each record and reset on change of field (Country). 
2) The data has to be sorted by Country, and then Order Date. This is so the running total summarizes the data in 
the correct sequence. 
3) Place the running total in the crosstab as a summarized field and suppress the values under the totals. 

A note of caution: if there are no records for the final year the reset is done earlier than you might expect. In the case of Australia above, there are no records for 2002 and so the last running total is displayed in 2001. 

We have a sample report available if any of you would like to look at this further. This technique and the sample report require Crystal Reports 9.0 (although we can send a PDF with the sample output to users of older versions).

You can also use a formula to generate a running total.

How do you do this? You need two formulas: one to set a variable to zero, and a second formula to accumulate a field into that variable. To behave correctly, all variable formulas need to be calculated WhilePrintingRecords

Formula: Reset Total. Insert this formula in the Report Header or Group Header section and suppress the field

WhilePrintingRecords; NumberVar Amount :=0;

Formula: Calculate Total. Insert this formula in the Details Section

WhilePrintingRecords; NumberVar Amount := Amount + {Order.Amount};

//Where {Order.Amount}; is the field you are interested in totaling.

Sometimes a third formula is needed to display the value in the variable without accumulating again.

WhilePrintingRecords; NumberVar Amount

.

Reset a running total on each page. So how would you specify a running total that resets on the change of page? We have not been able to find a formula that we could use in a running total field to do this. So we have to revert back to the older technique (above) and use two formulas using a variable. Just place the Reset formula into the Page Header section instead of the report header or group header.

This article is copyrighted by Crystalkeen, Mindconnection, and Chelsea Technologies Ltd. It may be freely copied and distributed as long as the original copyright is displayed and no modifications are made to this material. Extracts are permitted. The names Crystal Reports and Seagate Info are trademarks owned by Business Objects.