You're walking toward your office with the fresh cup of coffee you just got from the break room. As you pass Sue's office, you notice something doesn't look right. Poking your head through the doorway, you see her looking at a computer-generated report and punching numbers into a calculator. "Just what do you think you're doing?" you ask.

She looks up at you, annoyed because she's lost her train of thought and is not sure what number she keyed in last. "I'm adding up these numbers from this report," she replies, trying to keep her composure.

You cringe. "How often do you do this?"

"Every day."

"How long does it take?"

"About 30 minutes."

Sue explains what she's doing. The report she's working from is like the one in Figure 1, but it's usually about 30 pages long. Every day, she goes through the report to find what items sold best and which products each salesman moved.

"What if that report had a summary of sales by items and also by salesman and item number?" you ask. "Would that help?"

"If you can make it do that, I'll name my next kid after you and add you to my will," she replies.

How can you refuse an offer like that?

Adding recaps to a report is easy. It requires the following:

o Creating a work file to hold summary information

o Writing CL code to load the work file

o Adding recap report lines to printer DDS specs or RPG output specs

o Adding a subroutine to the RPG program to print the recaps

First, look at the RPG program that generates the report (Figure 2.) It's typical of "legacy" software, the rock-solid, dependable applications that have helped us stay in business through the years.

Here's how you modify it to add the recaps.

You can use arrays or multiple-occurrence data structures to holdsummary data, but in my opinion, using a work file is the only way to go. Using a work file means you don't have to predict how many lines of recaps will be generated. If you use an array or multiple-occurrence data structure, you have to define the number of recap lines at compilation time.

Figure 3 contains DDS for a physical file SSALESWK. It has four fields: summary fields for quantity and extended amount (the two report columns we're summarizing) and two fields- RTITLE and RVALUE-to describe the summarized data. RTITLE will hold a text description of the data, and RVALUE will hold the data values from the summarized field(s). That is, if you summarize by item number, RVALUE will contain item numbers. If you summarize by customer number, RVALUE will have customer numbers.

Use Open Query File (OPNQRYF) and Copy From Query File (CPYFRMQRYF) to load the summary file. It's effective and saves you the trouble of writing RPG programs to do it.

Your new CL program is in Figure 4. First, you copy the input file (in this case, a logical file called SSALES01) into a physical file in QTEMP. This prevents the recaps from getting out of sync with the data if someone modifies the sales file while this program is running.

Next, make a duplicate copy of the work file (the one that was created in Step 1) in QTEMP. This allows two or more people to run this program at the same time.

Notice that this code is designed to work whether the two work files are already in QTEMP or not.

These precautions might be unnecessary. Maybe this program runs only at night, when no one is on the system. Maybe only one copy will ever run at a time. Maybe this program will always run by itself in a batch job, so QTEMP will always be empty. I don't try to second-guess these types of things.

Now, start building recaps.

Each recap requires an OPNQRYF command to load the four fields in the summary file created in Step 1. In RTITLE, I put the value ITEM. This serves two purposes. First, it distinguishes the recaps, which are all stored in the same file, from one other. Second, it is printed in the page header of the report to inform the user how the data is summarized.

In RVALUE, place the value of the summary field(s). Since ITEMNO is character data, you can

map it directly. For numeric fields, you have to use the %DIGITS function. The second OPNQRYF command loads one numeric field (sales rep number), a blank, and an item number into RVALUE.

The rest of the fields in the summary file are figures from the report that are being summarized. In this case, there are two-the number of items sold and the extended price of each item (i.e., quantity sold times the unit price).

CPYFRMQRYF adds the results generated by each OPNQRYF to the summary file.

A really nice feature of this approach is the ease of adding more recaps. If you decide you need a recap by customer number, you only need to add another OPNQRYF-CPYFRMQRYF-CLOF combination. You would do that by copying one of the existing recaps and modifying the copied OPNQRYF command. The only modifications you have to make to it are to the definitions of RTITLE and RVALUE. You don't have to create more work files. You don't have to modify the RPG program. Once this application has been fitted for recaps, you can add another recap in less than five minutes.

With the recapped data loaded into the summary file, you can call the RPG program. Notice that the files are overridden to point to the ones in QTEMP.

The modified RPG program is shown in Figure 5. The first change that has been made to this program is to control output specs with exception output. You need to print final totals before beginning the recap routine, but you won't be able to unless you force the L1 and LR total lines to print during total calculations instead of at total output time.

Next, add the recap lines to the end of the output specs. The lines with the exception name RCPHD1 are headings that print before each recap starts or at page overflow. RCPDTL will print for each recap record.

The other change is to add the RECAP subroutine and force it to print after the original report has finished (see Figure 6). The RECAP subroutine reads the summary file from top to bottom, in order of RTITLE and RVALUE, checking for a control break on RTITLE. Each time it finds another value, it begins to print another recap.

You might find it useful to allow users to select the recaps they want at runtime. I've never done that in production work, but, in case you're interested, here's what you do.

You need to replace the definitions for the mapped fields RTITLE and RVALUE at runtime. There are probably several good ways to do that. I've included some fragments of CL code in Figure 7 to show you how to define the RTITLE and RVALUE mapped fields. The user can enter a word into parameter &RECAPFLD. If he enters CUSTOMER, we assign the quoted value CUSTOMER to variable &PTITLE and the expression %DIGITS(CUSNO) to &PVALUE. These CL variables are then used in the OPNQRYF command, instead of hard- coded values like those in Figure 4.

Sue is ecstatic over the change you've made to her sales report. Nevertheless, six months later, Sue gives birth to a baby boy and names him Heber Charlemagne. "Oh well," you muse, "she probably didn't keep her word about her will either." No matter. You've made your business run

a little more efficiently, and you've helped someone. Isn't it a great feeling?

Ted Holt is a technical editor for Midrange Computing and a consultant living in Corinth, Mississippi. He welcomes your feedback. You can contact him by email at This email address is being protected from spambots. You need JavaScript enabled to view it..

OS/400 CL Reference Commands - DATA through RPLxxx V3R7 (SC41-4725, CD-ROM QBJAU401)

Figure 1: The report the user keys from

SALES REPORT 1/02/98 PAGE 1

CUS ORD REP ITEM QTY PRICE EXTEND

182 003 2 J28 1 10.00 10.00
182 004 2 A44 10 8.00 80.00

182 TOTAL 90.00 *

221 002 3 A44 4 7.75 31.00
221 007 1 J28 2 5.00 10.00

221 TOTAL 41.00 *

384 005 1 J70 5 2.00 10.00
384 006 1 J28 2 11.00 22.00

384 TOTAL 32.00 *

390 008 3 A44 6 7.00 42.00

390 TOTAL 42.00 *

408 001 2 A44 3 7.50 22.50

408 TOTAL 22.50 *

GRAND TOTAL 227.50 ** FSSALES01IP E K DISK
FQSYSPRT O F 132 OF PRINTER
ISALESREC 01
I CUSNO L1
C L1 MOVE *ZERO XAMT01
C*
C QTY MULT UPRICE XPRICE
C ADD XPRICE XAMT01
C*
CL1 ADD XAMT01 XAMTGT
C*
C *LIKE DEFN UPRICE XPRICE+ 3
C *LIKE DEFN XPRICE XAMT01
C *LIKE DEFN XPRICE XAMTGT
OQSYSPRT H 203 1P
OOR OF
O 'SALES REPORT'

O UDATE Y 64
O 72 'PAGE'
OPAGE
OH 21P
OOR OF
O 'CUS ORD REP ITEM'
O 58 'QTY PRICE'

O 71 'EXTEND'

Figure 2: The RPG program that builds the sales report

OD 101
O CUSNO
O ORDRNO + 2
O SLSNO + 2
O ITEMNO + 2
O QTYM50
O UPRICEM + 2
O XPRICEJ + 2
OT 12L1
O CUSNO
O + 1 'TOTAL'
O XAMT01J 72
O+ 1 '*'
OT 1LR
O 'GRAND TOTAL'
O XAMTGTJ 72
O+ 1 '** A REF(SSALES)
A R WORKREC
A RTITLE 40
A RVALUE 40
A RQTY R +2 REFFLD(QTY)
A RAMT R +3 REFFLD(UPRICE)
A K RTITLE
A K RVALUE

Figure 4: The CL program builds the recaps and drives
the RPG program

PGM

DCL VAR(&LIB) TYPE(*CHAR) LEN(10)

MONMSG MSGID(CPF0000) EXEC(GOTO CMDLBL(ERROR_RTN))

/* Copy the data */
CPYF FROMFILE(SSALES01) TOFILE(QTEMP/SALESTEMP) +

MBROPT(*REPLACE) CRTFILE(*YES)

/* Create the work file in QTEMP */
CLRPFM FILE(QTEMP/SSALESWK)
MONMSG CPF3142 EXEC(DO)

RTVOBJD OBJ(SSALESWK) OBJTYPE(*FILE) RTNLIB(&LIB)
CRTDUPOBJ OBJ(SSALESWK) FROMLIB(&LIB) OBJTYPE(*FILE) +

TOLIB(QTEMP)
ENDDO

/* Summarize by item */
OPNQRYF FILE((QTEMP/SALESTEMP)) FORMAT(SSALESWK) +

KEYFLD((RTITLE) (RVALUE)) GRPFLD(RVALUE) +
MAPFLD((RTITLE '"ITEM"') (RVALUE +
'ITEMNO') (RQTY '%SUM(QTY)') (RAMT +
'%SUM((QTY * UPRICE))'))

CPYFRMQRYF FROMOPNID(SALESTEMP) TOFILE(QTEMP/SSALESWK) +

MBROPT(*ADD) FMTOPT(*MAP)
CLOF OPNID(SALESTEMP)

/* Summarize by sales rep & item */
OPNQRYF FILE((QTEMP/SALESTEMP)) FORMAT(SSALESWK) +

KEYFLD((RTITLE) (RVALUE)) GRPFLD(RVALUE) +
MAPFLD((RTITLE '"SALES REP & ITEM"') +
(RVALUE'%DIGITS(SLSNO)*CAT""*CAT+
ITEMNO') (RQTY '%SUM(QTY)') (RAMT +
'%SUM((QTY * UPRICE))'))

CPYFRMQRYF FROMOPNID(SALESTEMP) TOFILE(QTEMP/SSALESWK) +

MBROPT(*ADD) FMTOPT(*MAP)
CLOF OPNID(SALESTEMP)

Figure 3: This DDS defines the work file used to store
recapped data.

/* Print the report */
OVRDBF FILE(SSALES01) TOFILE(QTEMP/SALESTEMP)
OVRDBF FILE(SSALESWK) TOFILE(QTEMP/SSALESWK)
CALL PGM(SSALES2RG)

DLTOVR FILE(SSALES01 SSALESWK)
RETURN

ERROR_RTN:
... error routine goes here
RETURN
ENDPGM FSSALES01IP E K DISK
FSSALESWKIF E K DISK
FQSYSPRT O F 132 OF PRINTER
ISALESREC 01
I CUSNO L1
C L1 MOVE *ZERO XAMT01
C*
C QTY MULT UPRICE XPRICE
C ADD XPRICE XAMT01
C *INOF IFEQ *ON
C EXCPTPAGHD1
C EXCPTPAGHD2
C MOVE *OFF *INOF
C ENDIF
C EXCPTDTL
C*
CL1 ADD XAMT01 XAMTGT
CL1 EXCPTTOTLN1
C*
CLR EXCPTTOTLNR
CLR EXSR RECAP
C***********
C RECAP BEGSR
C*
C MOVE *LOVAL STITLE
C READ WORKREC 91
C *IN91 DOWEQ*OFF
C RTITLE IFNE STITLE
C *INOF OREQ *ON
C EXCPTRCPHD1
C MOVE RTITLE STITLE
C *LIKE DEFN RTITLE STITLE
C MOVE *OFF *INOF
C ENDIF
C XAMTGT IFNE *ZERO
C RAMT DIV XAMTGT WORK1 43H
C MOVE WORK1 RPCT 41
C ENDIF
C EXCPTRCPDTL
C READ WORKREC 91
C ENDDO
C*
C ENDSR
C***********
C *INZSR BEGSR
C*
C EXCPTPAGHD1
C EXCPTPAGHD2
C*
C ENDSR
C***********
C *LIKE DEFN UPRICE XPRICE+ 3
C *LIKE DEFN XPRICE XAMT01
C *LIKE DEFN XPRICE XAMTGT
OQSYSPRT E 203 PAGHD1
O 'SALES REPORT'

O UDATE Y 64
O 72 'PAGE'
OPAGE

Figure 5: The RPG program has been modified to produce
recaps.

O E 2 PAGHD2
O 'CUS ORD REP ITEM'
O 58 'QTY PRICE'

O 71 'EXTEND'
OE 1 DTL
O CUSNO
O ORDRNO + 2
O SLSNO + 2
O ITEMNO + 2
O QTYM50
O UPRICEM + 2
O XPRICEJ + 2
O E 12 TOTLN1
O CUSNO
O + 1 'TOTAL'
O XAMT01J 72
O+ 1 '*'

O E 1 TOTLNR
O 'GRAND TOTAL'
O XAMTGTJ 72
O+ 1 '**'

O E 203 RCPHD1
O 'SALES REPORT'
O + 2 'RECAP BY'

O RTITLE + 1
O UDATE Y 64
O 72 'PAGE'
OPAGE
O E 2 RCPHD1
O 49 'QTY'
O 71 'EXTEND'
O 78 'PCT'

O E 1 RCPDTL
O RVALUE
ORQTY M 50
ORAMT J 72
ORPCT J 79 SALES REPORT RECAP BY ITEM 1/02/98 PAGE 2

QTY EXTEND PCT

A44 23 175.50 77.1
J28 5 42.00 18.5
J70 5 10.00 4.4

SALES REPORT RECAP BY SALES REP & ITEM 1/02/98 PAGE 3

QTY EXTEND PCT

1 J28 4 32.00 14.1
1 J70 5 10.00 4.4
2 A44 13 102.50 45.1
2 J28 1 10.00 4.4
3 A44 10 73.00 32.1 PGM PARM(&RECAPFLD)

DCL VAR(&RECAPFLD) TYPE(*CHAR) LEN(10)
DCL VAR(&PTITLE) TYPE(*CHAR) LEN(40)
DCL VAR(&PVALUE) TYPE(*CHAR) LEN(40)

IF (&RECAPFLD *EQ 'CUSTOMER') DO

CHGVAR VAR(&PTITLE) VALUE('"CUSTOMER"')
CHGVAR VAR(&PVALUE) VALUE('%DIGITS(CUSNO)')
ENDDO

Figure 6: The recaps that print at the end of the
report

Figure 7: Selecting recaps at runtime

OPNQRYF FILE((QTEMP/SALESWORK)) FORMAT(SSALESWK) +

KEYFLD((RTITLE) (RVALUE)) GRPFLD(RVALUE) +
MAPFLD((RTITLE &PTITLE) (RVALUE &PVALUE) +
(RQTY '%SUM(QTY)') (RAMT '%SUM((QTY * +
UPRICE))'))