Conditionally Formatting Reports With the WHEN Clause

Use the WHEN clause in a TABLE request to conditionally display summary lines and formatting options for BY fields. The expression in the WHEN clause enables you to control where options such as SUBTOTAL and SUBFOOT appear in the report.

The WHEN clause is an extension of the ON phrase, and must follow the ON phrase to which it applies. One WHEN clause can be specified for each option in the ON phrase. Multiple WHEN clauses are also permitted.

Used with certain formatting options in a TABLE request, the WHEN clause controls when those formatting options are displayed. If a WHEN clause is not used, the formatting options are displayed whenever the sort field value changes.

Syntax: How to Create Conditional Formatting

Syntax for the WHEN clause is:

ON fieldname option WHEN expression[;]

where:

option

Is any one of the following options for the ON phrase in a TABLE:

RECAP	PAGE-BREAK	SUBHEAD
RECOMPUTE	REPAGE	SUBFOOT
SUBTOTAL	SKIP-LINE	SUB-TOTAL
UNDER-LINE	SUMMARIZE

If the WHEN clause is used with SUBHEAD or SUBFOOT, it must be placed on the line following the text that is enclosed in double quotation marks (see Conditionally Formatting Reports With the WHEN Clause).

expression

Is any Boolean expression that is valid on the right side of a COMPUTE expression (see Using Expressions).

Note:

IF ... THEN ... ELSE logic is not necessary in a WHEN clause, and is not supported.
All non-numeric literals in a WHEN expression must be specified with single quotation marks.
The semicolon at the end of a WHEN expression is optional, and may be included for readability.

Reference: Usage Notes for Conditional Formatting

A separate WHEN clause may be used for each option specified in an ON phrase. The ON field name phrase needs to be specified only once.
You can use the WHEN clause to display a different SUBFOOT or SUBHEAD for each break group.
The WHEN clause only applies to the option that immediately precedes it.
If a WHEN clause specifies an aggregated field, the value tested is aggregated only within the break determined by the field in the corresponding ON phrase.
In the WHEN clause for a SUBFOOT, the SUBTOTAL is calculated and evaluated. This applies to fields with prefix operators and to summed fields. For alphanumeric fields, the last value in the break group is used in the test.

Top of page

Example: Conditionally Formatting Reports

In the following example, the WHEN clause prints a subfoot at the break for the field STORE_CODE only when the sum of PRODSALES exceeds $500:

DEFINE FILE SALES
PRODSALES/D9.2M = UNIT_SOLD * RETAIL_PRICE;
END
TABLE FILE SALES
SUM PRODSALES
BY STORE_CODE
ON STORE_CODE SUBFOOT
"*** SALES FOR STORE <STORE_CODE EXCEED $500 ****"
WHEN PRODSALES GT 500
END

The report output looks like this:

STORE_CODE    PRODSALES                 
----------    ---------                 
K1               $56.08                 
14B             $535.34                 
*** SALES FOR STORE 14B EXCEED $500 ****
14Z             $224.88                 
77F             $151.85

Top of page

Example: Selectively Displaying a SUBTOTAL and SUBFOOT

You can print a report that selectively displays a SUBTOTAL and a SUBFOOT with two WHEN phrases:

TABLE FILE SALES
PRINT UNIT_SOLD RETAIL_PRICE
BY PROD_CODE
ON PROD_CODE SUBTOTAL
WHEN PROD_CODE CONTAINS 'B'     
SUBFOOT
"PLEASE CHECK RECORDS FOR THIS PRODUCT GROUP"
WHEN PROD_CODE CONTAINS 'C'     
END

The relevant parts of the output are:

PROD_CODE  UNIT_SOLD  RETAIL_PRICE 
---------  ---------  ------------ 
B10               60          $.95 
                  30          $.85 
                  13          $.99 
                                   
*TOTAL B10                         
                 103         $2.79 
                                   
B12               40         $1.29 
                  29         $1.49 
                                   
*TOTAL B12                         
                  69         $2.78 
                                   
B17               29         $1.89 
                  20         $1.89 
                                   
*TOTAL B17                         
                  49         $3.78 
   .
   .
   .
C13               25         $1.99         
                                           
PLEASE CHECK RECORDS FOR THIS PRODUCT GROUP
C17               12         $2.09         
                                           
PLEASE CHECK RECORDS FOR THIS PRODUCT GROUP
   .
   .
   .

Top of page

Example: Selectively Displaying Multiple Subheads

In the following example, a different subhead is displayed depending on the value of the BY field. If the value of PROD_CODE contains the literal B, C, or E, the subhead CURRENT PRODUCT LINE is displayed. If PROD_CODE contains the literal D, the subhead DISCONTINUED PRODUCT is displayed.

TABLE FILE SALES
PRINT UNIT_SOLD RETAIL_PRICE
BY PROD_CODE
ON PROD_CODE
SUBHEAD
"CURRENT PRODUCT LINE"
WHEN PROD_CODE CONTAINS 'B' OR 'C' OR 'E'
SUBHEAD
"DISCONTINUED PRODUCT"
WHEN PROD_CODE CONTAINS 'D'
END

This produces the following report:

PROD_CODE  UNIT_SOLD  RETAIL_PRICE 
---------  ---------  ------------ 
CURRENT PRODUCT LINE               
B10               60          $.95 
                  30          $.85 
                  13          $.99 
CURRENT PRODUCT LINE               
B12               40         $1.29 
                  29         $1.49 
CURRENT PRODUCT LINE               
B17               29         $1.89 
                  20         $1.89 
CURRENT PRODUCT LINE               
B20               15         $1.99 
                  25         $2.09 
CURRENT PRODUCT LINE               
C13               25         $1.99
CURRENT PRODUCT LINE              
C17               12         $2.09
CURRENT PRODUCT LINE              
C7                45         $2.39
                  40         $2.49
DISCONTINUED PRODUCT              
D12               27         $2.19
                  20         $2.09
CURRENT PRODUCT LINE              
E1                30          $.89
CURRENT PRODUCT LINE              
E2                80          $.99
CURRENT PRODUCT LINE              
E3                70         $1.09
                  35         $1.09

Top of page

Example: Selectively Displaying a Subfoot

In the following example, a subtotal is calculated for each PROD_CODE, but the subfoot is displayed only when PROD_CODE contains the literal B:

SET PAGE-NUM = OFF                           
SET SCREEN = PAPER                           
-RUN                                         
TABLE FILE SALES                             
PRINT UNIT_SOLD RETAIL_PRICE                 
BY PROD_CODE                                 
ON PROD_CODE SUBTOTAL AND SUBFOOT            
"PLEASE CHECK RECORDS FOR THIS PRODUCT GROUP"
" "                                          
WHEN PROD_CODE CONTAINS 'B'                  
END

The partial output is:

PROD_CODE  UNIT_SOLD  RETAIL_PRICE         
---------  ---------  ------------         
B10               60          $.95         
                  30          $.85         
                  13          $.99         
                                           
*TOTAL B10                                 
                 103         $2.79         
          
PLEASE CHECK RECORDS FOR THIS PRODUCT GROUP 
B12               40         $1.29         
                  29         $1.49         
                                           
*TOTAL B12                                 
                  69         $2.78        
 PLEASE CHECK RECORDS FOR THIS PRODUCT GROUP 
B17               29         $1.89         
                  20         $1.89          
                                            
*TOTAL B17                                  
                  49         $3.78          
                                            
PLEASE CHECK RECORDS FOR THIS PRODUCT GROUP 
B20               15         $1.99          
                  25         $2.09          
*TOTAL B20                                  
                  40         $4.08          
                                            
PLEASE CHECK RECORDS FOR THIS PRODUCT GROUP                                             
C13               25         $1.99          
*TOTAL C13                                  
                  25         $1.99          
                                            
C17               12         $2.09          
                                            
*TOTAL C17                                  
                  12         $2.09

Top of page

Example: Using Aggregation in the WHEN Clause

The following request prints a subfoot depending on the sum of the UNIT_SOLD field. Each subfoot displays the subtotal of the UNIT_SOLD field within its sort group:

TABLE FILE SALES
SUM UNIT_SOLD
 BY STORE_CODE BY PROD_CODE
  
ON STORE_CODE SUBFOOT
"SELLING ABOVE QUOTA <ST.UNIT_SOLD "
" "
WHEN UNIT_SOLD GT 100
SUBFOOT
"SELLING AT QUOTA <ST.UNIT_SOLD"
" "
WHEN UNIT_SOLD GE 40 AND UNIT_SOLD LT 100
SUBFOOT
"SELLING BELOW QUOTA <ST.UNIT_SOLD"
" "
WHEN UNIT_SOLD LT 40
END

The output is:

STORE_CODE  PROD_CODE  UNIT_SOLD
----------  ---------  ---------
K1          B10               13
            B12               29
SELLING AT QUOTA    42          
                                
14B         B10               60
            B12               40
            B17               29
            C13               25
            C7                45
            D12               27
            E2                80
            E3                70
SELLING ABOVE QUOTA   376       
                                
14Z         B10               30
            B17               20
            B20               15
            C17               12
            D12               20
            E1                30
14Z         E3                35
SELLING ABOVE QUOTA   162       
                                
77F         B20               25
            C7                40
SELLING AT QUOTA    65