Colleague Reporting: Finding and Manipulating Data

1
Colleague Reporting Finding and Manipulating
Data

• Matthew H. Smith
• Research Coordinator
• Pitt Community College
• Greenville, NC

2
Things You Need

• Query Builder
• SAAL SAVELIST Algebra
• ST--gtCC--gtRUL--gtRDELRDEPRFEI
• 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--gtAC--gtSTM--gtSTRSSTDT
• Student Terms Summary and Student Terms Detail
• View RDVF
• Virtual Fields

3
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

4
Finding Data ST Screen/Process

• LIST ST.PRCS.DEF
• WITH PROCESS.DATA.ELEMENTS "ltltEnter name of
  fieldgtgt" DEFAULT.MENU.MNEMONIC
• LIST ST.PRCS.DEF
• WITH DEFAULT.MENU.MNEMONIC "ltltEnter
  Mnemonicgtgt" PROCESS.DATA.ELEMENTS

5
Colleague Data for IR

• Common Questions/Requests
• Enrollment (Term, Year, Trend)
• Completers
• Non-Completers
• FTE
• Grade Distribution
• Placement Scores/Developmental Studies
• Persistence

6
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

7
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

8
Colleague Data for IR
Financial Aid TA.YEAR SA.YEAR
People FOREIGN.PERSON PERSON
Student ACAD.CREDENTIALS APPLICANTS INSTITUTIONS.A
TTEND STUDENT.NON.COURSES XNC.PERSON STUDENTS STUD
ENT.TERMS STUDENT.ACAD.CRED STUDENT.COURSE.SEC
FTE XCE.ICR XCU.ICR
HR/Payroll XSTAFF.INFO.WORK HRPER PERPOSWG PAYTODA
T PERPOS
Other COUNTIES DIVISIONS DEPTS COURSE.SECTIONS ACA
D.PROGRAMS
9
Colleague Data for IRHR/Payroll
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 XSI
W.OTHER.SALARY XSIW.STATE.SALARY
HR/Payroll XSTAFF.INFO.WORK HRPER PERPOSWG PAYTODA
T PERPOS
10
Colleague Data for IRPeople
People FOREIGN.PERSON PERSON
FPER.ALIEN.STATUS
Y Yes/NonResident R Resident Alien U
Undocumented Alien X Error/US Citizen
11
Colleague Data for IRFTE
FTE XCE.ICR XCU.ICR
XCU.ACAD.PROGRAM XCU.TOTAL.STUDENTS
12
Colleague Data for IRFinancial Aid
Financial Aid TA.YEAR SA.YEAR
13
Colleague Data for IROther
Program Status A - Approved by Sys Office P -
Pending AL - Approved Locally AH - Approved by
Host College AP - Approved By President S -
Submitted for Approval EA - External Approval I -
Inactive O - Obsolete D - Disapproved DH -
Disapproved by Host College ED - External
Disapproval
SEC.ACTIVE.STUDENT.COUNT SEC.CAPACITY SEC.CONTACT.
HOURS SEC.COURSE.NAME SEC.CURRENT.STATUS SEC.DEPT.
PCTS SEC.DEPTS SEC.FIRST.FACULTY SEC.FULL.FACULTY
SEC.INSTR.METHODS SEC.USER1 SEC.PRINTED.COMMENTS X
.SEC.TOT.CONTACT XSEC.MEM.HRS
Other COUNTIES DIVISIONS DEPTS COURSE.SECTIONS ACA
D.PROGRAMS
SEC.CURRENT.STATUS A - Active C - Cancelled H -
Hold for Cancellation P - Pending
14
Colleague Data for IRStudent
Student ACAD.CREDENTIALS APPLICANTS INSTITUTIONS.A
TTEND STUDENT.NON.COURSES XNC.PERSON STUDENTS STUD
ENT.TERMS STUDENT.ACAD.CRED STUDENT.COURSE.SEC
STTR.ACAD.PROGRAMS STTR.ACTIVE.PROGRAMS STTR.ADMIT
.STATUS STTR.ALIEN.FLAG STTR.CURRENT.STATUS STTR.S
TART.TERM STTR.STUDENT.LOAD X.STTR.ACTIVE.PROGRAMS
X.STTR.PRI.PROG.FLAG XSTTR.ACAD.YEAR
STC.ATT.CRED STC.CMPL.CRED STC.CRED STC.CRED.TYPE
STC.CURRENT.STATUS STC.FINAL.GRADE STC.STNC.NON.CO
URSE STC.STNC.SCORE STC.VERIFIED.GRADE X.STC.VRFD.
GRADE
ACAD.ACAD.PROGRAM ACAD.CCD ACAD.END.DATE
INSTA.END.DATES INSTA.GRAD.TYPE INSTA.INSTITUTIONS
.ID
STNC.NON.COURSE STNC.SCORE
APP.START.TERMS
PST.STUDENT.ACAD.CRED
XNC.EDUCATIONAL.LEVEL
A - Add C - Canceled D - Dropped N -
New NC - Non-Course Credit (similar to transfer
credit CLEP, AP credit, Military, etc.) NP - Not
Paid PR - Preliminary (used only with transfer
credit) TR - Transfer Credit (CCL courses) W -
Withdrew X - Inactive XT - External Transfer
Credit (non-CCL)
0 (Never Attended) 1, 2, 3, 4, 5, 6, 7, 8, 9, 10,
11 (grade completed) 12 (completed high school) -
- (GED), 13 (Adult High School Diploma) 14 (One
Year Vocational Diploma) 15 (Associate Degree)
16 (Bachelor's Degree) 17 (Master's Degree or
higher)
STC.ATT.CRED Attempted Credits (No OW etc, but
with failing grades) STC.CMPL.CRED Completed
Credits Credits Passed (No F's, W, OW,
etc) STC.CRED Registered Credit All Credit
15
Credit Types for STUDENT.ACAD.CRED
Student STUDENT.ACAD.CRED STC.CRED.TYPE AHS Ad
ult High School BSP Basic Skills CE Continuing
Education CL Collaborative CN Consortium DE
Developmental HSE AHS Exams HSN AHS
NonCourse HST Adult High School
Transfer IN Instructional NC NonCourse PTC P
lacement Test Credit TD Transfers of
Developmental Course TP Tech Prep TR Transfer

16
Efficiency

• Prompted Conditions
• Combining Queries
• Access
• Automation
• Data Storage
• Consistent Data Format
• (Safari/SAS)

17
Efficiency Combined Queries
SELECT STUDENTS WITH STU.TERMS "ltltEnter 4 Digit
YeargtgtFA SAVING UNIQUE _at_ID
SELECT INSTITUTIONS.ATTEND WITH INSTA.END.DATES
GE "01/01/ltltEnter 2 Digit Yeargtgt" 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
18
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)

19
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

20
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

21
Access Reuse Common Tables

• EthCode EthCodeDesc
• 1 White (Non-Hispanic)
• 2 Black (Non-Hispanic)
• 3 American Indian / Alaskan Native
• 4 Hispanic
• 5 Asian / Pacific Islander
• 6 Other / Unknown / Multiple

Institution InstitutionDesc 34550 D.H.
Conley 390631 South Central 34304 North
Pitt 34288 Ayden-Grifton 34552 J.H.
Rose 34475 Farmville Central 49917 Ayden-Grifton
22
Access Reuse Common Tables

• ACADYEAR TERM ORDER AltTerm QSCollTerm QSIIPSTerm
  StartDate EndDate
• 2002 2002FA 86 200203 "2002FA" "200203"
  9/1/2002 12/31/2002
• 2002 2003SP 87 200301 "2003SP" "200301"
  1/1/2003 5/31/2003
• 2002 2003SU 88 200302 "2003SU" "200302"
  6/1/2003 8/31/2003
• 2003 2003FA 89 200303 "2003FA" "200303"
  9/1/2003 12/31/2003
• 2003 2004SP 90 200401 "2004SP" "200401"
  1/1/2004 5/31/2004
• 2003 2004SU 91 200402 "2004SU" "200402"
  6/1/2004 8/31/2004
• 2004 2004FA 92 200403 "2004FA" "200403"
  9/1/2004 12/31/2004
• 2004 2005SP 93 200501 "2005SP" "200501"
  1/1/2005 5/31/2005
• 2004 2005SU 94 200502 "2005SU" "200502"
  6/1/2005 8/31/2005
• 2005 2005FA 95 200503 "2005FA" "200503"
  9/1/2005 12/31/2005
• 2005 2006SP 96 200601 "2006SP" "200601"
  1/1/2006 5/31/2006
• 2005 2006SU 97 200602 "2006SU" "200602"
  6/1/2006 8/31/2006
• 2006 2006FA 98 200603 "2006FA" "200603"
  9/1/2006 12/31/2006
• 2006 2007SP 99 200701 "2007SP" "200701"
  1/1/2007 5/31/2007
• 2006 2007SU 100 200702 "2007SU" "200702"
  6/1/2007 8/31/2007

23
Access Reuse Common Tables
Grade AttCred CmplCred MSCrseCmpl MSCrseCmplDesc M
SGTD 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 Abov
e 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
24
Access Reuse Common Tables
msTest msRawScore msMatchValue msNormalScore msPlacement msSubject msLevel msDevCount CPTAR 82 CPTAR82 116.1454545 MAT-070eq MAT DEV 1 ASTN 46 ASTN46 116.0769231 MAT-070eq MAT DEV 1 CPTSS 88 CPTSS88 115.9130435 NoDEV ENGLab NoDEV 0 COMPA 62 COMPA62 115.5185185 MAT-070eq MAT DEV 1 COMW 76 COMW76 115.4117647 NoDEV ENGLab NoDEV 0 CPTAR 81 CPTAR81 115.2545455 MAT-070eq MAT DEV 1 ASTW 45 ASTW45 115 NoDEV ENGLab NoDEV 0 CPTRC 69 CPTRC69 115 ENG-095 ENG DEV 1 CPTSS 87 CPTSS87 114.8478261 NoDEV ENGLab NoDEV 0 COMPA 61 COMPA61 114.6111111 MAT-070eq MAT DEV 1 CPTAR 80 CPTAR80 114.3636364 MAT-070eq MAT DEV 1 COMW 75 COMW75 113.9705882 NoDEV ENGLab NoDEV 0 COME 63 COME63 113.8947368 ENG-095 ENG DEV 1 CPTSS 86 CPTSS86 113.7826087 NoDEV ENGLab NoDEV 0 COMPA 60 COMPA60 113.7037037 MAT-070eq MAT DEV 1 CPTAR 79 CPTAR79 113.4727273 MAT-070eq MAT DEV 1
25
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)

26
Colleague Data for IR The Big Picture

• Report Types/Reporting Architecture
• Datatel Presentation at SEDUG 2005