Conditionally Including Summary Lines, Underlines, Skipped Lines, and Page Breaks

How to:

You can conditionally include sort-based options such as subtotals and other summary lines, sort headings and footings, underlines, skipped lines, and page breaks, as well as conditionally restart page numbering, by using the WHEN phrase in your report request. The WHEN phrase specifies a condition that is evaluated for each value of a vertical sort (BY) field. The sort-based option (summary line, underline, skipped line, or page break) is applied to each sort group that satisfies the condition, and is ignored by sort groups that do not satisfy the condition.

The WHEN phrase is an extension of the ON sortfield and BY sortfield phrases. You can specify a WHEN phrase for each sortfield phrase. For example: 

ON ORDER_NUM UNDER-LINE WHEN QUANTITY GT 5
ON COUNTRY PAGE-BREAK WHEN LINEPRICE GT 200000

If a sortfield phrase includes several sort-related options, you can specify a different WHEN phrase for each option. For example: 

ON ORDER_NUM SKIP-LINE WHEN QUANTITY GT 5; UNDER-LINE WHEN QUANTITY GT 10

Syntax: How to Conditionally Display Summary Lines, Underlines, Skipped lines, and Page Breaks 

{BY|ON} sortfield [option WHEN condition [;] [AND]]...

where:

BY|ON

These are functionally identical. The only difference is syntactic (BY enables you to specify the sort-based feature as part of the sort phrase, while ON enables you to specify it separately from the sort phrase). For more information, see the documentation for the sortfield option you are using.

sortfield

Is the name of a vertical sort (BY) field.

option

Is one of the following sort-based features: PAGE-BREAK, PAGE-BREAK REPAGE, RECAP, RECOMPUTE, SKIP-LINE, SUBFOOT, SUBHEAD, SUBTOTAL, SUB-TOTAL, SUMMARIZE, UNDER-LINE.

If you specify SUBHEAD or SUBFOOT, you must place the WHEN phrase on the line following the text of the heading or footing.

condition

Is a logical expression. For more information, see Using Expressions.

You must enclose non-numeric constants, such as character strings and dates, in single quotation marks.

If the condition evaluates a numeric detail field, it evaluates the sum of the detail field values within each sort group, not the individual detail values. For example, in the request 

TABLE FILE CENTHR 
PRINT ID_NUM SALARY 
BY PLANT
ON PLANT UNDER-LINE  
WHEN SALARY GT 2000000 
END

the condition evaluates the sum of the SALARY values within each PLANT value.

If the condition evaluates an alphanumeric field that appears multiple times in a sort group, it evaluates the last value of the field in each sort group.

You can apply a prefix operator to a field in the condition (for example, WHEN AVE.PRICE GT 300) even if the operator and the field are not used in the report. The aggregation is performed for each value of the sort field.

If the BY or ON phrase includes several options, the WHEN condition applies only to the option that immediately precedes it.

;

Is required if WHEN phrases are being included for several options in this BY or ON phrase. In all other situations it is optional and just enhances readability.

AND

Can be included between two sets of sortfield options to enhance readability.

Example: Using a WHEN Condition for a Sort Option

This example illustrates how to conditionally display a sub footing in a report. This report uses a conditional sort footing to draw attention to orders that total less than 200,000. 

TABLE FILE CENTORD
HEADING
"Order Revenue"
" "
SUM ORDER_DATE LINEPRICE AS 'Order,Total:'
BY HIGHEST 5 ORDER_NUM
  
ON ORDER_NUM 
    SUBFOOT
       "--- Order total is less than $200,000 ---" 
       " "
       WHEN LINEPRICE LT 200000 
 
ON TABLE SET PAGE-NUM OFF
ON TABLE SET STYLESHEET *
TYPE=REPORT, GRID=OFF, $
ENDSTYLE
END

The output is:

Example: Using WHEN Conditions for Multiple Sort Options

This example illustrates how to apply multiple conditions to a report component. This report uses conditional sort footings to distinguish between orders that total more than 200,000 and less than 200,000.

Notice that one sort phrase (ON ORDER_NUM) specifies several sort-related options (two different SUBFOOT phrases), and that each option has its own WHEN phrase. 

TABLE FILE CENTORD
HEADING
"Order Revenue"
" "
SUM ORDER_DATE LINEPRICE AS 'Order,Total:'
BY HIGHEST 5 ORDER_NUM 
ON ORDER_NUM
   SUBFOOT
      "--- Order total is less than $200,000 ---"
      " "
      WHEN LINEPRICE LT 200000;
   SUBFOOT
      "+++ Order total is greater than or equal to $200,000 +++"
      " "
      WHEN LINEPRICE GE 200000; 
 
ON TABLE SET PAGE-NUM OFF
ON TABLE SET STYLESHEET *
TYPE=REPORT, GRID=OFF, $
ENDSTYLE
END

The output is: