Displaying unique title and volume count on the Koha staff client

An upgrade-friendly way to show unique title and copies’ count using Koha’s SQL reports in tandem with Koha’s Reports web service API, JSON formatted data and some Javascript.

Kia ora!

Last week my good friend Dr. Gautam Sarma, Asst Librarian, K.K. Handique State Open University in Assam posed an question on the Koha Users forum on Facebook. He wanted to know what was the easiest way to view the total count of unique titles as well as total number of copies (item holdings) in his Koha catalog.

Given that he comes to us from a SOUL user’s background, this is a reasonable demand. SOUL developed and marketed by Inflibnet does show this information at a glance, especially when using the cataloging module. Similarly, so does e-Granthalaya, another LMS developed by NIC, Govt of India. The following screenshot snippets show how this information is displayed for SOUL v2.0 and e-Granthalaya 4.0 respectively.

soul

egrant

In Gautam’s own words he was looking for something that would allow him to:

hurriedly say that how much data we have by entering our ILS

While Koha’s SQL reporting facility makes finding this information a breeze, thanks to this ready-made SQL report by Nicole C. Baratta, it does not immediately allow the users to have the sort of heads-up view that SOUL and e-Granthalaya allows. A library staffer have to go to Reports -> Guided Reports -> Use Saved and then finally manually select the actual report and run it to see the unique title and copy count statistics.

At L2C2 Technologies we have been doing some things like these for our client-partners. So I promised Gautam that I will soon post about it. And as it would happen, yesterday Mr. Kalipada Jana, Librarian, Basanti Devi College and a client partner wanted the same facility for his hosted instance’s staff client. We decided to use the Basanti Devi College library as a live use-case (since they are currently doing retro-conversion) and document the same so that other users may also be able to benefit from the same.

Concepts – or what makes it all work

Koha since long have provided a set of HTTP based web service API. According to the World Wide Web Consortium (W3C) – the global standards body that defines anything and everything related to the web, a web service is “a software system designed to support inter-operable machine-to-machine interaction over a network”.

Among the various things Koha’s web services API allow includes the Reports web service, which allows an SQL report’s data to be made available in JSON format, as long as the report is marked as “public“. A public report is accessible via a URL that looks like this: http://MYOPAC/cgi-bin/koha/svc/report?id=REPORTID.

basanti-stats1

In this case, we called the report as a public report using the reports web service via https://bdcl-staff.l2c2.co.in/cgi-bin/koha/svc/report?id=6&annotated=1.

The SQL query being executed by the call was :

SELECT homebranch, count(DISTINCT biblionumber) AS bibs, 
       count(itemnumber) AS items 
FROM items 
GROUP BY homebranch 
ORDER BY homebranch ASC

The JSON data (which thanks to the annotated=1 querystring parameter) was returned as the following key-value pairs : [{"bibs":"1374","homebranch":"BDCL","items":"2359"}].

The solution

With the report (in this case report id #6) returning the branch-wise unique title and copies information in JSON format, it was now only a small matter of putting together some JavaScript so that the information could be displayed in a more human-friendly format on the staff client. The following JQuery snippet was all that was required to be placed into BDCL’s IntranetUserJS system preference to get our desired result.

$(document).ready(function() {
    if ( $('#main_intranet-main').length ) {
    $.getJSON("https://bdcl-staff.l2c2.co.in/cgi-bin/koha/svc/report?id=6&annotated=1", function(data) {
        var branches = data[0].homebranch;
        var bibs = data[0].bibs;
        var items = data[0].items;
        $('div.newsitem').prepend('<div class="newsitem" id="mystats"><table class="table table-striped" style="width: 100%; background: none;"><thead><th colspan="3" style="text-align: center; font-weight: bold; padding: 8px; line-height: 1.42857143; vertical-align: middle; text-transform: uppercase;">Library Statistics</thead><tbody><tr><td><strong>Branch</strong></td><td><strong>Unique titles</strong></td><td><strong>Total Copies</strong></td></tr><tr><td class="text-center">'+branches+'</td><td class="text-center">'+bibs+'</td><td class="text-center">'+items+'</td></tr></tbody></table></div>');
    });
    }
});

And voila! Houston! We have touchdown! ๐Ÿ™‚

koha-count

Hope this post helps you at your library. Happy hacking!

9 thoughts on “Displaying unique title and volume count on the Koha staff client”

  1. This report output is not consolidated in multiple library concept.
    In my case the books are purchased centrally and later transferred to departmental library and item record in 952 is changed accordingly.

    So, the sql report shows library wise data.

  2. i’ve been looking for a custom report in wiki.koha, communities and others and i didn’t get what i want, i want a report where shows me how many times 1 book have been searched in a month, year or date since it was published.
    I’m using koha v. 17.05.06.000

    1. The reason you do not see that report is because IIRC, we do not track that information presently. However, Koha already provides an external link tracker (you can see this video from Bywater Solutions). You can jerryrig that report using your apache2 server logs OR extend Koha by adding custom code to do the specific tracking and reporting your want. What makes your query problematic is that then when you run a search on a subject, it pulls up (often) many records that match. So do you say that *those* are the searched books? OR is it only when someone runs a specific title search? OR when someone clicks on a title from the search results of many books? These questions you need to answer first.

      HTH
      -idg

  3. Really helpful indeed :). One question, if the system were to have more than 2 branches, will the screen show information according to the branch the user logged in?

    1. Hi Freddy,

      Greetings from India ๐Ÿ™‚ The code in the blog post won’t do that. You will have to extend it with any additional loop in the logic along with a conditional statement to check the branch to do that. It would be a nice little extension ๐Ÿ™‚

  4. Dear IDG Sir,
    This post would be very helpful for us. We appreciate and acknowledges your great effort. Thank you, Sir!
    I also tried but didn’t get the display on my staff client. I have followed the step carefully. May be I have missed something. Do we have place something in IntranetUserCSS?

    1. Hi Sonam,

      No, you do not need to add anything extra to IntranetUserCSS syspref. The report id needs to match the id of *your* report as well as the URL should match your own staff-client. Also check your Javascript console for possible errors.

Leave a Reply

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