I’m sure every systems librarian does it on an almost weekly basis. I’m talking about writing reports that extract data out of a system, in this case our ILMS, and presenting it in a nice way for users to use. One of my goals for all of this report writing is coming up with a system whereby the reports generate themselves. I’ve recently completed a series of Perl scripts that allow me to achieve this goal.
The system has three main components. They are:
- Custom scripts for data extraction
- Custom scripts for report generation
- A generalised Perl script for emailing reports
Custom scripts for data extraction
Each report is always different. Typically differences include what data is extracted, how it is ordered, time periods for specific data etc. Each report has its own script that extracts the data required. They all have similarities in that they extract data from the Oracle database of our ILMS, and then copy it to a small Linux based server. For those that are interested it is an Ubuntu Linux based server.
Custom scripts for report generation
At the moment each report has a script that is used to generate the report. People need reports that easy to use, so I’m using the excellent Spreadsheet::WriteExcel Perl module to create Microsoft Excel workbooks. The brilliant thing with this module is that it doesn’t need a copy of Microsoft Excel to interface with to create the spreadsheets. In this way I can generate reports on the Linux based server, that the users can use the familiar Microsoft Excel program to work with.
Currently there is one script for each report because they interface with data held by the Calling All Numbers website. The other day I posted about changes I was making to the database, this is why. I plan on having a more general script that will be able to take a data extract and produce a spreadsheet as well. The spreadsheet is then moved into a directory for emailing to the nominated user or users.
A Generalised Perl script for emailing reports
Lastly there is a Perl script that I use to email reports to users. The hard work of emailing the reports as an attachment to an email is carried out by the very useful MIME::Lite Perl module. Accompaning the script is a small file that lists report file names and matches these to email addresses. Once all of the emails have been sent, the script archives a copy of the file in a different directory. This means we have a copy in case something odd happens with the email system.
I’m very happy with the system and the way it ties everything together. I’ll be interested to see how it scales of the next couple of months as more reports are added.
