Have you ever wished you could design a custom report without having to write a program? Have there ever been times when Query/400 just wouldn't give you the report format you really needed? If so, you need to get acquainted with Query Management (QM) forms.
Query Management/400 far outshines Query/400 in forms control. Here are some advantages that make QM reporting a far more dynamic tool than plain Query/400 reporting.
o You can use variables from the report columns in your page headings, text printed at control breaks (break text), and text associated with final totals. The variables used in the break text don't necessarily have to be break fields.
o You can explicitly design and place page heading and footing text, break heading and footing text, and final text lines. For example, final summary values can all be put on the last page in any order, instead of being spread throughout the report in separate columns.
o Forms and queries are separate objects, so each can be changed independently.
o Query Management/400 uses SAA date, time, and numeric edits.
o Query Management/400 queries and forms control specifications are supported on other IBM operating systems, such as MVS, VM, and OS/2.
o Query Management/400 reports can be run in batch mode even if they require variables to be passed to them at run time. This can be done using the SETVAR parameter of the Start Query Management Query (STRQMQRY) command.
QM is an OS/400 tool that is meant for data processing professionals-it is not an end-user tool. It gives programmers the ability to create queries that can be used within CL and HLL programs. With QM, a programmer creates an AS/400 object that retrieves information from the OS/400 database. This object is known as a QM query (object type QMQRY).
You can create a QMQRY object from an SQL statement entered into a source member. You can also create a QMQRY object by retrieving the SQL statement from a Query/400 query definition.
The output from a QMQRY presents a basic, generic report format. You get column headings, but that's about it. However, you can create your own custom format with another object known as a QM form (object type QMFORM). This article will show you how to code the specifications required to create a QMFORM object. (For more information on QM, see "Query Management Magic," MC, November 1994.)
Creating QM Forms with Other AS/400 Products
QM forms can be created in several ways. Two of the methods-SQL/400 and Query/400-require additional AS/400 licensed programs. SQL/400 Query Manager is a free functions of OS/400 and it makes designing a form much easier and faster by taking you through a series of prompted displays in setting up your report.
To create a QM form from a Query/400 query definition, first run the Analyze Query (ANZQRY) command on the query definition. You'll get messages suggesting ways to adjust the query definition before converting it to QM/400 objects. Use the Work with Queries (WRKQRY) command to make these adjustments. Then create a source file member for the QM form. (QQMFORMSRC is the IBM-suggested source file.) Run the Retrieve Query Management Form (RTVQMFORM) command to extract the form definition. Edit the definition as needed to make any desired changes to it. (Editing the form will be covered in the remainder of this article.) Finally, use the Create Query Management Form (CRTQMFORM) command to create the QM form object.
Creating QM Forms From Scratch
If you don't have SQL/400 Query Manager or Query/400, don't despair. It's still possible to code the source member for a QM form yourself in a source physical file and then use CRTQMFORM to create the form object from this source member.
It isn't even necessary to create a form; a QM/Query will run without a form being specified for it. It will simply default to the SAA forms control standards. For most people, though, this default form is a bit sparse.
Let's assume you want to design your own report form, assuming the SQL statement for retrieving the information is
SELECT REFDSC, REFNO, JRNRNO, ACCTNO, ACCTDS, GLAMT FROM GLTRAN
This statement may have been retrieved from an existing Query/400 query or keyed from scratch.
The SAA forms control standard is for a report 80 columns wide. You probably want a wider report-say, 132 columns. One of the first things to do is use the Change Printer File (CHGPRTF) command to change the QM print file QPQXPRTF in library QSYS to a width of 132 positions per line.
QM Source Record Types
A QM form, like an RPG program, is made up of several record types. Each record type controls specific information for the report. The basic QM record types are listed here and summarized in 1.
A QM form, like an RPG program, is made up of several record types. Each record type controls specific information for the report. The basic QM record types are listed here and summarized in Figure 1.
o Header (H) record
o Value (V) record
o Table description (T) record
o Table row (R) record
o End of object (E) record
The Header Record: The H record must be the first record in the file. It gives information about the object-what kind of object it is, its characteristics, and its format. Don't let this one scare you; much of the information in it is not needed on input, so those positions can be left blank. The format of the H record is given in 1, section A.
The Header Record: The H record must be the first record in the file. It gives information about the object-what kind of object it is, its characteristics, and its format. Don't let this one scare you; much of the information in it is not needed on input, so those positions can be left blank. The format of the H record is given in Figure 1, section A.
For example, H QM4 03 F 01 E V W E R 01 03 92/06/30 19:12 means a QM4 FORM file is at object level 1, in encoded format, with a valid status (no errors or warnings), containing code for a whole object, in English, usable for complete replacement, with one byte of control area and three bytes for integer lengths, written on June 30, 1992, at 7:12 p.m.
The Value Record: The V record describes a single form attribute (such as detail line spacing or column head separators). 1, section B, contains the format of the V record. It includes a unique field number for each attribute. For example, 1201 is the "blank lines before page heading" attribute. It also includes the value length value for that attribute.
The Value Record: The V record describes a single form attribute (such as detail line spacing or column head separators). Figure 1, section B, contains the format of the V record. It includes a unique field number for each attribute. For example, 1201 is the "blank lines before page heading" attribute. It also includes the value length value for that attribute.
For example, V 1201 003 001 is the value record for the "blank lines before page heading" (field number 1201) attribute. The data value has a length of 3, and the data value is 1.
The Table Description Record: The T record describes the content and format of the information that follows in the R records. The T record identifies which table is being described (by its unique table number), which columns are included (by their unique field numbers), what order they come in, and the lengths of the values in these columns. 1, section C, contains the format of the T record.
The Table Description Record: The T record describes the content and format of the information that follows in the R records. The T record identifies which table is being described (by its unique table number), which columns are included (by their unique field numbers), what order they come in, and the lengths of the values in these columns. Figure 1, section C, contains the format of the T record.
For example, T 1210 001 003 1212 003 1213 006 1214 055 is form table 1210 (page heading text table), containing one row and three columns, with column 1212 (page heading line number) of length 3, column 1213 (page heading align) of length 6, and column 1214 (page heading text) of length 55. 2 contains a chart of table numbers, field numbers, and their descriptions:
For example, T 1210 001 003 1212 003 1213 006 1214 055 is form table 1210 (page heading text table), containing one row and three columns, with column 1212 (page heading line number) of length 3, column 1213 (page heading align) of length 6, and column 1214 (page heading text) of length 55. Figure 2 contains a chart of table numbers, field numbers, and their descriptions:
The Table Row Record: The R record gives a set of values for a single row in the current table. It contains an ordered list of values that are described by the corresponding T record. An R record must exactly match the positions and lengths described in the T record. 1, section D, contains the format of the R record.
The Table Row Record: The R record gives a set of values for a single row in the current table. It contains an ordered list of values that are described by the corresponding T record. An R record must exactly match the positions and lengths described in the T record. Figure 1, section D, contains the format of the R record.
The End-of-Object Record: The E record is used to mark the end of the object. It simply consists of an E in the first position. It should be the last record in the file.
Coding a Sample QM Form Source
To code a QM form, you will need to create a source physical file called QQMFORMSRC in the library of your choice, with a record length of 162. You will probably want to create a template form source member in this file, as follows.
First, set up a default form source member called DEFAULT, source type QMFORM. You will need an H record and at least one T, R, and E record, as illustrated in 3. This is a one-column form.
First, set up a default form source member called DEFAULT, source type QMFORM. You will need an H record and at least one T, R, and E record, as illustrated in Figure 3. This is a one-column form.
Run CRTQMFORM to create the form object, and then run RTVQMFORM to retrieve a template source member from it, with all the information filled in for a one- column report with all default values. Here are examples of the commands:
CRTQMFORM QMFORM(MYLIB/TMPLAT)+ SRCFILE(MYLIB/QQMFORMSRC)+ SRCMBR(DEFAULT) RTVQMFORM QMFORM(MYLIB/TMPLAT)+ SRCFILE(MYLIB/QQMFORMSRC)+ SRCMBR(TEMPLT)
The resulting template source member (edited for space limitations) is listed in 4. Now all you have to do is copy and edit TEMPLT, changing field values and adding more columns.
The resulting template source member (edited for space limitations) is listed in Figure 4. Now all you have to do is copy and edit TEMPLT, changing field values and adding more columns.
Defining The Report Columns
This example uses six columns in the report, in this sequence:
o REFDSC, character, 21 bytes wide.
o REFNO, character, 20 bytes wide.
o JOURNAL_REF NO, character, 12 bytes wide.
o ACCOUNT NUMBER, numeric, 15 bytes wide, zero decimal places, showing leading zeros, no negative sign, no thousands separator, no currency symbol, and no percent sign.
o DESCRIPTION, character, 15 bytes wide.
o G/L AMT, numeric, 21 bytes wide (the actual field is 13 bytes long, but it allows extra positions for editing and totals), two decimal places, no leading zeros, negative sign to print, thousands separator to print, no currency symbol, and no percent sign.
This example has one blank space between the columns and a level break on the field REFDSC. Total the G/L AMT field. 5, section A contains the T record that defines this report columns table.
This example has one blank space between the columns and a level break on the field REFDSC. Total the G/L AMT field. Figure 5, section A contains the T record that defines this report columns table.
The field number 1110 identifies this table as the columns table. The 006 means that there are six R records following this T record. The 007 means that there are seven field number/field length pairs in the T record. These length pairs represent information about various characteristics of the report columns. The information is given in the R records following this T record. You can have any number of field number/field length pairs you desire, as long as the information in the R records matches the field number/field length pairs in value and length. Any fields (attributes) you don't specify will be given their default values at run time.
5, section B contains the R records for this columns table. Each record contains the information about column usage, column indent, column width, column data type, column edit, column sequence, and column heading, just as specified in the field number/field length pairs in the T record preceding them. Each field number/field length pair in the R record must be separated by a blank.
Figure 5, section B contains the R records for this columns table. Each record contains the information about column usage, column indent, column width, column data type, column edit, column sequence, and column heading, just as specified in the field number/field length pairs in the T record preceding them. Each field number/field length pair in the R record must be separated by a blank.
The field number/field length pairs in the T record, which appear in the following order, control specific aspects of the report's form:
o 1114 stands for column usage and has a length of 6. The first R record has "BREAK1" in the first field. This means that the REFDSC field is going to be the first level break field for this report. The last R record has "SUM" in the first field. This means that the G/L_AMT field will be accumulated and totaled. The other R records have blanks in the first field, meaning that they don't have a special usage value.
o 1115 stands for column indent and has a length of 2. All of the R records have a column indent of 1.
o 1116 stands for column width and has a length of 3. The REFDSC column is 21 bytes wide; the REFNO column is 20, the JOURNAL_REF_NO column is 12, the ACCOUNT_NUMBER column is 15, and the G/L_AMT column is 21.
o 1112 stands for column data type (character or numeric) and has a length of 9. ACCOUNT_NUMBER and G/L_AMT are numeric, and the other four columns are character fields.
o 1117 stands for column edit and has a length of 5. 6 contains a chart of the edit codes used by QM to format numeric data. The numbers after some of the codes tell how many decimal positions are in the field.
o 1117 stands for column edit and has a length of 5. Figure 6 contains a chart of the edit codes used by QM to format numeric data. The numbers after some of the codes tell how many decimal positions are in the field.
You can also use the edit codes field to control the format of character fields. C causes no change in the display of a character value. CW allows it to be wrapped if it can't fit on one line; and CT wraps it according to the text in the column (so it is broken at a blank rather than in the middle of a word).
o 1118 stands for column sequence and has a length of 3. Notice that the columns are numbered in multiples of ten, so a column may be inserted between two other columns later.
o 1113 stands for column heading and has a length of 62. The underline character in JOURNAL_REF NO indicates a new line for a multiple line heading.
Setting Up The Page
This sample page heading reads, "TRY TO BALANCE G/L." It should be centered, with one blank line before the heading and two blank lines after it. Here are the records that define the page heading text table (these records can also be found in 5, section C):
This sample page heading reads, "TRY TO BALANCE G/L." It should be centered, with one blank line before the heading and two blank lines after it. Here are the records that define the page heading text table (these records can also be found in Figure 5, section C):
V 1201 003 001
The 1201 field number is the "blank lines before page heading" attribute. It has a value length of 3 and a value of 1.
V 1202 003 002
The 1202 field number is the "blank lines after page heading" attribute. It has a value length of 3 and a value of 2.
The next T record is the page heading text table:
T 1210 001 003 1212 003 1213 006 1214 055 R 001 CENTER TRY TO BALANCE G/L
This T record shows that the fields in the R record are 1212 (line number) with a length of 3, 1213 (alignment) with a length of 6, and 1214 (text) with a length of 55, respectively. The R record shows the heading is on line number 1, centered, with a text of "TRY TO BALANCE G/L."
5, section D, contains the records that define the page footing. These records create two blank lines before the page footing and no blank lines after it. The page footing is left aligned, with a text of "GLREFSQL."
Figure 5, section D, contains the records that define the page footing. These records create two blank lines before the page footing and no blank lines after it. The page footing is left aligned, with a text of "GLREFSQL."
Defining The Final Summary
There is no new page for the final summary. It reads "FINAL TOTAL:" and is left aligned and one line is skipped before it. 5, section E, contains the records that define the final summary.
There is no new page for the final summary. It reads "FINAL TOTAL:" and is left aligned and one line is skipped before it. Figure 5, section E, contains the records that define the final summary.
Defining Options
The report formatting options for this example are defined in 5, section F. In this case, the detail lines are single spaced and break columns are outlined, so the break value is only printed when it changes at a level break. There are asterisks at each break level.
The report formatting options for this example are defined in Figure 5, section F. In this case, the detail lines are single spaced and break columns are outlined, so the break value is only printed when it changes at a level break. There are asterisks at each break level.
Other options defined here include column wrapped lines being kept on the same page, column heading separators and break summary separators appearing as dash lines in the report, and the final summary separators appearing as equal signs.
Defining Break Levels
This example contains no new pages for the break heading and the break footing. Column headings are not repeated at break headings, and there are no blank lines before or after.
One blank line appears before and after the break footing, which reads "REFDSC &1 TOTAL:" and is left aligned. (The "&1" is a reference to a QM/400 variable; in this case, the value of column 1 of the last record before the break footing.) 5, section G, contains the records defining the break level options selected.
One blank line appears before and after the break footing, which reads "REFDSC &1 TOTAL:" and is left aligned. (The "&1" is a reference to a QM/400 variable; in this case, the value of column 1 of the last record before the break footing.) Figure 5, section G, contains the records defining the break level options selected.
At this point, the CRTQMFORM command is used to create the form object. Once the form object is created, you can run the QM query with the STRQMQRY command specifying the QM form object name in the QMFORM parameter.
Arm Yourself with Knowledge
This article is by no means intended to be exhaustive, but it should give you some idea of the raw power and flexibility of QM forms. For more information, consult Query Management/400: Programmer's Guide and Reference. Armed with this knowledge, you'll be well on your way to designing your own QM report forms.
Sharon Cannon is a programmer/analyst with Lenoir Mirror Company. She can be contacted through MC-BBS.
REFERENCES
V2: Query Management/400: Programmer's Guide and Reference (SC41-0090, CD-ROM
QBKA7K01).
V3: DB2/400 Query Management Programming (SC41-3703, CD-ROM QBKAUE00).
Query Management Forms
Figure 1 Record Formats
Section A: Header Record Format H-PPP-RR-T-OO-F-U-S-N-A-CC-II-YY/MM/DD-HH:MM- PPP is the product identifier (QM4); RR is the product level in which the object was created; T is the object type (F=form); OO is the product object level at the time the object type was produced (it is incremented by 1 each time the format is changed); F is the format of the object in the file (E=encoded); U is the status of the object (E=contains errors, W=contains warnings, or V=valid); S is the subset of the object that is included (W=whole object); N is the language (E=English); A is the action against the object (R=replace); CC is the length of the control area at the beginning of each following record, including the one-position record type (01=forms); II is the length of the integer length fields in the V and T records (03=all objects); YY/MM/DD is the date stamp; HH:MM is the time stamp; and - stands for a blank position. The following values are not used on input, so they may be omitted: RR; OO; U; S; CC; II; YY/MM/DD; and HH:MM. Section B: The Value Record Format V-FFFF-LLL-VVV FFFF is the field number; LLL is the length of the value (an asterisk used instead of a numeric value indicates that the value is delimited by the end of the record); and VVV is the value (defaults to a blank if it is left blank on input). Section C: Table Description Record Format T-TTTT-NNN-MMM-FFFF-LLL-FFFF-LLL-FFFF-LLL ... TTTT is the table number; NNN is the number of rows in this table (an asterisk instead of a number means the table consists of all R records following it; a table with zero rows in it causes Query Management to set all columns to their default values); MMM is the number of columns (field and field length pairs) in this table; FFFF is the field number for this column; and LLL is the length of the values in this column. Section D: The Table Row Record R-VVVV-VVVV-VVVV ... VVVV is the data value for this row and column. For example: R 001 CENTER TRY TO BALANCE G/L is a form row with first column value of 001, a second column value of CENTER, and a third column value of TRY TO BALANCE G/L.
Query Management Forms
Figure 2 Table Record Chart
Table Field number number Description 1110 Column Fields 1112 Column data type 1113 Column heading 1114 Column usage (e.g., AVERAGE, COUNT, SUM,) 1115 Column indent 1116 Column width 1117 Column edit 1118 Column sequence 1210 Page heading text table 1212 Page heading line number 1213 Page heading alignment 1214 Page heading text 1310 Page footing text table 1312 Page footing line number 1313 Page footing alignment 1314 Page footing text 1410 Final text table 1412 Final text line number 1413 Final text alignment 1414 Final text 3110 Break heading table 3112 Break heading line number 3113 Break heading alignment 3114 Break heading text 3210 Break footing table 3212 Break footing line number 3213 Break footing alignment 3214 Break footing text
Query Management Forms
Figure 3 A Default QM form Source Member
H QM4 03 F 01 E V W E R 01 03 92/06/30 19:12 T 1110 001 000 R E
Query Management Forms
Figure 4 Source Template Created From Default Source Member
H QM4 05 F 01 E V W E R 01 03 95/01/02 09:44 V 1001 050 Default form source T 1110 001 002 1115 006 1118 003 R 2 1 V 1201 001 0 V 1202 001 2 V 1301 001 2 V 1302 001 0 . . . V 3203 006 0 V 3204 001 1 E
Query Management Forms
Figure 5 The Complete Form Source for Sample Report
H QM4 05 F 01 E V W E R 01 03 92/06/30 19:12 V 1001 050 TEST FORM TEXT T 1110 006 007 1114 006 1115 002 1116 003 1112 009 1117 005 1118 003 1113 062 R BREAK1 01 021 CHAR C 010 REFDSC R 01 020 CHAR C 020 REFNO R 01 012 CHAR C 030 JOURNAL_REF NO R 01 015 NUMERIC J0 040 ACCOUNT NUMBER R 01 015 CHAR C 050 DESCRIPTION R SUM 01 021 NUMERIC K2 060 G/L AMT * report header V 1201 003 001 * blanks lines before page V 1202 003 002 * blanks lines after page T 1210 001 003 1212 003 1213 006 1214 055 R 001 CENTER TRY TO BALANCE G/L V 1301 003 002 * blank lines before footer V 1302 003 000 * blank lines after footer T 1310 001 003 1312 003 1313 006 1314 055 R 001 LEFT GLREFSQL * final text option V 1401 003 NO * new page for final text V 1402 003 001 * put final sum at line V 1403 006 000 * skip line before final text (BOTTOM) T 1410 001 003 1412 003 1413 006 1414 055 R 001 LEFT FINAL TOTAL: * default report options V 1501 003 001 * detail line spacing V 1502 003 YES * outline for break columns V 1503 003 YES * default break text asterisks V 1505 003 YES * col wrap line kept on a page V 1507 003 YES * column head separators '-' V 1508 003 YES * break sum separators '-' V 1510 003 YES * final summary separators '=' * break number one V 3080 001 1 * break level number one V 3101 003 NO * new page V 3102 003 NO * repeat column headings V 3103 003 000 * blank lines before break text V 3104 003 000 * blank lines after break text T 3110 000 003 3112 003 3113 006 3114 055 * break footer options V 3201 003 NO * new page for break footer V 3202 003 001 * put break at summary line V 3203 006 001 * blank lines before break footer or BOTTOM V 3204 003 001 * blank line after break footer T 3210 001 003 3212 003 3213 006 3214 055 R 001 LEFT REFDSC &1 TOTAL: E
Query Management Forms
Figure 6 QM Edit Codes
Edit Lead Negative Thousands Currency Percent Display of Code Zeros Sign Separators Symbol Sign -1234567.885 E No Yes No No No -1.23456789E+06 D2 No Yes Yes Yes No -$1,234,567.89 I3 Yes Yes No No No -0001234567.885 J2 Yes No No No No 000001234567.89 K3 No Yes Yes No No -1,234,567.885 L2 No Yes No No No -1234567.89 P2 No Yes Yes No Yes -1,234,567.89%
More 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.
TRY 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.
Forms 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.
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:
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.
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:
LATEST COMMENTS
MC Press Online