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-->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

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 = "<<Enter name of field>>" DEFAULT.MENU.MNEMONIC • LIST ST.PRCS.DEF WITH DEFAULT.MENU.MNEMONIC = "<<Enter Mnemonic:>>" 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} 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

9. Colleague Data for IR:HR/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 XSIW.OTHER.SALARY XSIW.STATE.SALARY HR/Payroll XSTAFF.INFO.WORK HRPER PERPOSWG PAYTODAT PERPOS

10. 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

11. Colleague Data for IR:FTE FTE XCE.ICR XCU.ICR XCU.ACAD.PROGRAM XCU.TOTAL.STUDENTS

12. Colleague Data for IR:Financial Aid Financial Aid TA.{YEAR} SA.{YEAR}

13. Colleague Data for IR:Other 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 ACAD.PROGRAMS SEC.CURRENT.STATUS A - Active C - Cancelled H - Hold for Cancellation P - Pending

14. 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 STTR.ACAD.PROGRAMS STTR.ACTIVE.PROGRAMS STTR.ADMIT.STATUS STTR.ALIEN.FLAG STTR.CURRENT.STATUS STTR.START.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.COURSE 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 XNC.EDUCATIONAL.LEVEL APP.START.TERMS PST.STUDENT.ACAD.CRED 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 Adult 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 Placement 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 = "<<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

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 ACADYEARTERM ORDER AltTerm QSCollTerm QSIIPSTerm StartDate EndDate 2002 2002FA 86 2002*03 "2002FA" "2002*03" 9/1/2002 12/31/2002 2002 2003SP 87 2003*01 "2003SP" "2003*01" 1/1/2003 5/31/2003 2002 2003SU 88 2003*02 "2003SU" "2003*02" 6/1/2003 8/31/2003 2003 2003FA 89 2003*03 "2003FA" "2003*03" 9/1/2003 12/31/2003 2003 2004SP 90 2004*01 "2004SP" "2004*01" 1/1/2004 5/31/2004 2003 2004SU 91 2004*02 "2004SU" "2004*02" 6/1/2004 8/31/2004 2004 2004FA 92 2004*03 "2004FA" "2004*03" 9/1/2004 12/31/2004 2004 2005SP 93 2005*01 "2005SP" "2005*01" 1/1/2005 5/31/2005 2004 2005SU 94 2005*02 "2005SU" "2005*02" 6/1/2005 8/31/2005 2005 2005FA 95 2005*03 "2005FA" "2005*03" 9/1/2005 12/31/2005 2005 2006SP 96 2006*01 "2006SP" "2006*01" 1/1/2006 5/31/2006 2005 2006SU 97 2006*02 "2006SU" "2006*02" 6/1/2006 8/31/2006 2006 2006FA 98 2006*03 "2006FA" "2006*03" 9/1/2006 12/31/2006 2006 2007SP 99 2007*01 "2007SP" "2007*01" 1/1/2007 5/31/2007 2006 2007SU 100 2007*02 "2007SU" "2007*02" 6/1/2007 8/31/2007

23. 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

24. Access: Reuse Common Tables

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