Prefix operators are useful commands that perform calculations directly on the aggregate (SUM or COUNT) value of a specified field. They are applied to a single fieldname and affect only that field. In many cases they can eliminate the need for a define and/or a hold file. This article will highlight four of those operators, MIN., AVE., PCT., and DST.
The prefix operator: MIN.
The prefix operator, MIN. will identify the minimum values within a sort group. If the request does not include a sort phrase, then it will identify the minimum values for the entire report. In the example below, MIN. is used to identify the earliest effective date (TR:EFFDATE) an employee was appointed to a lecturer classification at your campus. The report uses the Transaction Data file which has active and separated positions for employees, so the field Employee Status (TR:STATUS) is used to evaluate only position sequences for employees whose overall status is active.
|
Report Request
|
Report Generated
|
|
EX TR
TABLE FILE TR
SUM MIN.TR:EFFDATE
BY TR:SSA
IF TR:STATUS EQ 1
IF TR:CLASS EQ 2358
eND
|
min
tr:ssa tr:effdate
------ ----------
011-11-1111 01/29/2004
022-22-2222 08/22/1989
033-33-3333 08/21/2002
|
The prefix operator: AVE.
The prefix operator, AVE. computes the average value for the specified field. If your report request does not include a sort phrase, it calculates the average for the entire report. Otherwise, the average is computed as the sum of the values within a sort group divided by the number of records in that sort group. In the example below, AVE. is used to determine the average systemwide base pay, by class code, for active MPP employees.
|
Report Request
|
Report Generated
|
|
EX SAC
TABLE FILE SAC
SUM AVE.SAC:BASEPAY
BY SAC:CLASS
IF SAC:CBID EQ M80
END
|
AVE
SAC:CLASS SAC:BASEPAY
--------- -----------
3300 $12,670.18
3306 $9,111.35
3312 $6,347.01
3318 $4,553.00
|
The prefix operator: PCT.
The prefix operator, PCT. calculates the percentage that a row (sort group) makes up of the column’s total value. In the example below, PCT. it is used to determine the percent of active and onleave full-time equivalent positions, by ethnic group, at your campus. Note: The ethnic group value of '---' indicates an invalid ethnic code value on the employee's record.
|
Report Request
|
Output Generated
|
|
EX AC
TABLE FILE AC
SUM PCT.AC:FTE
BY AC:ETHNICGRP
END
|
PCT
AC:ETHNICGRP AC:FTE
----------- ------
---- .139
AMI .362
ASN 9.951
BLK 4.776
HSP 2.208
OTH 1.726
WHT 80.834
|
The prefix operator: DST.
The prefix operator, DST. allows you to determine the total number of distinct values for a specific field in a single pass of the database. In the example below, DST. is used to identify the distinct number of employees paid by your campus, for the last five tax years. An employee will be counted only once regardless of how many payments were issued during the specified timeframe.
|
Report Request
|
Output Generated
|
|
EX phs
TABLE FILE phs
count DST.phs:ssa
by phs:taxyear
if phs:taxyear from 2001 to 2005
end
|
DST
PHs:taxyear PHs:SSA
----------- -------
2001 4,034
2002 4,135
2003 4,297
2004 4,303
2005 4,456
|
For complete information on these and other prefix operators, refer to your FOCUS documentation. A general discussion of prefix operators is also available in Module 2 of the training materials for Advanced CIRS Reporting.