Displaying Subtotals Conditionally: WHEN

Another way to control when subtotals appear in your report is to specify a condition that must be satisfied in order for the subtotal to be displayed. You accomplish this using WHEN.

WHEN enables you to specify under which conditions subtotals appear. Do not confuse this with WHERE. Although the concept is similar to screening, there is an important distinction. When you use WHERE, you determine what data is included in a report. When you use WHEN, you determine when a subtotal is displayed based on the data in the report. You can use multiple WHEN phrases on the same sort field in a request.

You use WHEN in conjunction with each of the following commands explained in this chapter:

• SUBTOTAL
• SUB-TOTAL
• RECOMPUTE
• SUMMARIZE

First, you use one of the above commands in a report, then you follow it with WHEN. For instance, assume you have a report that contains the following line:

ON DEPARTMENT SUBTOTAL

To control when the subtotal appears in the report, you add a line like the following:

WHEN DEPARTMENT EQ 'MIS'

Regardless of which of the above commands you choose to use with WHEN, the format of WHEN is the same. The format is:

WHEN fieldname calculation

WHEN signals the beginning of a condition. The field name is the same one used with the preceding subtotaling command of your choice. The calculation can be a simple or complex equation, using either alphanumeric or numeric fields.

To produce a departmental subtotal only for the MIS department, issue the following request:

TABLE FILE EMPLOYEE 
SUM GROSS
BY DEPARTMENT BY CURR_JOBCODE
ON DEPARTMENT SUBTOTAL
WHEN DEPARTMENT EQ 'MIS'
END

Run the request. The output is:

As a result of the WHEN condition, a subtotal is displayed for the MIS department, but not the Production department.

To display different subfoot text, depending on the department value, issue the following request:

TABLE FILE EMPLOYEE
SUM GROSS
BY DEPARTMENT
BY EMP_ID
ON DEPARTMENT SUBFOOT
" "
"COST CENTER 123"
" "
WHEN DEPARTMENT EQ 'MIS'
SUBFOOT
" "
"COST CENTER 456"
" "
WHEN DEPARTMENT EQ 'PRODUCTION'
END

Run the request. The output is: