Using variables in Koha Reports

by ByWater Partners on Oct 21, 2014

Today’s guest post comes from Christopher Brannon at Cooperative Information Network (CIN). Christopher asked us if there was a way to not have enter the date 2 times in a report like this one:

SELECT ai.location AS 'Shelf Location', COUNT(ai.dateaccessioned) AS 'Count'
FROM (SELECT dateaccessioned, homebranch, location, timestamp
FROM items
UNION ALL
SELECT dateaccessioned, homebranch, location, timestamp
FROM deleteditems di
WHERE date(di.timestamp) > <<Cutoff date|date>>) AS ai
WHERE ai.homebranch = <<Items from|branches>> AND date(ai.dateaccessioned) < <<Repeat cutoff date|date>>
GROUP BY ai.location
ORDER BY ai.location ASC

We told him that there wasn’t a way that we knew of, but he didn’t give up. Below is his solution:

I finally figured out how to use variables in Koha SQL so you don’t have to have staff repeat parameters. Here is an example where I had to use the same date in two places. Rather than asking for the same date twice, I use the variable @TargetDate:

SELECT ai.location AS 'Shelf Location', COUNT(ai.dateaccessioned) AS 'Count'
FROM (SELECT dateaccessioned, homebranch, location, @blank="" AS timestamp
FROM items
UNION ALL
SELECT dateaccessioned, homebranch, location, timestamp
FROM deleteditems di
WHERE date(di.timestamp) > @TargetDate:= <<Cutoff date|date>>) AS ai
WHERE ai.homebranch = <<Items from|branches>> AND date(ai.dateaccessioned) < @TargetDate GROUP BY ai.location ORDER BY ai.location ASC

Hope this helps.