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 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
[/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).