Koha reports and HTML

by Nick Clemens on Dec 17, 2015

Reports have always been one of my favorite parts of Koha. The unfettered access to all of the tables and data in the system was a refreshing change from proprietary ILSes I had worked with in the past. From day one I was writing reports to track everything in the system: when to rotate new books, when to purge patrons, what books belonging to other libraries were on our shelves. It was a long time though before I learned another trick: reports will process HTML and, through that, CSS!

Actions:
The Koha reports library has many examples of using links in reports, you can concat biblionumber with an html link as below to generate an active link in your report results directly to a biblio:

[code language=”sql” wraplines=”true” toolbar=”true”]
CONCAT(‘<a href="/cgi-bin/koha/catalogue/detail.pl?biblionumber=’,biblionumber,’">’,title,'</a>’) AS Title
[/code]

but did you know you can do more than just link? (Almost) all link in koha are static links. The impact of this may not be immediately celar, bit what it means is: you can perform actions by creating links! Want to check in a patron’s item from a report:

[code language=”sql” wraplines=”true” toolbar=”true”]
SELECT CONCAT(‘<a href="/cgi-bin/koha/circ/returns.pl?barcode=’,barcode,’">Checkin</a>’) AS "Check-in link", biblio.title, CONCAT(surname,’,’,firstname) as Patron
FROM issues
LEFT JOIN items USING (itemnumber)
LEFT JOIN biblio USING (biblionumber)
LEFT JOIN borrowers USING (borrowernumber)
[/code]

Yields results like:

And clicking the link gives you:

Styling:
Let’s say you want to see a list of items that are lost or damaged, you want them in shelf order, but you want to easily see which are lost and which are damaged, let’s add color:

[code language=”sql” wraplines=”true” toolbar=”true”]
SELECT IF(itemlost,'<p style="background-color:red">lost</p>’,IF(damaged,'<p style="background-color:green">damaged</p>’,"")) AS indicator, barcode, itemcallnumber, damaged, itemlost
FROM items
WHERE damaged OR itemlost
[/code]

You can even add classes to take advantage of existing styles in koha:
[code language=”sql” wraplines=”true” toolbar=”true”]
SELECT IF(itemlost,'<p style="background-color:red">lost</p>’,IF(damaged,'<p style="background-color:green">damaged</p>’,"")) AS indicator, barcode, itemcallnumber, damaged, itemlost
FROM items
WHERE damaged OR itemlost
[/code]

or add your own unique class name and corresponding code in intranetusercss:
REPORT:

[code language=”sql” wraplines=”true” toolbar=”true”]
SELECT IF(itemlost,'<span class="report-color">lost</span>’,IF(damaged,'<p style="background-color:green">damaged</p>’,"")) AS indicator, barcode, itemcallnumber, damaged, itemlost
FROM items
WHERE damaged OR itemlost
[/code]

IntranetUserCSS:

[code language=”css”]
.report-color {
font-weight: bold;
text-transform: uppercase;
color:DeepPink;
}
[/code]

Gives you:

One last tidbit, you can add links in report descriptions. Let’s say you are building a new report from an existing one, and it’s not quite ready yet. You can include a link back to the good report in the Notes field:

[code language=”html” wraplines=”true” toolbar=”true”]
This is a work in progress report based on <a href="/cgi-bin/koha/reports/guided_reports.pl?reports=4&amp;phase=Show%20SQL">this report</a> Please do not run this report, but use the link above to run the original report
[/code]

Hopefully these examples give a you a good start for exploring the possibilities. Let us know what amazing things you do with your reports!