I've setup an alert on the Koha Reports wiki page -- http://wiki.koha-community.org/wiki/SQL_Reports_Library -- & thought others might also be interested in hearing about new reports as they're added. I'm using a Chrome extension, Page Monitor to get the updates, available here:- https://chrome.google.com/webstore/detail/page-monitor/pemhgklkefakciniebenbfclihhmmfcd<https://chrome.google.com/webstore/detail/page-monitor/pemhgklkefakciniebenbfclihhmmfcd?hl=en> Unless there's an overwhelming hue & cry to cease & desist, I'll continue with updates as they happen. Cheers, Cab Vinton Sanbornton Public Library Holds Ratio by Homebranches - *Developer:* George H. Williams - Latah County Library District - *Module:* Holds - *Purpose:* Essentially the same as "Hold Ratios" in the circulation reports but with item Homebranch information instead of Holdingbranch - *Notes:* I didn't build the link to the biblio in the title field (as it is in the circulation report) so it would be easy for our staff to download the file as a spreadsheet if necessary. I also included some very basic 'notforloan' data for their information. - *Status:* Complete SELECT count(DISTINCT reserves.borrowernumber) AS HOLDCOUNT, count(DISTINCT items.itemnumber) AS ITEMCOUNT, (COUNT(DISTINCT reserves.borrowernumber) / count(DISTINCT items.itemnumber)) AS RATIO, biblio.title, CONCAT( '<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=', biblio.biblionumber,'\" target="_blank">', biblio.biblionumber, '</a>' ) AS 'LINK_TO_BIBLIO', GROUP_CONCAT(DISTINCT items.homebranch SEPARATOR ' // ') AS HOMEBRANCHES, GROUP_CONCAT(DISTINCT items.location SEPARATOR ' // ') AS LOCATIONS, GROUP_CONCAT(DISTINCT items.itype SEPARATOR ' // ') AS ITYPES, GROUP_CONCAT(DISTINCT items.itemcallnumber SEPARATOR ' // ') AS CALLNUMBERS, GROUP_CONCAT(DISTINCT items.notforloan SEPARATOR ' // ') AS NOTLOAN FROM reserves LEFT JOIN items ON items.biblionumber=reserves.biblionumber LEFT JOIN biblio ON reserves.biblionumber=biblio.biblionumber WHERE items.itemlost=0 AND items.damaged=0 GROUP BY biblio.biblionumber HAVING (COUNT(DISTINCT reserves.borrowernumber) / count(DISTINCT items.itemnumber))>3 ORDER BY RATIO DESC
I've setup an alert on the Koha Reports wiki page --
You can also track changes for any page in the wiki through RSS. Click the "Atom" link in the left-hand sidebar. -- Owen -- Web Developer Athens County Public Libraries http://www.myacpl.org
Overdues With Holds Waiting - *Developer:* Nicole C. Engard, ByWater Solutions - *Module:* Holds/Circulation - *Purpose:* A list of items that are overdue that have holds on them. A report to help you know who to call with overdues to tell them others are waiting for their items - *Status:* Complete SELECT p.cardnumber, p.surname, p.firstname, p.phone, p.address, p.city, p.zipcode, c.date_due, (TO_DAYS(curdate())-TO_DAYS( date_due)) AS 'days overdue', i.itype, b.title, b.author, i.itemcallnumber, i.barcode, COUNT(h.biblionumber) AS 'holds' FROM borrowers pLEFT JOIN issues c USING (borrowernumber) LEFT JOIN items i USING (itemnumber) LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber) LEFT JOIN reserves h ON (b.biblionumber=h.biblionumber) WHERE c.branchcode = <<Branch Code|branches>> AND c.date_due < curdate()GROUP BY h.biblionumber HAVING COUNT(h.biblionumber) > 0 ORDER BY p.surname ASC, c.date_due ASC
Holds Queue Workaround - *Developer:* George H. Williams - Latah County Library District - *Module:* Holds - *Purpose:* In Koha 3.10 there is a bug that is making the built in "Holds queue" report in the circulation module work incorrectly (bug 9950). This report can act as a replacement for the built in report until the bug is fixed. - *Status:* Complete SELECT items.location, authorised_values.lib_opac AS 'COLLECTIONCODE', items.itemcallnumber, items.enumchron, biblio.author, biblio.title, items.barcode FROM reserves JOIN biblio ON reserves.biblionumber = biblio.biblionumber JOIN borrowers ON reserves.borrowernumber = borrowers.borrowernumber JOIN items ON biblio.biblionumber = items.biblionumber LEFT JOIN hold_fill_targets ON reserves.biblionumber = hold_fill_targets.biblionumber AND reserves.borrowernumber = hold_fill_targets.borrowernumber LEFT JOIN branchtransfers ON items.itemnumber = branchtransfers.itemnumber LEFT JOIN authorised_values ON items.ccode = authorised_values.authorised_value WHERE ((authorised_values.category)="ccode") AND ((reserves.reservedate)<=now()) AND ((reserves.suspend)=0) AND ((reserves.waitingdate) IS NULL) AND ((reserves.priority)=1) AND ((items.notforloan)=0) AND ((items.damaged)=0) AND ((items.itemlost)=0) AND ((items.wthdrawn)=0) AND ((items.onloan) IS NULL) AND ((reserves.itemnumber) IS NOT NULL AND (reserves.itemnumber)=items.itemnumber) AND ((items.itype)<>"REF") AND ((hold_fill_targets.itemnumber) IS NULL) OR (authorised_values.category="ccode" AND hold_fill_targets.itemnumber=items.itemnumber) GROUP BY items.holdingbranch, authorised_values.lib_opac, items.itemcallnumber, items.enumchron, biblio.author, biblio.title, items.barcode, reserves.branchcode HAVING items.holdingbranch=<<Branch filled at|branches>> AND Count(branchtransfers.datesent)=Count(branchtransfers.datearrived) ORDER BY items.location, authorised_values.lib_opac, items.itemcallnumber, items.enumchron, biblio.author, biblio.title ASC
participants (2)
-
Cab Vinton -
Owen Leonard