Sorting With Multiple Display Commands

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:


Top of page

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:

   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

Top of page

x
Controlling Formatting of Reports With Multiple Display Commands

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.



x
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


x
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:

  1. SUM CURR_SAL ED_HRS BY DEPARTMENT (totals by department).
  2. 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 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