If you are citizen of an European Union member nation, you may not use this service unless you are at least 16 years old.
You already know Dokkio is an AI-powered assistant to organize & manage your digital files & messages. Very soon, Dokkio will support Outlook as well as One Drive. Check it out today!

Reports

Page history last edited by Vimal Kumar 4 years, 7 months ago

You can make customised reports in Koha using MySQL Query.

Go to Reports > Create from SQL

Copy the code into box for SQL query.

For more reports visit KOHA SQL Report Library

Report for Label

SELECT items.barcode,ExtractValue(metadata,'//datafield[@tag="082"]/subfield[@code="a"]') AS ClassNo, ExtractValue(metadata,'//datafield[@tag="082"]/subfield[@code="b"]') AS BookNo,items.itype
FROM items
LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber)
LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber)
JOIN biblio_metadata ON (biblioitems.biblionumber = biblio_metadata.biblionumber)
WHERE items.dateaccessioned

Serial List

SELECT b.title, ExtractValue(marcxml,'//datafield[@tag="082"]/*') AS ClassNo, ExtractValue(marcxml,'//datafield[@tag="650"]/*') AS Subject, i.issn, i.publishercode, i.place
FROM serial s
LEFT JOIN biblio b USING (biblionumber)
LEFT JOIN biblioitems i USING (biblionumber)
WHERE s.STATUS=2
GROUP BY b.biblionumber
ORDER BY b.title ASC

Accession Register (Joined Title and subtitle, Authors and Editors)

Koha version: 19.05.3

SELECT items.barcode,items.itemcallnumber,
CONCAT_WS('',biblio.author,'; ',ExtractValue(metadata,'//datafield[@tag="700"]/subfield[@code="a"]')) AS Author,CONCAT(biblio.title,' ',ExtractValue(metadata,'//datafield[@tag="245"]/subfield[@code="b"]')) AS Title,biblioitems.publishercode
FROM items
LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber)
LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber)
LEFT JOIN biblio_metadata on (biblio_metadata.biblionumber=biblio.biblionumber)
WHERE items.homebranch =<<Branch|branches>> AND items.itype=<<Item type|itemtypes>> AND items.dateaccessioned BETWEEN <<Between Date (yyyy-mm-dd)|date>> and <<and (yyyy-mm-dd)|date>> ORDER BY items.barcode DESC

Accession Register II

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 ASC

Accession Register with Keywords/subject

SELECT items.barcode, items.dateaccessioned, items.itemcallnumber, biblio.author, biblio.title, ExtractValue( metadata, '//datafield[@tag="650"]/subfield[@code="a"]' ) AS Keyword,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)
JOIN biblio_metadata ON (biblioitems.biblionumber = biblio_metadata.biblionumber)
ORDER BY LPAD(items.barcode,40,' ') ASC

Accession Register IV (COALESCE)

SELECT items.barcode,biblio.biblionumber,COALESCE(biblio.author,items.homebranch) AS Author,CONCAT(biblio.title,' ',ExtractValue(marcxml,'//datafield[@tag="245"]/subfield[@code="b"]')) AS Title, biblioitems.publishercode,biblio.copyrightdate, biblioitems.isbn,items.itemcallnumber,items.ccode,items.homebranch,biblioitems.pages ,biblioitems.place
FROM items
LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber)
LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber)
ORDER BY items.barcode ASC

Book list between range of accession numbers

SELECT items.barcode, biblio.author, biblio.title, biblioitems.place, biblioitems.publishercode,

biblio.copyrightdate, biblioitems.pages, biblioitems.size,items.itemcallnumber, items.price
FROM items LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber) WHERE items.barcode BETWEEN 'B-422645' AND 'B-442360'
AND items.homebranch ='L'
AND items.itype='HN'
ORDER BY biblioitems.lccn ASC

Book list between range of accession numbers II

SELECT CONCAT('<a href=\"/cgi-bin/koha/

catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\">',biblio.biblionumber,'</a>') AS biblionumbers, 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)
WHERE items.homebranch =<<Branch|branches>> AND items.barcode BETWEEN <<From Acc. No.>> AND <<To Acc. No.)>>
ORDER BY LPAD(items.barcode,30,' ') ASC

Date wise List of Books

SELECT items.dateaccessioned,items.barcode,items.itemcallnumber,biblio.author,biblio.title,biblioitems.publishercode FROM items
LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber)
LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber)

WHERE items.dateaccessioned BETWEEN <<Between Date (yyyy-mm-dd)|date>> and <<and (yyyy-mm-dd)|date>>
ORDER BY items.barcode DESC

Accession Number Search

Call Number Search

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)
WHERE homebranch = <<Pick your branch|branches>> AND items.itemcallnumber LIKE <<Call number LIKE (USE % FOR wildcard)>>
ORDER BY items.itemcallnumber ASC

Title List without copies

SELECT
b.title,
b.author,
t.editionstatement,
t.publishercode,
t.isbn,
count(i.biblionumber) AS "Copies"
FROM biblio b

LEFT JOIN biblioitems t USING(biblionumber)
LEFT JOIN items i USING(biblionumber)
GROUP BY b.biblionumber
ORDER BY Copies ASC

Record Count

SELECT COUNT(biblionumber) AS Count FROM biblio

Overdue 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' ORDER BY borrowers.surname ASC, issues.date_due ASC

Patron with Fine

SELECT borrowers.cardnumber, borrowers.surname, borrowers.firstname,borrowers.cardnumber FORMAT(SUM(accountlines.amountoutstanding),2) AS due
FROM borrowers LEFT JOIN accountlines ON (borrowers.borrowernumber=accountlines.borrowernumber)
WHERE accountlines.amountoutstanding > 0
GROUP BY borrowers.cardnumber
ORDER BY borrowers.surname ASC

Patron with Fine (2)

SELECT 
    (SELECT CONCAT('<a href=\"/cgi-bin/koha/members/boraccount.pl?borrowernumber=',b.borrowernumber,'\">', b.surname,', ', b.firstname,'</a>') 
    FROM borrowers b WHERE b.borrowernumber = a.borrowernumber) AS Patron, 
    format(sum(amountoutstanding),2) AS 'Outstanding',
    (SELECT count(i.itemnumber) FROM issues i WHERE b.borrowernumber = i.borrowernumber) AS 'Checkouts'
FROM 
    accountlines a, borrowers b
WHERE 
    (SELECT sum(amountoutstanding) FROM accountlines a2 WHERE a2.borrowernumber = a.borrowernumber)  > '0.00'
    AND a.borrowernumber = b.borrowernumber
GROUP BY 
    a.borrowernumber ORDER BY b.surname, b.firstname, Outstanding ASC

List of New Items

SELECT items.dateaccessioned,items.barcode,items.itemcallnumber,biblio.author,biblio.title,biblioitems.publishercode,biblio.copyrightdate
FROM items LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber)
LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber)
WHERE DATE (items.dateaccessioned) BETWEEN <<Between Date (yyyy-mm-dd)|date>> and <<and (yyyy-mm-dd)|date>>
ORDER BY items.dateaccessioned ASC

Old Issue Reports

SELECT DATE_FORMAT(old_issues.issuedate, "%d %b %Y %h:%i %p") As issuedate, DATE_FORMAT(old_issues.date_due, "%d %b %Y %h:%i %p") As date_due, DATE_FORMAT(old_issues.returndate, "%d %b %Y %h:%i %p") As returndate, DATE_FORMAT(old_issues.timestamp, "%d %b %Y %h:%i %p") As timestamp, borrowers.cardnumber, borrowers.surname,
borrowers.firstname, items.barcode, items.datelastborrowed, items.datelastseen, items.itemcallnumber,
EXTRACTVALUE( b.marcxml, '//datafield[@tag="245"]/subfield[@code>="a"]' ) AS TITLE
FROM old_issues
LEFT JOIN borrowers ON ( borrowers.borrowernumber = old_issues.borrowernumber )
LEFT JOIN items ON ( items.itemnumber = old_issues.itemnumber )
LEFT JOIN biblioitems AS b ON ( items.biblioitemnumber = b.biblioitemnumber)
WHERE old_issues.issuedate BETWEEN <<Between Date (yyyy-mm-dd)|date>> and <<and (yyyy-mm-dd)|date>> ORDER BY old_issues.issuedate

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

Circulation-All Checked Out Books

SELECT issues.issuedate, issues.date_due, borrowers.surname, borrowers.firstname,
borrowers.phone, borrowers.email, biblio.title, biblio.author,
items.itemcallnumber, items.barcode, items.location
FROM issues
LEFT JOIN items ON (issues.itemnumber=items.itemnumber)
LEFT JOIN borrowers ON (issues.borrowernumber=borrowers.borrowernumber)
LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
ORDER BY issues.date_due ASC

Check in List of Books (Date wise)

SELECT old_issues.returndate,items.barcode,biblio.title,biblio.author,borrowers.firstname,borrowers.surname,borrowers.cardnumber,borrowers.categorycode

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 <<Between Date (yyyy-mm-dd)|date>> and <<and (yyyy-mm-dd)|date>> ORDER BY old_issues.returndate DESC

Circulation Report by Date

SELECT issues.issuedate,items.barcode,biblio.title,author,borrowers.firstname,borrowers.surname
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
WHERE issues.issuedate BETWEEN <<Between Date (yyyy-mm-dd)|date>> and <<and (yyyy-mm-dd)|date>> ORDER BY issues.issuedate

Enter parameters for report Overdues w/ Contact Info

SELECT borrowers.cardnumber, borrowers.surname, borrowers.firstname,
       borrowers.phone, borrowers.email, issues.date_due,
       (TO_DAYS(curdate())-TO_DAYS( date_due)) AS 'days overdue',
       biblio.title, biblio.author, items.itemcallnumber,
       items.barcode
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 issues.branchcode = <<Branch Code>> AND (TO_DAYS(curdate())-TO_DAYS(
date_due)) >= <<Days overdue>>
ORDER BY borrowers.surname ASC, borrowers.firstname ASC, issues.date_due ASC

Patrons w/ Books Due Tomorrow

SELECT borrowers.cardnumber, borrowers.surname, borrowers.firstname, issues.date_due, items.barcode, biblio.title, biblio.author
FROM borrowers
LEFT JOIN issues ON (issues.borrowernumber=borrowers.borrowernumber)
LEFT JOIN items ON (issues.itemnumber=items.itemnumber)
LEFT JOIN biblio ON (biblio.biblionumber=items.biblionumber)
WHERE issues.date_due = DATE_ADD(curdate(), INTERVAL 1 DAY)
ORDER BY borrowers.surname ASC

Items with list of Collection Code

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) where items.homebranch =<<Enter patrons library|branches>> AND items.ccode LIKE <<Enter Collection Code|CCODE>>

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>>

Subject search (Search term from 650$a)

SELECT barcode,items.itemcallnumber,

title,author,editionstatement,EXTRACTVALUE(metadata,'/record/datafield[@tag="260"]/subfield[@code="c"]') AS Year,location
FROM items,biblio,biblioitems,biblio_metadata
WHERE items.biblionumber = biblio.biblionumber and biblio.biblionumber = biblio_metadata.biblionumber and EXTRACTVALUE(metadata,'/record/datafield[@tag="650"]/subfield[@code="a"]') like <<Search Term (USE % AS wildcard)>> AND items.itype=<<Item type|itemtypes>>

Comments (0)

You don't have permission to comment on this page.

To join this workspace, request access.

Already have an account? Log in!

Loading…

This is your Sidebar, which you can edit like any other page in your workspace.

This Sidebar appears everywhere on your workspace. Add to it whatever you like -- a navigation section, a link to your favorite web sites, or anything else.

Loading…

Reports

Reports

Page Tools

Insert links

Comments (0)

Join this workspace

Navigator

SideBar

Recent Activity