Consultor Eletrônico

How and why of ACCUM, ACCUMULATE, aggregates NEXT LAST-OF

920626-ptg01

INTRODUCTION:
=============
This Product Services Technical Support Library Entry explains
the use of the ACCUMULATE statement, the ACCUM function, and
the AGGREGATE phrase as is defined in Version 6 of PROGRESS.

WHY YOU NEED TO DO THIS:
========================

The aggregate functions provide a useful and convenient way
to accumulate and quantify large tables of data.

These functions are particularly important when you need to
compute aggregations within groups. PROGRESS will provide
as many sub-group variables as there are groups that the
data define. This means the developer does not have to know
in advance how many variables will be needed, which would be
the case if the 3GL construct of
totcount = totcount + 1.
totprice = totprice + (qty * price).
etc. were used.


PROCEDURAL APPROACH:
====================

Aggregate Phrase

The AGGREGATE phrase defines which fields are tallied. The
AGGREGATE phrase can be one of two types, total aggregate or
sub-aggregate.

Grand aggregates relate to all records being tallied. Sub-
aggregates relate to tallies within a break group and do not
produce a tally for the complete group. All grand aggregates
have a corresponding sub-aggregate

Grand Sub
--------------- ---------------
AVERAGE SUB-AVERAGE
COUNT SUB-COUNT
MAXIMUM SUB-MAXIMUM
MINIMUM SUB-MINIMUM
TOTAL SUB-TOTAL

Examples:

Produce the greatest order value
ORDER-LINE.QTY * ORDER-LINE.PRICE (MAXIMUM)

Produce the average ratio of items on hand to items
on order
ITEM.ON-HAND / ITEM.OORDER (AVERAGE)

DISPLAY Aggregate
------------------

In an iterative (REPEAT, FOR) block, aggregates may be used
in DISPLAY statements, as well as in ACCUMULATE statements and
ACCUM function.
1. If you use PROGRESS aggregates, PROGRESS puts the
aggregrates where it wants.
2. If you use a default aggregation, PROGRESS doesn't
explicitly make the aggregation available to the programmer.
PROGRESS internally tracks the aggregate. There is no
variable to retrieve a value from.
3. If you want to retrieve aggregate information you must use
the ACCUMULATE statement explicitly.

Examples:

Display the customer number and state of each record
in the customer file and accumulate the total number
of records in that same file.

REPEAT WHILE TRUE:
FIND NEXT customer NO-LOCK NO-WAIT NO-ERROR.
IF NOT AVAILABLE customer THEN LEAVE.
DISPLAY customer.st(COUNT).
END.

Note that while the LEAVE statement precedes the
DISPLAY statement, PROGRESS will still produce a
display of the count of all records in the file.


The following example prints out information on
orders grouped by the sales representative, totaling
the number of orders that exist for representative.

FOR EACH ORDER BREAK BY ORDER.SALES-REP:
DISPLAY ORDER.ORDER-NUM
ORDER.SALES-REP
(TOTAL BY ORDER.SALES-REP).
END.


ACCUM Function
--------------

The ACCUM function returns the value of a tally previously
defined in an ACCUMULATE statement. The aggregate phrase used
in the ACCUM function must match the aggregate in the
ACCUMULATE statement exactly.

ACCUMULATE Statement
-------------------

The ACCUMULATE statement expressly designates to a Progress
procedure what fields or values to tally and how to group the
tally using the aggregate phrase.

Examples:

Tallying Records -

Determine how many records are in the customer table.
FOR EACH customer:
ACCUMULATE CUSTOMER.ST (COUNT).
END.
?
Determine how many customers are in each state.
FOR EACH customer BREAK BY st:
ACCUMULATE CUSTOMER.ST (COUNT BY ST).
IF LAST-OF(customer.st)
THEN DISPLAY ACCUM COUNT BY ST
CUSTOMER.ST.
END.
DISPLAY ACCUM COUNT CUSTOMER.ST.

Notice that in this case you must combine the COUNT BY
with a BREAK BY on the same field. You can then use the
LAST-OF function to determine the end of a break group
and print a results of the count within the break group.


Determine the dollar amount of all orders receieved on
a particular date.

FOR EACH order WHERE order.odate EQ 09/06/90 :
FOR EACH order-line OF ORDER:
ACCUMULATE
order-line.price * order-line.qty
(TOTAL)
END.
DISPLAY ACCUM order-line.proce * order-
line.qty(TOTAL)
END.


Determine the dollar amount of all orders by sales
representative.

FOR EACH order, EACH order-line OF order
BREAK BY order.sales-rep:
ACCUMULATE order-line.price * order-line.qty
(SUB-TOTAL BY order.sales-rep).
IF LAST-OF(order.sales-rep)
THEN DO:
FIND salesrep OF order no-lock.
DISPLAY order.sales-rep salesrep.slsname
salesrep.slsrgn
ACCUM SUB-TOTAL BY order.sales-rep
order-line.price * order-line.qty.
END.

Note that if you wanted to display the total of all orders
outside the FOR loop that you would have to change the
SUB-TOTAL to TOTAL, since values of sub-aggregates are
unknown outside the scope of their iterative block.

Note the syntax difference between ACCUMULATE, which COMPUTES
the value
ACCUMULATE
expression--the "WHAT"
(aggregate phrase--the "HOW",
can be followed by "BY" break-group)

and ACCUM, a function which RETURNS the computed value.

ACCUM
aggregate-phrase--the "HOW"
can be followed by "BY" break-group)
expression--the "WHAT"

(The expression, usually a data field, comes first when
computing, and comes second when retrieving the value.)

Impact of using NEXT and LAST-OF or FIRST-OF with BREAK BY
----------------------------------------------------------
There is a side-effect of using the NEXT
statement to skip processing of certain records
that can come into play if you use the IF LAST-OF
or IF FIRST-OF tests to perform conditional
processing on a data field BREAK BY group.
This is particularly evident when you
use any of the aggregate functions supported by
ACCUMULATE.

For example, suppose you want to analyze the potential impact
of dropping low-end products from your inventory, that is,
any product with a price under 5$. Run the procedure
below with and without the statement
"IF price < 5 THEN NEXT.".
You will see that, because for some of the state groups,
the last record is dropped from the analysis by the NEXT
command, the conditional processing triggered by the
IF LAST-OF and IF LAST does not get performed. To
correct this side-effect without compromising the analysis,
you need to limit the "NEXT" action to only those records
which are NOT the LAST-OF the break group, and only
ACCUMULATE only those records which ARE within the analysis
criteria.

for each customer
, each order of customer
, each order-line of order
, each item of order-line
BREAK BY customer.st WITH FRAME outer-fr
TITLE " PROBLEM CAUSED BY NEXT AND LAST-OF ":
IF price < 5 THEN NEXT.
/* SKIP ITEMS THAT ARE BELOW ANALYSIS THRESHHOLD */
ACCUMULATE (order-line.price * order-line.qty)
(TOTAL BY customer.st).
ACCUMULATE (order-line.price * order-line.qty) TOTAL.

DISPLAY
customer.st
customer.cust-num COLUMN-LABEL "Cust!Num"
order.order-num COLUMN-LABEL "Ord!Num"
order-line.item-num COLUMN-LABEL "Item!Num"
order-line.price FORMAT "$>,>>9.99"
order-line.qty
item.idesc.

IF LAST-OF(customer.st) THEN
DO:
DISPLAY
ACCUM TOTAL BY customer.st
(order-line.price * order-line.qty)
COLUMN-LABEL "State!Total".
END.
IF LAST(customer.st) THEN
DO:
DISPLAY
ACCUM TOTAL (order-line.price * order-line.qty)
COLUMN-LABEL "Grand!Total".
DOWN WITH FRAME outer-fr.
END.
END.

/**************** CORRECTED PR0CEDURE *****************/
for each customer
, each order of customer
, each order-line of order
, each item of order-line
BREAK BY customer.st WITH FRAME outer-fr2
TITLE " CORRECT USE OF NEXT AND LAST-OF ":
IF price < 5 AND NOT LAST-OF(customer.st) THEN
NEXT.
IF price >= 5 THEN
DO:
ACCUMULATE (order-line.price * order-line.qty)
(TOTAL BY customer.st).
ACCUMULATE (order-line.price * order-line.qty)
TOTAL.

DISPLAY
customer.st
customer.cust-num COLUMN-LABEL "Cust!Num"
order.order-num COLUMN-LABEL "Ord!Num"
order-line.item-num COLUMN-LABEL "Item!Num"
order-line.price FORMAT "$>,>>9.99"
order-line.qty
item.idesc.
END.
IF LAST-OF(customer.st) THEN
DO:
DISPLAY
ACCUM TOTAL BY customer.st
(order-line.price * order-line.qty)
COLUMN-LABEL "State!Total".
END.
IF LAST(customer.st) THEN
DO:
DISPLAY
ACCUM TOTAL (order-line.price * order-line.qty)
COLUMN-LABEL "Grand!Total".
DOWN WITH FRAME outer-fr2.
END.
END.

?
REFERENCES TO WRITTEN DOCUMENTATION:
====================================

Progress Language Reference, ACCUM Function

Progress Language Reference, ACCUMULATE Statement

Progress Language Reference, Aggregate Phrase

Progress Language Tutorial, Ch. 12, Writing Reports

Progress Language Reference, NEXT Statement

Progress Language Reference, LAST Function

Progress Language Reference, LAST-OF Function

Progress Language Reference, FIRST Function

Progress Language Reference, FIRST-OF Function

Progress Software Technical Support Note # 6056