Create Interactive and Customizable Reports with Software You Already Have! David Rudden Director of Institutional Resea

1. Create Interactive and Customizable Reports with Software You Already Have! David Rudden Director of Institutional Research Elgin Community College 11/5/2010

2. A Brief History There are plenty of reporting and data mining tools out there. Why did we try to make our own at ECC?

3. A Brief HistoryIt’s all Achieving the Dream’s fault! We were confronted with a harsh reality in June, 2009 • Long story short, it was all Achieving the Dream’s fault! • Implementation plan had beginning of roll-out to rest of college by start of fall term, using key findings as talking points • Had to sift through a lot of institutional data in a relatively short period of time • Keywords = ‘Disaggregation’ and ‘Longitudinal’ • Caused a shift in how and what types of data we had typically looked at

4. A Brief History (cont’d)Why did we even bother trying to make a homemade tool? We wanted something that would be easy to use (and create) • Our AtD data team included some members who hadn’t previously worked with data much (or at all). • How do we get as many members of the data team (and maybe others throughout the college) involved when they don’t have access to or aren’t familiar with our institutional data? • Didn’t want this to end up being a case where the IR office was just spitting out reports to people • The college was in the process of purchasing a data warehouse but the time-line didn’t mesh with what we needed to do for that first year of Achieving the Dream • i.e., we needed something quick and cheap to get us through that first year of Achieving the Dream

5. The Key Ingredients Hint: it’s software that you already have

6. Key IngredientsMultiple reporting tool formats, same foundation of software • SPSS (or other statistical software of your choosing) • Mainly used to convert your raw data into something useable for the end product, it’s the first step in the process - this is where the magic happens! • Need this piece to merge multiple files together and to create dummy-coded variables specifically for use in end product • Inevitably, when someone asks “This is great, can we just add to the tool?”, this is where you have to incorporate those changes • MS Access • Once the data is set up like you want it, you have to store it somewhere • More universally compatible than storing data as an SPSS or SAS dataset • Also has some nice security features that make it useful • MS Excel • The homemade reporting tool – the only part that everyone else will see • We found the Pivot Tables feature to be really useful • Hate giving any MS products free publicity, but Excel and Access work really well together

7. Starting Simple Course Enrollments

8. Course Enrollment ToolUnit of Analysis is Course

9. Course Enrollment Tool Some Key Syntax and the Data Source “Successful” is only assigned values if they get a certain grade (all other grades are blank) because these were used in spreadsheet to tally counts of students Note that “Success Rate” does not include missing/withdrawn grades. Gives the success rate for only students who COMPLETE the class. “Withdraw Rate”, on the other hand, assigns a 1 or 0 to all grades assigned. This gives the withdrawal rate out of all students who ENROLLED in the course The Access Dataset

10. Course ProgressionCombining Course and Student as Unit of Analysis

11. Course Progression Some Key Syntax and the Data Source The real magic for this one happens in a special query within the Access database. SPSS is still used to create two datasets and some dummy-coded variables, but this query (in SQL) merges the two course files together. When the two course files are merged, every course enrollment (for a particular student) is merged with every other course enrollment for that same student. Unless the first course is distinct from the second course occurs in a term before the 2nd course is enrolled in, the code to the left recodes all of the dummy-coded values created in SPSS to blanks. The Access Dataset

12. Making it Longitudinal Student Outcomes

13. Student OutcomesUnit of Analysis is Student

14. Course Progression Some Key Syntax and the Data Source Calculating 4-yr grad rate only for those who actually transferred to a 4-yr school after ECC. All others are blank. The Access Dataset

15. The “Developmental Climb”Combining Student and Course as Units of Analysis

16. The “Developmental Climb” Some Key Syntax and the Data Source The syntax above recodes grade fields from AtD Term Data submission so that we have a dummy-coded “Success” indicator for the pivot tables. The term file has a separate record for each term that a student enrolled in over a 3 or 4 year period. When trying to convert this into a dataset where each student has a unique record, we have to collapse all of the info from those multiple terms. The code to the left takes the earliest instance of a student’s outcome in each of the respective courses, giving priority to successful instances. The Access Dataset

17. Some Prototypes We’re branching out from AtD data

18. Tracking ApplicantsWe’re an open-enrollment college, but we’re still interested in the “yield”

19. Tracking ApplicantsSome Key Syntax and the Data Source The ApplyAfterInquiry field is only counted for students who inquired. If a student didn’t inquire, the value is blank. This is important in setting the denominator for the percentages in the spreadsheets. The Access Dataset

20. Common Course CombinationsUseful for planning learning communities (or at least planning to talk about LC’s)

21. Common Course CombinationsSome Key Syntax and the Data Source Setting up course combinations to be displayed in spreadsheet The Access Dataset

22. Some Parting Tips Maybe this slide should have been at the beginning of the presentation? Oh well.

23. Parting TipsA couple of things that may make the whole process a lot smoother and easier • Try to keep each pivot table at the same level of analysis • Course, Student by Course by Term, Student Career Milestones, etc. • Make the Access database and Excel spreadsheet password protected • Will help prevent having to go back to re-do anything that gets accidentally changed • Will also allow you to include fields in original data source that you don’t want others to get access to through the pivot tables (e.g., names, IDs, etc.) • Can add password to Excel through “Save as” options • Can add password encryption to Access through “Database Tools” (but must open “exclusively” first) • Keep the pivot table fields as minimalist as possible • But put as many fields in “Report Filter” area as you want • The more variables in the table, the more confusing it gets • Linking the Excel and Access files together can be a double-edged sword • Makes it much quicker and easier to use Excel tables • But Excel table also has to have a link to the Access database (e.g., on same network) • Requires a little bit of planning when setting these up