11
Fri, Oct
6 New Articles

Flexible Reporting with Open Query File

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

OPNQRYF can help you adapt reports to the needs of many users.

by Ted Holt

It's very common around my shop for a user to call the data processing department and say something along the lines of, "I really like the report Joe Smith gets. I'd like to get the same report, except that I only need to see figures for department 4, and I'd like to have it sorted by date instead of customer number." Chances are, that sounds familiar to you. If you read my article in last month's issue, you should not be surprised when I say that such requests are often easily handled by the OPNQRYF command. The techniques we'll discuss this month combined with what we talked about last month will help you write applications which are flexible and easily adapted to the needs of different users.

More About Record Selection

We learned last month that a high-level language (HLL) program which accesses data through the open data path (ODP) created by OPNQRYF, does not need to and should not select records to be processed. Instead, OPNQRYF should be given that task, since it can select the records much more quickly. Record selection is coded in a free-format string in the QRYSLT parameter. The HLL program is coded as if it will process the entire file.

In the following paragraphs we'll look at some functions and an operator which increase the power of and simplify coding of the QRYSLT parameter. These examples will require the use of four database files: CUSMAST (customer master), SLSHIST (sales history), ITMMAST (item master), and REPMAST (sales rep master). These files are described in 1.

In the following paragraphs we'll look at some functions and an operator which increase the power of and simplify coding of the QRYSLT parameter. These examples will require the use of four database files: CUSMAST (customer master), SLSHIST (sales history), ITMMAST (item master), and REPMAST (sales rep master). These files are described in Figure 1.

Because it is very common (in our imaginary sales system) for information from the sales history and the customer master to be combined based on a common customer number, we will define a join logical file over these two files. This will give us a format to reference in our HLL programs, and will eliminate the need to code join criteria in OPNQRYF commands.

It is possible (though not likely) that an erroneous customer number may find its way into the sales history file, so let's make SLSHIST the primary file and add the JDFTVAL keyword to the DDS. JDFTVAL tells OS/400 that any SLSHIST record which has no corresponding CUSMAST record will be joined to a dummy CUSMAST record made up of default values (usually blanks for character fields and zeros for numeric ones). This type of join is called a left-outer-join.

2 shows the DDS for the join file, which we have named CUSHIST. We have given it a key so we'll be able to code the file as a keyed file in HLL programs which will use the file. The actual key field(s) will be specified at run-time in the KEYFLD parameter of OPNQRYF. (I will cover more detail on join files in a follow-up article next month. In the meantime, you can refer to the Data Base Guide, SC21-9620.)

Figure 2 shows the DDS for the join file, which we have named CUSHIST. We have given it a key so we'll be able to code the file as a keyed file in HLL programs which will use the file. The actual key field(s) will be specified at run-time in the KEYFLD parameter of OPNQRYF. (I will cover more detail on join files in a follow-up article next month. In the meantime, you can refer to the Data Base Guide, SC21-9620.)

If we wish to list sales for one day, we can use the *EQ operator (e.g., QRYSLT('SLSDAT *EQ 900401')) to select only that day's sales. For a monthly report, we could code QRYSLT('SLSDAT *GE 900401 *AND SLSDAT *LE 900430'), but the %RANGE function is simpler. CL program SLSC1A, shown in 3, demonstrates the use of this function. If parameter &MONTH_YEAR is passed the value '0490', the QRYSLT parameter re-solves to 'SLSDAT *EQ %RANGE(900400 900499)', selecting only April 1990 sales. Other CL programs could build weekly, quarterly, or yearly reports in the same manner.

If we wish to list sales for one day, we can use the *EQ operator (e.g., QRYSLT('SLSDAT *EQ 900401')) to select only that day's sales. For a monthly report, we could code QRYSLT('SLSDAT *GE 900401 *AND SLSDAT *LE 900430'), but the %RANGE function is simpler. CL program SLSC1A, shown in Figure 3, demonstrates the use of this function. If parameter &MONTH_YEAR is passed the value '0490', the QRYSLT parameter re-solves to 'SLSDAT *EQ %RANGE(900400 900499)', selecting only April 1990 sales. Other CL programs could build weekly, quarterly, or yearly reports in the same manner.

Since there is no way to predict at compilation time which sales records will be selected at run time, a 40-byte character parameter (&SUBTITLE) is used to pass a report title to the CALLed report program SLSR1.

Another powerful function is %VALUES, which takes the place of multiple *OR's. To produce a report of our largest-selling items, we could code QRYSLT('ITMNBR *EQ 30002 *OR ITMNBR *EQ 30014 *OR ITMNBR *EQ 30017'), but that's rather cumbersome. The %VALUES function takes one or more arguments, and any record containing any of the specified values will be selected. Take a look at the QRYSLT parameter of program SLSC1B shown in 4 for an illustration.

Another powerful function is %VALUES, which takes the place of multiple *OR's. To produce a report of our largest-selling items, we could code QRYSLT('ITMNBR *EQ 30002 *OR ITMNBR *EQ 30014 *OR ITMNBR *EQ 30017'), but that's rather cumbersome. The %VALUES function takes one or more arguments, and any record containing any of the specified values will be selected. Take a look at the QRYSLT parameter of program SLSC1B shown in Figure 4 for an illustration.

You can improve string searches by using the *CT (contains) operator. Unlike *EQ, which selects just the records with exactly matching values, *CT allows to you match records on a substring of a field. Program SLSC2 in 5 demonstrates the power of *CT. The CL statement CALL SLSC2 'WIDGET' would generate QRYSLT('ITMDES *CT "WIDGET"'), and would retrieve records whose descriptions were '1/2 INCH WIDGET', 'LEFT-HANDED WIDGET- PULLER', and 'WIDGET KIT'.

You can improve string searches by using the *CT (contains) operator. Unlike *EQ, which selects just the records with exactly matching values, *CT allows to you match records on a substring of a field. Program SLSC2 in Figure 5 demonstrates the power of *CT. The CL statement CALL SLSC2 'WIDGET' would generate QRYSLT('ITMDES *CT "WIDGET"'), and would retrieve records whose descriptions were '1/2 INCH WIDGET', 'LEFT-HANDED WIDGET- PULLER', and 'WIDGET KIT'.

The *CT operator, like the relational operators *EQ or *GT, is case- sensitive. If we wish to ignore cases, we have the %XLATE function, which will translate from one character set to another. We could use table QSYSTRNTBL to translate both the search string and the target field to upper case before the comparison is made. 6 shows the modifed QRYSLT parameter.

The *CT operator, like the relational operators *EQ or *GT, is case- sensitive. If we wish to ignore cases, we have the %XLATE function, which will translate from one character set to another. We could use table QSYSTRNTBL to translate both the search string and the target field to upper case before the comparison is made. Figure 6 shows the modifed QRYSLT parameter.

By the way, the QRYSLT parameter accepts both variable and expressions. Using a variable is almost always better, because you can build more complex selection criteria,and most importantly, you can see the resultant QRYSLT expression in a program dump if your OPNQRYF command terminates abnormally.

So far we've considered record selection based on one file. One nice thing about join files, however, is that we can select records based on criteria from more than one file. Program SLSC5 in 7 joins four files, SLSHIST, CUSMAST, ITMMAST, and REPMAST, with an inner join, and selects joined records for processing if they fall within a certain period of time (determined by data from SLSHIST) and pertain to a certain sales representative (determined by data from CUSMAST). We have added some flexibility by ignoring the sales rep selection when parameter &REP_NBR is zero. This allows us to print a report for a single sales rep or for all sales reps.

So far we've considered record selection based on one file. One nice thing about join files, however, is that we can select records based on criteria from more than one file. Program SLSC5 in Figure 7 joins four files, SLSHIST, CUSMAST, ITMMAST, and REPMAST, with an inner join, and selects joined records for processing if they fall within a certain period of time (determined by data from SLSHIST) and pertain to a certain sales representative (determined by data from CUSMAST). We have added some flexibility by ignoring the sales rep selection when parameter &REP_NBR is zero. This allows us to print a report for a single sales rep or for all sales reps.

We can also select records which have no match in another file. Program SLSC6 (8) prints a report showing customers who have no sales in the sales history file. The JDFTVAL parameter has a value of *ONLYDFT, which means that only primary file records with no matching secondary records will be selected. The HLL program which it calls is a simple "read-a- record print-a-line" report program in which CUSMAST is the input primary file. It can be called by other CL programs to print customer master listings of all types (e.g., all customers, or customers for a certain sales representative).

We can also select records which have no match in another file. Program SLSC6 (Figure 8) prints a report showing customers who have no sales in the sales history file. The JDFTVAL parameter has a value of *ONLYDFT, which means that only primary file records with no matching secondary records will be selected. The HLL program which it calls is a simple "read-a- record print-a-line" report program in which CUSMAST is the input primary file. It can be called by other CL programs to print customer master listings of all types (e.g., all customers, or customers for a certain sales representative).

Grouping Functions

The techniques discussed above work with one record at a time. Grouping functions, on the other hand, are designed to return results gathered from a group of records, which may be the entire file or only the records which share a common field value.

Two very simple functions are %COUNT, which returns the number of records in a group, and %SUM, which returns the sum of the values in a numeric field. Let's look at program SLSC3A in 9 to get an idea how to use them.

Two very simple functions are %COUNT, which returns the number of records in a group, and %SUM, which returns the sum of the values in a numeric field. Let's look at program SLSC3A in Figure 9 to get an idea how to use them.

The GRPFLD (group field) parameter tells OPNQRYF by which fields to summarize. In this case, records will be summarized by customer number. The MAPFLD (map field) parameter tells how the fields are to be calculated. CUSSCT will contain the number of records found for each customer. CUSSLS will accumulate the extended sales for a customer. OPNQRYF will send to the HLL program one record for each distinct customer number in the SLSHIST file.

Since no physical file contains these summary figures, we must create a dummy shell file, CUSSUM, to define the customer number, count, and sum fields to HLL program SLSR3, shown in 10. All such dummy files can be compiled with the MBR(*none) option, because they contain no data. Specifying the CUSSUM file in the FORMAT parameter of the OPNQRYF command will cause OPNQRYF to pass the data to the HLL program in the correct format.

Since no physical file contains these summary figures, we must create a dummy shell file, CUSSUM, to define the customer number, count, and sum fields to HLL program SLSR3, shown in Figure 10. All such dummy files can be compiled with the MBR(*none) option, because they contain no data. Specifying the CUSSUM file in the FORMAT parameter of the OPNQRYF command will cause OPNQRYF to pass the data to the HLL program in the correct format.

The QRYSLT parameter may still be used in summary queries. Any records not matching the QRYSLT criteria will not be included in the summary totals.

If we wish to omit certain summary records on the basis of summary figures, we can do so with the GRPSLT (group select) parameter. Program SLSC3B in 11 includes the parameter GRPSLT('CUSSLS *GE 2000'). OPNQRYF will now omit any customers who did not buy at least $2,000 of merchandise.

If we wish to omit certain summary records on the basis of summary figures, we can do so with the GRPSLT (group select) parameter. Program SLSC3B in Figure 11 includes the parameter GRPSLT('CUSSLS *GE 2000'). OPNQRYF will now omit any customers who did not buy at least $2,000 of merchandise.

The CL program in 11 calls a program that produces a summary report, printing one line for each customer summary record it reads. However, the summary functions do not have to be used only in summary reports.

The CL program in Figure 11 calls a program that produces a summary report, printing one line for each customer summary record it reads. However, the summary functions do not have to be used only in summary reports.

The CL program in 12 and the program it calls produce a detailed sales report with features not available to HLL programs. Since no GRPFLD parameter is specified, only one record, containing the average sale amount, the highest sale, the lowest sale, and the sum of all sales, will be sent to the report program (not listed in this article), which will retrieve it on the first cycle. Each detail record in the file can be compared to these figures, so that the report can flag the highest sale, lowest sale, and above-average sales, as well as percentages of the total sales.

The CL program in Figure 12 and the program it calls produce a detailed sales report with features not available to HLL programs. Since no GRPFLD parameter is specified, only one record, containing the average sale amount, the highest sale, the lowest sale, and the sum of all sales, will be sent to the report program (not listed in this article), which will retrieve it on the first cycle. Each detail record in the file can be compared to these figures, so that the report can flag the highest sale, lowest sale, and above-average sales, as well as percentages of the total sales.

I don't know how things are in your shop, but in my shop we don't have time to write a dozen reports which show basically the same information with slight variations. The detail selection and group selection capabilities, combined with the dynamic sorting ability of OPNQRYF, are so powerful that I can often satisfy everyone's wishes with a generic RPG program called by one or more CL programs which use OPNQRYF.

You may come across some pleasant surprises while using these techniques. For instance, I took the record selection logic out of an RPG program and put it into an OPNQRYF command. Run time went from 2 hours and 15 minutes to 35 seconds. I can't guarantee that you'll get results like that, but you'll never know until you try, will you?


Flexible Reporting with Open Query File

Figure 1 DDS specifications for sample files

 Figure 1: Customer Master, Sales History, Item Master, and Sales Representative Master DDS Specifications A* CUSTOMER MASTER FILE CUSMAST A A UNIQUE A R CUSMASTR A CUSNBR 4 TEXT('CUSTOMER NUMBER') A CUSNAM 15 TEXT('CUSTOMER NAME') A CUSADD 25 TEXT('ADDRESS') A CUSCTY 15 TEXT('CITY') A CUSSTT 2 TEXT('STATE') A CUSZIP 10 TEXT('CUSTOMER ZIP') A CUSCLS 1 0 TEXT('CUSTOMER CLASS') A REPNBR R REFFLD(REPNBR REPMAST) A K CUSNBR A* SALES HISTORY FILE A* A R SLSHISTR A ORDNBR 6S 0 TEXT('ORDER NUMBER') A CUSNBR R REFFLD(CUSNBR CUSMAST) A SLSDAT 6S 0 TEXT('DATE OF SALE') A ITMNBR R REFFLD(ITMNBR ITMMAST) A QTY 7 0 TEXT('QUANTITY') A UPRICE 9 2 TEXT('UNIT PRICE') A* ITEM MASTER FILE A UNIQUE A R ITMMASTR A ITMNBR 5 0 TEXT('ITEM NUMBER') A ITMDES 30 TEXT('ITEM DESCRIPTION') A K ITMNBR A* SALES REPRESENTATIVE MASTER FILE REPMAST A A UNIQUE A R REPMASTR A REPNBR 3 0 TEXT('SALES REP NUMBER') A REPNAM 15 TEXT('SALES REP NAME') A REPCOM 3 3 TEXT('COMMISSION RATE') A K REPNBR 
Flexible Reporting with Open Query File

Figure 10 File format for sales summary report

 Figure 10: File format for sales summary report A* SHELL FILE FOR SALES SUMMARY BY CUSTOMER A* MAY BE COMPILED MBR(*NONE) A R CUSSUMR A CUSNBR R REFFLD(CUSNBR CUSMAST) A CUSSLS 11 2 TEXT('TOTAL AMT SOLD TO CUST') A CUSSCT 5 0 TEXT('NUMBER OF SALES TO CUST') A K CUSNBR 
Flexible Reporting with Open Query File

Figure 11 CL program SLSC3B - summary sales, major customers

 Figure 11: SLSC3B Summary Sales Report of Major Customers CL Program PGM PARM(&MONTH_YEAR) DCL &MONTH_YEAR *CHAR 4 DCL &YEAR_MONTH *CHAR 4 DCL &YEAR *CHAR 2 DCL &MONTH *CHAR 2 DCL &QRYSLT *CHAR 256 DCL &SUBTITLE *CHAR 40 CHGVAR &MONTH %SST(&MONTH_YEAR 1 2) CHGVAR &YEAR %SST(&MONTH_YEAR 3 2) CHGVAR &YEAR_MONTH (&YEAR *CAT &MONTH) CHGVAR &QRYSLT ('SLSDAT *EQ %RANGE(' *CAT + &YEAR_MONTH *CAT '00' *BCAT + &YEAR_MONTH *CAT '99)') CHGVAR VAR(&SUBTITLE) VALUE('OF MAJOR CUSTOMERS FOR + MONTH OF' *BCAT &MONTH *CAT '/' *CAT &YEAR) OVRDBF FILE(CUSSUM) TOFILE(SLSHIST) SHARE(*YES) OPNQRYF FILE((SLSHIST)) + FORMAT(CUSSUM) + QRYSLT(&QRYSLT) + KEYFLD((CUSSLS *DESCEND)) + GRPFLD(CUSNBR) + GRPSLT('CUSSLS *GE 2000') + MAPFLD((EXTPRICE 'QTY * UPRICE') + (CUSSCT '%COUNT') + (CUSSLS '%SUM(EXTPRICE)')) CALL PGM(SLSR3) PARM(&SUBTITLE) CLOF OPNID(SLSHIST) DLTOVR FILE(CUSSUM) ENDPGM 
Flexible Reporting with Open Query File

Figure 12 CL program SLSC4 - sales report with details

 Figure 12: SLSC4 Sales Report with Detail Figures CL Program PGM DCL VAR(&PARM) TYPE(*CHAR) LEN(40) VALUE(' -- + ALL SALES') OVRDBF FILE(SUMFIGS) TOFILE(SLSHIST) SHARE(*YES) OPNQRYF FILE((SLSHIST)) FORMAT(SUMFIGS) + MAPFLD((EXTPRICE 'QTY * UPRICE' *DEC 11 2) + (TOTSAL '%SUM(EXTPRICE)') + (AVGSAL '%AVG(EXTPRICE)') + (MINSAL '%MIN(EXTPRICE)') + (MAXSAL '%MAX(EXTPRICE)')) CALL PGM(SLSR4) PARM(&PARM) CLOF OPNID(SLSHIST) DLTOVR FILE(SUMFIGS) ENDPGM 
Flexible Reporting with Open Query File

Figure 2 The Join logical file

 Figure 2: The Join Logical File A* JOIN SALES HISTORY FILE & CUSTOMER MASTER A A JDFTVAL A R CUSHIST JFILE(SLSHIST CUSMAST) A J JOIN(SLSHIST CUSMAST) A JFLD(CUSNBR CUSNBR) A CUSNBR JREF(1) A CUSNAM A CUSADD A CUSSTT A CUSZIP A CUSCLS A ORDNBR A SLSDAT A ITMNBR A QTY A UPRICE A K ORDNBR 
Flexible Reporting with Open Query File

Figure 3 CL program SLSC1A - Monthly Sales Report

 Figure 3: SLSC1A Monthly Sales Report CL Program PGM PARM(&MONTH_YEAR) DCL &MONTH_YEAR *CHAR 4 DCL &YEAR_MONTH *CHAR 4 DCL &MONTH *CHAR 2 DCL &YEAR *CHAR 2 DCL &QRYSLT *CHAR 256 DCL &SUBTITLE *CHAR 40 CHGVAR &MONTH %SST(&MONTH_YEAR 1 2) CHGVAR &YEAR %SST(&MONTH_YEAR 3 2) CHGVAR &YEAR_MONTH (&YEAR *CAT &MONTH) CHGVAR &QRYSLT ('SLSDAT *EQ %RANGE(' *CAT + &YEAR_MONTH *CAT '00' *BCAT + &YEAR_MONTH *CAT '99)') CHGVAR &SUBTITLE ('FOR MONTH OF' *BCAT &MONTH + *CAT '/' *CAT &YEAR) OVRDBF FILE(CUSHIST) SHARE(*YES) OPNQRYF FILE((CUSHIST)) + QRYSLT(&QRYSLT) + KEYFLD((ORDNBR)) CALL PGM(SLSR1) PARM(&SUBTITLE) CLOF OPNID(CUSHIST) DLTOVR FILE(CUSHIST) ENDPGM 
Flexible Reporting with Open Query File

Figure 4 CL program SLSC1B - Largest selling items

 Figure 4: SLSC1B Largest Selling Items Report CL Program PGM DCL &SUBTITLE *CHAR 40 CHGVAR &SUBTITLE ('FOR LARGEST-SELLING ITEMS') OVRDBF FILE(CUSHIST) SHARE(*YES) OPNQRYF FILE((CUSHIST)) + QRYSLT('ITMNBR *EQ + %VALUES(30002 30014 30017)') + KEYFLD((ITMNBR)) CALL PGM(SLSR1) PARM(&SUBTITLE) CLOF OPNID(CUSHIST) DLTOVR FILE(CUSHIST) ENDPGM 
Flexible Reporting with Open Query File

Figure 5 CL program SLSC2 - search on item description

 Figure 5: SLSC2 Search on Item Description CL Program PGM PARM(&SEARCHSTRG) DCL VAR(&SEARCHSTRG) TYPE(*CHAR) LEN(40) OVRDBF FILE(ITMMAST) SHARE(*YES) OPNQRYF FILE((ITMMAST)) + QRYSLT('ITMDES *CT "' *CAT + &SEARCHSTRG *TCAT '"') + KEYFLD(ITMNBR) CALL PGM(SLSR2) PARM(&SEARCHSTRG) CLOF OPNID(ITMMAST) DLTOVR FILE(ITMMAST) ENDPGM 
Flexible Reporting with Open Query File

Figure 6 OPNQRY modification

 Figure 6: OPNQRYF modification OPNQRYF FILE((ITMMAST)) QRYSLT('%XLATE(ITMDES + QSYSTRNTBL) *CT %XLATE("' *CAT &SEARCHSTRG + *TCAT '" QSYSTRNTBL)') KEYFLD((ITMNBR)) 
Flexible Reporting with Open Query File

Figure 7 CL program SLSC5 - Monthly Sales Report

 Figure 7: SLSC5 Monthly Sales Report CL Program PGM PARM(&REP_NBR &MONTH_YEAR) DCL &REP_NBR *CHAR 3 DCL &REP_NBR_D *DEC (3 0) DCL &MONTH_YEAR *CHAR 4 DCL &YEAR_MONTH *CHAR 4 DCL &MONTH *CHAR 2 DCL &YEAR *CHAR DCL &QRYSLT *CHAR 256 DCL &SUBTITLE *CHAR 40 CHGVAR &MONTH %SST(&MONTH_YEAR 1 2) CHGVAR &YEAR %SST(&MONTH_YEAR 3 2) CHGVAR &YEAR_MONTH (&YEAR *CAT &MONTH) CHGVAR &REP_NBR_D (&REP_NBR) CHGVAR &QRYSLT ('SLSDAT *EQ %RANGE(' *CAT + &YEAR_MONTH *CAT '00' *BCAT + &YEAR_MONTH *CAT '99)') IF (&REP_NBR_D *NE 0) DO CHGVAR VAR(&QRYSLT) + VALUE(&QRYSLT *BCAT '*AND REPNBR + *EQ' *BCAT &REP_NBR) ENDDO CHGVAR &SUBTITLE ('FOR MONTH OF' *BCAT &MONTH + *CAT '/' *CAT &YEAR) IF (&REP_NBR_D *EQ 0) DO CHGVAR VAR(&SUBTITLE) + VALUE(&SUBTITLE *BCAT '- ALL REPS') ENDDO ELSE DO CHGVAR VAR(&SUBTITLE) + VALUE(&SUBTITLE *BCAT '- REP' *BCAT + &REP_NBR *BCAT 'ONLY') ENDDO OVRDBF FILE(REPHIST) TOFILE(SLSHIST) SHARE(*YES) OPNQRYF FILE((SLSHIST) (CUSMAST) (ITMMAST) (REPMAST)) + FORMAT(REPHIST) QRYSLT(&QRYSLT) + KEYFLD((REPNBR) (ORDNBR)) + JFLD((SLSHIST/ITMNBR ITMMAST/ITMNBR) + (SLSHIST/CUSNBR CUSMAST/CUSNBR) + (CUSMAST/REPNBR REPMAST/REPNBR)) + MAPFLD((CUSNBR 'SLSHIST/CUSNBR') + (REPNBR 'CUSMAST/REPNBR') + (ITMNBR 'SLSHIST/ITMNBR')) CALL PGM(SLSR5) PARM(&SUBTITLE) CLOF OPNID(SLSHIST) DLTOVR FILE(REPHIST) ENDPGM 
Flexible Reporting with Open Query File

Figure 8 CL program SLSC6 - list inactive customers

 Figure 8: SLSC6 List Inactive Customers CL Program PGM DCL &SUBTITLE *CHAR 40 CHGVAR VAR(&SUBTITLE) VALUE('- INACTIVE CUSTOMERS') OVRDBF FILE(CUSMAST) SHARE(*YES) OPNQRYF FILE((CUSMAST) (SLSHIST)) + FORMAT(CUSMAST) + KEYFLD((CUSNBR)) + JFLD((CUSMAST/CUSNBR SLSHIST/CUSNBR)) + JDFTVAL(*ONLYDFT) + MAPFLD((CUSNBR 'CUSMAST/CUSNBR')) CALL PGM(SLSR6) PARM(&SUBTITLE) CLOF OPNID(CUSMAST) DLTOVR FILE(CUSMAST) ENDPGM 
Flexible Reporting with Open Query File

Figure 9 CL program SLSC3A - summary sales report by custom

 Figure 9: SLSC3A Summary Sales Report by Customer CL Program PGM PARM(&MONTH_YEAR) DCL &MONTH_YEAR *CHAR 4 DCL &YEAR_MONTH *CHAR 4 DCL &YEAR *CHAR 2 DCL &MONTH *CHAR 2 DCL &QRYSLT *CHAR 256 DCL &SUBTITLE *CHAR 40 CHGVAR &MONTH %SST(&MONTH_YEAR 1 2) CHGVAR &YEAR %SST(&MONTH_YEAR 3 2) CHGVAR &YEAR_MONTH (&YEAR *CAT &MONTH) CHGVAR &QRYSLT ('SLSDAT *EQ %RANGE(' *CAT + &YEAR_MONTH *CAT '00' *BCAT + &YEAR_MONTH *CAT '99)') CHGVAR &SUBTITLE ('FOR MONTH OF' *BCAT &MONTH + *CAT '/' *CAT &YEAR) OVRDBF FILE(CUSSUM) TOFILE(SLSHIST) SHARE(*YES) OPNQRYF FILE((SLSHIST)) + FORMAT(CUSSUM) + QRYSLT(&QRYSLT) + KEYFLD((CUSNBR)) + GRPFLD(CUSNBR) + MAPFLD((EXTPRICE 'QTY * UPRICE') + (CUSSCT '%COUNT') + (CUSSLS '%SUM(EXTPRICE)')) CALL PGM(SLSR3) PARM(&SUBTITLE) CLOF OPNID(SLSHIST) DLTOVR FILE(CUSSUM) ENDPGM 
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: