Using autorised values tables and wildcards in Koha reports

ByWater solutions did an example of what I'm about to demonstrate on their website while I was in the process of preparing this.  You can see their description of this same process at http://bywatersolutions.com/2017/06/08/koha-tutorial-branch-reporting/.

In the reporting module in Koha you can use runtime parameters when creating SQL reports.  Taking a simple query like:

SELECT
  branches.branchcode,
  branches.branchname
FROM
  branches
WHERE
  branches.branchcode = "DIGITAL"

And replacing

  branches.branchcode = "DIGITAL"

with

  branches.branchcode = <<Enter a branchcode>>

allows you to limit the results of the query to a different branch each time you run it.

You can go a step further though and, instead of using <<Enter a branchcode>> you can use <<Choose a branch from the drop-down|branches>>.  The text that comes after the pipe, "branches," tells Koha to give you that selection.  Currently the options you can use after a pipe include:


branches
itemtypes
date
categorycode

and the name of any authorised values table in your installation of Koha.

In my situation at my old job in VALNet and in my new job at NExpress, I have often had the need to re-write existing reports for libraries because the existing report worked fine for most libraries, but needed just one slight change in flexibility in order for it to work at other libraries.  One good example would be a simple circulation statistics report like:

SELECT
  statistics.branch,
  count(*) AS CIRC_PLUS_RENEW
FROM
  statistics
WHERE
  (statistics.type = 'issue' OR
  statistics.type = 'renew') AND
  statistics.datetime BETWEEN "2017-07-01" AND "2017-08-01"
GROUP BY
  statistics.branch

There are many times I will introduce a report like this into our consortium and someone will ask "Isn't there a way you could make that report so it only shows the results for my library?"  At that point it could be easily written as so:

SELECT
  statistics.branch,
  count(*) AS CIRC_PLUS_RENEW
FROM
  statistics
WHERE
  (statistics.type = 'issue' OR
  statistics.type = 'renew') AND
  statistics.datetime BETWEEN "2017-07-01" AND "2017-08-01" AND
  statistics.branch = <<Enter branch code>>
GROUP BY
  statistics.branch

My guess would be the immediate reaction to this report would be someone asking "Why do I have to type my library's code?" or "I put my library's name in there, but it didn't give me any results.  What happened?"  No one is going to be happy with having to type something in.  The simple solution there is to change <<Enter branch code>> to <<Choose your branch|branches>> so that the user gets a drop down menu to allow them to choose their branch.  Something like this:

SELECT
  statistics.branch,
  count(*) AS CIRC_PLUS_RENEW
FROM
  statistics
WHERE
  (statistics.type = 'issue' OR
  statistics.type = 'renew') AND
  statistics.datetime BETWEEN "2017-07-01" AND "2017-08-01" AND
  statistics.branch = <<Choose your branch|branches>>
GROUP BY
  statistics.branch

But this won't solve the problem because now some other librarian is going to ask "How can I run this report so that I see everyone's circulation statistics?  It used to show that.  Why doesn't it do that any more?"  Similarly I have two library districts in the NExpress consortium.  One school district and one public library district with four branches.  They often want results that encompass all of their libraries in one report and the "branches" parameter can't cover those circumstances.

My solution to this problem was to stop using the "branches" parameter after the pipe when I wanted a report with more flexibility.  I went ahead and created a new set of authorised values called "LBRANCH" that recreates all of the branch informaiton with a couple of changes.

First of all, the first value I created was "%" with a description called "All libraries."  Then, since all of the Doniphan county library district branches have a branchcode that starts with "DONI" and all of the school districts start with "PH," I created a value called "DON%" and a value called "PH%".

The second step was to re-write the SQL so that WHERE statistics.branch = <<Choose your branch|branches>> used the term "LIKE" instead of an equals sign.  The equals sign requires an exact match with no wildcards, but "LIKE" is a little more flexible because it can use wildcards.

The final step was to change "|branches" to "|LBRANCH".  The final result looks like this:

SELECT
  statistics.branch,
  count(*) AS CIRC_PLUS_RENEW
FROM
  statistics
WHERE
  (statistics.type = 'issue' OR
  statistics.type = 'renew') AND
  statistics.datetime BETWEEN "2017-07-01" AND "2017-08-01" AND
  statistics.branch LIKE <<Choose your branch|LBRANCH>>
GROUP BY
  statistics.branch

By doing this, I can select an individual branch, or I can select all branches.  I can also select the Doniphan county libraries one at a time, or I can select all of them at once.  I can also look at one of the schools in the district at a time, or I can see all of the school district libraries together.

I did discover a drawback, though.  If I used "|LBRANCH" on certain reports and chose "All branches," the report might shut down the system.  We have about 400,000 biblios, over 1,000,000 items, and about 125,000 patrons.  In some cases, asking Koha to deliver results for "All branches" could potentially slow down or crash the system.

So, I created a second alternative authorised values table called ZBRANCH that does the same things for Doniphan and the school district but omits the "All branches" option.  It looks like this in this sample query:

SELECT
  statistics.branch,
  count(*) AS CIRC_PLUS_RENEW
FROM
  statistics
WHERE
  (statistics.type = 'issue' OR
  statistics.type = 'renew') AND
  statistics.datetime BETWEEN "2017-07-01" AND "2017-08-01" AND
  statistics.branch LIKE <<Choose your branch|ZBRAN>>
GROUP BY
  statistics.branch

And I haven't stopped with branchcodes.  I have authorised values tables that cover collection codes, item types, patron categories and other variables that allow me to create flexible reports that can focus on one category of item or all categories of items.

The work-horse of these queries is the one called "Flexible weeding report."  It looks like this:

SELECT
  CONCAT( '<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=', biblio.biblionumber,'\" target="_blank">', biblio.biblionumber, '</a>' ) AS LINK_TO_TITLE,
  items.itemnumber,
  items.homebranch,
  Concat("-",Coalesce(items.barcode, "-"),"-") AS BARCODE,
  items.location,
  items.itype,
  items.ccode,
  items.itemcallnumber,
  biblio.author,
  CONCAT_WS(' ', biblio.title, ExtractValue(biblioitems.marcxml, '//datafield[@tag="245"]/subfield[@code="b"]'), ExtractValue(biblioitems.marcxml, '//datafield[@tag="245"]/subfield[@code="p"]'), ExtractValue(biblioitems.marcxml, '//datafield[@tag="245"]/subfield[@code="n"]') ) AS FULL_TITLE,
  items.dateaccessioned,
  items.datelastborrowed,
  items.datelastseen,
  Sum((Coalesce(items.issues, 0)) + Coalesce(items.renewals, 0)) AS CHECKOUTS_PLUS_RENEWALS,
  IF(items.onloan IS NULL,'No','Yes') AS CHECKED_OUT,
  IF(SUM(coalesce(items.damaged,0) + coalesce(items.itemlost,0) + coalesce(items.withdrawn,0))=0,'No','Yes') AS STATUS_PROBLEMS,
  items.itemnotes,
  items.itemnotes_nonpublic
FROM
  items JOIN
  biblio
    ON items.biblionumber = biblio.biblionumber INNER JOIN
  biblioitems
    ON biblioitems.biblionumber = biblio.biblionumber AND
    items.biblioitemnumber = biblioitems.biblioitemnumber
WHERE
  items.homebranch LIKE <<Item home library|ZBRAN>> AND
  items.location LIKE <<Item shelving location|LLOC>> AND
  items.itype LIKE <<Item type|LITYPES>> AND
  items.ccode LIKE <<Item collection code|LCCODE>> AND
  items.dateaccessioned <= <<Item was added before|date>> AND
  Coalesce(items.datelastborrowed, "0") <= <<Date last borrowed was before|date>> AND
  items.datelastseen <= <<Date last seen was before|date>>
GROUP BY
  items.homebranch,
  BARCODE,
  items.location,
  items.itype,
  items.ccode,
  items.itemcallnumber,
  items.enumchron,
  biblio.author,
  biblio.title
HAVING CHECKOUTS_PLUS_RENEWALS <= <<With X or fewer checkouts|ZNUMBERS>> AND
  CHECKED_OUT LIKE <<Display checked out items|ZYES_NO>> AND
  STATUS_PROBLEMS LIKE <<Display lost, missing, and withdrawn items|ZYES_NO>>


A screenshot of it's parameter screen looks like this:

The creation of this report was, ultimately, a byproduct of laziness.  Within a month of arriving in Kansas I had people calling me saying "We normally use weeding report X, that Liz wrote in 2010, and I want that same report, I just want to be able to run it by collection code instead of by item type" or "I like weeding report Y but I want to limit it to items we added more than a year ago but will only let me limit based on the last seen date.  Can you make a copy that does that?" followed by Doniphan County staff asking if there could be a separate report for them that included all of their libraries and limited to things that had checked out fewer than 5 times.

This report was really a way for me to stop re-writing the same report every month for 4 different libraries.

Learning how to add new authorised values tables to Koha has the potential to give you a lot of flexibility in the reports you write.