Running total in Crystal Reports

Here is an email response I received directly from Seagate CR several years ago when I ask that question. You may be able to gleen something from this:

Hi Michael,

You will basically need to do Running Totals to get the kind of sum
you require. I have attached a couple of documents which explains
how to use Running Totals.

Running Totals Document 96/05/21

Product: ALL Version: ALL
Database: ALL Platform: ALL
Type: TIP Status: NEW

Problem:
Running Totals and Variables

Using Running Totals and Variables in Crystal Reports is a very
powerful and versatile feature.
Running Totals are used in situations where totals must be calculated
under conditional circumstances.

For Running Totals to work properly, variables are required.
Variables can be declared in different types:

Number Variables
String Variables
Date Variables
Currency Variables
Boolean Variables

When the same variable is used in multiple formulas, the formula must
be evaluated on Crystal's second
pass over the database. This is called controlling the Evaluation
Time. The Evaluation Time can be set
in the formula using one of the three Evaluation Time functions:

BeforeReadingRecords
WhileReadingRecords
WhilePrintingRecords

The reason for the Evaluation Time is to allow Crystal to perform any
necessary calculations first, before the variables are calculated then
have the proper values passed to the other formulas using the
variables.

A simple Running Total will consist of two formulas. The first
formula will process the records and perform the calculations and the
second formula will display the final value of the variable or the
total.

The formulas will look like:

WhilePrintingRecords;
NumberVar Counter;

If {table.FIELD} = "A" Then
Counter := Counter + 1
Else Counter := Counter;

This formula will count the number of records where the field is equal
the value "A". If the field has a value anything other than "A" then
the variable Counter will not be incremented.

This formula will need to be placed in the Details section of the
report. This will allow the formula to process each record in the
report. Because the formula will show the counter incrementing with
each record, in most cases, this detailed information is not required
to be displayed. As a result, the formula field can be formatted to
Hide When Printing.

The next formula will display the final value of the variable
Counter.

WhilePrintingRecords;
NumberVar Counter;

Counter;

Note that the variable must still be declared in this formula as well.
In order for Crystal to use variables in the formula, it must have
some reference to it. With database fields, they are referenced in
the Fields box in the top left box of the Formula Editor. Variables
are not displayed in the same box because they are set in memory.

This formula will need to be placed in the Summary section or Grand
Total section if one is inserted. What will appear will the total
number of records with a field value of "A".

In some cases a grand total is not enough. Subtotals may be required
to see a more detailed break down of the data. For this reason, we
need to format the report with a Group section and have a sub total
for each group.

An example of this would be a Sales database. You would like a break
down of all sales for each country and a grand total at the end. For
this situation, you will need to insert a Group section based on the
Country field. Crystal will sort and group the database according to
country.

We will need the use of four formulas. The first formula will declare
the variable and reset the variable to zero when the country changes.

WhilePrintingRecords;
NumberVar SubTotal := 0;

This formula will need to be placed in the Group Header section,
immediately above the Details section. This formula will display a
value of 0 each time the country changes. Though this formula is
crucial to the calculation, the value is not required to be displayed.
Simply format the field to Hide When Printing.

The next formula will calculate the subtotals and grand total.

WhilePrintingRecords;
NumberVar SubTotal;
NumberVar GrandTotal;

SubTotal := SubTotal + {company.LastYrSale};
GrandTotal := GrandTotal + {company.LastYrSale};

Because this formula will need to process each record in the database,
it should be placed in the Details section. Again this detailed
information is not usually required for viewing and can be formatted
to Hide When Printing.

The next formula will display the sub total for each country.

WhilePrintingRecords;
NumberVar SubTotal;

SubTotal;

This formula will display the final value of the SubTotal variable for
each country.
Place this formula in the Group Footer section, immediately below the
Details section. Note we are not declaring the GrandTotal variable in
this formula. If a variable is not required in a formula, it does not
need to be declared.

When a group break occurs, Crystal will begin at the Group Header
again. This will force Crystal to perform the first formula and reset
the variable SubTotal back to 0 for the next country.

The final total will display the grand total for all the records in
the report.

WhilePrintingRecords;
NumberVar GrandTotal;

GrandTotal;

Place this formula in the Summary section of the report or if a Grand
Total section has been inserted, it can be placed in there. The
reason it needs to be placed in either of these two sections is both
the Grant Total and Summary sections are only used at the end of the
report. They are not used in any other part of the report and are
used solely for the purpose of summarizing the report.

Please note how the GrandTotal variable is being calculated. Because
it is not being reset with each group as the SubTotal variable is, the
value continually increases.

c1358378

Filtered and Multiple Counts on a field

Product: ?-PR Version: ?-3.03
Database: ?-MSACCESS11 Platform: WIN3x
Type: TIP Status: GOOD

Problem:
Wish to count the number of records where an instance of a field has a
certain value eg: {file.JOB} = "Mortician"

Also wish to get separate counts for the number of times in a report
that a field is equal to each of several possible values for that
field

eg if a field that contains a bunch of codes any of which it can be
equal to (eg occupation)
{file.JOB} = P, L, B. T, M

P=physician
L=Lawyer
B=Brick Layer
T=Technical Writer
M=Mortician

Then we wish to get a total count of the total occurrences of each of
P,L,B,T,M to determine the number of physicians, lawyers, brick
layers, technical writers, and morticians.

Solution:
The following single formula will allow you place a
running-total-style count on your report to display the number of
occurrences of Brick Layers and Morticians. The formula can be
expanded to accomodate all five professions.

°°°°
numberVar Morticians;
numberVar Lawyers;
if {file.JOB} = "m" or {file.JOB}="M"
then
Morticians:=Morticians+1
else
Morticians:=Morticians;
if {file.JOB}="b" or {file.JOB}="B"
then
BrickLayer:=BrickLayer+1
else
BrickLayer:=BrickLayer;
"Morticians: " + ToText(Morticians,0) + " BrickLayers: " +
ToText(BrickLayer,0)

°°°°
The final line of the formula will display an entry on the report
similar to the following: Morticians 45 BrickLayer: 20. Note that
this formula can be extended to include virtually any number of
separate values you wish to check for and count. On a simple report,
you can place this formula in the Detail section to get a
running-total on each record value, or it can be placed in the Page
Footer or Grand Total sections to get an overall count.

c1269016

Regards,
Mary-Jane
Seagate Software.