Sample Request: Employee Counts Over 5 Years

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

EX JPHSPSAG

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;

END

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'

BY YEAR

BY PHS:AGYUNIT

BY PHS:CLASS

BY PS:TITLEA

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

END

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

END

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.