Koha SQL Reports


On this page I’ll add Koha SQL reports over time.  Keep in mind that I only have basic SQL knowledge that I’ve learned on the job as needed, so I may not be able to answer many questions or assist in creating different reports that what is on this page.  However, rest assured every report posted here works for what it was designed for.  Whether or not it works for you depends largely on what version SQL your Koha installation uses.

My Koha SQL version: mysql Ver 14.14

All items with datelastseen

This report is used for inventory purposes mostly.  The datelastseen attribute is used to determine what is likely missing or what was not picked up in an inventory scan.  It includes lost and withdrawn statuses to see what items may legitimately have an older last seen date than your most recent inventory.

SELECT  biblio.title,biblio.author,biblio.copyrightdate,items.barcode, items.homebranch,items.datelastseen, items.itemcallnumber,items.location,items.itemtype,items.itemlost, items.wthdrawn,items.onloan FROM items LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber)  ORDER BY items.datelastseen

Accession list

This report is used for reporting how many items we added during a particular timeframe.  The SQL itself doesn’t allow for specifying a date range (although that’s possible), we just export the whole report to Excel then sort by accession date and pick out what we’re interested in. Note: you will need to specify which holding branch you are querying or delete the “where” phrase altogether to include all branches (or if you only have one branch)

SELECT  biblio.title,items.dateaccessioned FROM items LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber)   WHERE items.homebranch=’MCAMAIN’ ORDER BY items.dateaccessioned desc

All items of a certain type

Similar to the accession list report, we use this to see how many items of a specific type we have added over a period of time by exporting and sorting this report.  It can be used for a variety of purposes though because it’s just a list of all items of whatever item type you set based on your collection.  Here, it’s set to ‘GRAP NOVEL’, our graphic novel item type.

SELECT  items.itemtype,items.itemcallnumber,items.dateaccessioned FROM items LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber)   WHERE items.itemtype=’GRAP NOVEL’ ORDER BY items.itemcallnumber asc

Items from a certain location, minus checkouts

This specific report was used to track down books with the location type of “New”, but that weren’t actually on our new shelf.  For any number of reasons we had books that naturally rotated off of our “new” shelf, but didn’t have their location status changed, and this report generated a list of items that we could use to then shelf check our non-new collection.  Of note is that this list excludes any items that are checked out with the phrase “items.onloan IS NULL” which can be useful in other reports too.

SELECT  items.itemcallnumber,biblio.title,items.barcode,items.location,items.itemtype,items.itemlost,items.datelastseen FROM items LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber)   WHERE items.location=’NEW’ AND items.onloan IS NULL ORDER BY items.itemcallnumber asc

One thought on “Koha SQL Reports

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s