An Alternative to Externally Described Printer Files
April 14, 2004 Cletus the Codeslinger
[The code for this article is available for download.]
There is no shortage of ways to build a report from an iSeries database. IBM offers Query/400, Query Management, and SQL/400, but they’re all limited in one way or another. For example, Query/400 has to be told to read the library list, it has no conditional processing, it has limited calculation abilities, and passing parameters to a Query/400 query is hardly intuitive. There are many third-party query packages. Some run on the iSeries; some run on PCs. Many overcome the limitations of IBM’s query offerings.
Nonetheless, most query packages run out of steam. A Query/400 query may work fine, until a user requests that a certain calculation be done for control breaks, or that a field be printed only under certain circumstances. That’s when you have no choice but to drag out the RPG or COBOL compiler.
If, like me, you have a job and a family, and constantly feel like you’re trying to live six lives at once, you look for ways to do things quickly. In this article, I’ll share one technique that I use to make quick work of a boring but necessary job: generating reports with RPG. This technique is applicable to COBOL, as well.
A THIRD WAY
In many iSeries shops, the debate over report-creation methodologies rages. Old-timers want to use the RPG cycle and output specifications. Others don’t understand the cycle and want to use externally described printer files.
Please participate in our iSeries programming survey at http://www.surveymonkey.com/s.asp?u=74193419903 |
I have developed a third technique that doesn’t require coding O-specs or DDS or using RLU. I don’t claim it’s appropriate for all situations, but it works for many general SCS-type reports. Rather than O-specs or DDS, I define a data structure containing columns for field values.
When I use the word “column,” I don’t mean print positions, as in “there are 132 columns on a report printed on standard green-bar paper at ten characters per inch.” I use the word column to mean a group of one or more print positions in which a value is printed. This approach fits many run-of-the-mill reports. For example, a report might have eight columns, which contain company number, customer number, customer name, sales rep number, sales rep name, current receivable balance, past-due balance, and date of last payment. Here’s the data structure I would define:
D PrintLine DS 132 D Column01 4 D 1 D Column02 5 D 1 D Column03 20 D 1 D Column04 5 D 1 D Column05 18 D 1 D Column06 12 D 1 D Column07 12 D 1 D Column08 8
I initially define each column with what I estimate to be an appropriate width. I use unnamed spacer subfields to separate the columns from one another. As I develop the report, I will adjust the column widths and spacer widths as needed.
Here is what I most like about this approach to building repots. I use these columns for column headings, detail fields, and total fields. When I adjust the size of a column or the width of a spacer, I automatically keep headings, details, and totals in alignment. It beats the heck out of RLU.
To load the columns, I use EVAL for non-numeric fields and use EVALR for numeric fields. To copy a numeric value to a column, I use an appropriate edit code or edit word. The following three groups of source code lines show how I load a customer name (alpha) and current accounts receivable balance (numeric):
* load the detail line EVAL Column03 = CustomerName EVALR Column06 = %EDITC(CurBalance:'J') * load the column heading EVAL Column03 = 'Name' EVALR Column06 = 'Balance' * load the final totals EVALR Column06 = %EDITC(TotCurBalance:'J')
Using this approach makes it easy for me to:
- Increase or decrease the size of a column.
- Leave more or less space between columns.
- Reorder the columns, since I do not rename the columns when moving them around in the data structure.
- Insert new columns or remove columns.
PRINTING THINGS THAT DON’T FIT IN THE COLUMNS
Some things don’t fit the columns. The first line of a report often includes a title, a run date, a page number, and such. I load these things into the data structure by referring to the data structure directly:
EVAL PrintLine = 'Current Accounts Receivable' EVAL %SUBST(PrintLine:70) = %CHAR(SysDate:*MDY) + ' Page ' + %CHAR(PageNbr)
VERTICAL SPACING
Since there are no data definition specifications or output specifications, I use a printer control data structure to control vertical spacing:
FQSYSPRT O F 132 PRINTER OFLIND(Overflow) F PRTCTL(PrtCtlDS) D PrtCtlDS DS 15 D pcSpacing 1 12 D pcSpcBefore 1 3 D pcSpcAfter 4 6 D pcSkpBefore 7 9 D pcSkpAfter 10 12 D pcLineNbr 13 15 D D Overflow S N
Before printing a line, I load the appropriate space or skip entries. After printing a line, I clear the space and skip entries to prepare for the next printed line.
THE PRINT ROUTINE
My programs contain only one WRITE command to the printer file. Here is the subroutine that contains that command:
C Write BEGSR C C IF pcSpacing = *blanks C EVAL pcSpcBefore = '001' C ENDIF C C WRITE QSYSPRT PrintLine C CLEAR PrintLine C CLEAR pcSpacing C C ENDSR
If no spacing or skipping is selected, this routine single-spaces by default.
A SAMPLE PROGRAM
The code fragments listed above came from a sample program I put together for this article. I’ve also provided the output produced by this program from four customer records.
Permit me to make a few comments about the program.
First, I use SQL to read the input, but this technique works for native I/O as well. I like SQL because of the flexibility it offers. For example, SQL lets me sort on calculated fields, something that is difficult to do with native I/O.
Second, I have intentionally kept this example as simple as possible; therefore it does not do anything that couldn’t be done with a query program. The beauty of this technique becomes apparent when you add additional calculations to handle special circumstances.
Third, this technique also works with double lines. For example, if you want to print the customer name on the line following the one with the customer number, you can define two separate sets of columns within the data structure. In the following code, columns 1 through 7 print on one line and column 8 prints on a separate line:
D PrintLine DS 132 D Column01 4 D 1 D Column02 5 D 1 D Column03 5 D 1 D Column04 18 D 1 D Column05 12 D 1 D Column06 12 D 1 D Column07 8 D 1 D Column08 20 OVERLAY(PrintLine:6)
COBOL TECHNIQUES
COBOL programmers can use the same technique. Define a group item to contain the columns in the working-storage section. Use the MOVE verb to load the columns in the procedure division. To control spacing, use a variable after the ADVANCING phrase of the WRITE verb. I’ve provided a rough equivalent of the RPG program presented above.
You may also be interested in reading the report “Simplifying Batch Outputting in COBOL” (in PDF format), by Barbara Russell of Northwestern State University. Russell does not define columns for the data, as I do, but she does create print lines in the procedure division. Notice the comparisons she makes to “traditional” methods of report definition.
THE PROCESS
I have found this technique easy to use and productive. When I am called on to build a report, I usually begin by creating a Query/400 that builds the desired report as much as possible. I use the Retrieve QM Query (RTVQMQRY) command to build a SQL statement, which I paste into the new RPG source member. The column widths in the query become the starting definitions of the columns in the data structure. In a short time, I have a working RPG report program that I can modify and tweak as necessary.
Good, I had lost this tip… but there is not the sample program