Koha reports and HTML 1


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:

CONCAT('<a href="/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblionumber,'">',title,'</a>') AS Title

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:

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)

Yields results like:
checkinlink
And clicking the link gives you:

checkinaction

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:

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

lostcolor

You can even add classes to take advantage of existing styles in koha:

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

lostcss

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

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

IntranetUserCSS:

.report-color {
font-weight: bold;
text-transform: uppercase;
color:DeepPink;
}

Gives you:
lostusercss

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:

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

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


Leave a comment

Your email address will not be published. Required fields are marked *

Are you human? * Time limit is exhausted. Please reload CAPTCHA.

One thought on “Koha reports and HTML

  • Jenny Schmidt

    Okay, I’m finally getting more ready to broach (sp?) this topic…. I love the idea of styling or adding HTML in the staff client, but what I’d really like to do is try and create a Koha report that is public and generate a URL and style/format that…. Why? I want to do a library dashboard and have the report do an interval like, last month… or last 6 months so that it’s current and from a web dashboard patrons can link/access fresh data….