Earlier in this chapter, you learned formatting techniques to create a report. In this section, you learn to prepare totals, create an outline format, and specify report titles.
To illustrate these functions, type the following query statement and format commands:
select projno,actno,acstaff,acstaff + .25,acstdate - from proj_act - where projno = 'AD3100' - or projno = 'AD3111' - or projno = 'AD3112' - order by projno,actno
format separator ' | ' column 'expression 1' name - 'staff + .25' - column projno width 8
This produces the following result in Figure 27.
Figure 27. A Formatted Query Result to Be Used to Illustrate Report Format
+--------------------------------------------------------------------------------+ | PROJNO | ACTNO | ACSTAFF | STAFF + .25 | ACSTDATE | | | -------- | ------ | ------- | -------------- | ---------- | | | AD3100 | 10 | 0.50 | 0.75 | 1982-01-01 | | | AD3111 | 60 | 0.80 | 1.05 | 1982-01-01 | | | AD3111 | 60 | 0.50 | 0.75 | 1982-03-15 | | | AD3111 | 70 | 1.50 | 1.75 | 1982-02-15 | | | AD3111 | 70 | 0.50 | 0.75 | 1982-03-15 | | | AD3111 | 80 | 1.25 | 1.50 | 1982-04-15 | | | AD3111 | 80 | 1.00 | 1.25 | 1982-09-15 | | | AD3111 | 180 | 1.00 | 1.25 | 1982-10-15 | | | AD3112 | 60 | 0.75 | 1.00 | 1982-01-01 | | | AD3112 | 60 | 0.50 | 0.75 | 1982-02-01 | | | AD3112 | 60 | 0.75 | 1.00 | 1982-12-01 | | | AD3112 | 60 | 1.00 | 1.25 | 1983-01-01 | | | AD3112 | 70 | 0.75 | 1.00 | 1982-01-01 | | | AD3112 | 70 | 0.50 | 0.75 | 1982-02-01 | | | AD3112 | 70 | 1.00 | 1.25 | 1982-03-15 | | | AD3112 | 70 | 0.25 | 0.50 | 1982-08-15 | | | AD3112 | 80 | 0.35 | 0.60 | 1982-08-15 | | | AD3112 | 80 | 0.50 | 0.75 | 1982-10-15 | | | AD3112 | 180 | 0.50 | 0.75 | 1982-08-15 | | | * End of Result ************* 19 Rows Displayed ****** Cost Estimate is 1 **| +--------------------------------------------------------------------------------+
An outline report format suppresses the display of duplicate values in a particular column. To provide this feature for the PROJNO column, type the following command:
format group (projno)
This produces Figure 28.
Figure 28. A Query Result Displayed in Outline Report Format
+--------------------------------------------------------------------------------+ | PROJNO | ACTNO | ACSTAFF | STAFF + .25 | ACSTDATE | | | -------- | ------ | ------- | -------------- | ---------- | | | AD3100 | 10 | 0.50 | 0.75 | 1982-01-01 | | | | | AD3111 | 60 | 0.80 | 1.05 | 1982-01-01 | | | | 60 | 0.50 | 0.75 | 1982-03-15 | | | | 70 | 1.50 | 1.75 | 1982-02-15 | | | | 70 | 0.50 | 0.75 | 1982-03-15 | | | | 80 | 1.25 | 1.50 | 1982-04-15 | | | | 80 | 1.00 | 1.25 | 1982-09-15 | | | | 180 | 1.00 | 1.25 | 1982-10-15 | | | | | AD3112 | 60 | 0.75 | 1.00 | 1982-01-01 | | | | 60 | 0.50 | 0.75 | 1982-02-01 | | | | 60 | 0.75 | 1.00 | 1982-12-01 | | | | 60 | 1.00 | 1.25 | 1983-01-01 | | | | 70 | 0.75 | 1.00 | 1982-01-01 | | | | 70 | 0.50 | 0.75 | 1982-02-01 | | | | 70 | 1.00 | 1.25 | 1982-03-15 | | | | 70 | 0.25 | 0.50 | 1982-08-15 | | | | 80 | 0.35 | 0.60 | 1982-08-15 | | | | 80 | 0.50 | 0.75 | 1982-10-15 | | | | 180 | 0.50 | 0.75 | 1982-08-15 | | | * End of Result ************* 19 Rows Displayed ****** Cost Estimate is 1 **| +--------------------------------------------------------------------------------+
Outlining is appropriate only on a column that has been ordered into groups of similar values (through an ORDER BY clause in the SELECT statement). Although outlining can be performed on an unordered column, the frequent changes in the values that are likely to occur in that column cause such outlining to be of little value.
Outlining is normally performed when you specify GROUP on a FORMAT command. For a description of how this process is controlled, see the FORMAT command section in Chapter 10, ISQL Commands.
| Note: | If you have VARCHAR or VARGRAPHIC columns with values that differ only by trailing blanks, the FORMAT GROUP command treats them as duplicates. Therefore, if you have 'AD3100' and 'AD3100 ' in a VARCHAR column, a FORMAT GROUP command eliminates one of them. |
To produce a total for the STAFF + .25 column, type:
format total ('staff + .25')
| Note: | Although included here, the parentheses are necessary only when specifying multiple columns to be totaled. |
This FORMAT command displays a result similar to Figure 29.
Figure 29. A Query Result Formatted to Produce Totals
+--------------------------------------------------------------------------------+ | PROJNO | ACTNO | ACSTAFF | STAFF + .25 | ACSTDATE | | | -------- | ------ | ------- | -------------- | ---------- | | | AD3100 | 10 | 0.50 | 0.75 | 1982-01-01 | | | | | AD3111 | 60 | 0.80 | 1.05 | 1982-01-01 | | | | 60 | 0.50 | 0.75 | 1982-03-15 | | | | 70 | 1.50 | 1.75 | 1982-02-15 | | | | 70 | 0.50 | 0.75 | 1982-03-15 | | | | 80 | 1.25 | 1.50 | 1982-04-15 | | | | 80 | 1.00 | 1.25 | 1982-09-15 | | | | 180 | 1.00 | 1.25 | 1982-10-15 | | | | | AD3112 | 60 | 0.75 | 1.00 | 1982-01-01 | | | | 60 | 0.50 | 0.75 | 1982-02-01 | | | | 60 | 0.75 | 1.00 | 1982-12-01 | | | | 60 | 1.00 | 1.25 | 1983-01-01 | | | | 70 | 0.75 | 1.00 | 1982-01-01 | | | | 70 | 0.50 | 0.75 | 1982-02-01 | | | | 70 | 1.00 | 1.25 | 1982-03-15 | | | | 70 | 0.25 | 0.50 | 1982-08-15 | | | | 80 | 0.35 | 0.60 | 1982-08-15 | | | | 80 | 0.50 | 0.75 | 1982-10-15 | | | | 180 | 0.50 | 0.75 | 1982-08-15 | | | | | | ============== | | | | | | | 18.65 | | | | * End of Result ************* 19 Rows Displayed ****** Cost Estimate is 1 **| +--------------------------------------------------------------------------------+
You can also create subtotals for this column by typing the following command:
format subtotal ('staff + .25')
This displays Figure 30.
Figure 30. A Query Result Formatted to Produce Subtotals
+--------------------------------------------------------------------------------+ | PROJNO | ACTNO | ACSTAFF | STAFF + .25 | ACSTDATE | | | -------- | ------ | ------- | -------------- | ---------- | | | AD3100 | 10 | 0.50 | 0.75 | 1982-01-01 | | | | | | -------------- | | | | ******** | | | 0.75 | | | | | | AD3111 | 60 | 0.80 | 1.05 | 1982-01-01 | | | | 60 | 0.50 | 0.75 | 1982-03-15 | | | | 70 | 1.50 | 1.75 | 1982-02-15 | | | | 70 | 0.50 | 0.75 | 1982-03-15 | | | | 80 | 1.25 | 1.50 | 1982-04-15 | | | | 80 | 1.00 | 1.25 | 1982-09-15 | | | | 180 | 1.00 | 1.25 | 1982-10-15 | | | | | | -------------- | | | | ******** | | | 8.30 | | | | | | AD3112 | 60 | 0.75 | 1.00 | 1982-01-01 | | | | 60 | 0.50 | 0.75 | 1982-02-01 | | | | 60 | 0.75 | 1.00 | 1982-12-01 | | | | 60 | 1.00 | 1.25 | 1983-01-01 | | | | 70 | 0.75 | 1.00 | 1982-01-01 | | | | 70 | 0.50 | 0.75 | 1982-02-01 | | | | 70 | 1.00 | 1.25 | 1982-03-15 | | | | 70 | 0.25 | 0.50 | 1982-08-15 | | +--------------------------------------------------------------------------------+
Notice that subtotals are created in the STAFF + .25 column for each change in the value in the PROJNO column.
The conditions on which subtotals are created are defined, like outlining, by the GROUP option of a FORMAT command. Subtotals are created whenever the value changes in the column (or columns) specified with FORMAT GROUP. Columns identified with FORMAT GROUP should have been specified in the ORDER BY clause of the SELECT statement that produced the query results, and should appear in the same sequence as they appeared in the ORDER BY clause. Otherwise, the resulting subtotals can be meaningless.
You can erase these totals and subtotals. For example, to erase the totals in the above report, you would type:
format total erase
This FORMAT command would display a result similar to Figure 31.
Figure 31. A Query Result Formatted with the Totals Erased
+--------------------------------------------------------------------------------+ | PROJNO | ACTNO | ACSTAFF | STAFF + .25 | ACSTDATE | | | -------- | ------ | ------- | -------------- | ---------- | | | | 60 | 0.50 | 0.75 | 1982-03-15 | | | | 70 | 1.50 | 1.75 | 1982-02-15 | | | | 70 | 0.50 | 0.75 | 1982-03-15 | | | | 80 | 1.25 | 1.50 | 1982-04-15 | | | | 80 | 1.00 | 1.25 | 1982-09-15 | | | | 180 | 1.00 | 1.25 | 1982-10-15 | | | | | | -------------- | | | | ******** | | | 8.30 | | | | | | AD3112 | 60 | 0.75 | 1.00 | 1982-01-01 | | | | 60 | 0.50 | 0.75 | 1982-02-01 | | | | 60 | 0.75 | 1.00 | 1982-12-01 | | | | 60 | 1.00 | 1.25 | 1983-01-01 | | | | 70 | 0.75 | 1.00 | 1982-01-01 | | | | 70 | 0.50 | 0.75 | 1982-02-01 | | | | 70 | 1.00 | 1.25 | 1982-03-15 | | | | 70 | 0.25 | 0.50 | 1982-08-15 | | | | 80 | 0.35 | 0.60 | 1982-08-15 | | | | 80 | 0.50 | 0.75 | 1982-10-15 | | | | 180 | 0.50 | 0.75 | 1982-08-15 | | | | | | -------------- | | | | ******** | | | 9.60 | | | +--------------------------------------------------------------------------------+
Subtotals can be erased and included in the same manner by substituting SUBTOTAL for TOTAL in the above example. Erasing subtotals also erases totals for the specified columns.
There are some variations in the use of GROUP, SUBTOTAL, and TOTAL with FORMAT commands. See the FORMAT command section in Chapter 10, ISQL Commands for details.
Specify a top title for the current report by typing:
format ttitle 'summary of employee time'
The quotation marks are needed because the title contains blanks. The command displays the current top title, and prompts you to return to the query result by issuing the following message in the status area:
| DB2 Server for VSE |
|---|
|
Press clear key to continue |
| DB2 Server for VM |
|---|
|
MORE ... |
To return to the query result, press CLEAR.
The top title can be erased and replaced with the first 100 characters of the associated SELECT statement by typing:
format ttitle erase
A bottom title can also be specified. Use the following command to create a bottom title for this report:
format btitle 'company confidential'
The bottom title can also be erased by typing:
format btitle erase
Top and bottom titles are centered in the top and bottom margins of the printed report. Although the titles cannot be seen until your report is printed, you can view them by typing FORMAT TTITLE (or FORMAT BTITLE) without specifying a title. For example, type:
format ttitle
Pressing CLEAR in response to this message returns you to the query result.
Now print a copy of this report by typing:
Your printed report is similar to Figure 32.
Figure 32. Example of a Printed Report
. 08/10/89 SUMMARY OF EMPLOYEE TIME .
. .
. .
. PROJNO | ACTNO | ACSTAFF | STAFF + .25 | ACSTDATE | .
. _______ | _____ | _______ | _____________ | __________ | .
. AD3100 | 10 | 0.50 | 0.75 | 1982-01-01 | .
. | | | ______________ | | .
. ******** | | | 0.75 | | .
. .
. AD3111 | 60 | 0.80 | 1.05 | 1982-01-01 | .
. | 60 | 0.50 | 0.75 | 1982-03-15 | .
. | 70 | 1.50 | 1.75 | 1982-02-15 | .
. | 70 | 0.50 | 0.75 | 1982-03-15 | .
. | 80 | 1.25 | 1.50 | 1982-04-15 | .
. | 80 | 1.00 | 1.25 | 1982-09-15 | .
. | 180 | 1.00 | 1.25 | 1982-10-15 | .
. | | | ______________ | | .
. ******** | | | 8.30 | | .
. .
. AD3112 | 60 | 0.75 | 1.00 | 1982-01-01 | .
. | 60 | 0.50 | 0.75 | 1982-02-01 | .
. | 60 | 0.75 | 1.00 | 1982-12-01 | .
. | 60 | 1.00 | 1.25 | 1983-01-01 | .
. | 70 | 0.75 | 1.00 | 1982-01-01 | .
. | 70 | 0.50 | 0.75 | 1982-02-01 | .
. | 70 | 1.00 | 1.25 | 1982-03-15 | .
. | 70 | 0.25 | 0.50 | 1982-08-15 | .
. | 80 | 0.35 | 0.60 | 1982-08-15 | .
. | 80 | 0.50 | 0.75 | 1982-10-15 | .
. | 180 | 0.50 | 0.75 | 1982-08-15 | .
. | | | ______________ | | .
. ******** | | | 9.60 | | .
. | | | ============== | | .
. | | | 18.65 | | .
. .
. .
. COMPANY CONFIDENTIAL .
The following example provides additional practice in using multiple-keyword FORMAT commands.
Assume you want to create a report from a query result that is to include the following modifications:
First, type the following statement:
select actno,acstaff,acstdate - from proj_act - where actno between 0 and 20 - order by actno
This produces the display in Figure 33.
Figure 33. A Query Result to Be Used for a Multiple-Keyword Format Command
+--------------------------------------------------------------------------------+ | ACTNO ACSTAFF ACSTDATE | | ------ ------- ---------- | | 10 0.50 1982-01-01 | | 10 1.00 1982-01-01 | | 10 0.25 1982-01-01 | | 10 1.00 1982-01-01 | | 10 0.50 1982-01-01 | | 10 0.50 1982-01-01 | | 10 0.50 1982-06-01 | | 10 0.50 1982-01-01 | | 10 1.00 1982-01-01 | | 10 1.00 1982-01-01 | | 20 1.00 1982-01-01 | | * End of Result *** 11 Rows Displayed ***Cost Estimate is 1********************| +--------------------------------------------------------------------------------+
Using the above query result, type the following FORMAT command:
format column 2 name 'mean empl' dplaces 1 -
separator ' | ' exclude (acstdate) -
group actno -
subtotal ('mean empl')
The command produces Figure 34.
Figure 34. A Query Result Formatted by a Multiple-Keyword Format Command
+--------------------------------------------------------------------------------+ | ACTNO | MEAN EMPL | | | ------ | --------- | | | 10 | 0.5 | | | | 1.0 | | | | 0.2 | | | | 1.0 | | | | 0.5 | | | | 0.5 | | | | 0.5 | | | | 0.5 | | | | 1.0 | | | | 1.0 | | | | --------- | | | ****** | 6.7 | | | | | 20 | 1.0 | | | | --------- | | | ****** | 1.0 | | | | ========= | | | | 7.7 | | | | | * End of Result ************* 11 Rows Displayed ****** Cost Estimate is 1 **| +--------------------------------------------------------------------------------+
Use the END command to end this query and clear the display.
Null values (indicated by a question mark) and arithmetic errors (indicated by number signs separated by blanks) are displayed in a formatted table. They are treated as zeros in any total or subtotal calculations of the columns they appear in.
In the sample report in Figure 35, the mean employee staff is unknown for one project activity, and contains an arithmetic error for another.
Figure 35. Sample Report Displaying Null Values and Arithmetic Errors
. SUMMARY OF EMPLOYEE DISTRIBUTION FOR ACTIVITIES 10 AND 20 .
. .
. .
. 02/09/89 PAGE 1 .
. .
. .
. ACTNO | MEAN EMPL | .
. ______ | _________ | .
. 10 | 0.5 | .
. | # # # # # | .
. | 0.2 | .
. | 1.0 | .
. | 0.5 | .
. | 0.5 | .
. | 0.5 | .
. | ? | .
. | 1.0 | .
. | _________ | .
. ****** | 6.7 | .
. .
. 20 | 1.0 | .
. | _________ | .
. ****** | 1.0 | .
. | ========= | .
. | 7.7 | .
. .
. .
. .
. .
. COMPANY CONFIDENTIAL .
When formatting reports, you may want something other than a question mark to be used to represent null values. To illustrate, type the following statement:
select * - from department - order by mgrno
This displays Figure 36.
Figure 36. A Query Result Displaying a Null Value
+--------------------------------------------------------------------------------+ | DEPTNO DEPTNAME MGRNO ADMRDEPT | | ------ -------------------- ------ -------- | | A00 SPIFFY COMPUTER SERV< 000010 A00 | | B01 PLANNING 000020 A00 | | C01 INFORMATION CENTER 000030 A00 | | E01 SUPPORT SERVICES 000050 A00 | | D11 MANUFACTURING SYSTEM< 000060 D01 | | D21 ADMINISTRATION SYSTE< 000070 D01 | | E11 OPERATIONS 000090 E01 | | E21 SOFTWARE SUPPORT 000100 E01 | | D01 DEVELOPMENT CENTER ? A00 | | * End of Result *** 9 Rows Displayed ***Cost Estimate is 1*********************| +--------------------------------------------------------------------------------+
To format a report from this query result that replaces the question mark with *NULL*, type:
format null *null*
This FORMAT command should display a result similar to Figure 37.
Figure 37. A Query Result Displaying a Formatted Null Field
+--------------------------------------------------------------------------------+ | DEPTNO DEPTNAME MGRNO ADMRDEPT | | ------ -------------------- ------ -------- | | A00 SPIFFY COMPUTER SERV< 000010 A00 | | B01 PLANNING 000020 A00 | | C01 INFORMATION CENTER 000030 A00 | | E01 SUPPORT SERVICES 000050 A00 | | D11 MANUFACTURING SYSTEM< 000060 D01 | | D21 ADMINISTRATION SYSTE< 000070 D01 | | E11 OPERATIONS 000090 E01 | | E21 SOFTWARE SUPPORT 000100 E01 | | D01 DEVELOPMENT CENTER *NULL* A00 | | * End of Result *** 9 Rows Displayed ***Cost Estimate is 1*********************| +--------------------------------------------------------------------------------+
The maximum number of characters that can be used as a null field indicator is 20.
Use the END command to end this query and clear the display.