Including Subtotals

How to:

Reference:

You can use the SUBTOTAL and SUB-TOTAL commands to sum individual values, such as columns of numbers, each time a named sort field changes value.

Both SUB-TOTAL and SUBTOTAL produce grand totals. You can suppress grand totals using the NOTOTAL command. See Suppressing Grand Totals.

The subtotal is calculated every time the sort field value changes or, if WHEN criteria are applied to the sort field, every time the WHEN conditions are met.

A BY, ACROSS, or ON phrase is required to initialize the syntax.


Top of page

x
Syntax: How to Create Subtotals
{BY|ON} fieldname {SUB-TOTAL|SUBTOTAL} [MULTILINES]
      [field1 [AND] field2...] [AS 'text'][WHEN expression;]

where:

fieldname
Must be the name of a field in a sort phrase. A BY phrase can include a summary command. The number of fields to subtotal multiplied by the number of levels of subtotals counts in the number of display fields permitted for the request. For details on determining the maximum number of display fields that can be used in a request, see Displaying Report Data.
SUB-TOTAL|SUBTOTAL
SUB-TOTAL displays subtotals for numeric values when the BY|ON field changes value, and for any higher-level sort fields when their values change.

SUBTOTAL displays a subtotal only when the specified sort field changes value.

MULTILINES
Suppresses the printing of a subtotal line for every sort break that has only one detail line, since the subtotal value is equal to this one value. Note that MULTI-LINES is a synonym for MULTILINES. MULTILINES is not supported with horizontal (ACROSS) sort fields.
field1, field2, ...
Denotes a list of specific fields to subtotal. This list overrides the default, which includes all numeric display fields.

You can use the asterisk (*) wildcard character instead of a field list to indicate that all fields, numeric and alphanumeric, should be included on the summary lines. This is required if you want to display alphanumeric columns on summary lines when SET SUMMARYLINES is set to OLD. For other values of SUMMARYLINES, you can either use the asterisk to display all columns or reference the specific columns you want to display.

AS 'text'
Enables you to specify a different label. For related information, see Using Headings, Footings, Titles, and Labels.
WHEN expression
Specifies the conditional display of subtotals as determined by a Boolean expression. You must end the expression with a semicolon.

Top of page

x
Syntax: How to Control Placement of Summary Lines
SET SUBTOTALS = {ABOVE|BELOW}

where:

ABOVE
Places summary lines above the detail lines and displays the sort field values on every detail line of the report output.
BELOW
Places summary lines below the detail lines. BELOW is the default value.

Note: SET SUBTOTALS = ABOVE is not supported with format EXL2K FORMULA.



Example: Placing Subtotals Above the Data

The following request against the EMPLOYEE data source sums deduction amounts and gross salaries by department, deduction code, and last name. It then subtotals the deduction amounts and gross salaries for each department. The following request places the subtotals below the detail lines (the default):

TABLE FILE EMPLOYEE               
SUM DED_AMT GROSS                 
BY DEPARTMENT                     
BY DED_CODE                       
  BY LAST_NAME                    
WHERE BANK_ACCT NE 0              
WHERE DED_CODE EQ 'FICA' OR 'CITY'
  ON DEPARTMENT SUBTOTAL          
  ON TABLE SET SUBTOTALS BELOW    
  ON TABLE SET PAGE NOPAGE        
END

The output is:

DEPARTMENT  DED_CODE  LAST_NAME                DED_AMT            GROSS
----------  --------  ---------                -------            -----
MIS         CITY      BLACKWOOD                 $31.76        $9,075.00
                      CROSS                     $82.69       $22,013.77
                      JONES                     $14.01        $6,099.50
            FICA      BLACKWOOD              $2,223.37        $9,075.00
                      CROSS                  $5,788.01       $22,013.77
                      JONES                    $980.64        $6,099.50
                                                                       
*TOTAL DEPARTMENT MIS                        $9,120.47       $74,376.54
                                                                       
PRODUCTION  CITY      BANNING                    $7.42        $2,475.00
                      IRVING                    $60.24       $17,094.00
                      MCKNIGHT                  $18.26        $9,129.99
            FICA      BANNING                  $519.75        $2,475.00
                      IRVING                 $4,216.53       $17,094.00
                      MCKNIGHT               $1,278.21        $9,129.99
                                                                       
*TOTAL DEPARTMENT PRODUCTION                 $6,100.40       $57,397.98
                                                                       
                                                                       
TOTAL                                       $15,220.88      $131,774.52

The following is the same request, but with the subtotals placed above the detail lines:

TABLE FILE EMPLOYEE               
SUM DED_AMT GROSS                 
BY DEPARTMENT                     
BY DED_CODE                       
  BY LAST_NAME                    
WHERE BANK_ACCT NE 0              
WHERE DED_CODE EQ 'FICA' OR 'CITY'
  ON DEPARTMENT SUBTOTAL          
  ON TABLE SET SUBTOTALS ABOVE    
  ON TABLE SET PAGE NOPAGE        
END

On the output, the grand total line comes first, then the subtotal for the MIS department followed by the detail lines for the MIS department, followed by the subtotal for the PRODUCTION department and its detail lines. Note that all sort field values display on each line of the report output:

DEPARTMENT  DED_CODE  LAST_NAME                DED_AMT            GROSS
----------  --------  ---------                -------            -----
TOTAL                                       $15,220.88      $131,774.52
*TOTAL DEPARTMENT MIS                        $9,120.47       $74,376.54
                                                                       
MIS         CITY      BLACKWOOD                 $31.76        $9,075.00
MIS         CITY      CROSS                     $82.69       $22,013.77
MIS         CITY      JONES                     $14.01        $6,099.50
MIS         FICA      BLACKWOOD              $2,223.37        $9,075.00
MIS         FICA      CROSS                  $5,788.01       $22,013.77
MIS         FICA      JONES                    $980.64        $6,099.50
                                                                       
*TOTAL DEPARTMENT PRODUCTION                 $6,100.40       $57,397.98
                                                                       
PRODUCTION  CITY      BANNING                    $7.42        $2,475.00
PRODUCTION  CITY      IRVING                    $60.24       $17,094.00
PRODUCTION  CITY      MCKNIGHT                  $18.26        $9,129.99
PRODUCTION  FICA      BANNING                  $519.75        $2,475.00
PRODUCTION  FICA      IRVING                 $4,216.53       $17,094.00
PRODUCTION  FICA      MCKNIGHT               $1,278.21        $9,129.99

Top of page

x
Reference: Usage Notes for Subtotals


Example: Generating Subtotals

The following request illustrates how to create a subtotal for SALES every time the country value changes.

TABLE FILE CAR
SUM AVE.MPG AND SALES AND AVE.RETAIL_COST
BY COUNTRY SUB-TOTAL SALES
BY BODYTYPE
END

The output is:

                             AVE           AVE        
COUNTRY     BODYTYPE         MPG    SALES  RETAIL_COST
-------     --------         ----   -----  -----------
ENGLAND     CONVERTIBLE        16       0        8,878
            HARDTOP            25       0        5,100
            SEDAN              10   12000       15,671
                                                      
*TOTAL ENGLAND                      12000             
                                                      
FRANCE      SEDAN              21       0        5,610
                                                      
*TOTAL FRANCE                           0             
                                                      
ITALY       COUPE              11   12400       19,160
            ROADSTER           21   13000        6,820
            SEDAN              21    4800        5,925
*TOTAL ITALY                        30200             
                                                      
JAPAN       SEDAN              14   78030        3,239
                                                      
*TOTAL JAPAN                        78030             
                                                      
W GERMANY   SEDAN              20   88190        9,247
                                                      
*TOTAL W GERMANY                    88190             
                                                      
                                                      
TOTAL                              208420             


Example: Comparing SUB-TOTAL and SUBTOTAL

The following request illustrates how to create a subtotal for the numeric fields DED_AMT and GROSS when the department value changes, and for the higher-level sort field (DED_CODE) when its value changes.

TABLE FILE EMPLOYEE
SUM DED_AMT GROSS BY DED_CODE BY DEPARTMENT
BY BANK_ACCT
WHERE BANK_ACCT NE 0
ON DEPARTMENT SUB-TOTAL
END

If you use SUBTOTAL instead of SUB-TOTAL, the totals for DED_AMT and GROSS display only when the DEPARTMENT value changes.

The first portion of the output is:

DED_CODE  DEPARTMENT  BANK_ACCT          DED_AMT            GROSS
--------  ----------  ---------          -------            -----
CITY      MIS          40950036           $14.00        $6,099.50
                      122850108           $31.75        $9,075.00
                      163800144           $82.70       $22,013.75
                                                                 
*TOTAL DEPARTMENT MIS                    $128.45       $37,188.25
                                                                 
          PRODUCTION     160633            $7.42        $2,475.00
                      136500120           $18.25        $9,130.00
                      819000702           $60.20       $17,094.00
                                                                 
*TOTAL DEPARTMENT PRODUCTION              $85.87       $28,699.00
*TOTAL DED_CODE CITY                     $214.32       $65,887.25

The last portion of the output is:

DED_CODE  DEPARTMENT  BANK_ACCT          DED_AMT            GROSS
--------  ----------  ---------          -------            -----
STAT      MIS          40950036          $196.13        $6,099.50
                      122850108          $444.65        $9,075.00
                      163800144        $1,157.60       $22,013.75
                                                                 
*TOTAL DEPARTMENT MIS                  $1,798.38       $37,188.25
                                                                 
          PRODUCTION     160633          $103.95        $2,475.00
                      136500120          $255.65        $9,130.00
                      819000702          $843.32       $17,094.00
                                                                 
*TOTAL DEPARTMENT PRODUCTION           $1,202.92       $28,699.00
*TOTAL DED_CODE STAT                   $3,001.30       $65,887.25
                                                                 
                                                                 
TOTAL                                 $41,521.18      $461,210.75

WebFOCUS