Our IPAR group produces many online reports that help administrative offices at WSU to do their job more effectively. These reports can quickly retrieve lists of student data from our central student data repository, ISRS, that meet a variety of criteria. These results can be exported to Excel for further processing.
One example is our International Office’s Admitted Students report. This report allows the user to select a term and retrieve a list of international student’s that have been admitted for that term. The report shows ID number, student name, VISA status, some financial information, etc that is stored in ISRS. Much of this data is meant to indicate if the student has completed all the steps required for them to enroll at WSU.
The International Office finds this report quite useful, but there is data not stored in ISRS that they need to keep track of as well. Has the student booked an airline ticket? Have they checked into the office when they get here? What day are they scheduled for an orientation session? There is a whole list of data they need to keep track of that isn’t stored in ISRS, and doesn’t need to be. This information will never be reported on, and is irrelevant after the student has enrolled for classes.
So the practice has been that at some point the International Office runs the Admitted Student report and exports it to Excel. They then add columns for the extra information they need to keep track of. They sort the data, color code rows for various purposes and generally use this Excel document to keep track of their students between the time they are admitted and they enroll. They are quite happy with this procedure, except that it is difficult for them to keep track of when new students have been admitted and need to be added to this Excel document. They also have a hard time keeping track of when data in ISRS changes and therefore needs to change in the Excel document.
IPAR considered several options for helping the International Office here, from full CRM solutions, custom software, to utilizing SharePoint lists and/or Access databases. Our primary motivator was to come up with a solution that was quick and easy to maintain and that would be a significant improvement to the International Office’s situation, if not a perfect solution.
We and they are pretty happy with what we came up with. IPAR developed a macro-driven Excel document that can read (but not write) data from the ISRS database. The Excel document contains the same data as the Admitted Students report contains, but also allows the International Office to add their own data. Whenever they hit the “Refresh” button, any newly admitted students are added to the file. Any data from ISRS that has changed is updated, and highlighted in green (indicating to the International Office that they should re-assess the student’s status). Any student’s that are no longer admitted are highlighted in red, and the International Office can delete these students once they have assured themselves that the change in admit status wasn’t made in error. The data can be sorted, color coded and so on without breaking the functionality.
This is a pretty new tool for us, and I’m sure we have bugs to work out, but I am very optimistic. This type of “smart” Excel document can be used in many situations across our campus, especially on the administrative side of the house. My biggest concern at this point is that these spreadsheets become too popular, and that supporting them becomes too time-intensive. But, our plan is to stick to just this one for now and learn from the experience, and take it slow from here.