Dr. Michelle Amoruso
Dr. Michelle Amoruso

Michelle Amoruso, PhD.

If you need to provide data summaries on a weekly or daily basis, you might want to consider using Excel to query results directly from your database. This will allow you to produce and update standardized reports in seconds, with the flexibility to make rapid reports modifications (well, as fast as you can edit your SQL code). Sure, you can save your existing SQL queries and rerun them every time you need to produce a report. But why not save time and eliminate human error? Additionally, automating your reports production makes it easy to provide multiple levels of data that can be used to identify problems, track resolution, and monitor project goals.

Eliminating Human Error
Instead of pasting in data from your SQL results viewer, why not connect directly to your database and remove human error entirely? Excel will connect to your SQL platform, query all the data from a designated view, and insert it into a worksheet. Once you have established this connection, you will just need to refresh the data to update your spreadsheet.

Monitoring the Data Collection Process
You can use SQL views to identify null values in your database, making it easy to track and resolve missing data. You can also design your views to exclude invalid data, to ensure that only valid data is included in the data summaries. Conversely, you can create a view that includes only invalid data, creating a separate worksheet to flag and track invalid cases.

Tracking the Progress of Project Goals
For a large-scale educational survey research project, we created SQL views to monitor the progress of our survey invitations, issued incentives, and overall participation rates. This was especially useful since we were simultaneously recruiting schools to participate, sending email invitations, overseeing the survey registration and administration process, and issuing gift certificates. Automating our Excel reports made it easy to monitor these different stages of the research project, and evaluate the progress of project goals.

Visualizing the Data

When writing the code for your views, create a series of tables, each of which is a potential flat file that a researcher could easily import into a data analysis program such as SPSS or SAS. Once you create your views and you have created your worksheets containing source data, you can begin to create more accessible summary data, utilizing the Pivot Table and graphing functionalities embedded in Excel. These visuals can be used for internal monitoring or to provide regular updates summarizing progress to partner organizations. In the next posting, I will go into more detail about how to use SQL views to create an automated report that will provide you with run-time data.

– Michelle