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.

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. The list can included numeric and alphanumeric 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.

AS 'text'
Enables you to specify a different label.
WHEN expression
Specifies the conditional display of subtotals as determined by a Boolean expression. You must end the expression with a semicolon.

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 XLSX, EXL07, or 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

Reference: Usage Notes for Subtotals

  • When using a SUM or COUNT command with only one sort phrase in the request, SUB-TOTAL and SUBTOTAL produce the same result as the value of the SUM or COUNT command. However, when using a PRINT command with one sort phrase, SUBTOTAL is useful because there can be many values within a sort break.
  • All SUB-TOTALs display up to and including the point where the sort break occurs, so only the innermost point of subtotaling should be requested. For instance, if the BY fields are
    BY AREA
BY PROD_CODE
BY DATE SUB-TOTAL

    then, when AREA changes, subtotals are displayed for DATE, PROD_CODE, and AREA on three lines (one under the other).

  • If you use a WHERE TOTAL or IF TOTAL test, the display of the sort field value for the subtotal line is suppressed unless PRINTPLUS is ON. For details about using PRINTPLUS in FOCUS, see Using PRINTPLUS.
  • Subtotals display on the next line if the subtotal text does not fit on the line prior to the displayed field columns.
  • If a report request has multiple BY phrases, with SUBTOTAL/SUMMARIZE/RECOMPUTE/SUB-TOTAL at several levels, and MULTILINES or MULTI-LINES is specified at any one of those levels, it applies to all levels.

    Note: ON BYfield SUBFOOT applies only to the level specified.

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

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
 Information Builders