Reports Training

Report Resources:

  1. Koha Reports Manual Site
  2. Developers Wiki – SQL Library – Statements contributed world-wide to Koha (including ours)
  3. Circulation Statistics Wizard Instructions (.doc) NEW December 2009!
    - Useful for running monthly check out and renewal reports divided by Item Type (or Collection Code) and Shelving Location
  4. Monthly Reports Sent by NEKLS in Excel workbooks, using January 2009 as an example:
    • Jan 09 NExpress ILL’s among our member libraries
    • Jan 09 Circulation and Renewal (each library has their own worksheet) – Uses Circ Wizard
    • Jan 09 New items added divided by Collection code and Shelving location
    • Jan 09 New items added divided by Item type and Shelving location
    • Jan 09 New patrons by patron category

SQL (see quill) Statements – Curious about what these are?  Wikipedia to the Rescue

Statistics:

New Patrons added in a month – Update the month (12) and the year (2008):

SELECT branchcode,categorycode,COUNT(*) FROM borrowers WHERE MONTH(dateenrolled) = 12 AND YEAR(dateenrolled)= 2008 GROUP BY branchcode,categorycode ORDER BY branchcode,categorycode

New Items added in a month, divided by Shelving Location – update the month and year.  Clunky output.

SELECT count(i.biblionumber) as added, i.itype,i.homebranch,i.location FROM items i
WHERE YEAR(i.dateaccessioned) = 2009
AND MONTH(i.dateaccessioned) = 12
GROUP BY i.homebranch,i.itype,i.location
ORDER BY i.homebranch,i.itype,i.location asc

Total Collection Size – Created by LibLime for our year end Statistical report.  Grouped by item type and home library.

SELECT count(i.biblionumber) as added, i.itype,i.homebranch,i.location
FROM items i
WHERE i.dateaccessioned < '2009-01-01'
GROUP BY i.homebranch,i.itype,i.location
ORDER BY i.homebranch,i.itype,i.location asc

Interlibrary Loan – Revised January 2010 after Chris and Joe looked at it and shared a secret – our old report was counting issues, renewals and other misc. scans.  So for 2010, these two reports will be used:
Loans:

SELECT items.homebranch, COUNT(*) FROM branchtransfers
LEFT JOIN items on (branchtransfers.itemnumber=items.itemnumber)
WHERE (items.homebranch != branchtransfers.tobranch)
AND (branchtransfers.frombranch != branchtransfers.tobranch)
AND YEAR(datesent)=YEAR(NOW())-1
GROUP BY items.homebranch 

Borrows:

SELECT branchtransfers.tobranch, COUNT(*) FROM branchtransfers
LEFT JOIN items on (branchtransfers.itemnumber=items.itemnumber)
WHERE (branchtransfers.tobranch != items.homebranch)
AND (branchtransfers.tobranch != branchtransfers.frombranch)
AND YEAR(datesent)=YEAR(NOW())-1
GROUP BY branchtransfers.tobranch

Patron-related Reports:

Bounced Email - By replacing the ‘xxx@yyy.zzz’ place saver in this statement with the bounced email from the notice generated by Koha, you can find the name and barcode of the patron belonging to the invalid email address.  * If you want to add or remove the number of email addresses in the search, add or delete the phrase ‘OR ‘xxx2@yyy.zzz in (B_email, email, emailpro)’ to or from the statement.

SELECT cardnumber, surname, firstname, branchcode, email, emailpro, b_email
FROM borrowers
WHERE 'xxx@yyy.zzz' IN (B_email, email, emailpro)
OR 'xxx2@yyy.zzz' IN (B_email, email, emailpro)

Overdue Materials by Transacting Library – better for our shared environment – NEW(ish)

SELECT borrowers.surname,borrowers.firstname,borrowers.phone,borrowers.cardnumber,
borrowers.address,borrowers.city,borrowers.zipcode,issues.date_due,
(TO_DAYS(curdate())-TO_DAYS( date_due)) as 'days overdue',items.itype,items.itemcallnumber,
items.barcode,items.homebranch,biblio.title,biblio.author
FROM borrowers
LEFT JOIN issues on (borrowers.borrowernumber=issues.borrowernumber)
LEFT JOIN items on (issues.itemnumber=items.itemnumber)
LEFT JOIN biblio on (items.biblionumber=biblio.biblionumber)
WHERE (TO_DAYS(curdate())-TO_DAYS(date_due)) > '5'
AND issues.branchcode = 'MCLOUTH'
ORDER BY borrowers.surname asc, issues.date_due asc

Overdue List for shelf checking

SELECT items.itemcallnumber,biblio.title,biblio.author,items.itype,items.ccode,
items.location,items.barcode,borrowers.cardnumber
FROM borrowers
LEFT JOIN issues on (borrowers.borrowernumber=issues.borrowernumber)
LEFT JOIN items on (issues.itemnumber=items.itemnumber)
LEFT JOIN biblio on (items.biblionumber=biblio.biblionumber)
WHERE (TO_DAYS(curdate())-TO_DAYS(date_due)) >= '2'
AND issues.branchcode = 'mclouth'
ORDER BY items.itemcallnumber asc

Fines Outstanding – NEW

SELECT borrowers.cardnumber, borrowers.surname, borrowers.firstname,
        FORMAT(SUM(accountlines.amountoutstanding),2) as due
FROM borrowers
LEFT JOIN accountlines ON (borrowers.borrowernumber=accountlines.borrowernumber)
WHERE borrowers.branchcode='YOURLIBRARY'
AND accountlines.amountoutstanding > 0
GROUP BY borrowers.cardnumber
ORDER BY borrowers.surname ASC

Missing Email Report NEW! – Created by Mickey for our libraries, as email notification of Holds is an enhancement we paid for and want to utilize!

SELECT cardnumber,surname,firstname,branchcode,debarred,dateexpiry
FROM borrowers WHERE ' ' IN (email)

Catalog Maintenance Reports:

Weeded/Deleted Items by date range, update the homebranch and timestamp, with the % wildcard

SELECT biblio.title,deleteditems.barcode,deleteditems.timestamp
FROM deleteditems, biblio
WHERE deleteditems.biblionumber = biblio.biblionumber
AND deleteditems.homebranch = 'OTTAWA'
AND deleteditems.timestamp LIKE '2008-12-%'
ORDER BY deleteditems.timestamp asc

Null Item Type – Bib maintenance report to show items that are missing Item Type information at a given library.  Could easily be changed to “WHERE items.ccode IS NULL” or shelving location.

SELECT items.dateaccessioned,items.ccode,items.itemcallnumber,
items.itype,biblio.author,biblio.title, biblio.copyrightdate
FROM items
LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber)
LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber)
WHERE items.itype IS NULL
AND items.homebranch='OTTAWA'

Items with the “NEW” collection code – used to manage the New Books shelf

SELECT items.dateaccessioned,items.ccode,items.itemcallnumber,items.itype,
biblio.author,biblio.title, biblio.copyrightdate
FROM items
LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber)
LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber)
WHERE items.homebranch='BONNERSPGS'
AND items.ccode='NEW'
ORDER BY items.dateaccessioned desc

Items with the NEWBOOKS and NEWMEDIA Item Types - used to manage New materials
Additional instructions: Download into Excel. Sort by dateaccession column to identify oldest items, or by itemcallnumber to locate quickly. Barcode data omits leading zeroes, if present.

SELECT items.dateaccessioned,items.itype,items.itemcallnumber,items.barcode,
biblio.author,biblio.title, biblio.copyrightdate
FROM items
LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber)
LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber)
WHERE items.homebranch='BASEHOR'
AND items.itype='NEWBOOK'
OR items.homebranch='BASEHOR'
AND items.itype='NEWMEDIA'
ORDER BY items.dateaccessioned desc

Item Types of “LOCALHOLD%” or “WALKIN%”- replace “LIBRARY” with library name.  Using the “LIKE” statement and “%” wildcard pulls all 3 LOCALHOLD item types: LOCALHOLD, LOCALHOLD1 and LOCALHOLD2 and 2 WALKIN item types: WALKIN1 and WALKIN2

SELECT items.itype,items.itemcallnumber,items.barcode,biblio.title,
biblio.copyrightdate
FROM items
LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber)
LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber)  
WHERE items.homebranch='LIBRARY'
AND items.itype LIKE 'LOCALHOLD%' OR items.homebranch='LIBRARY'
AND items.itype LIKE 'WALKIN%' ORDER BY items.itemcallnumber asc

Call Number Report – Used to give a shelf list by call number scheme.  This example is for Lyndon’s Inspirational Fiction (FI).

SELECT items.itype,items.itemcallnumber,items.barcode,biblio.title,
biblio.copyrightdate
FROM items
LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber)
LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber)  
WHERE items.homebranch='LYNDON'
AND items.itemcallnumber LIKE 'FI%'
ORDER BY items.itemcallnumber asc

Complete Shelf List - Two of our libraries needed shelf lists for insurance/audit purposes.  We had to download this something OTHER than csv, as many titles have commas in them.  There is a limit on this report – known bug.

SELECT items.price,items.replacementprice,biblio.title,biblio.author,
items.itemcallnumber
FROM items
LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber)
LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber)  
WHERE items.homebranch='BONNERSPGS'
ORDER BY items.itemcallnumber asc

All Barcodes NEW! - Created to find incomplete, short or otherwise incorrect barcodes for McLouth.  By pulling this into Excel and sorting the Barcode field, we could omit the ‘good’ barcodes and have a list of the ‘bad’ ones for fixing. 
NOTE OF WARNING!! McLouth only has 4,000 items, total.  If you have 50,000 items, you MAY time out!

SELECT items.barcode,items.location,biblio.title,items.itemcallnumber
FROM items
LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber)
LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber)
WHERE items.homebranch='MCLOUTH'

New Items by Title – If you are interested in knowing the title and call number of new items added to the collection for Board reports or Display purposes, this is the report for you!  Just change the date range and Library to personalize it.

SELECT items.dateaccessioned,biblio.title,items.itemcallnumber
FROM items
LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber)
LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber)
WHERE DATE (items.dateaccessioned)
BETWEEN '2009-03-01' AND'2009-04-27'
AND items.homebranch='LINWOOD'
ORDER BY items.itemcallnumber ASC

Titles Sent as ILLs – New in January 2010! With help from an email discussion and Bev, we pieced together a report that lists titles circulated (issued) at Library A that do not belong to Library A.  The thought is that this information is useful for collection development.

SELECT statistics.branch as Library, biblio.title, biblio.author, items.itemcallnumber,
CONCAT( '<a href=\"/cgi-bin/koha/cataloguing/additem.pl?biblionumber=',biblio.biblionumber,  
'\">',items.barcode,'</a>' ) as 'Barcode', items.homebranch as ItemBranch, statistics.itemtype
FROM statistics
LEFT JOIN items ON statistics.itemnumber =items.itemnumber
LEFT JOIN biblio ON items.biblionumber = biblio.biblionumber
WHERE statistics.branch != items.homebranch
AND statistics.type='issue'
AND date(statistics.datetime) >= concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-'),'01')
AND date(statistics.datetime) <= LAST_DAY(now() - interval 1 month)
ORDER BY statistics.branch, biblio.title