Prefix Operators

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.