21
Thu, Nov
1 New Articles

Adding Recaps to Reports

Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

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))'))

TED HOLT

Ted Holt is IT manager of Manufacturing Systems Development for Day-Brite Capri Omega, a manufacturer of lighting fixtures in Tupelo, Mississippi. He has worked in the information processing industry since 1981 and is the author or co-author of seven books. 


MC Press books written by Ted Holt available now on the MC Press Bookstore.

Complete CL: Fifth Edition Complete CL: Fifth Edition
Become a CL guru and fully leverage the abilities of your system.
List Price $79.95

Now On Sale

Complete CL: Sixth Edition Complete CL: Sixth Edition
Now fully updated! Get the master guide to Control Language programming.
List Price $79.95

Now On Sale

IBM i5/iSeries Primer IBM i5/iSeries Primer
Check out the ultimate resource and “must-have” guide for every professional working with the i5/iSeries.
List Price $99.95

Now On Sale

Qshell for iSeries Qshell for iSeries
Check out this Unix-style shell and utilities command interface for OS/400.
List Price $79.95

Now On Sale

BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: