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@bywatersolutions.com Urgency : enhancement Status : RESOLVED On Wed, Jun 6, 2018 at 10:41 AM, Cab Vinton <bibliwho@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@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha