How to: |
You can use the SET DUPLICATECOL command to reformat report requests that use multiple display commands (Sort Groups), placing aggregated fields in the same column above the displayed field.
By default (ON), 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.
In a FOCEXEC or in a profile:
SET DUPLICATECOL={ON|OFF}In a request:
ON TABLE SET DUPLICATECOL {ON|OFF}where:
- ON
Displays the report with each field as a column. This is the default value.
- OFF
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.00With 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.00The following request adds a PRINT command sorted by department and by last name to the previous request:
TABLE FILE EMPLOYEE SUM CURR_SAL ED_HRS SUM CURR_SAL ED_HRS BY DEPARTMENT 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:
DEPARTMENT 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:
- n
Is the ordinal number of the display command in the report request.
The following request has two display commands:
- SUM CURR_SAL ED_HRS BY DEPARTMENT (totals by department).
- PRINT FIRST_NAME CURR_SAL ED_HRS BY
DEPARTMENT BY LAST_NAME (values by employee by department).
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 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:
- The fourth displayed column (P4, department total of CURR_SAL) for the SUM command is italic and blue.
- The second BY field (LAST_NAME) for the PRINT command is underlined and red.
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:
