Reports returning different totals
We recently went live and I'm now trying to run the monthly statistics reports I've set up. One of the reports is to total the number of checkouts and renewals for the month listed by library and the other is the same total number of checkouts and renewals but listed by the borrower category code. These two reports should equal but don't. When I was trying to figure out what is going wrong, I found the following two reports don't give the same results but as near as I can tell, they should. Can anyone please, please tell me why I'm seeing different results and which numbers are correct? We're on PTFS Koha. Thanks! Report 1 totals for the first three libraries: LIB1 4497 LIB2 10130 LIB3 1710 SQL for Report 1: SELECT borrowers.branchcode AS 'Library', COUNT(statistics.itemnumber) AS 'Total CKO' FROM statistics JOIN borrowers ON (borrowers.borrowernumber = statistics.borrowernumber) WHERE (statistics.datetime) >= '2011-04-15' AND DATE(statistics.datetime) <= '2011-04-30' AND statistics.type IN ('issue','renew') GROUP BY borrowers.branchcode Report 2 totals for the first three libraries: LIB1 4161 LIB2 9746 LIB3 1647 SQL for Report 2: SELECT statistics.branch AS Library, COUNT(statistics.itemnumber) AS 'Total CKO' FROM statistics WHERE DATE(statistics.datetime) >= '2011-04-15' AND DATE(statistics.datetime) <= '2011-04-30' AND statistics.type IN ('issue','renew') GROUP BY statistics.branch -- Cindy Weber cweber@scls.lib.wi.us System Support Specialist (608)242-4710 South Central Library System
We recently went live and I'm now trying to run the monthly statistics reports I've set up. One of the reports is to total the number of checkouts and renewals for the month listed by library and the other is the same total number of checkouts and renewals but listed by the borrower category code.
In Koha there is a bug which causes renewals to be recorded in the statistics table with no branch code. I don't know if PTFS's fork has a fix for this, but it sounds like that is a likely cause. Pending a solution I've been including a report of circulations with no branch alongside the other branch stats. -- Owen -- Web Developer Athens County Public Libraries http://www.myacpl.org
Thank you for the explanation! Between your explanation and Beverly Church's I've managed to figure out the problem. The top report listed the checkouts by the home library of the patron, not the checkout library so that was part of the issue. The other is we do appear to be affected by the reporting bug as well. There are thousands of renewals that show as having no agency code so they weren't included in the second report totals. Is there someplace else to get the renewal information? I would think issues.lastrenewdate and old_issues.lastrenewdate would get me some of them but not if the item had been renewed twice. Thanks! Cindy Owen Leonard wrote:
We recently went live and I'm now trying to run the monthly statistics reports I've set up. One of the reports is to total the number of checkouts and renewals for the month listed by library and the other is the same total number of checkouts and renewals but listed by the borrower category code.
In Koha there is a bug which causes renewals to be recorded in the statistics table with no branch code. I don't know if PTFS's fork has a fix for this, but it sounds like that is a likely cause. Pending a solution I've been including a report of circulations with no branch alongside the other branch stats.
-- Owen
-- Cindy Weber cweber@scls.lib.wi.us System Support Specialist (608)242-4710 South Central Library System
On 6 May 2011 06:59, Cindy Weber <cweber@scls.lib.wi.us> wrote:
Thank you for the explanation! Between your explanation and Beverly Church's I've managed to figure out the problem. The top report listed the checkouts by the home library of the patron, not the checkout library so that was part of the issue. The other is we do appear to be affected by the reporting bug as well. There are thousands of renewals that show as having no agency code so they weren't included in the second report totals.
Is there someplace else to get the renewal information? I would think issues.lastrenewdate and old_issues.lastrenewdate would get me some of them but not if the item had been renewed twice.
Hi Cindy The renewal information in the statistics table is more complete than using the lastrenewate, all it is missing is the branch info. So I would follow Owens suggestion for amending the report so it does a left join and groups the renewals together under a null branch code. If I remember correctly, the bug with renewals is limited only to those renewed from the OPAC, and the sticking point for the fix is what branch to store, since they aren't really renewing them from anywhere. I think the consensus was to use the borrowers homebranch. If people think this is a good enough fix, I could do a patch to get that being stored and submit it for sign off today. I'm assuming the PTFS fork pulls in code from actual Koha from time to time? If so, they should be able to apply that fix there too. Chris
If I remember correctly, the bug with renewals is limited only to those renewed from the OPAC, and the sticking point for the fix is what branch to store, since they aren't really renewing them from anywhere. I think the consensus was to use the borrowers homebranch.
If it were up to me I would say the circ stat should go to the branch where the item was checked out. So if a patron from Library A checks out an item from Library B, when the patron renews it Library B should get the credit. But I'll take just about anything over nothing. -- Owen -- Web Developer Athens County Public Libraries http://www.myacpl.org
On 6 May 2011 07:19, Owen Leonard <oleonard@myacpl.org> wrote:
If I remember correctly, the bug with renewals is limited only to those renewed from the OPAC, and the sticking point for the fix is what branch to store, since they aren't really renewing them from anywhere. I think the consensus was to use the borrowers homebranch.
If it were up to me I would say the circ stat should go to the branch where the item was checked out. So if a patron from Library A checks out an item from Library B, when the patron renews it Library B should get the credit.
That would work, any counter opinions? Chris
On 6 May 2011 07:19, Owen Leonard <oleonard@myacpl.org> wrote:
If I remember correctly, the bug with renewals is limited only to those renewed from the OPAC, and the sticking point for the fix is what branch to store, since they aren't really renewing them from anywhere. I think the consensus was to use the borrowers homebranch.
If it were up to me I would say the circ stat should go to the branch where the item was checked out. So if a patron from Library A checks out an item from Library B, when the patron renews it Library B should get the credit.
We discussed this on IRC and using a systempreference was the consensus. I am working on the patch now. You can track progress at http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=5199 Chris
Le 05/05/2011 17:37, Owen Leonard a écrit :
We recently went live and I'm now trying to run the monthly statistics reports I've set up. One of the reports is to total the number of checkouts and renewals for the month listed by library and the other is the same total number of checkouts and renewals but listed by the borrower category code.
In Koha there is a bug which causes renewals to be recorded in the statistics table with no branch code. I don't know if PTFS's fork has a fix for this, but it sounds like that is a likely cause. Pending a solution I've been including a report of circulations with no branch alongside the other branch stats.
-- Owen
Hi For what it is worth, we published a script to recreate statistic records out of issues and old issues tables. anyone feeling like testing could take that. http://git.biblibre.com/?p=koha;a=blob;f=misc/recreateIssueStatistics.pl;h=1... I think it was in the patches sent some time ago But maybe it was overlooked. Hope that helps. -- Henri-Damien LAURENT BibLibre
* LAURENT Henri-Damien (henridamien.laurent@biblibre.com) wrote:
Le 05/05/2011 17:37, Owen Leonard a écrit :
We recently went live and I'm now trying to run the monthly statistics reports I've set up. One of the reports is to total the number of checkouts and renewals for the month listed by library and the other is the same total number of checkouts and renewals but listed by the borrower category code.
In Koha there is a bug which causes renewals to be recorded in the statistics table with no branch code. I don't know if PTFS's fork has a fix for this, but it sounds like that is a likely cause. Pending a solution I've been including a report of circulations with no branch alongside the other branch stats.
Hi All
There is a patch now sent for bug 5199 http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=5199 Chris -- Chris Cormack Catalyst IT Ltd. +64 4 803 2238 PO Box 11-053, Manners St, Wellington 6142, New Zealand
participants (5)
-
Chris Cormack -
Chris Cormack -
Cindy Weber -
LAURENT Henri-Damien -
Owen Leonard