[Koha] New Report Update

[Cab Vinton]

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