Including Subtotals
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.
- SUB-TOTAL displays a subtotal when the sort 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. It does not give subtotals for higher-level fields.
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.
Create Subtotals
{BY|ON} fieldname {SUB-TOTAL|SUBTOTAL} [MULTILINES]
[field1 [AND] field2...] [AS 'text'][WHEN expression;]
SUBTOTAL displays a subtotal only when the specified sort field changes value.
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.
Control Placement of Summary Lines
SET SUBTOTALS = {ABOVE|BELOW}
where:
Places summary lines above the detail lines and displays the sort field values on every detail line of the report output.
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.
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
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 WebFOCUS, see Positioning Headings, Footings, or Items Within Them.
- 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.
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
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