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.
No comments:
Post a Comment