|
One could describe your SA system as robust, meaning it has a lot of features. One of the side affects of such a robust system is the challenge to get meaningful/useful data out in an easy (very relative term, like user friendly) fashion. Two of the general purpose tools which come bundled in all PeopleSoft applications are the PeopleSoft Query and BI publisher reporting tools. All of the data in the SA system is stored in objects referred to as “records”, which can be thought of as a filing cabinet with lots of folders of information. In your SA system, there are literally thousands (45,687 as of the last count) of these records where data is stored in fields (514,275 of them). Using the query and BI publisher reporting tools, we’ve predefined some of the more common questions asked of the data and stored them in query and BI publisher definitions.
I am always eager to find/develop new ways to make JMU faculty/staff’s lives easier as it relates to the SA data. Before embarking on a labor intensive task related to SA data, why not drop me an email (desmitpa@jmu.edu) or give me a call (8-3605) as I might just be able to make your data analysis chore a lot easier!
Academic Affairs
Queries whose name begins with jacadaffairs. Primary contacts are Jason McClain, Joy McBride and Christina Myers. Unlike most other queries found in this document, these queries were written specifically in a manner which (generally) do not require the user to provide values for prompts. Most of the queries in this series have hard coded terms, academic years, etc. These queries also make heavy use of the drilling query feature. Due to the fact that only a few characters can be used in the hyperlinks which can be followed to “drill down” into details, it is suggested that users new to this series of queries read the following documentation which will allow them to better understand the scope of the data they will be accessing. There is also no security attached to any of these queries, please abide by the data stewardship policy when working with the results. To run the queries, navigate: Reporting Tools, Query, Query Viewer. In the field labeled “begins with” enter the letters “jacadaffairs” and press Enter or search. Locate the desired query, press the HTML hyperlink. The query results will always open in a new window, IF this is the first time you’ve run a query, you may receive a pop up blocker message which you’ll need to address. With the results displayed, locate the desired College row and follow the hyperlink from the desired results. Each drill down will appear in a new browser window. Section last updated 7/10/2018.
General notes:
-
Although all of the queries respond when run interactively, there are some which will take “a few minutes” to return results. Bear in mind; such queries are oftentimes processing millions of rows of data.
-
When a field named “capacity ” is present, it uses a field named enrollment capacity set for each section of a course. For any given term, approximately 30% of the courses have had their enrollment capacity set to zero by the administrative users who perform the data entry. These courses cannot be included in calculations because division by zero is undefined and would cause an error. IF you are interested in finding which courses have their enrollment capacity set to zero, follow the hyperlink labeled “Outlying data” for a given college. Ideally, no course should ever have it’s enrollment capacity set to zero. There are setup and configuration options to limit a course’s enrollment to specific majors, etc. vs. setting the enrollment capacity to zero and requiring course override permissions.
-
When prompted for term, you can determine the values according to the following details. The first digit indicates the century (0 prior to the year 2000, 1 for the year 2000 and beyond). Digits two and three are the last two digits of the year; 17 would be for 2017, 18 for 2018, etc. The fourth/last digit is the month of the year in which the term begins; 1 for Spring term, 5 for any/all Summer terms and 8 for the Fall term. A term of 1178 is the Fall of 2017.
-
Credit Hour Production (CHP) is derived by multiplying the total number of enrolled students by the number of credit hours. Where applicable, courses offered for variable credits are taken into account.
-
When ethnicity is present, they are self reported values. A person can self report that they are any/all ethnicities. An attempt to reconcile the percentages should never be made to equal 100% for a given row returned by any query.
-
A special note about “College”. Whenever present, this field is a result of setup/configuration data at one of the following three levels.
-
A course subject (NOT each individual course) belongs to one and only one College. This value is set and maintained by the office of the registrar.
-
An academic plan. Each of the 349 majors/minors are tracked internally via a ten digit number known as an academic plan number. Associated with each major/minor is a single value commonly referred to as College although internally it is known as the field of study.
-
An instructor. When present, this is a collective term which includes all academic ranks. Each instructor is assigned to a single College. It is clearly understood that this single College does not represent how course assignments actually occur. There are instructors assigned to courses across multiple colleges. For this very reason, unless otherwise documented, whenever you drill down into the various query results following a single college, the results are being returned based upon either the course subject or the academic plan.
-
Department is assigned by the Office of the Registrar. Internally, this field is known as the academic organization and is considered a child of the College. To view a list of departments and course subjects associated with a given college, follow the drill down labeled “List of Departments” found in the jacadaffairs01 query.
-
Unless otherwise noted, this series does not include Summer terms.
-
Whenever just the most recent academic year is present, it is due to the fact the data being used by the query is not available historically, only for the current academic year.
-
JACADAFFAIRS01 Personnel metrics:
-
5 year FTE (JAA02). Returns one row for each of the past 5 academic years for the given college. FTE is defined by the office of Academic Affairs. It is calculated by taking the total number of units divided by (12* the number of terms). Please do NOT attempt to reconcile this FTE figure with others labeled as actual or distinct counts. This column represents the total number of full time instructors it WOULD take to teach the given number of course sections if they were all assigned a full time load of 12 hours per instructor. The % honors and % grad fields represent what percentage of the course credits are being offered for honors and graduate (course number begins with 5-9) purposes.
-
Select a displayed academic year (JAA02A). Returns one row for each combination of college, and course department for each of the past 5 academic years.
-
Select a displayed FTE (JAA02B). Returns one row for each combination of college, department and instructor. An instructor is returned if they taught one/more courses whose subject is assigned by the Office of the Registrar to the returned college/department. The following fields are returned.
-
Fall units; the total number of units the instructor was assigned for the given academic year and Fall term. ONLY courses associated with the selected College are included in this field. IF an instructor taught courses in another college, those units are not represented.
-
Spring units; the total number of units the instructor was assigned for the given academic year and Fall term. ONLY courses associated with the selected College are included in this field. IF an instructor taught courses in another college, those units are not represented.
-
AY units. Is simply a total of the Fall and Spring terms for the given academic year.
-
Inside the department units. Each instructor is assigned a single department by the Office of the Registrar. IF this assigned department matches the selected course department, this field represents those total units.
-
Outside the department units. Represents the total units when the home department of the instructor is not the same as the course department.
-
Home department. Represents the home department assigned to the given instructor by the Office of the Registrar.
-
5 year # sections (JAA04). Returns one row for each of the past 5 academic years. Includes a distinct count of the number of instructors and their average number of course sections.
-
Select a displayed academic year (JAA04A). Returns one row for each combination of academic year selected and all departments within the selected college.
-
Select a displayed department. Returns one row for each instructor.
-
FT/PT% (JAA06). Returns one row for the current academic year. Aggregate values are present for the number and percentages of course sections and CHP taught by FT (Full Time) and PT (Part Time) instructors. A distinct count of the two major categories of instructors (full time vs. part time) is also returned.
-
Select a displayed academic year (JAA06A). Returns one row for each department for the selected college.
-
Select a displayed FT distinct instructor number (JAA02B1). Returns one row of data for each of the instructors “behind the numbers”
-
Select a displayed PT distinct instructor number (JAA02B2). Returns one row of data for each of the instructors “behind the numbers”
-
CHP by rank (JAA19). Returns one row for the current academic year. Contains three fields for each of the 5 academic ranks.
-
Select a displayed academic year (JAA19A). Returns one row for each department for the selected college.
-
Average salary (JAA21). The current data is for full time instructional faculty only.
-
Select a displayed college (JAA21A). Returns one row for each department associated with the selected college.
-
List of Departments (JAA02C). Returns one row for each combination of department and subject area associated with courses for the given college.
-
Current Instructors (JAA02D). Returns one row for each instructor teaching 1/more courses assigned to the selected college for the current academic year.
-
Select a displayed Home Department (JAA02E) Returns a list of active instructors assigned to the selected department.
-
Any/All instructor schedules (JUSDS010): Can be used to return instructor teaching schedules by a series of 5 prompts. Enter as many or as few values into the prompts as desired. The term prompt is required, all other prompts are optional. Results are sorted by course subject, course catalog number and course section.
-
Missing Grades (JAA23): Returns one row of data for each instructor for which grades are “missing” for the most recent grading period. The office of the Registrar breaks missing grades into two categories:
-
Missing B4 deadline: Each grading period has a date/time when final grades are due. This date/time is clearly communicated to instructors and department heads in advance as well as published on the Registrars web page. Grades missing B4 deadline is a very time related category.
-
Missing AFTER deadline. Once the stated deadline has passed, any student who is missing an official course grade receives a grade of “NR” which stands for “Not Reviewed”. This grade will be visible to the student (and all others with access to view grades) within My Madison.
-
Select a displayed number in the “Missing AFTER deadline” column (JAA23A) to drill into the course details of the missing grades.
-
Late history (JAA23B). Follow this hyperlink to return a historical list of late grades entered for the selected instructor.
-
All salaries/tenure (JAA21C): Similar to JAA21 in that it includes just instructional faculty (account code 112600). Allows all colleges to be viewed in one result set. The tenure acquisition number is calculated by subtracting the tenure track start data from the tenure granted date.
-
JOB changes (JAA21D). This series requires some explanation. Although we do not attempt to replicate the entire payroll system within the Student Administration system, there are a few very meaningful transactions which are of great value. One such set of transactions are those which take place against a record/table/process known simply as “JOB changes”. As “things” occur during an employees lifecycle @ JMU, many are recorded and tracked within the HRMS system. Such changes occur as a combination of JOB actions and action reasons (see table below). Some such transactions are associated with money, and such, are desirable tasks to track.
-
Compensation transactions. Are pay increase related changes (equity, adjustments, promotions, etc.)
-
Hire/Rehires/XFR (Transfers).
-
Position.
-
Terminations.
-
Other. With over 200 different possible action/reasons, this represents those which occur with less frequency than those above. The following table lists all of the possible JOB change actions and action reasons.
Action
|
Action description
|
Reason
|
Reason description
|
Times ever used
|
DTA
|
Data Change
|
ABB
|
ABBR Change due to Addl Pay
|
88
|
DTA
|
Data Change
|
CAC
|
Correction-acct code
|
351
|
DTA
|
Data Change
|
CCR
|
Conversion to Comp Reform
|
2
|
DTA
|
Data Change
|
CDP
|
Correction-Department
|
480
|
DTA
|
Data Change
|
CHD
|
Correction - Hire Date
|
5
|
DTA
|
Data Change
|
CJC
|
Correction-Job Code
|
113
|
DTA
|
Data Change
|
CPO
|
Correction - Position
|
186
|
DTA
|
Data Change
|
CPR
|
Correction-Pay Rate
|
1,037
|
DTA
|
Data Change
|
CTA
|
Contract Extension (Academic)
|
372
|
DTA
|
Data Change
|
CTE
|
Contract Extension
|
9,493
|
DTA
|
Data Change
|
CTE
|
Contract Extension (Athletics)
|
9,493
|
DTA
|
Data Change
|
CTR
|
Term of Contract Change
|
436
|
DTA
|
Data Change
|
CTS
|
Contract Extension (Student)
|
686
|
DTA
|
Data Change
|
DEP
|
Department code change
|
244
|
DTA
|
Data Change
|
FLS
|
FLSA Status Change
|
409
|
DTA
|
Data Change
|
FTC
|
Fica Tax Change
|
22,379
|
DTA
|
Data Change
|
NAC
|
New Account Code
|
107
|
DTA
|
Data Change
|
PPE
|
Performance Planning & Eval
|
1
|
DTA
|
Data Change
|
SSC
|
Salary Step Change
|
1,015
|
DTA
|
Data Change
|
SUP
|
Change of Supervisor
|
2,626
|
DTA
|
Data Change
|
UPD
|
Update Data
|
3,852
|
DTA
|
Data Change
|
WJC
|
Wage Job Code Change
|
134
|
HIR
|
Hire
|
AFF
|
Affiliate
|
1,073
|
HIR
|
Hire
|
CCJ
|
Concurrent Job
|
17,564
|
HIR
|
Hire
|
FTP
|
Full-time to Part-time
|
1
|
HIR
|
Hire
|
JSH
|
Job Share
|
1
|
HIR
|
Hire
|
MAD
|
MAD Employee
|
36
|
HIR
|
Hire
|
NEW
|
New Hire
|
58,007
|
HIR
|
Hire
|
PTF
|
Part-time to Full-time
|
37
|
HIR
|
Hire
|
SRT
|
Supplemental Retirement
|
12
|
HIR
|
Hire
|
STA
|
Prior State Agency
|
13
|
HIR
|
Hire
|
STE
|
Student to Employee
|
190
|
HIR
|
Hire
|
TMP
|
Temporary Assignment
|
17
|
LOA
|
Leave of Absence
|
ED
|
Educational Unconditional
|
6
|
LOA
|
Leave of Absence
|
EDU
|
Educational
|
20
|
LOA
|
Leave of Absence
|
EDU
|
Educational Conditional
|
20
|
LOA
|
Leave of Absence
|
FML
|
Family and Medical Leave Act
|
124
|
LOA
|
Leave of Absence
|
HEA
|
Health Reasons
|
34
|
LOA
|
Leave of Absence
|
HEA
|
Health Reasons Conditional
|
34
|
LOA
|
Leave of Absence
|
HTH
|
Health Reasons Unconditional
|
21
|
LOA
|
Leave of Absence
|
LYO
|
Layoff
|
28
|
LOA
|
Leave of Absence
|
MIL
|
Military Service
|
10
|
LOA
|
Leave of Absence
|
PE
|
Personal Unconditional
|
10
|
LOA
|
Leave of Absence
|
PER
|
Personal
|
80
|
LOA
|
Leave of Absence
|
PER
|
Personal Conditional
|
80
|
PAY
|
Pay Rate Change
|
ADJ
|
Adjustment
|
6,288
|
PAY
|
Pay Rate Change
|
ATB
|
Across-The-Board
|
12,935
|
PAY
|
Pay Rate Change
|
CEP
|
Contract Extension Promotion
|
72
|
PAY
|
Pay Rate Change
|
CLS
|
Employee Class Change
|
143
|
PAY
|
Pay Rate Change
|
CRN
|
Contract Renewal
|
349
|
PAY
|
Pay Rate Change
|
CSO
|
Competive Salary Offer
|
154
|
PAY
|
Pay Rate Change
|
CTE
|
Contract Extension w/ Pay Chg
|
1,706
|
PAY
|
Pay Rate Change
|
CTR
|
Term of Contract Change
|
748
|
PAY
|
Pay Rate Change
|
DEM
|
Demotion
|
66
|
PAY
|
Pay Rate Change
|
DMI
|
Demotion Involuntary
|
18
|
PAY
|
Pay Rate Change
|
DMV
|
Demotion Voluntary
|
21
|
PAY
|
Pay Rate Change
|
DRC
|
Downward Role Change
|
1
|
PAY
|
Pay Rate Change
|
DWR
|
Downward Reallocation
|
1
|
PAY
|
Pay Rate Change
|
EAP
|
End Acting Pay
|
5
|
PAY
|
Pay Rate Change
|
ETP
|
End Temporary Pay
|
95
|
PAY
|
Pay Rate Change
|
FLR
|
Floor Increase
|
406
|
PAY
|
Pay Rate Change
|
ICD
|
In Band Adj Change Duties
|
591
|
PAY
|
Pay Rate Change
|
IGA
|
In Grade Adjustment
|
31
|
PAY
|
Pay Rate Change
|
IIA
|
In Band Adj Internal Alignment
|
978
|
PAY
|
Pay Rate Change
|
IKS
|
In Band Adjustment KSA's
|
204
|
PAY
|
Pay Rate Change
|
IRT
|
In Band Adjustment Retention
|
1,313
|
PAY
|
Pay Rate Change
|
JAF
|
JMU AP Faculty
|
171
|
PAY
|
Pay Rate Change
|
JEQ
|
JMU Equity
|
345
|
PAY
|
Pay Rate Change
|
JMT
|
JMU Merit
|
886
|
PAY
|
Pay Rate Change
|
JRT
|
JMU Retention
|
1
|
PAY
|
Pay Rate Change
|
JYS
|
JMU Years of Service
|
175
|
PAY
|
Pay Rate Change
|
LRC
|
Lateral Role Change
|
94
|
PAY
|
Pay Rate Change
|
MER
|
Merit
|
32,114
|
PAY
|
Pay Rate Change
|
MWC
|
Minimum Wage Change
|
122
|
PAY
|
Pay Rate Change
|
MWI
|
Minimum Wage Increase
|
229
|
PAY
|
Pay Rate Change
|
OTH
|
Other
|
31
|
PAY
|
Pay Rate Change
|
PHB
|
Promotion Higher Pay Band
|
71
|
PAY
|
Pay Rate Change
|
PRO
|
Promotion
|
5,328
|
PAY
|
Pay Rate Change
|
REC
|
Job Reallocation
|
610
|
PAY
|
Pay Rate Change
|
RNK
|
Reduction in Rank/salary
|
2
|
PAY
|
Pay Rate Change
|
RTA
|
Retro Adjustment
|
115
|
PAY
|
Pay Rate Change
|
RTR
|
Retro Reallocation
|
11
|
PAY
|
Pay Rate Change
|
SAP
|
Start Acting Pay
|
31
|
PAY
|
Pay Rate Change
|
SER
|
Service Merit
|
2,615
|
PAY
|
Pay Rate Change
|
SHF
|
Shift transfer
|
25
|
PAY
|
Pay Rate Change
|
STP
|
Start Temporary Pay
|
133
|
PAY
|
Pay Rate Change
|
SUP
|
Administrative Supplement
|
3
|
PAY
|
Pay Rate Change
|
URC
|
Upward Role Change
|
192
|
PAY
|
Pay Rate Change
|
VRS
|
VRS 5% Adjustment
|
1,601
|
PLA
|
Paid Leave of Absence
|
ADM
|
Paid LOA - Administrative
|
5
|
PLA
|
Paid Leave of Absence
|
EDU
|
Educational
|
83
|
PLA
|
Paid Leave of Absence
|
FML
|
FMLA Paid Leave of Absence
|
3
|
PLA
|
Paid Leave of Absence
|
MED
|
Medical Paid Leave of Absence
|
1
|
PLA
|
Paid Leave of Absence
|
SEP
|
Separation
|
53
|
PLA
|
Paid Leave of Absence
|
SVR
|
Layoff-Severance
|
35
|
POI
|
Add Person of Interest
|
SRT
|
Supplemental Retirement
|
183
|
POS
|
Position Change
|
ACC
|
Administrative Class Change
|
6
|
POS
|
Position Change
|
ARC
|
Administrative Role Change
|
3
|
Dostları ilə paylaş: |
