University of California, Riverside

Human Resources Data Warehouse



Personnel List Summed by Distribution Percent


This report has 5 sections:
Section 1: Lists employee information, ID, name, gender, age, ethnicity hire date, UC total service months, time in current title, total distribution percentage, home department, next salary review date, appointment type, primary title code, grade, dist. pay rate, appointment dept, and appointment dept name.
Sections 2-5 of the report will show a summarization of the employees listed on part 1 by headcount and Full Time Equivalents (sum of all distribution percent of the employees).
Section 2: Summarizes by Appointment type. Please note if an employee has two appointments, for an example, one Career and one Contract then he/she will be counted in both categories as this section reports on Appointment Type.
Section 3: Summarizes by Age. Please note the count here may be less than the count in Section one as this counts distinct employees by Age.
Section 4: Summarizes by Gender. Please note the count here may be less than the count in Section one as this counts distinct employees by Gender.
Section 5: Summarizes by Ethnicity. Please note the count here may be less than the count in Section one as this counts distinct employees by Ethnicity Groups of White, Black, Hispanic (Latino, Mexican and Other Spanish), Asian (Chinese, Japanese, Filipino, Pakistani, Other Asian), American Indian and Unknown.

The information above can be selected by organization, by division within organization, by department within division, and by activity within division. The employee information can also be shown by occupational group, by title code within occupational group, and by personnel program code. The employee information can also be shown by selecting just one or more of the criteria already mentioned.

Using the Report Return to Top
In this report, the latest Fiscal Year and Accounting Period are selected by default.

Users may change the Fiscal Year Accounting Period and then further select Organization, Division, Department and Activity by clicking on the hour glass symbol next to each item. Users may further select Occupational Subgroups, Title Codes and Personnel Program Codes.

Users may then click on OK and the report is generated on the web below. If users wish to export the generated report to Excel then they click on the Excel Icon below the report selection drop down box.

PPS Screens Return to Top
The PPS screens are organized so that an individual employee's data can be obtained by entering the employee's name or ID. To verify the data on the data warehouse reports using the PPS screens, each employee's name or ID from a group of employees(by org code or department, or division or activity) must be entered and each PPS screen listed must be interrogated. A count must be kept for each employee in order to summarize the counts and compare the counts to the counts of each report.

PPS Screens:
IGEN: Shows ethnic codes, employee status, sex code, hire date, separation date and other general information
IAPP: Shows Appointment and Distribution Data, this screen has appointment Type, grade, personnel program type, FAU, home department, bargaining unit, DOS code, distribution pay rate, Distribution %
IPER: Shows employment service credit months

To reproduce this report in PPS go to screen IAPP to get salary (distribution pay rate), distribution %, dos code, title code, department and appointment type. Add up distribution % for this employee and add that to an fte count by ethnic code or age or appointment type or gender, add 1 to a head count by ethnic code, or age or appointment type or gender. Then go to screen IPER to get service credit. Repeat this iteration for each employee in the group of employees that you have selected.

Repeat this iteration for each employee in the group of employees that you have selected.

Query Builder - Section 1a Return to Top
To reproduce Personnel List By Distribution Percent 1a in the Query Builder select from the following in the Criteria and the Columns tabs:

Criteria Tab

Fiscal/Year section
- Specify Fiscal Year
- Specify Accounting Period

Scope section
- Specify Organization, Division, and Department
- Deselect HOME Department and select Dist. Department

Appointment/Distribution section
- Specify Occupational Group (Level 1, 2, 3)
- If you have specified any Occ. Group Level code then uncheck PRIMARY and check APPT Title Code
- Specify Personnel Program Code
- Specify DOS Code
- Specify Begin Date (<=) and End Date (>=)

Employee Info section
- Clear all Employment Status Codes

Columns Tab

Employee section
-Select Employee ID
-Select Last Name, First Name
-Select Home Department Code
-Select Hire Date-Most Recent (also helps to calculate the UC Tot Svc.)

Appointment section
- Select Appointment Number
- Select Department Code/Description
- Select Title Code Description
- Select Appointment Type Code Description
- Select Salary Grade Code
- Select Rate Code (to help calculate the salary)
- Select Percent Full Time (to help calculate the Salary)

Distribution section
- Select DOS Code
- Select Distribution Number
- Select Distribution Percent
- Select Distribution Pay Rate
- Select Distribution Begin Date
- Select Distribution End Date

Personnel section
- Select Next Salary Review Date

Personal section
- Select Date of Birth (to calculate the Age)
- Select Sex Code
- Select Ethnic Origin Code Description

-Export the results of you query into Excel
DROP rows where the Distribution End Date is less than the date being searched.
Please, note certain columns such as Age, Time Current Title (from Appointment Begin Date), UC Tot Svc (from the Hire Date) are derived columns and therefore calculated as described below.


Age(in Years): Determine the number of years between the Birth Date and today
- Add 3 new columns: Today, Month and Age.
--Today: Current Date
--Month: Formula - (YEAR(Today)-Year(DOB))*12+MONTH(Today)-MONTH(DOB)
--Age: Month/12
Time Current Title(in Months): Determine the number of months between the Appointment Begin Date and today
UC Total Svc.(in Months): Determine the number of months between the Hire Date and today

Query Builder - Section 1b Return to Top
To reproduce Personnel List by Distribution Percent 1b in the Query Builder select from the following in the Criteria and the Columns tabs:

The example below is for Career Staff Employees
Criteria Tab

Fiscal/Year section
- Specify Fiscal Year
- Specify Accounting Period

Scope section
- Specify Organization, Division, and Department
- Deselect HOME Department and select Dist. Department

Appointment/Distribution section
- Specify Occupational Group (Level 1, 2, 3)
- If you have specified any Occ. Group Level code then uncheck PRIMARY and check APPT Title Code
- Specify Personnel Program Code
- Specify DOS Code
- Specify the appropriate Appointment Type Code (for an example, Appt Type (2,7) for Career)
- Specify Begin Date (<=) and End Date (>=)

Employee Info section
- Clear all Employment Status Codes

Columns Tab

Employee section
- Select Employee ID
- Select Last Name, First Name

Appointment section
- Select Rate Code (to calculate the Dist. Monthly Average Salary)
- Select Percent Full Time (to calculate the Appt. FTE)
- Select Appointment Type Code Description

Distribution section
- Select Distribution Pay Rate (to calculate the Dist. Monthly Average Salary)
- Select Distribution Percent (to calculate the Percent Full Time)
- Select Distribution Number
- Select Distribution Begin Date
- Select Distribution End Date

-Export the results of the query into excel.
DROP rows where the Distribution End Date is less than the date being searched.
Dist. FTE: Add up the percent full time from ALL rows returned to match it to the percent full time on 10b for each appointment type.
Headcount: Count DISTINCT Employee BY Appointment Type. e.g. If an employee has two rows for the same Appt. Type then count him as 1
Salary: If Rate Code A then Dist Pay Rate*Percent Full Time; IF Rate Code H then Dist Pay Rate*Percent Full Time* 174
Dist. Monthly Average Salary: Add up the Salary (for all rows) and divide by the headcount to match it to 10b in the Report tab for each appointment type.

Query Builder - Section 1c Return to Top
To reproduce Personnel List by Distribution Percent 1c in the Query Builder select from the following in the Criteria and the Columns tabs:

The example below is for Age Group 20-29

Criteria Tab

Fiscal/Year section
- Specify Fiscal Year
- Specify Accounting Period

Scope section
- Specify Organization, Division, Department
- Deselect HOME Department and select Dist. Department

Appointment/Distribution section
- Specify Occupational Group (Level 1, 2, 3)
- If you have specified any Occ. Group Level code then uncheck PRIMARY and check APPT Title Code
- Specify Personnel Program Code
- Specify DOS Code
- Specify Begin Date (<=) and End Date (>=)

Employee Info section
- Clear all Employment Status Codes

Columns Tab

Employee section
- Select Employee ID
- Select Last Name, First Name
- Select Department Code/Department Code Description

Appointment section
- Select Rate Code (to calculate the Dist. Monthly Average Salary)
- Select Percent Full Time (to calculate the Appt. FTE)
- Select Department Code/Description

Distribution section
- Select Distribution Pay Rate (to calculate the Dist. Monthly Average Salary)
- Select Distribution Percent (to calculate the Dist. Monthly Average Salary)
- Select Distribution Number
- Select Distribution Begin Date
- Select Distribution End Date

Personal section
- Select Birth Date (to calculate the age)

-Export the results of the query into excel.
DROP rows where the Distribution End Date is less than the date being searched
-Calculate each age group by the Birth Date. For an example, for the age group of 20-29 - add up all the people within that age group based on the Birth Date)
Age(in Years): Determine the number of years between the Birth Date and today
- Add 3 new columns: Today, Month and Age.
--Today: Current Date
--Month: Formula - (YEAR(Today)-Year(DOB))*12+MONTH(Today)-MONTH(DOB)
--Age: Month/12
-Place the counts (e.g. for group 20-29) by Age Group and calculate the following within the age group:
Dist. FTE: Add up the Percent FULL TIME from ALL rows returned to match it to the percent full time on 10c for each appointment type.
Headcount: Count DISTINCT Employee BY Age in the Age Group. e.g. If an employee has two rows for the age then count him as 1
Salary: If Rate Code A then Dist Pay Rate*Percent Full Time; IF Rate Code H then Dist Pay Rate*Percent Full Time* 174
Dist. Monthly Average Salary: Add up the Salary (for all rows) and divide by the headcount to match it to 10c in the Report tab for each appointment type.

Repeat the above steps for each appointment type.

Query Builder - Section 1d Return to Top
To reproduce Personnel List by Distribution Percent 1d in the Query Builder select from the following in the Criteria and the Columns tabs:

The example below is for Female

Criteria Tab

Fiscal/Year section
- Specify Fiscal Year
- Specify Accounting Period

Scope section
- Specify Organization, Division, and Department
- Deselect HOME Department and select Dist. Department

Appointment/Distribution section
- Specify Occupational Group (Level 1, 2, 3)
- If you have specified any Occ. Group Level code then uncheck PRIMARY and check APPT Title Code
- Specify Personnel Program Code
- Specify DOS Code
- Specify Begin Date (<=) and End Date (>=)

Employee Info section
- Clear all Employment Status Codes

Personal/Personnel Info section
- Select the appropriate Gender Code (for an example, F for Female)

Columns Tab

Employee section
- Select Employee ID
- Select Last Name, First Name

Appointment Section
- Select Rate Code (to calculate the Dist. Monthly Average Salary)
- Select Percent Full Time (to calculate the Appt. FTE)

Distribution section
- Select Distribution Pay Rate (to calculate the Dist. Monthly Average Salary)
- Select Distribution Percent (to calculate the Dist. Monthly Average Salary)
- Select Distribution Number
- Select Distribution Begin Date
- Select Distribution End Date

-Export the results of the query into excel.
DROP rows where the Distribution End Date is less than the date being searched
Dist. FTE: Add up the distribution percent from ALL rows returned to match it to the percent full time on 10d for each appointment type.
Headcount: Count DISTINCT Employee BY Gender. e.g. If an employee has two rows for the same Gender then count him as 1
Salary: If Rate Code A then Dist Pay Rate*Percent Full Time; IF Rate Code H then Dist Pay Rate*Percent Full Time* 174
Dist. Monthly Average Salary: Add up the Salary (for all rows) and divide by the headcount to match it to 10d in the Report tab for each appointment type.

Repeat the above steps for Male

Query Builder - Section 1e Return to Top
To reproduce Personnel List by Distribution Percent 1e in the Query Builder select from the following in the Criteria and the Columns tabs:

The example below is for White

Criteria Tab

Fiscal/Year section
- Specify Fiscal Year
- Specify Accounting Period

Scope section
- Specify Organization, Division, and Department
- Deselect HOME Department and select Dist. Department

Appointment/Distribution section
- Specify Occupational Group (Level 1, 2, 3), Title Code and Personnel Program Code
- Select DOS code

Employee Info section
- Clear all Employment Status Codes

Personal/Personnel Info section
- Select the appropriate Ethnic Origin Code (for an example, F for White)

Columns Tab

Employee section
- Select Employee ID
- Select Last Name, First Name

Appointment section
- Select Percent Full Time

Distribution section
- Select Distribution Pay Rate (to calculate the Weighted Average Salary)
- Select Distribution Percent (to calculate the Dist. FTE)
- Select Distribution Number
- Select Distribution Begin Date
- Select Distribution End Date

-Export the results of the query into excel.
DROP rows where the Distribution End date is less than the date being searched.
Dist. FTE: Add up the distribution percent from ALL rows returned to match it to the percent full time on 10e for each appointment type.
Headcount: Count DISTINCT Employee BY Ethnic Origin Code Grouping. e.g. If an employee has two rows for the same Ethnic Origin Group then count him as 1
Salary: If Rate Code A then Dist Pay Rate*Percent Full Time; IF Rate Code H then Dist Pay Rate*Percent Full Time* 174
Dist. Monthly Average Salary: Add up the Salary (for all rows) and divide by the headcount to match it to 10e in the Report tab for each appointment type.

Repeat the above steps for all Ethnic Origin Codes

Return to Top

More Information 

General Campus Information

University of California, Riverside
900 University Ave.
Riverside, CA 92521
Tel: (951) 827-1012

Department Information

Computing & Communications
Computing & Communications Bldg.

Tel: (951) 827-3555
Fax: (951) 827-4541
E-mail: helpdesk@ucr.edu

Footer