[Koha] Variables in reports

[Barton Chittenden]

Cab,

I assume that you were talking about the 'Runtime Variables' section of the
wiki:

https://wiki.koha-community.org/wiki/SQL_Reports_Library#Runtime_Variables

... Nick, Jesse (pianohacker) and I have all played with this at one time
or another, and I think that our consensus was that trying to set runtime
variables is to avoid having to re-enter run-time parameters is a crapshoot
-- it's possible to make it work sometimes, but it's hard to get right.

So I think that you'll have to re-enter parameters by hand until Koha
18.05, when we get this enhancement that makes the problem go away:

Bug 9634    : Allow for parameters re-use on SQL reports
URL         : https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=9634
Priority    : P5 - low
Assigned To : nick at bywatersolutions.com
Urgency     : enhancement
Status      : RESOLVED


On Wed, Jun 6, 2018 at 10:41 AM, Cab Vinton <bibliwho at gmail.com> wrote:

> Hi, All --
>
> We have a report with the following WHERE clause:
>
>   WHERE b.biblionumber IN (SELECT biblionumber FROM items WHERE
> DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= dateaccessioned AND
> homebranch='KIMBALL')
>   AND b.biblionumber IN (SELECT biblionumber FROM items WHERE
> DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= dateaccessioned AND
> homebranch='PLAISTOW')
>   AND b.biblionumber IN (SELECT biblionumber FROM items WHERE
> DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= dateaccessioned AND
> homebranch='SANDOWN')
>
> Is there a way to declare & assign variables within a standard report?
>
> I'm familiar w/ the section in the Reports wiki on runtime parameters
> & variables (https://wiki.koha-community.org/wiki/SQL_Reports_Library#
> Runtime_Parameters).
> I've also played around w/ SET statements. But no luck finding a way
> to avoid repeating the date calculation three times without getting
> errors.
>
> As an aside, here's another approach to finding items owned by all 3
> branches:
>
>   WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= i.dateaccessioned AND
> i.homebranch IN ('kimball','plaistow','sandown')
>   GROUP BY biblionumber
>   HAVING count(DISTINCT homebranch) = 3
>
> Thanks in advance for any guidance.
>
> All best,
>
> Cab Vinton
> Plaistow Public Library
> Plaistow, NH
> _______________________________________________
> Koha mailing list  http://koha-community.org
> Koha at lists.katipo.co.nz
> https://lists.katipo.co.nz/mailman/listinfo/koha
>