Koha users have begun building a library of useful SQL statements for use in building Koha reports. It’s on the Koha Wiki. You can add your own or put in a request for a report you’d like to know how to do. If you’d like to contribute you can either register on the site or use OpenID to log in. If you’ve never edited a wiki before, be sure to check out the the Wiki’s help page on editing before you jump in.
Koha 3 has always had a selection of built-in reports, and Koha 3 adds additional reports and new a “Guided Reports” system (partially sponsored by this library). The Guided Reports system is still a little rough around the edges, but folks are doing some interesting stuff with them, in particular by using the system’s ability to run custom SQL queries via the Koha interface.
I’d like to take some time to explore how we’re starting to leverage the data that Koha collects to build some reports about how the library is being used. I’ll start with circulation statistics.
The Statistics Table
The health of the library is (for better or worse) judged by its circulation statistics, so that’s first priority. In Koha, every checkout, check-in, and renewal is recorded in one table in the database called statistics. Here’s what it looks like:
Some of those columns aren’t even used–I’m not sure if they were in the past, or whether there were plans for them for the future. other, usercode, and associatedborrower don’t seem to be in use.
- datetime records the time and date of the transaction.
- branch is the location of the transaction.
- proccode is related to tracking patron fines, payments etc.
- value records a currency amount (for fines, payments, etc).
- type records the type of transaction: issue [checkout], return [check-in], renew, payment, or writeoff.
- itemnumber is the id number (defined by items table) of the item that was handled in the transaction.
- itemtype is a category assigned to the item as defined in Koha’s Item Type management.
- borrowernumber is the id number (defined by the members table) of the patron involved in the transaction.
When you check something out in Koha, a line is added to this statistics table (SELECTing datetime,branch,type,itemnumber,itemtype, and borrowernumber):
To get a quick look at how much you’ve circulated today, you could run this:
select count(*) from statistics where year(datetime) = year(curdate()) AND month(datetime) = month(curdate()) AND DAY(datetime) = day(curdate()) and (type='issue' OR type='renew');
The SQL matches the year, month, and day of transactions against today’s date and limits the results to checkouts and renewals. Here’s our count for March 16, 2009:
1 row in set (36.76 sec)
Notice how long the query took. The statistics table gets really big: ours has data going back to May 2003, and it has 7,581,248 rows in it. That makes for some slow queries.
Getting a quick count is a great way to put a number to your day’s work. At the end of the month, though, you’ll want to get some good numbers to show your Board about what kind of business you did. Let’s break it down by collection code so we can see what kind of materials our patrons checked out. We’ll look at the Athens branch’s circulation last month:
SELECT items.ccode, COUNT( statistics.itemnumber ) AS count FROM items, statistics WHERE statistics.branch = 'APL' AND statistics.itemnumber = items.itemnumber AND year( statistics.datetime ) =2009 AND month( statistics.datetime ) =02 AND ( statistics.type = 'issue' OR statistics.type = 'renew' ) GROUP BY items.ccode
We get a nice breakdown of how each category (or at least a selection, in this example) fared during the month:
You should be able to try out these examples yourself using the Guided Reports system in Koha (or right in MySQL if you have direct access to your database).