Creating a Calculated Value

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.


Top of page

x
Reference: Usage Notes for Calculated Field Values

The following apply to the use of calculated values:


Top of page

x
Syntax: How to Create a Calculated Value
COMPUTE fld [/format]= expression;[AS 'title'] [NOPRINT] [IN [+n]]

where:

fld
Is the name of the calculated value.

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.

format
Is the format of the field. All formats except text fields (TX) are allowed. The default is D12.2. For information on formats, see the Describing Data manual.
expression
Can be an arithmetic and/or logical expression or function (see Using Expressions). Each field used in the expression must be part of the request. Each expression must end with a semicolon.
NOPRINT
Suppresses printing of the field. .
AS 'title'
Changes the name of the calculated value. F
IN [+n]
Specifies the location of the column.

Top of page

x
Syntax: How to Create a Calculated Value Without a Calculation
COMPUTE fld [/format]= ;

where:

fld
Is the name of the calculated value.

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.

format
Is the format of the field. All formats except text fields (TX) are allowed. The default is D12.2. For information on formats, see the Describing Data manual.


Example: Calculating a Field Value

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

Top of page

x
Using Positional Column Referencing With Calculated Values

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.



Example: Using Positional Column Referencing

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

Top of page

x
Using ACROSS With Calculated Values

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.



Example: Using COMPUTE as Part of a Display Command
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 


Example: Using ACROSS With Calculated Values

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

Top of page

x
Sorting Calculated Values

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.


Top of page

x
Screening on Calculated Values

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