For Koha Explorers Group. (NExpress Libraries: These will be discussed in upcoming Reports Training)
We discussed sharing MySQL statements for the Guided Reports Wizard.
These are the reports I run monthly for statistics using the Guided Reports Wizard and the Statistics Wizard. The Guided Reports MySQL statements are copied, pasted, updated and run at the beginning of each month, then the data is sent to the libraries in an Excel spreadsheet. Two of our libraries have been trained to run the New Items Added by Collection Code on their own. – Sharon
New PATRONS (categorycode from borrowers) added to the Shared Catalog in a given Month/Year.
SELECT branchcode,categorycode,COUNT(*) FROM borrowers WHERE MONTH(dateenrolled) = 11 AND YEAR(dateenrolled)= 2008 GROUP BY branchcode,categorycode ORDER BY branchcode,categorycode
New ITEMS (by itype from items (not biblio)) added to the Shared Catalog in a given Month/Year.
SELECT homebranch,itype,COUNT(*) FROM items WHERE MONTH(dateaccessioned) = 11 AND YEAR(dateaccessioned) = 2008 GROUP BY homebranch,itype ORDER BY homebranch,itype
New ITEMS (by ccode from items (not biblio)) added to the Shared Catalog in a given Month/Year.
SELECT homebranch,ccode,COUNT(*) FROM items WHERE MONTH(dateaccessioned) = 11 AND YEAR(dateaccessioned) = 2008 GROUP BY homebranch,ccode ORDER BY homebranch,ccode
Circulation information comes for the Statistics Wizard : Circulation
Period: Using the calendar wizard, I set the beginning and ending dates (mm/01/yyyy to mm/31/yyyy)
Item Type: Selected as ROW
Library: Select the library I want to generate data for
Shelving Location: Selected as COLUMN (we use this to indicate audience level)
Cell Value: Count Total Items selected
I then copy the data table and paste the values into Excel, creating a separate worksheet for each library in a single workbook.
Other Useful Statements Created for us or that we created ourselves:
Interlibrary Loans through the Koha System’s Hold functionality (supplements Agent ILL). Designed to show “2008 checkouts where Atchison was the home branch but the issuing branch wasn’t Atchison.” When run for the entire Shared Catalog, it should give us both Loans sent and received.
SELECT statistics.branch,count(*) as total from statistics LEFT JOIN items on (statistics.itemnumber = items.itemnumber) where statistics.branch != items.homebranch AND statistics.datetime between ’2008-01-01′ and ’2008-12-31′ and items.homebranch = ‘ATCHISON’ group by statistics.branch
New Items Added in Date order, to help with moving new book management (removing from display and chaning either the Item Type or Collection Code, as needed)
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
Collection List by Item Types – Used for bibliographic maintenance or to change NEW Item types
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=’BRANCH‘ AND items.itype=’NEWBOOK‘ OR items.homebranch=’BRANCH‘ AND items.itype=’NEWMEDIA‘ ORDER BY items.dateaccessioned desc
I’ll go ahead and post a few of the reports I’ve written that we’ve found useful. I’m not sure how readable this will be but here goes.
Report #1
This report was written to generate a shelf check/call list for 7, 14, 21, and 28 day overdues every day it is clicked. It was actually one of the first reports I wrote so the code could be a bit messy. Of note is the Interval section where you choose what day you want overdues for.
Select items.itemcallnumber,biblio.title,issues.date_due,borrowers.firstname,borrowers.surname,borrowers.categorycode,borrowers.phone,borrowers.email FROM items
LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber)
LEFT JOIN issues on (items.itemnumber=issues.itemnumber)
LEFT JOIN borrowers on (issues.borrowernumber=borrowers.borrowernumber)
Where issues.date_due=DATE_SUB(curdate(), INTERVAL 7 DAY) AND issues.branchcode=’INDEPENDNC’
Report #2
This is a fairly simple report that when clicked generates a list of dvds added within the last two months. Again interval is where you adjust the time.
Select
biblio.title,
items.itemcallnumber,
items.dateaccessioned as ‘Date Entered’
from items
Left Join biblio on
(biblio.biblionumber=items.biblionumber)
Where
items.homebranch=’independnc’
and
items.itemcallnumber like ‘dvd %’
and items.dateaccessioned>DATE_SUB(curdate(), INTERVAL 2 MONTH)
Report #3
This report is used in weekly reports to give a total of the circulation and renewals in a week for a given month. Date format is used so I can group by week, it’s not the most elegant but it works.
select date_format(`datetime`,”%X-%V”) as ‘Week’, count(*) as ‘Checkouts and Renews’ from statistics where datetime like ’2009-02%’ and branch=’Independnc’ and type in (‘issue’,'renew’) group by date_format(`datetime`,”%X-%V”)
Report #4
This is another weekly report to show the number of items entered in a week.
select date_format(`dateaccessioned`,”%X-%V”) as ‘Week’, count(*) as ‘# of items’ from items where dateaccessioned like ’2009-02%’ and homebranch=’Independnc’ group by date_format(`dateaccessioned`,”%X-%V”)
One thing to note is on some of the more complex reports it gets kind of buggy I wrote a report to total up circ and renewals for a month grouped by day and you can view the report but you can’t go to the next page or download the report, I think the date format command is what causes this sort of thing though.
Ok it turns out that wasn’t very readable I had it broken up into sections, honest.
Thank you for sharing – I will be working on our monthly suite of reports tomorrow (I hope). We just discovered YESTERDAY that renewals are counted separately from check outs, so all of our circulation stats have been incomplete. I’m fixing that mistake now.