In this section: |
A request can consist of up to sixteen sets of separate display commands (also known as verb phrases), each with its own sort conditions. In order to display all of the information, a meaningful relationship has to exist among the separate sort condition sets. The following rules apply:
TABLE FILE EMPLOYEE SUM ED_HRS SUM CURR_SAL CNT.CURR_SAL BY DEPARTMENT PRINT FIRST_NAME BY DEPARTMENT BY LAST_NAME END
The first SUM does not have a sort condition. The second SUM has a sort condition: BY DEPARTMENT. Because of this sort condition, the PRINT command must have BY DEPARTMENT as the first sort condition, and other sort conditions may be added as needed.
The following request summarizes several levels of detail in the data source.
TABLE FILE EMPLOYEE SUM CURR_SAL SUM CURR_SAL BY DEPARTMENT SUM CURR_SAL BY DEPARTMENT BY LAST_NAME END
The command SUM CURR_SAL calculates the total amount of current salaries; SUM CURR_SAL BY DEPARTMENT calculates the total amounts of current salaries in each department; SUM CURR_SAL BY DEPARTMENT BY LAST_NAME calculates the total amounts of current salaries for each employee name.
The output is:
CURR_SAL DEPARTMENT CURR_SAL LAST_NAME CURR_SAL -------- ---------- -------- --------- -------- $222,284.00 MIS $108,002.00 BLACKWOOD $21,780.00 CROSS $27,062.00 GREENSPAN $9,000.00 JONES $18,480.00 MCCOY $18,480.00 SMITH $13,200.00 PRODUCTION $114,282.00 BANNING $29,700.00 IRVING $26,862.00 MCKNIGHT $16,100.00 ROMANS $21,120.00 SMITH $9,500.00 STEVENS $11,000.00
How to: |
You can use the SET DUPLICATECOL command to reformat report requests that use multiple display commands, placing aggregated fields in the same column above the displayed field.
By default, each new display command in a request generates additional sort field and display field columns. With DUPLICATECOL set to OFF, each field occupies only one column in the request, with the values from each display command stacked under the values for the previous display command.
SET DUPLICATECOL={ON|OFF}
where:
Displays the report with each field as a column. This is the default value.
Displays the report with common fields as a row.
The following request sums current salaries and education hours for the entire EMPLOYEE data source and for each department:
TABLE FILE EMPLOYEE SUM CURR_SAL ED_HRS SUM CURR_SAL ED_HRS BY DEPARTMENT END
With DUPLICATECOL=ON, the output has separate columns for the grand totals and for the departmental totals:
CURR_SAL ED_HRS DEPARTMENT CURR_SAL ED_HRS -------- ------ ---------- -------- ------ $222,284.00 351.00 MIS $108,002.00 231.00 PRODUCTION $114,282.00 120.00
With DUPLICATECOL=OFF, the output has one column for each field. The grand totals are on the top row of the report, and the departmental totals are on additional rows below the grand totals:
DEPARTMENT CURR_SAL ED_HRS ---------- -------- ------ $222,284.00 351.00 MIS $108,002.00 231.00 PRODUCTION $114,282.00 120.00
The following request adds a PRINT command sorted by department and by last name to the previous request:
SET SPACES = 1 TABLE FILE EMPLOYEE SUM CURR_SAL ED_HRS SUM CURR_SAL ED_HRS BY DEPARTMENT AS 'DEPT' PRINT FIRST_NAME CURR_SAL ED_HRS BY DEPARTMENT BY LAST_NAME END
With DUPLICATECOL=ON, the output has separate columns for the grand totals, for the departmental totals, and for each last name:
With DUPLICATECOL=OFF, the output has one column for each field. The grand totals are on the top row of the report, the departmental totals are on additional rows below the grand totals, and the values for each last name are on additional rows below their departmental totals:
DEPT LAST_NAME FIRST_NAME CURR_SAL ED_HRS ---- --------- ---------- -------- ------ $222,284.00 351.00 MIS $108,002.00 231.00 BLACKWOOD ROSEMARIE $21,780.00 75.00 CROSS BARBARA $27,062.00 45.00 GREENSPAN MARY $9,000.00 25.00 JONES DIANE $18,480.00 50.00 MCCOY JOHN $18,480.00 .00 SMITH MARY $13,200.00 36.00 PRODUCTION $114,282.00 120.00 BANNING JOHN $29,700.00 .00 IRVING JOAN $26,862.00 30.00 MCKNIGHT ROGER $16,100.00 50.00 ROMANS ANTHONY $21,120.00 5.00 SMITH RICHARD $9,500.00 10.00 STEVENS ALFRED $11,000.00 25.00
In a StyleSheet, you can identify the rows you want to style by specifying which display command created those rows:
VERBSET = n
where:
Is the ordinal number of the display command in the report request.
The following request has two display commands:
SET DUPLICATECOL = OFF
TABLE FILE EMPLOYEE
SUM CURR_SAL ED_HRS BY DEPARTMENT
PRINT FIRST_NAME CURR_SAL ED_HRS BY DEPARTMENT BY LAST_NAME ON TABLE HOLD FORMAT HTML
ON TABLE SET STYLE *
TYPE = REPORT, COLUMN= P4, VERBSET = 1, STYLE = ITALIC, COLOR=BLUE,$
TYPE = REPORT, COLUMN= B2, VERBSET = 2, STYLE = UNDERLINE, COLOR = RED,$
ENDSTYLE
END
On the output:
When you style specific columns, using P notation means that you count every column that displays on the report output, including BY columns. Therefore, P1 is the DEPARTMENT column, P2 is the LAST_NAME column (this is also B2, the second BY field column), P3 is the FIRST_NAME column, P4 is the displayed version of the CURR_SAL column (the internal matrix has multiple CURR_SAL columns), and P5 is the displayed ED_HRS column (the internal matrix has multiple ED_HRS columns).
The output is:
Information Builders |