Transcript Colleague Reporting: Finding and Manipulating Data

Colleague Reporting:
Finding and
Manipulating Data
Matthew H. Smith
Research Coordinator
Pitt Community College
Greenville, NC
Things You Need

Query Builder

SAAL – SAVELIST Algebra

ST-->CC-->RUL-->RDEL&RDEP&RFEI
• RDEL: Database Element Linkages (Enter a Field Name)
• RDEP: Database Element Presentation (Enter a Field Name)
• RFEI: File Element Inquiry (Enter a File Name)

ST-->AC-->STM-->STRS&STDT
• Student Terms Summary and Student Terms Detail

View RDVF
• Virtual Fields
Finding Data: From Users

Find out how user adds data to
system
• Ex. Graduate a student in BSP by
entering graduation date in a certain
field.
• Click on field
• Click red “Help” question mark
• Read off name of field in title bar

Ex. “HELP information for the field:
STTR.STUDENT”
Finding Data: ST Screen/Process


LIST ST.PRCS.DEF
WITH PROCESS.DATA.ELEMENTS =
"<<Enter name of field>>"
DEFAULT.MENU.MNEMONIC
LIST ST.PRCS.DEF
WITH DEFAULT.MENU.MNEMONIC =
"<<Enter Mnemonic:>>"
PROCESS.DATA.ELEMENTS
Colleague Data for IR

Common Questions/Requests
• Enrollment (Term, Year, Trend)
• Completers
• Non-Completers
• FTE
• Grade Distribution
• Placement Scores/Developmental
Studies
• Persistence
Colleague Data for IR

Things to Consider:
• Time



Term – Student Files (either by (S) or including (M))
Date Range – Graduates, Institutions Attended
None – PERSON, POSITITONS, (CONTRACTS)
• Status

What to include/exclude
• Compatibility

Managing compatible data between duplicated tables
while preserving integrity of summative statistics
Colleague Data for IR
www.pittcc.edu/planres/ccpro
25 Colleague Files with ~200 annotated fields
~50 Assorted Queries
Non-Completers Process with Queries (Uses SAAL)
SACS Institutional Profile Database
IPEDS HR Database
Colleague Data for IR
Financial Aid
TA.{YEAR}
SA.{YEAR}
Student
ACAD.CREDENTIALS
APPLICANTS
INSTITUTIONS.ATTEND
STUDENT.NON.COURSES
XNC.PERSON
STUDENTS
STUDENT.TERMS
STUDENT.ACAD.CRED
STUDENT.COURSE.SEC
People
FOREIGN.PERSON
PERSON
HR/Payroll
XSTAFF.INFO.WORK
HRPER
PERPOSWG
PAYTODAT
PERPOS
FTE
XCE.ICR
XCU.ICR
Other
COUNTIES
DIVISIONS
DEPTS
COURSE.SECTIONS
ACAD.PROGRAMS
Colleague Data for IR:
HR/Payroll
HR/Payroll
XSTAFF.INFO.WORK
HRPER
PERPOSWG
PAYTODAT
PERPOS
X.XSIW.LOCAL.SALARY*
XSIW.EMP.CLASSIFICATION
XSIW.ETHNIC
XSIW.FEDERAL.SALARY
XSIW.GENDER
XSIW.LOCAL.SALARY
XSIW.MONTHS.OF.EMPLOYMENT
XSIW.NON.RESIDENT.ALIEN
XSIW.OCR.CODE
XSIW.ORIG.EMPLOYMENT.DATE
XSIW.OTHER.SALARY
XSIW.STATE.SALARY
Colleague Data for IR:
People
People
FOREIGN.PERSON
PERSON
FPER.ALIEN.STATUS
Y = Yes/NonResident
R = Resident Alien
U = Undocumented Alien
X = Error/US Citizen
Colleague Data for IR:
FTE
FTE
XCE.ICR
XCU.ICR
XCU.ACAD.PROGRAM
XCU.TOTAL.STUDENTS
Colleague Data for IR:
Financial Aid
Financial Aid
TA.{YEAR}
SA.{YEAR}
Colleague Data for IR:
Other
Other
COUNTIES
DIVISIONS
DEPTS
COURSE.SECTIONS
ACAD.PROGRAMS
SEC.CURRENT.STATUS
A - Active
C - Cancelled
H - Hold for Cancellation
P - Pending
Program
Status
SEC.ACTIVE.STUDENT.COUNT
A
- Approved by Sys Office
SEC.CAPACITY
P
- Pending
SEC.CONTACT.HOURS
AL
- Approved Locally
SEC.COURSE.NAME
AH
- Approved by Host College
SEC.CURRENT.STATUS
AP
- Approved By President
SEC.DEPT.PCTS
S
- Submitted for Approval
SEC.DEPTS
EA
- External Approval
SEC.FIRST.FACULTY
ISEC.FULL.FACULTY
- Inactive
O
- Obsolete
SEC.INSTR.METHODS
D
- Disapproved
SEC.USER1
DH
- Disapproved by Host College
SEC.PRINTED.COMMENTS
ED
- External Disapproval
X.SEC.TOT.CONTACT
XSEC.MEM.HRS
Colleague Data for IR:
Student
Student
ACAD.CREDENTIALS
APPLICANTS
INSTITUTIONS.ATTEND
STUDENT.NON.COURSES
XNC.PERSON
STUDENTS
STUDENT.TERMS
STUDENT.ACAD.CRED
STUDENT.COURSE.SEC
A
- Add
STTR.ACAD.PROGRAMS
STC.ATT.CRED
STTR.ACTIVE.PROGRAMS
STC.CMPL.CRED
STTR.ADMIT.STATUS
STC.CRED
STTR.ALIEN.FLAG
ACAD.ACAD.PROGRAM
STC.CRED.TYPE
INSTA.END.DATES
STTR.CURRENT.STATUS
STNC.NON.COURSE
ACAD.CCD
STC.CURRENT.STATUS
INSTA.GRAD.TYPE
XNC.EDUCATIONAL.LEVEL
PST.STUDENT.ACAD.CRED
APP.START.TERMS
STTR.START.TERM
STNC.SCORE
ACAD.END.DATE
STC.FINAL.GRADE
INSTA.INSTITUTIONS.ID
STTR.STUDENT.LOAD
STC.STNC.NON.COURSE
X.STTR.ACTIVE.PROGRAMS
STC.STNC.SCORE
X.STTR.PRI.PROG.FLAG
STC.VERIFIED.GRADE
XSTTR.ACAD.YEAR
X.STC.VRFD.GRADE
0 (Never
Attended)
C - Canceled
1, 2,D3,-4,
5, 6, 7, 8, 9, 10, 11 (grade completed)
Dropped
12 (completed
high school)
N - New
STC.ATT.CRED
Attempted
Credits
OW
etc, but
with failing grades)
NC - Non-Course
transfer
credit; CLEP, AP(No
credit,
Military,
etc.)
- - (GED),
13 (AdultCredit
High(similar
SchooltoDiploma)
NP - Not
Paid
14 (One
Year
Vocational Diploma)
STC.CMPL.CRED
Completed Credits
Credits Passed (No F's, W, OW, etc)
PR
Preliminary
(used
only
with
transfer
credit)
15 (Associate Degree)
STC.CRED
Credit
All Credit
TR - Transfer Credit (CCLRegistered
courses)
16 (Bachelor's
Degree)
W - Withdrew
17 (Master's
Degree or higher)
X - Inactive
XT - External Transfer Credit (non-CCL)
Credit Types for
STUDENT.ACAD.CRED
Student
STUDENT.ACAD.CRED
STC.CRED.TYPE
AHS
BSP
CE
CL
CN
DE
HSE
HSN
HST
IN
NC
PTC
TD
TP
TR
Adult High School
Basic Skills
Continuing Education
Collaborative
Consortium
Developmental
AHS Exams
AHS NonCourse
Adult High School Transfer
Instructional
NonCourse
Placement Test Credit
Transfers of Developmental Course
Tech Prep
Transfer
Efficiency

Prompted Conditions
Combining Queries

Access

• Automation
• Data Storage
• Consistent Data Format

(Safari/SAS)
Efficiency: Combined Queries
SELECT STUDENTS WITH
STU.TERMS = "<<Enter 4 Digit
Year:>>FA” SAVING UNIQUE @ID
SELECT INSTITUTIONS.ATTEND WITH
INSTA.END.DATES GE "01/01/<<Enter 2
Digit Year:>>" AND WITH
INSTA.GRAD.TYPE = "Y" AND WITH
INSTA.INSTITUTIONS.ID = "0049917"
SAVING UNIQUE INSTA.PERSON.ID
SAVE.LIST MS_HS_STUDENTS
Saves unique list of student IDs for students graduating from a particular
institution after a certain date who were actively enrolled in a particular
term
Efficiency: Microsoft Access

More General Colleague Queries
• Run Faster
• Less Strain on Server


Can save logic
More Complex Manipulation
• SQL
• Crosstabs
• Pivot Tables



Automation with Visual Basic for Applications
(VBA)
Interface programming language (VBA) with
database language (SQL)
Data Storage (Small Scale Data Warehouse)
Efficiency: Microsoft Access


Copy and Paste Between Applications
Import
• Excel/Text/DB Format
• Colleague Data (Import)
• Internet Survey Data

Export
• Excel/Text/DB Format
• Access to Access


.pdf based output
Reuse Common Tables
Efficiency: Microsoft Access

Recommendations
• Large Hard Drive (80GB or Larger)
• Fast Pentium Processor (~3GHz)
• RAM (1GB Min.)
• Backup System


DVD Burner (Dual Layer ~8.5 GB/Disc)
Additional Hard Drive
• Internal/External

Drawbacks
• No Statistical Capability
Access: Reuse Common Tables
EthCode
1
2
3
4
5
6
EthCodeDesc
White (Non-Hispanic)
Black (Non-Hispanic)
American Indian / Alaskan Native
Hispanic
Asian / Pacific Islander
Other / Unknown / Multiple
Institution
34550
390631
34304
34288
34552
34475
49917
InstitutionDesc
D.H. Conley
South Central
North Pitt
Ayden-Grifton
J.H. Rose
Farmville Central
Ayden-Grifton
Access: Reuse Common Tables
ACADYEAR
2002
2002
2002
2003
2003
2003
2004
2004
2004
2005
2005
2005
2006
2006
2006
TERM
2002FA
2003SP
2003SU
2003FA
2004SP
2004SU
2004FA
2005SP
2005SU
2005FA
2006SP
2006SU
2006FA
2007SP
2007SU
ORDER
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
AltTerm
2002*03
2003*01
2003*02
2003*03
2004*01
2004*02
2004*03
2005*01
2005*02
2005*03
2006*01
2006*02
2006*03
2007*01
2007*02
QSCollTerm
"2002FA"
"2003SP"
"2003SU"
"2003FA"
"2004SP"
"2004SU"
"2004FA"
"2005SP"
"2005SU"
"2005FA"
"2006SP"
"2006SU"
"2006FA"
"2007SP"
"2007SU"
QSIIPSTermStartDate
"2002*03"
9/1/2002
"2003*01"
1/1/2003
"2003*02"
6/1/2003
"2003*03"
9/1/2003
"2004*01"
1/1/2004
"2004*02"
6/1/2004
"2004*03"
9/1/2004
"2005*01"
1/1/2005
"2005*02"
6/1/2005
"2005*03"
9/1/2005
"2006*01"
1/1/2006
"2006*02"
6/1/2006
"2006*03"
9/1/2006
"2007*01"
1/1/2007
"2007*02"
6/1/2007
EndDate
12/31/2002
5/31/2003
8/31/2003
12/31/2003
5/31/2004
8/31/2004
12/31/2004
5/31/2005
8/31/2005
12/31/2005
5/31/2006
8/31/2006
12/31/2006
5/31/2007
8/31/2007
Access: Reuse Common Tables
Grade AttCred CmplCred MSCrseCmpl MSCrseCmplDesc MSGTD MSGTDDesc
GPA Cred Legend
Value
T
N
Y
1
Y
1
Y
N
Transfer
IP
N
N
1
Y
0
N
N
In Progress
AP
N
Y
1
Y
1
Y
N
PCC Advanced Placement
NA
N
N
0
N
0
N
N
Never Attended
NG
N
N
0
N
0
N
N
No Grade Submitted
S
N
Y
1
Y
1
Y
N
Satisfactory
U
N
N
1
Y
0
N
N
Unsatisfactory
OW.
N
N
0
N
0
N
N
Official Withdrawal
I
N
N
1
Y
0
N
N
Incomplete
OW
N
N
0
N
0
N
N
Official Withdrawal
AU
N
N
1
Y
0
N
N
Audit
A
Y
Y
1
Y
1
Y
Y
Excellent
4
B
Y
Y
1
Y
1
Y
Y
Above Average
3
W
Y
N
0
N
0
N
Y
Unofficial Withdrawal
0
C
Y
Y
1
Y
1
Y
Y
Average
2
D
Y
Y
1
Y
0
N
Y
Below Average
1
F
Y
N
1
Y
0
N
Y
Failing
0
Access: Reuse Common Tables
msTest
CPTAR
ASTN
CPTSS
COMPA
COMW
CPTAR
ASTW
CPTRC
CPTSS
COMPA
CPTAR
COMW
COME
CPTSS
COMPA
CPTAR
msRawScore
82
46
88
62
76
81
45
69
87
61
80
75
63
86
60
79
msMatchValue
CPTAR82
ASTN46
CPTSS88
COMPA62
COMW76
CPTAR81
ASTW45
CPTRC69
CPTSS87
COMPA61
CPTAR80
COMW75
COME63
CPTSS86
COMPA60
CPTAR79
msNormalScore
116.1454545
116.0769231
115.9130435
115.5185185
115.4117647
115.2545455
115
115
114.8478261
114.6111111
114.3636364
113.9705882
113.8947368
113.7826087
113.7037037
113.4727273
msPlacement
MAT-070eq
MAT-070eq
NoDEV
MAT-070eq
NoDEV
MAT-070eq
NoDEV
ENG-095
NoDEV
MAT-070eq
MAT-070eq
NoDEV
ENG-095
NoDEV
MAT-070eq
MAT-070eq
msSubject
MAT
MAT
ENGLab
MAT
ENGLab
MAT
ENGLab
ENG
ENGLab
MAT
MAT
ENGLab
ENG
ENGLab
MAT
MAT
msLevel
DEV
DEV
NoDEV
DEV
NoDEV
DEV
NoDEV
DEV
NoDEV
DEV
DEV
NoDEV
DEV
NoDEV
DEV
DEV
msDevCount
1
1
0
1
0
1
0
1
0
1
1
0
1
0
1
1
Colleague Data for IR:
The Big Picture

Reporting Mechanics
• Retrieval
 Query Builder
(XLIST/XSELECT)


(Safari)
(SAS)
• Manipulating
 Access
 Excel
 (Safari)
 (SAS)
 (SPSS)
• Dissemination
 Web: pdf, (OLAP)
 Email pdf (Access, XL)
 Excel
 (Safari)
 (SAS)
Colleague Data for IR:
The Big Picture

Report Types/Reporting Architecture
• Datatel Presentation at SEDUG 2005