Category : Dbase (Clipper, FoxBase, etc) Languages Source Code
Archive   : TN9101.ZIP
Filename : SUMSUMM.TXT

This article is reprinted from the January 1991 edition of
TechNotes/dBASE IV. Due to the limitations of this media, certain
graphic elements such as screen shots, illustrations and some tables
have been omitted. Where possible, reference to such items has been
deleted. As a result, continuity may be compromised.

TechNotes is a monthly publication from the Ashton-Tate Software
Support Center. For subscription information, call 800-545-9364.

Summed Summaries
Joel Saltzman

The normal function of summary fields in dBASE IV reports is to
aggregate data from individual records in a database or view.
However, there can be times when one would like to summarize group
summary information. Consider these two examples: a sales manager who
wants to average the number of separate sales for each salesperson and
a production foreman who wants to see the work group that has produced
the most units. At first look, the report generator appears that it
would have a difficult time producing the summary of group summaries.
But it can be done, without any modification to the REPORT.COD
template file or the dBASE IV source code file (the name of your
report with the .FRG extension).

1. First you need to create a grouped report which contains a
summary field in the group summary band. The summary field must have
a name (you are allowed to have unnamed summary and calculated fields)
and may be either hidden or visible.

2. Create a hidden calculated field in the group summary band.
The caclulated field must have a unique name. The expression for this
caclulated field will be the name of the summary field created in step
1.

3. In the Report summary band, create a summary field. The field
to summarize on will be the hidden calculated field defined in step 2.

Now we can apply this method to our examples. For the first scenario,
the sales manager report, index the database by salesperson. Each
record in the database represents one sale. Group the report by
salesperson. In the Group Summary band, create a summary field with
the operation of "COUNT". For this example, the field is given the
name "CNTSALE1".

Next, create a hidden calculated field by the name of "CNTSALE2" in
the Group Summary band. The expression for this new field will be
CNTSALE1, which contains the number of sales for a salesperson. In
the report summary band, create a summary field with the "AVERAGE"
operation. The field to summarize on will be CNTSALE2, the hidden
calculated field from the group summary band.

The second scenario would involve first indexing the database by a
field such as the work group code shown in the simple database
structure below.

Field Name Type Length Decimals Index


WORK_GROUP C 15
Y
PRODUCTION N 10 2
N
...

The report should be grouped by this indexed field. A summary field,
"SUMGRP1", will be created in the Ggroup Summary band with the "SUM"
operation. The next step is to create "SUMGRP2", a hidden calculated
field also in the Group Summary band. The expression for this field
will be SUMGRP1, which represents the total production for a work
group. We now move to the Report Summary band. You can now create a
summary field, using the "MAX" operation to find the greatest of each
work group's total production. Use SUMGRP2 as the field to summarize
on.

This is just another example of the wonderful uses of calculated
fields in reports to accomplish difficult tasks. With a little
applied innovation, there are undoubtedly many tasks you can handle.
s