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:
Field | Type | Collation | Null | Key | Default |
---|---|---|---|---|---|
datetime | datetime | Yes | MUL | NULL | |
branch | varchar(10) | utf8_general_ci | Yes | NULL | |
proccode | varchar(4) | utf8_general_ci | Yes | NULL | |
value | double(16,4) | Yes | NULL | ||
type | varchar(16) | utf8_general_ci | Yes | NULL | |
other | mediumtext | utf8_general_ci | Yes | NULL | |
usercode | varchar(10) | utf8_general_ci | Yes | NULL | |
itemnumber | int(11) | Yes | NULL | ||
itemtype | varchar(10) | utf8_general_ci | Yes | NULL | |
borrowernumber | int(11) | Yes | NULL | ||
associatedborrower | int(11) | Yes | NULL |
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):
datetime | branch | type | itemnumber | itemtype | borrowernumber |
---|---|---|---|---|---|
2008-10-07 14:07:31 | CPL | issue | 40235 | CIRC | 20351 |
To get a quick look at how much you’ve circulated today, you could run this:
[source language=’sql’]
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’);
[/source]
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:
count(*) |
---|
2190 |
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:
[sourcecode language=’sql’] SELECT items.ccode, COUNT( statistics.itemnumber ) AS countFROM 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
[/sourcecode] We get a nice breakdown of how each category (or at least a selection, in this example) fared during the month:
ccode | count |
---|---|
AB | 427 |
AF | 1355 |
AV | 1002 |
CDM | 681 |
DVD | 1006 |
EASY | 1754 |
JF | 519 |
JNF | 1028 |
LP | 667 |
MYS | 770 |
NF | 1994 |
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).
Owen,
Bless you for showing me how to combine Issues and Renewals into a single statement, freeing us from running the statistics wizard at all!
Sharon
NExpress
Hi,
First, thanks for this article. In second place, I have a question for you. using reporting, how can i know who (staff member) was loan of material?, because i can know the borrower but not the staff member.
If you have the system preference “IssueLog” turned on all checkouts will be recorded in the Koha database’s “action_logs” table. You’re supposed to be able to access this information from the “Modification log” tab on a bibliographic record in the staff client, but for some reason this isn’t working. You can query the database directly, however at the moment Bug 7241 prevents the action log from recording the item number of a checkout. Instead it records the biblionumber. This could present problems generating accurate reports. I’m testing the patch for that bug now, so I hope the fix will be in the next release.