Since the beginning of this year, the University of Lincoln has used its Repository as the ‘system of record’ for internal monitoring and reporting on research activity. In particular, Quarterly Research Output Reports are generated, every three months, from the Repo. These reports contain lists of ‘substantive‘ research outputs which first appeared “in published form” (or equivalent for non-textual outputs) during the relevant quarter.
We work one full quarter in hand (to give people plenty of time to deposit/record their publications on the Repository), so we’ve just produced the report for Q4 (October-December) of 2010.
At the moment, the finished reports are treated as confidential: though the information from which they’re automatically generated is freely available on the Repository.
Here’s how we do it:
Step 1. Imagine a Repository search for all items published or in press, from an individual department of the University. It’d look something like this.
Step 2. Export the search as RSS with citations.
Step 3. Next, use Google Docs’ =ImportFeed() function to import the RSS feed into a Google spreadsheet. To save having to edit the [rather long and horrible] formula for each of the University’s 21 departments, and also from having to update it for each quarter, we’ve used cell references within the formula to pick those values up from elsewhere in the spreadsheet. (Those cell references are coloured red and blue in the formula below.)
We’ve also used a couple | of custom Google scripts to make it easier to look up the reference ID for each department (in the Google spreadsheet it’s the name of each [work]sheet; one sheet per department) and use that value in the EPrints RSS query string.
Step 4. Once all the departmental sheets have been populated with RSS data, export the result to MS Excel, format nicely using Word, “sanity check” with several pairs of human eyes (to pick out the inevitable thing-or-six that the Repository has spat out that makes no sense on the page), and we’re done!
F.A.O. University of Lincoln academic staff: the next Quarterly Research Output Report, covering Q1 (January-March) 2011, will be produced from the Repository on or after the 30th of June 2011.