Re: [Koha] Report Help - With Rollup

[Nicole Engard]

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=<> and s.datetime between
<> and <> 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  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  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=<> branch|branches>> and s.datetime between
>> <> 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 
>> 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 
>> > 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 
>> >> 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

[Nick Clemens]

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  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=< branch|branches>> and s.datetime between
> <> 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 
> 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 
> 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 
> 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

[Nicole Engard]

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=<> and s.datetime between
<> and <> 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  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  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  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 Clemens]

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  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  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

[Nicole Engard]

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  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

Re: [Koha] Report Help - With Rollup

[Nicole Engard]

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

[Nick Clemens]

It looks like there isn't a direct way to do that, see
here,
 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=<> and s.datetime between
<> and <>
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  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=< branch|branches>> and a.category='LOC' and s.datetime between
> <> 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=<>
> and a.category='LOC' and s.datetime between < renewed between (-mm-dd)|date>>
> and <>
> 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=<>
> and a.category='LOC' and s.datetime between < renewed between (-mm-dd)|date>>
> and <>
> 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

[Koha] Report Help - With Rollup

[Nicole Engard]

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=<> and a.category='LOC' and s.datetime between
<> and <> 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=<>
and a.category='LOC' and s.datetime between <>
and <>
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=<>
and a.category='LOC' and s.datetime between <>
and <>
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