Sorting With Multiple Display Commands

In this section:

Controlling Formatting of Reports With Multiple Display Commands

A request can consist of up to 64 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:

Up to 64 display commands and their associated sort conditions can be used. The first display command does not have to have any sort condition. Only the last display command may be a detail command, such as PRINT or LIST. Other preceding display commands must be aggregating commands.
WHERE and IF criteria apply to the records selected for the report as a whole. WHERE and IF criteria are explained in Selecting Records for Your Report.
When a sort phrase is used with a display command, the display commands following it must use the same sorting condition in the same order. For example:
```
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.

Example: Using Multiple Display and Sort Fields

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:

Controlling Formatting of Reports With Multiple Display Commands

How to:

Control the Format of Reports With Multiple Display Commands
Style a Report With SET DUPLICATECOL=ON

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.

Syntax: How to Control the Format of Reports With Multiple Display Commands

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.

Example: Displaying Reports With Multiple Display Commands

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

Syntax: How to Style a Report With SET DUPLICATECOL=ON

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.

Example: Styling Rows Associated With a Specific Display Command

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:

Reference: Stacking Duplicate Columns in Multi-Verb Requests Based on AS Names

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.

In prior releases, the duplicate columns were matched based on field names. Now, fields can also be matched based on AS names. An AS name will not be matched to a field name. When a field has an AS name, it will only be matched to other fields that have the same AS name.

Example: Stacking Duplicate Columns in Multi-Verb Requests Based on AS Names

The following request has three display commands. The first sums the CURR_SAL field. The second sums the SALARY field by department. The third prints the GROSS field by department and last name. Each field is assigned the same AS name, even the CURR_SAL field.

TABLE FILE EMPLOYEE
SUM CURR_SAL AS CURR_SAL  ED_HRS
SUM SALARY AS CURR_SAL ED_HRS BY DEPARTMENT AS 'DEPT'
PRINT FIRST_NAME GROSS AS CURR_SAL ED_HRS BY DEPARTMENT BY LAST_NAME
ON TABLE SET DUPLICATECOL OFF
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
GRID=OFF, SIZE=10, $
VERBSET=1, COLOR=RED,$
VERBSET=2, COLOR=BLUE,$
VERBSET=3,COLOR=BLACK,$
ENDSTYLE
END

The partial output is shown in the following image.