Archive for December 2011

Using Excel to Offer Smarter Online Reports

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.

More technical details about how this spreadsheet works can be found here. A copy of the spreadsheet, including the macros we wrote but scrubbed of any actual student data, can be found here.

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.

Qualtrics Tip: Rich Reporting by Importing Responses

Imagine being the administrator of two different surveys, both delivered to university sophomores, one in fall, and one in spring.  Responses to both surveys have been collected, and the surveys have closed.  At that late stage, you realize that it would be very helpful to explore the correlations between a question on Survey A and several questions on Survey B.  It would also be helpful to include the college affiliation (e.g., College of Business versus College of Liberal Arts) of the respondents in your reporting.  In our office, we frequently find ourselves in similar situations.  Obviously, there are many solutions to the problem, but Qualtrics provides a good one.

It is relatively straightforward in Qualtrics to approach the problem this way:

• create a survey, Survey C, that is a hybrid of Survey A and Survey B
• add an Embedded Data element (a Qualtrics term for extra information attached to a survey response) to hold the college affiliation of each respondent
• fill the new survey with selected answers from Survey A and Survey B
• include data from your student information system (SIS), such as the college of each respondent
• quickly put together charts, response statistics, and crosstab reports to analyze the hybrid data within Qualtrics.

I will follow this post with an example of how we’ve used this technique for quick, ad hoc reporting that brings together data from multiple surveys with SIS information using only duct tape and common household items.  Stay tuned.