Adding Section Totals and Grand Totals to Reports

The SUBTOTAL command enables you to produce a subtotal each time the sort field you specify in a request changes. For example, if you sort by LAST_NAME, you will see a subtotal following the data for each employee.

To produce section totals and a grand total for a specified sort field, issue the following request:

TABLE FILE EMPLOYEE
SUM GROSS
BY DEPARTMENT BY LAST_NAME BY PAY_DATE
ON LAST_NAME SUBTOTAL AS 'TOTAL GROSS PAY:'
END

Run the request. The output is:

Notice that the request contains three sort fields, but the report only subtotals on the sort field you specifically request. That is, it displays a section total for each named employee and a grand total at the end of the report.

The AS phrase in the request makes the subtotal line more readable by changing the default subtotal heading: *TOTAL. Therefore, instead of seeing

*TOTAL BLACKWOOD

on the report, you see the more descriptive phrase:

TOTAL GROSS PAY: BLACKWOOD

In this exercise, you again produce section subtotals for a specified sort field, but this time the report request contains a COMPUTE command. In order to demonstrate the effect of RECOMPUTE, you first type the request incorrectly, using the SUBTOTAL command. After examining the results, you change the command to RECOMPUTE and rerun the report.

Issue the following request:

TABLE FILE EMPLOYEE
SUM GROSS DED_AMT AS 'DEDUCTIONS'
AND COMPUTE DED_PCT=(DED_AMT/GROSS)*100;
BY DEPARTMENT BY CURR_JOBCODE
ON DEPARTMENT SUBTOTAL
END

Run the request. The output is:

Notice that gross pay and deductions are properly totaled. However, the numbers in the DED_PCT column are added, producing results that you might not expect to see. This happens because the SUBTOTAL command adds up numeric values, which is fine for data like salaries and gross pay. However, computed values, such as ratios, cannot be added together to produce meaningful results. Such values need to be recalculated, not just added.

Issue the previous request, substituting RECOMPUTE for SUBTOTAL, as follows:

TABLE FILE EMPLOYEE
SUM GROSS DED_AMT AS 'DEDUCTIONS'
AND COMPUTE DED_PCT=(DED_AMT/GROSS)*100;
BY DEPARTMENT BY CURR_JOBCODE
ON DEPARTMENT RECOMPUTE
END

Run the request. The output is:

This time, in addition to showing totals for gross pay and deductions, the deduction percentage is recalculated based on the subtotals for each department, using the COMPUTE command in the request. For example, to determine that the total DED_PCT for the MIS department is 55.82, the computation is:

$28,187.25/$50,499.12*100

Sometimes it is useful to display subtotals for more than one sort field, for example, for each employee in a department, and then for the department as a whole. You can do this easily using the SUB-TOTAL command. Once again, to demonstrate the effect of SUB-TOTAL, by comparison, you first type the request using SUBTOTAL. After reviewing the report, you change the command to SUB-TOTAL and examine the results.

Issue the following request:

TABLE FILE EMPLOYEE
SUM GROSS DED_AMT AS 'DEDUCTIONS'
BY DEPARTMENT BY LAST_NAME BY PAY_DATE
ON LAST_NAME SUBTOTAL
END

Run the request. The output is:

Notice that the report displays a subtotal for each employee name, but it does not display a subtotal for all employees in the department.

Issue the previous request, substituting SUB-TOTAL for SUBTOTAL, as follows:

TABLE FILE EMPLOYEE
SUM GROSS DED_AMT AS 'DEDUCTIONS'
BY DEPARTMENT BY LAST_NAME BY PAY_DATE
ON LAST_NAME SUB-TOTAL
END

The SUB-TOTAL command includes multiple levels of section totals in a report. Section totals for the sort field you explicitly request (in this case, LAST_NAME), as well as section totals for any sort fields that precede the specified field in the request (in this case, DEPARTMENT). These are also known as higher-level sort fields.

Run the request. The output is:

Notice that the report now shows a subtotal for each employee and a separate subtotal line for the department. To take full advantage of this capability, it is important to think about the order of sort fields as you type them in a request. Of course, the final line of the report still displays the grand total for all departments.

Once again, you produce a report with multiple section totals, however, this time you also compute and subtotal deduction percentages.

Issue the following request:

TABLE FILE EMPLOYEE
SUM GROSS DED_AMT AS 'DEDUCTIONS'
AND COMPUTE DED_PCT=(DED_AMT/GROSS)*100;
BY DEPARTMENT BY CURR_JOBCODE BY PAY_DATE NOPRINT
ON CURR_JOBCODE SUMMARIZE DED_PCT AS 'DEDUCTION PERCENTAGE: '
END

Run the request. The output is:

The SUMMARIZE command recalculates a combined deduction percentage for each job code, as well as a combined percentage for all job codes within each department, using the formula you specify in a request. Notice, however, that the following line in the request that produces this report restricts summary information to the computed field DED_PCT:

ON CURR_JOBCODE SUMMARIZE DED_PCT AS ' DEDUCTION PERCENTAGE: '

Therefore, you do not see subtotals in the GROSS or DEDUCTIONS columns. Nevertheless, these values are added internally and the resulting subtotals are used to compute a combined deduction percentage for each job code and for all job codes within each department.

Before moving on, look again at the line of the request that produced the previous report:

BY DEPARTMENT BY CURR_JOBCODE BY PAY_DATE NOPRINT

Notice that it includes the NOPRINT command, which you learned to use in Chapter 2, Sorting Records. In this instance, NOPRINT indicates that the PAY_DATE sort field column will not display on the report. This column would make the report too wide to fit on a single screen. By removing the PAY_DATE column, we are able to show all of the essential data on one screen.

You can produce row totals for horizontal (ACROSS) sort field values. Row totals for horizontal sort fields, referenced by ACROSS-TOTAL, are different from standard row totals because only horizontal sort field values, within each ACROSS, are included in the total. Integer, single precision floating point, double precision floating point, packed, and long packed fields can all be totaled.

To produce an ACROSS total for the count of CITY and FED deduction codes with each department, issue the following request:

TABLE FILE EMPLOYEE
SUM CNT.EMP_ID
BY CURR_JOBCODE
ACROSS DEPARTMENT 
ACROSS DED_CODE ACROSS-TOTAL
WHERE DED_CODE EQ 'CITY' OR 'FED' 
END

Run the request. The output is:

The following chart summarizes the four (4) situations illustrated by the exercises you have completed in this section: