Reporting Language Primer > Creating Basic Output Requests > Manipulating Display Fields With Prefix Operators

Manipulating Display Fields With Prefix Operators

You can use prefix operators to perform calculations directly on the values of aggregated fields. Each prefix operator is applied to a single field, and affects only that field.

The syntax is:

{SUM|COUNT} prefix.fieldname

The following table lists prefix operators and describes the function of each.

Prefix

Function

 
ASQ.

Computes the average sum of squares for standard deviation in statistical analysis. 

AVE.

Computes the average value of the field.

 
AVE.DST.

Averages the distinct values within a field. 

CNT.

Counts the number of occurrences of the field, even if used with the SUM verb. The data type of the result is always Integer. 

CNT.DST.

Counts the number of distinct values within a field. 

CT.

Produces a cumulative total of the field. This operator only applies when used in subfoots. 

DST.

Determines the total number of distinct values in a single pass of a data source. 

FST.

Generates the first physical instance of the field. Can be used with numeric or text fields. 

LST.

Generates the last physical instance of the field. Can be used with numeric or text fields. 

MAX.

Generates the maximum value of the field. 

MDE.

Computes the mode of the field values.

 
MDN.

Computes the median of the field values.

 
MIN.

Generates the minimum value of the field. 

PCT.

Computes a field percentage based on the total value for the field. The PCT operator can be used with detail, as well as summary fields. 

PCT.CNT.

Computes a field percentage based on the number of instances found. The format of the result is always F6.2. 

RNK.

Ranks the instances of a BY sort field in the request. Can be used in PRINT commands, COMPUTE commands, and IF or WHERE TOTAL tests. 

ROLL.

Recalculates values on summary lines using the aggregated values from lower level summary lines. 

RPCT.

Computes a field percentage based on the total values for the field across a row. 

ST.

Produces a subtotal value of the field at a sort break in the report. This operator only applies when used in subfoots. 

SUM.

Sums the field values, even if used with the COUNT verb.

 
SUM.DST.

Sums the distinct values within a field. 

TOT.

Totals the field values for use in a heading (includes footings, subheads, and subfoots).

AVE. Prefix Operator

To calculate the average number of education hours spent in each department, issue the following request: 

TABLE FILE EMPLOYEE
SUM AVE.ED_HRS BY DEPARTMENT
END

Run the request. The output is:

MAX. and MIN. Prefix Operators

To calculate the maximum and minimum values of SALARY, issue the following request: 

TABLE FILE EMPLOYEE
SUM MAX.SALARY AND MIN.SALARY
END

Run the request. The output is:

PCT. Prefix Operator

To calculate the percentage that a field makes up of the column total value, for example, each employee share of education hours, issue the following request: 

TABLE FILE EMPLOYEE
SUM ED_HRS PCT.ED_HRS BY LAST_NAME
ON TABLE COLUMN-TOTAL
END

Run the request. The output is:

RCPT. Prefix Operator

To calculate the total education hours (ED_HRS column) and the percentage that total makes up in relation to the sum of all education hours (RPCT.ED_HRS) for each employee within department, issue the following request: 

TABLE FILE EMPLOYEE
SUM ED_HRS RPCT.ED_HRS ROW-TOTAL BY LAST_NAME
ACROSS DEPARTMENT 
END

Run the request. The output is:

SUM and COUNT in the Same Request

To count the occurrences of EMP_ID and sum the value of ED_HRS, issue either of the following requests: 

TABLE FILE EMPLOYEE
SUM CNT.EMP_ID AND ED_HRS
END

or

TABLE FILE EMPLOYEE
COUNT EMP_ID AND SUM.ED_HRS
END

Run the request. The output is:

PCT.CNT. Prefix Operator

To count the occurrences of EMP_ID and the relative percentage of the values in the EMP_ID field for each department, issue the following request: 

TABLE FILE EMPLOYEE
SUM CNT.EMP_ID PCT.CNT.EMP_ID
BY DEPARTMENT
END

Run the request. The output is:

CNT.DST. Prefix Operator

To count the number of unique ED_HRS values, issue either of the following requests: 

TABLE FILE EMPLOYEE
SUM CNT.DST.ED_HRS
END

or

TABLE FILE EMPLOYEE
COUNT DST.ED_HRS
END

Run the request. The output is:

FST. and LST. Prefix Operators

To retrieve the first and last logical record in the EMP_ID field, issue the following request: 

TABLE FILE EMPLOYEE
SUM FST.EMP_ID LST.EMP_ID
END

Run the request. The output is:

WebFOCUS

Feedback