I currently work in a library that utilizes a single Koha system shared by multiple branches. To generate reports specific to my branch, I have compiled and modified several SQL reports. These reports may prove beneficial to others working in similar environments.
Note: I am interested in discovering additional branch-specific reports that you may have created or found on the official wiki.
Accession Register Report by Branch (Selectable from a drop-down list)
**SELECT items.barcode,items.itemcallnumber,items.homebranch,items.itype,items.ccode,items.location,biblioitems.isbn,biblio.author,biblio.title,biblio.subtitle,biblioitems.editionstatement,biblioitems.place,biblioitems.publishercode,biblio.copyrightdate,biblioitems.pages,items.price,items.enumchron,items.dateaccessioned **
**FROM items **
**LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) **
LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber) where items.homebranch =<>
ORDER BY items.barcode ASC
New Arrivals by Branch (All Items, Selectable from a drop-down list)
SELECT b.biblionumber, SUBSTRING_INDEX(m.isbn, ’ ‘, 1) AS isbn, b.title
FROM items i
LEFT JOIN biblioitems m USING (biblioitemnumber)
LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber)
WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= i.dateaccessioned AND m.isbn IS NOT NULL AND m.isbn != ‘’
AND i.homebranch = <>
GROUP BY biblionumber
HAVING isbn != “”
ORDER BY rand()
LIMIT 30
New Arrivals by Branch (All Items, Direct Input)
SELECT b.biblionumber, SUBSTRING_INDEX(m.isbn, ’ ‘, 1) AS isbn, b.title
FROM items i
LEFT JOIN biblioitems m USING (biblioitemnumber)
LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber)
WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= i.dateaccessioned AND m.isbn IS NOT NULL AND m.isbn != ‘’
AND i.homebranch = ‘EC’
GROUP BY biblionumber
HAVING isbn != “”
ORDER BY rand()
LIMIT 30
In this report, “EC” represents my branch code. Replace it with your specific branch code.
New Arrivals by Branch (Reference Books Only)
SELECT b.biblionumber, SUBSTRING_INDEX(m.isbn, ’ ‘, 1) AS isbn, b.title, i.barcode, i.itype
FROM items i
LEFT JOIN biblioitems m USING (biblioitemnumber)
LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber)
WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= i.dateaccessioned AND m.isbn IS NOT NULL AND m.isbn != ‘’
AND i.itype = ‘REF’ AND i.homebranch = ‘EC’
GROUP BY biblionumber
HAVING isbn != “”
ORDER BY rand()
LIMIT 30
Here, “REF” denotes our code for reference books, and “EC” represents my branch code. Replace these codes as needed.
New Arrivals by Branch (Books Only)
SELECT b.biblionumber, SUBSTRING_INDEX(m.isbn, ’ ‘, 1) AS isbn, b.title, i.barcode, i.itype
FROM items i
LEFT JOIN biblioitems m USING (biblioitemnumber)
LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber)
WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= i.dateaccessioned AND m.isbn IS NOT NULL AND m.isbn != ‘’
AND i.itype = ‘BK’ AND i.homebranch = ‘EC’
GROUP BY biblionumber
HAVING isbn != “”
ORDER BY rand()
LIMIT 30
“BK” represents our code for books, and “EC” is the code for my branch. Feel free to modify these codes accordingly.
Datewise List of Checked Out (Issued) Books by Branch (Selectable from a drop-down list)
**SELECT DATE_FORMAT(c.issuedate, “%d %b %Y %h:%i %p”) AS Issue_Date, DATE_FORMAT(c.date_due, “%d %b %Y”) AS Due_Date, **
i.barcode AS Barcode,
b.title AS Title,
**b.author AS Author, **
p.cardnumber AS Card_No,
**p.firstname AS First_Name, **
**p.surname AS Last_Name, **
i.homebranch
**FROM issues c **
**LEFT JOIN items i ON (c.itemnumber=i.itemnumber) **
**LEFT JOIN borrowers p ON (c.borrowernumber=p.borrowernumber) **
**LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber) **
**WHERE c.issuedate **
**BETWEEN <> AND <> **
AND i.homebranch = <>
ORDER BY c.issuedate DESC
Datewise List of Checked In (Returned) Books by Branch (Selectable from a drop-down list)
**SELECT old_issues.returndate,items.barcode,biblio.title,biblio.author,borrowers.firstname,borrowers.surname,borrowers.cardnumber,borrowers.categorycode,items.homebranch **
**FROM old_issues **
**LEFT JOIN borrowers ON borrowers.borrowernumber=old_issues.borrowernumber **
**LEFT JOIN items ON old_issues.itemnumber=items.itemnumber **
**LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber **
**WHERE old_issues.returndate **
**BETWEEN <> AND <> **
AND items.homebranch = <>
ORDER BY old_issues.returndate DESC
Overdue List By Branch (Selectable from a drop-down list)
**SELECT borrowers.surname,borrowers.firstname,issues.date_due, (TO_DAYS(curdate())-TO_DAYS( date_due)) AS ‘days overdue’, items.itemcallnumber, items.barcode,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)) > ‘30’ AND items.homebranch = <>
ORDER BY borrowers.surname ASC, issues.date_due ASC