In this section:
How to: Reference: |
A calculated value is a temporary field that is evaluated after all the data that meets the selection criteria is retrieved, sorted, and summed. Calculated values are available only for the specified report request.
You specify the COMPUTE command in the body of the report request, following the display command and optionally introduced by AND. You can compute more than one field with a single COMPUTE command.
The following apply to the use of calculated values:
ACROSS acrossfield [AND] COMPUTE compute_expression; COLUMNS values
COMPUTE fld [/format]= expression;[AS 'title'] [NOPRINT] [IN [+n]]
where:
The name can be up to 66 characters long and can include any combination of letters, digits, and underscores (_). It should begin with a letter. Other characters are not recommended, and may cause problems in some operating environments or when resolving expressions.
Do not use field names of the type Cn, En, and Xn (where n is any sequence of one or two digits), because they are reserved for other uses.
COMPUTE fld [/format]= ;
where:
The name can be up to 66 characters long and can include any combination of letters, digits, and underscores (_). It should begin with a letter. Other characters are not recommended, and may cause problems in some operating environments or when resolving expressions.
Do not use field names of the type Cn, En, and Xn (where n is any sequence of one or two digits), because they are reserved for other uses.
In the following example, the COMPUTE command creates a temporary field REVENUE based on the product of UNIT_SOLD and RETAIL_PRICE, and displays this information for New York City. The format D12.2M indicates the field format for REVENUE and the AS command changes the default column headings for UNIT_SOLD and RETAIL_PRICE. REVENUE is only available for this report request.
TABLE FILE SALES HEADING CENTER "NEW YORK PROFIT REPORT" " " SUM UNIT_SOLD AS 'UNITS,SOLD' RETAIL_PRICE AS 'RETAIL,PRICE' COMPUTE REVENUE/D12.2M = UNIT_SOLD * RETAIL_PRICE; BY PROD_CODE AS 'PROD,CODE' WHERE CITY EQ 'NEW YORK' END
The output is:
NEW YORK PROFIT REPORT PROD UNITS RETAIL CODE SOLD PRICE REVENUE ---- ----- ------ ------- B10 30 $.85 $25.50 B17 20 $1.89 $37.80 B20 15 $1.99 $29.85 C13 15 $1.99 $29.85 C14 18 $2.05 $36.90 C17 12 $2.09 $25.08 D12 20 $2.09 $41.80 E1 30 $.89 $26.70 E2 33 $.99 $32.67 E3 35 $1.09 $38.15
In a COMPUTE command, it is sometimes convenient to refer to a field by its report column position rather than its name. This option is especially useful when the same field is specified for several report columns.
Column referencing becomes essential when you are using the same field name in a variety of ways. The columns produced by display commands (whether displayed or not) can be referred to as C1 for the first column, C2 for the second column, and so forth. The BY field columns are not counted.
For additional information about column reference numbers, see Assigning Column Reference Numbers.
The following example demonstrates positional field references in a COMPUTE command:
TABLE FILE CAR SUM AVE.DEALER_COST SUM AVE.DEALER_COST AND COMPUTE RATIO=C1/C2; BY COUNTRY END
The columns produced by display commands can be referred to as C1 for the first column (AVE.DEALER_COST), C2 for the second column (AVE.DEALER_COST BY COUNTRY), and so forth. The BY field columns are not counted.
The output is:
AVE AVE DEALER_COST COUNTRY DEALER_COST RATIO ----------- ------- ----------- ----- 7,989 ENGLAND 9,463 .84 FRANCE 4,631 1.73 ITALY 10,309 .77 JAPAN 2,756 2.90 W GERMANY 7,795 1.02
If the COMPUTE command is issued immediately following an ACROSS phrase, only a recap type of the calculation is performed once for all columns. COMPUTE is used as part of a display command, so a new column is calculated for each set of values.
TABLE FILE SALES SUM UNIT_SOLD COMPUTE NEWVAL = UNIT_SOLD * RETAIL_PRICE; ACROSS CITY END
The first page of output is:
CITY NEW YORK NEWARK STAMFORD UNIONDALE UNIT_SOLD NEWVAL UNIT_SOLD NEWVAL UNIT_SOLD NEWVAL UNIT_SOLD NEWVAL -------------------------------------------------------------------------------- 162 1,764.18 42 104.16 376 4,805.28 65 297.70
In the following COMPUTE command, C1, C2, C3, C4, C5, and C6 are positional column references, and the COMPUTE command follows the ACROSS phrase. The COMPUTE is performed once for the report, and the results are displayed to the right of all sort groups.
TABLE FILE SALES SUM UNIT_SOLD AND RETURNS WHERE DATE GE '010' AND DATE LE '1031' ACROSS DATE COMPUTE TOT_UNITS/D5=C1 + C3 + C5; TOT_RETURNS = C2 + C4 + C6; END
The output is:
DATE 10/17 10/18 10/19 TOT_UNITS TOT_RETURNS TOT_UNITS TOT_RETURNS UNIT_SOLD RETURNS UNIT_SOLD RETURNS UNIT_SOLD RETURNS ---------------------------------------------------------------------------------------------- 162 15 78 2 29 1 269 18.00
You can sort a report by a virtual field or a calculated value. To sort by a calculated value, you must use the BY TOTAL phrase in your request. For details, see Sorting and Aggregating Report Columns.
You can screen on values produced by COMPUTE commands by using the WHERE TOTAL test, as described in Selecting Records for Your Report.
Information Builders |