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.

Reference: Usage Notes for Calculated Field Values

The following apply to the use of calculated values:

  • If you specify any optional COMPUTE phrases (such as, AS, IN, or NORPINT), and you compute additional fields following these phrases, you must repeat the commands COMPUTE or AND COMPUTE before specifying the additional fields.
  • You can rename and justify column totals and row totals. For information, see the examples in Including Totals and Subtotals.
  • Expressions in a COMPUTE command can include fields with prefix operators (see Manipulating Display Fields With Prefix Operators). For more information on valid expressions, see Using Expressions.
  • Fields referred to in a COMPUTE command are counted toward the display field limit, and appear in the internal matrix. For details on determining the maximum number of display fields that can be used in a request, see Displaying Report Data.
  • Field names used in the expression that defines the calculated field cannot be enclosed in single or double quotation marks. Any character string enclosed in quotation marks is treated as a literal string, not a field reference.
  • When using a COMPUTE with an ACROSS COLUMNS phrase, the COLUMNS should be specified last:
    ACROSS acrossfield [AND] COMPUTE compute_expression; COLUMNS values

Syntax: How to Create a Calculated Value 

COMPUTE fld [/format] [(GEOGRAPHIC_ROLE = georole)] = expression;
  [AS 'title'] [NOPRINT] [IN [+n]]

where:

fld
Is the name of the calculated value.

The name can be any name that complies with WebFOCUS field naming rules.

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. The default is D12.2. For information on formats, see the Describing Data With TIBCO WebFOCUS® Language manual.
georole
Is a valid geographic role. The following is a list of default geographic roles.
  • ADDRESS_FULL. Full address.
  • ADDRESS_LINE. Number and street name.
  • CITY. City name.
  • CONTINENT. Continent name.
  • COUNTY. County name.
  • COUNTRY. Country name.
  • GEOMETRY_AREA. Geometry area.
  • GEOMETRY_LINE. Geometry line.
  • GEOMETRY_POINT. Geometry point.
  • LATITUDE. Latitude.
  • LONGITUDE. Longitude.
  • POSTAL_CODE. Postal code.
  • STATE. State name.
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. For more information, see Laying Out the Report Page.
AS 'title'
Changes the name of the calculated value. For more information, see Using Headings, Footings, Titles, and Labels.
IN [+n]
Specifies the location of the column. For more information, see Using Headings, Footings, Titles, and Labels. IN only works in an HTML report when the STYLEMODE SET parameter is set to FIXED or OFF.

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 any name that complies with WebFOCUS field naming rules.

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. The default is D12.2. For information on formats, see the Describing Data With TIBCO WebFOCUS® Language 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

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 following image shows that 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

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

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 andAggregating Report Columns.

Screening on Calculated Values

You can screen on values produced by COMPUTE commands by using the WHERE TOTAL or WHERE_GROUPED test, as described in Selecting Records for Your Report.

If you use a WHERE test, it will automatically be changed to WHERE_GROUPED, if the test is eligible for WHERE_GROUPED processing, or to WHERE TOTAL, if it is not.