The sample report below provides an approximate count of employees at your campus or the last 5 years. The report is only an estimate because it simply counts the number of distinct SSNs paid for each year by agency/unit and classification. The Payment History Summary (PHS) file was chosen to generate the report because it contains data for the current and 5 prior fiscal years, thus eliminating the need to match multiple files. The report request is available in the CIRS common library as FOC5004.
agy/unit class title 2001 2002 2003 2004 2005
000999 1035 ASC-12MO-CII 1 1 1 1 1
1050 ISA . . . . 1
1870 STUDENT AST 1 1 2 5 2
1874 BR STUDNT AST 1 1 2 2 .
2358 LECTURER A,AY 1 1 5 6 4
TOTAL 4 4 10 14 8
Report Request Commands
Explanation Of Commands
A pre-programmed join is executed between the PHS/PS/AG files so that class title can be obtained from the PS file.
DEFINE FILE PHS ADD
YEAR/YY = PHS:PAYPERIOD;
This define breaks out the year component of pay period. The new field, YEAR, is used as the sort field in the table request.
TABLE FILE PHS
COUNT DST.PHS:SSA AS 'SSACNT'
If YEAR from 2001 to 2005
IF PHS:PAYMETYP EQ 0 OR L
IF PHS:CLASS GT 0
IF PHS:RANGE GE 0
ON TABLE HOLD AS PERMSML
This portion of the request gathers 5 years of data from the PHS file and holds it to the PERMSML file.
The prefix operator DST is used so that SSNs are counted only once per agency/unit and class within a given year, regardless of how many payments were issued.
By default, the fieldname for the count of distinct SSNs is COUNT. To avoid using the system supplied alias, the AS command is used to rename the field to SSACNT.
Screening on payment types 0 or L identifies regular and student payments.
The screening statements IF PHS:CLASS GT 0 and IF PHS:RANGE GE 0 are required to activate the join executed above.
The sort command ACROSS cannot be used with DST so the data must be held and further processed to format the report per the sample above.
TABLE FILE PERMSML
SUM SSACNT AS ''
BY PHS:AGYUNIT AS 'AGY/UNIT'
BY PHS:CLASS AS 'CLASS'
BY PS:TITLEA AS 'TITLE'
ACROSS YEAR AS ''
ON TABLE COLUMN-TOTAL
The final report request is generated from data in the hold file PERMSML.
The total number of distinct SSNs (SSACNT) is sorted by class across the years creating a row of data that can be easily compared from year to year.
If desired, the report can be modified for downloading by adding the following line before the END command: ON TABLE HOLD AS PERMLRG FORMAT LOTUS
If you have any questions about the report, refer to your FOCUS documentation or call the CIRS Hotline.
Last Updated: June 6, 2005