27/08/2020

Koha Reports – Tested with Koha 20.05

By Technical Digit

Koha Reports: You can easily add customized reports in your Koha Software.

Accession Register (Complete List of Books-Sorted by Barcode)
 SELECT CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',  
 biblio.biblionumber,'\">',biblio.biblionumber,'</a>') AS biblionumbers,   
 items.barcode, items.itemcallnumber, biblioitems.isbn, biblio.author,  
  biblio.title, biblioitems.pages, biblioitems.publishercode, biblioitems.place,   
  biblio.copyrightdate,items.price FROM items LEFT JOIN biblioitems ON   
  (items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio ON   
  (biblioitems.biblionumber=biblio.biblionumber) ORDER BY LPAD  
  (items.barcode,40,' ') ASC  

Detailed Accession Register Report

 SELECT oo.dateaccessioned AS 'Date', oo.barcode AS 'Acc. No./Barcode', ooo.title AS 'Title',   
  ooo.author AS 'Author/Editor', concat_ws(' , ', o.editionstatement, oo.enumchron)    
  AS 'Edition/Vol.', concat_ws(' ', o.place, o.publishercode) AS 'Place & Publisher',    
  ooo.copyrightdate AS 'Year', o.pages AS 'Pages', ooooooo.name AS 'Vendor/Source',   
  oo.itemcallnumber AS 'Full Call Number', concat_ws(', ?',    
  concat(' ', ooooo.symbol, oooo.listprice)) AS 'Cost/Price',    
  concat_ws(' , ', oooooo.invoicenumber, oooooo.shipmentdate) AS 'Bill No. & Date',    
  '' AS 'Withdrawn Date', '' AS 'Remarks' FROM biblioitems o   
  LEFT JOIN items oo ON oo.biblioitemnumber=o.biblioitemnumber   
  LEFT JOIN biblio ooo ON ooo.biblionumber=o.biblionumber   
  LEFT JOIN aqorders oooo ON oooo.biblionumber=o.biblionumber   
  LEFT JOIN currency ooooo ON ooooo.currency=oooo.currency   
  LEFT JOIN aqinvoices oooooo ON oooooo.booksellerid=oo.booksellerid   
  LEFT JOIN aqbooksellers ooooooo ON ooooooo.id=oo.booksellerid ORDER BY oo.barcode ASC  

Catalogue-List of New Items

 SELECT items.barcode,items.dateaccessioned,items.itemcallnumber,biblioitems.isbn,  
 biblio.author,biblio.title,biblioitems.pages,biblioitems.publishercode,  
 biblioitems.place,biblio.copyrightdate FROM items   
 LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber)   
 LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber)   
 ORDER BY items.barcode DESC  

Items Currently Checked-out

 SELECT issues.issuedate,items.barcode,biblio.title, author,borrowers.firstname,  
 borrowers.surname,borrowers.cardnumber FROM issues LEFT JOIN borrowers ON   
 borrowers.borrowernumber=issues.borrowernumber  LEFT JOIN items ON   
 issues.itemnumber=items.itemnumber  LEFT JOIN biblio ON   
 items.biblionumber=biblio.biblionumber ORDER BY issues.issuedate DESC  

All Circulation Transactions on Date Range with Patron & Item Details

 SELECT datetime AS "Date", cardnumber AS "Card number", surname AS "Last name",   
 firstname AS "First name", CASE type WHEN 'issue' THEN "Check out" WHEN 'localuse'   
 THEN "In house use" WHEN 'return' THEN "Check in" WHEN 'renew' THEN "Renew" WHEN   
 'writeoff' THEN "Amnesty" WHEN 'payment' THEN "Payment" ELSE "Other" END AS   
 "Transaction", CASE value WHEN '0' THEN "-" ELSE value END AS "Amount", barcode   
 AS "Barcode", biblio.title AS "Title", author AS "Author", items.homebranch,   
 items.holdingbranch FROM statistics JOIN borrowers ON   
 statistics.borrowernumber=borrowers.borrowernumber LEFT JOIN items ON   
 statistics.itemnumber=items.itemnumber LEFT JOIN biblio ON   
 items.biblionumber=biblio.biblionumber WHERE DATE (statistics.datetime)   
 BETWEEN <<From Date|date>> AND <<To Date|date>>  

Fines with Patron & Item Info

 SELECT b.surname, b.firstname, b.email, bib.title, i.barcode, a.amountoutstanding,   
 ni.issuedate, ni.date_due, IF ( ni.returndate IS NULL , " ", ni.returndate ) AS   
 returndate FROM accountlines a LEFT JOIN borrowers b ON ( b.borrowernumber = a.borrowernumber )   
 LEFT JOIN items i ON ( a.itemnumber = i.itemnumber ) LEFT JOIN biblio bib ON   
 ( i.biblionumber = bib.biblionumber ) LEFT JOIN ( SELECT * FROM issues UNION SELECT * FROM old_issues )   
 ni ON ( ni.itemnumber = i.itemnumber AND ni.borrowernumber = a.borrowernumber ) WHERE   
 a.amountoutstanding > 0 GROUP BY a.description ORDER BY b.surname, b.firstname, ni.timestamp DESC  

Patron List by Category

 SELECT borrowers.cardnumber,borrowers.surname,borrowers.firstname,borrowers.initials,  
 borrowers.dateenrolled FROM borrowers WHERE branchcode=<<Enter patrons library|branches>>   
 AND categorycode LIKE <<Enter Category borrowers|categorycode>>  

Fine Payment Details (Date Range)

 SELECT borrowers.borrowernumber, borrowers.cardnumber, accountlines.amount,   
 accountlines.date FROM accountlines, borrowers WHERE   
 borrowers.borrowernumber = accountlines.borrowernumber and   
 accounttype = 'pay' AND date BETWEEN <<Between (YYYY-MM-dd)|date>>   
 AND <<and (YYYY-MM-DD)|date>>  
Unique Titles Count
 SELECT homebranch, count(DISTINCT biblionumber) AS bibs, count(itemnumber) AS items   
 FROM items GROUP BY homebranch ORDER BY homebranch ASC  
Total Fines Paid Today
 SELECT SUM(amount) FROM accountlines WHERE DATE(timestamp)=CURDATE()   
 AND (accounttype='PAY' OR accounttype='C')  
Subject-wise Title Count Report
 SELECT ExtractValue( metadata, '//datafield[@tag="650"]/subfield[@code="a"]') AS 'Subject' ,   
 COUNT(ExtractValue( metadata, '//datafield[@tag="650"]/subfield[@code="a"]')) AS 'No. of Titles',   
 COUNT(itemnumber) AS 'No. of copies' FROM biblio_metadata LEFT JOIN items USING (biblionumber)  
 GROUP BY ExtractValue( metadata, '//datafield[@tag="650"]/subfield[@code="a"]')  
 ORDER BY ExtractValue( metadata, '//datafield[@tag="650"]/subfield[@code="a"]')  
Count of Items Added by Cataloger*
 SELECT concat(p.firstname, ' ', p.surname) AS Staff Name, concat(a.action, ' ', a.info) AS action,   
 count(a.timestamp) AS count FROM action_logs a LEFT JOIN borrowers p ON (a.user=p.borrowernumber)   
 WHERE a.module='CATALOGUING' AND a.timestamp BETWEEN <<Between (yyyy-mm-dd)|date>> AND <<and   
 (yyyy-mm-dd)|date>> AND a.info IN ('item','biblio') GROUP BY p.borrowernumber, concat(a.action, ' ', a.info)  
Total Views: 25