Re: [Koha] Report Help - With Rollup
Okay - now here's the new issue - the grand total does not show:
SELECT IFNULL(a.lib,'*GRAND TOTAL*') as Location, thingy.circs AS
Circulations FROM (select i.location as loction, count(s.datetime) as
circs from statistics s left join items i using (itemnumber) left join
borrowers p using (borrowernumber) where s.type in ('issue','renew')
and p.categorycode != 'LIBRARYUSE' and s.branch=Check out/renewal
branch|branches and s.datetime between
Checked out or renewed between (-mm-dd)|date and and
(-mm-dd)|date group by i.location with rollup ) AS thingy LEFT
JOIN authorised_values a on thingy.loction=a.authorised_value
where a.category='LOC'
On Tue, Mar 25, 2014 at 4:16 PM, Nick Clemens n...@quecheelibrary.org wrote:
Ah, I see now, not a bug, I thought the duplicate authorised_values weren't
showing up, they were just filing odd because I was using test values and
not sorting.
On Tue, Mar 25, 2014 at 4:03 PM, Nicole Engard neng...@gmail.com wrote:
Nick,
I figured it out - we needed to limit to LOC authorized values:
SELECT IFNULL(a.lib,'*GRAND TOTAL*') as Location, thingy.circs AS
Circulations FROM (select i.location as loction, count(s.datetime) as
circs from statistics s left join items i using (itemnumber) left join
borrowers p using (borrowernumber) where s.type in ('issue','renew')
and p.categorycode != 'LIBRARYUSE' and s.branch=Check out/renewal
branch|branches and s.datetime between
Checked out or renewed between (-mm-dd)|date and and
(-mm-dd)|date group by i.location with rollup ) AS thingy LEFT
JOIN authorised_values a on thingy.loction=a.authorised_value
where a.category='LOC'
On Tue, Mar 25, 2014 at 1:08 PM, Nick Clemens n...@quecheelibrary.org
wrote:
Here's a thought, it might be an error in the authorized values., and I
think it is a bug
I recreated it on our system by adding an authorized_value in a
different
category with the same value as one in the LOC:
i.e.
I added AFIC to our Vendor category, with description Error! The system
doesn't throw an error, and I can't see that value in the listing, but
it is
then 'Adult Fiction' is duplicated in my report
Is the issue clear?
On Thu, Mar 20, 2014 at 7:04 PM, Nicole Engard neng...@gmail.com
wrote:
Okay, any idea why the Juveasy might be showing 2 times?
http://screencast.com/t/6dbByW4eZ
I added the location code in case that was it - they're identical as
are their numbers.
On Thu, Mar 20, 2014 at 1:50 PM, Nicole Engard neng...@gmail.com
wrote:
Thank you so much Nick!!
Nicole
--
Nick Clemens
Quechee Wilder Libraries
n...@quecheelibrary.org
http://www.QuecheeLibrary.org
Q (802) 295-1232 W (802) 295-6341
--
Nick Clemens
Quechee Wilder Libraries
n...@quecheelibrary.org
http://www.QuecheeLibrary.org
Q (802) 295-1232 W (802) 295-6341
___
Koha mailing list http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha
Re: [Koha] Report Help - With Rollup
Here's a thought, it might be an error in the authorized values., and I think it is a bug I recreated it on our system by adding an authorized_value in a different category with the same value as one in the LOC: i.e. I added AFIC to our Vendor category, with description Error! The system doesn't throw an error, and I can't see that value in the listing, but it is then 'Adult Fiction' is duplicated in my report Is the issue clear? On Thu, Mar 20, 2014 at 7:04 PM, Nicole Engard neng...@gmail.com wrote: Okay, any idea why the Juveasy might be showing 2 times? http://screencast.com/t/6dbByW4eZ I added the location code in case that was it - they're identical as are their numbers. On Thu, Mar 20, 2014 at 1:50 PM, Nicole Engard neng...@gmail.com wrote: Thank you so much Nick!! Nicole -- Nick Clemens Quechee Wilder Libraries n...@quecheelibrary.org http://www.QuecheeLibrary.org Q (802) 295-1232 W (802) 295-6341 ___ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
Re: [Koha] Report Help - With Rollup
Nick,
I figured it out - we needed to limit to LOC authorized values:
SELECT IFNULL(a.lib,'*GRAND TOTAL*') as Location, thingy.circs AS
Circulations FROM (select i.location as loction, count(s.datetime) as
circs from statistics s left join items i using (itemnumber) left join
borrowers p using (borrowernumber) where s.type in ('issue','renew')
and p.categorycode != 'LIBRARYUSE' and s.branch=Check out/renewal
branch|branches and s.datetime between
Checked out or renewed between (-mm-dd)|date and and
(-mm-dd)|date group by i.location with rollup ) AS thingy LEFT
JOIN authorised_values a on thingy.loction=a.authorised_value
where a.category='LOC'
On Tue, Mar 25, 2014 at 1:08 PM, Nick Clemens n...@quecheelibrary.org wrote:
Here's a thought, it might be an error in the authorized values., and I
think it is a bug
I recreated it on our system by adding an authorized_value in a different
category with the same value as one in the LOC:
i.e.
I added AFIC to our Vendor category, with description Error! The system
doesn't throw an error, and I can't see that value in the listing, but it is
then 'Adult Fiction' is duplicated in my report
Is the issue clear?
On Thu, Mar 20, 2014 at 7:04 PM, Nicole Engard neng...@gmail.com wrote:
Okay, any idea why the Juveasy might be showing 2 times?
http://screencast.com/t/6dbByW4eZ
I added the location code in case that was it - they're identical as
are their numbers.
On Thu, Mar 20, 2014 at 1:50 PM, Nicole Engard neng...@gmail.com wrote:
Thank you so much Nick!!
Nicole
--
Nick Clemens
Quechee Wilder Libraries
n...@quecheelibrary.org
http://www.QuecheeLibrary.org
Q (802) 295-1232 W (802) 295-6341
___
Koha mailing list http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha
Re: [Koha] Report Help - With Rollup
Ah, I see now, not a bug, I thought the duplicate authorised_values weren't
showing up, they were just filing odd because I was using test values and
not sorting.
On Tue, Mar 25, 2014 at 4:03 PM, Nicole Engard neng...@gmail.com wrote:
Nick,
I figured it out - we needed to limit to LOC authorized values:
SELECT IFNULL(a.lib,'*GRAND TOTAL*') as Location, thingy.circs AS
Circulations FROM (select i.location as loction, count(s.datetime) as
circs from statistics s left join items i using (itemnumber) left join
borrowers p using (borrowernumber) where s.type in ('issue','renew')
and p.categorycode != 'LIBRARYUSE' and s.branch=Check out/renewal
branch|branches and s.datetime between
Checked out or renewed between (-mm-dd)|date and and
(-mm-dd)|date group by i.location with rollup ) AS thingy LEFT
JOIN authorised_values a on thingy.loction=a.authorised_value
where a.category='LOC'
On Tue, Mar 25, 2014 at 1:08 PM, Nick Clemens n...@quecheelibrary.org
wrote:
Here's a thought, it might be an error in the authorized values., and I
think it is a bug
I recreated it on our system by adding an authorized_value in a different
category with the same value as one in the LOC:
i.e.
I added AFIC to our Vendor category, with description Error! The system
doesn't throw an error, and I can't see that value in the listing, but
it is
then 'Adult Fiction' is duplicated in my report
Is the issue clear?
On Thu, Mar 20, 2014 at 7:04 PM, Nicole Engard neng...@gmail.com
wrote:
Okay, any idea why the Juveasy might be showing 2 times?
http://screencast.com/t/6dbByW4eZ
I added the location code in case that was it - they're identical as
are their numbers.
On Thu, Mar 20, 2014 at 1:50 PM, Nicole Engard neng...@gmail.com
wrote:
Thank you so much Nick!!
Nicole
--
Nick Clemens
Quechee Wilder Libraries
n...@quecheelibrary.org
http://www.QuecheeLibrary.org
Q (802) 295-1232 W (802) 295-6341
--
Nick Clemens
Quechee Wilder Libraries
n...@quecheelibrary.org
http://www.QuecheeLibrary.org
Q (802) 295-1232 W (802) 295-6341
___
Koha mailing list http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha
Re: [Koha] Report Help - With Rollup
It looks like there isn't a direct way to do that, see
herehttp://stackoverflow.com/questions/12940119/mysql-change-last-row-of-rollup,
but you can wrap the query up and join it to authorised values and make it
work with the caution that any circs without location defined will also end
up labeled Grand Total' and should be fixed before running the report.
SELECT IFNULL(a.lib,'Grand Total') as Location, thingy.circs AS Circulations
FROM (select i.location as loction, count(s.datetime) as
circs from statistics s left join items i using (itemnumber)
left join borrowers p using (borrowernumber)
where s.type in ('issue','renew') and p.categorycode != 'LIBRARYUSE' and
s.branch=Check out/renewal
branch|branches and s.datetime between
Checked out or renewed between (-mm-dd)|date and and
(-mm-dd)|date
group by i.location with rollup ) AS thingy
LEFT JOIN authorised_values a on thingy.loction=a.authorised_value
On Thu, Mar 20, 2014 at 9:11 AM, Nicole Engard neng...@gmail.com wrote:
Hi all,
I need some help with a report that has With RollUp in it. I have
used this before and had no problem, but for this report it keeps
showing the last value 2 times at the bottom instead of 'grand total'.
So here's how I started:
select IFNULL(a.lib,'*GRAND TOTAL*') as location, count(s.datetime) as
circs from statistics s left join items i using (itemnumber) left join
borrowers p using (borrowernumber) LEFT JOIN authorised_values a ON
(i.location=a.authorised_value) where s.type in ('issue','renew') and
p.categorycode != 'LIBRARYUSE' and s.branch=Check out/renewal
branch|branches and a.category='LOC' and s.datetime between
Checked out or renewed between (-mm-dd)|date and and
(-mm-dd)|date group by i.location with rollup
This runs and is right, but at the end it repeats the last shelving
location instead of showing 'Grand Total'. Next I tried:
select if(i.location is null, '*GRAND TOTAL*', a.lib) as location,
count(s.datetime) as circs
from statistics s
left join items i using (itemnumber)
left join borrowers p using (borrowernumber)
LEFT JOIN authorised_values a ON (i.location=a.authorised_value)
where s.type in ('issue','renew') and p.categorycode != 'LIBRARYUSE'
and s.branch='BAILEYCOVE'
and a.category='LOC' and s.datetime between '2014-03-19'
and '2014-03-20'
group by i.location with rollup
This repeated the same location over and over. So then I tried this:
select ifnull(i.location, 'GRAND TOTAL') as code, a.lib as location,
count(s.datetime) as circs
from statistics s
left join items i using (itemnumber)
left join borrowers p using (borrowernumber)
LEFT JOIN authorised_values a ON (i.location=a.authorised_value)
where s.type in ('issue','renew') and p.categorycode != 'LIBRARYUSE'
and s.branch=Check out/renewal branch|branches
and a.category='LOC' and s.datetime between Checked out or
renewed between (-mm-dd)|date
and and (-mm-dd)|date
group by i.location with rollup
Which shows grand total, but still repeats the darn location name again.
And this which shows only one location over and over:
select case a.location when null 'grand total' else a.lib as location,
count(s.datetime) as circs
from statistics s
left join items i using (itemnumber)
left join borrowers p using (borrowernumber)
LEFT JOIN authorised_values a ON (i.location=a.authorised_value)
where s.type in ('issue','renew') and p.categorycode != 'LIBRARYUSE'
and s.branch=Check out/renewal branch|branches
and a.category='LOC' and s.datetime between Checked out or
renewed between (-mm-dd)|date
and and (-mm-dd)|date
group by i.location with rollup
I'm up for any suggestion that gets me to have the final line just
have the total and not a false location name.
___
Koha mailing list http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha
--
Nick Clemens
Quechee Wilder Libraries
n...@quecheelibrary.org
http://www.QuecheeLibrary.org
Q (802) 295-1232 W (802) 295-6341
___
Koha mailing list http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha
Re: [Koha] Report Help - With Rollup
Thank you so much Nick!! Nicole ___ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
Re: [Koha] Report Help - With Rollup
Okay, any idea why the Juveasy might be showing 2 times? http://screencast.com/t/6dbByW4eZ I added the location code in case that was it - they're identical as are their numbers. On Thu, Mar 20, 2014 at 1:50 PM, Nicole Engard neng...@gmail.com wrote: Thank you so much Nick!! Nicole ___ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
