Hi everyone, I'm having some issues downloading the results of SQL reports. Running Koha 18.05. I'm using the "overdues by homeroom" code from the Koha Wiki SQL Reports library, as below: SELECT p.surname, p.firstname, c.date_due, g.attribute AS grade, (TO_DAYS(curdate())-TO_DAYS( date_due)) AS 'days overdue', b.title, b.author, i.itemcallnumber, i.barcode FROM borrowers p LEFT JOIN issues c ON (p.borrowernumber=c.borrowernumber) LEFT JOIN items i ON (c.itemnumber=i.itemnumber) LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber) LEFT JOIN borrower_attributes g ON (p.borrowernumber=g.borrowernumber) WHERE c.branchcode = '1490' AND (TO_DAYS(curdate())-TO_DAYS(date_due)) >= '' AND g.code="GRADE" ORDER BY g.attribute, p.surname ASC Which generates a report of overdue items on-screen as expected with no problems. BUT when I try to download the results as a CSV, OpenDocument, or tab-separated file, I only get the column heading labels, and none of the actual report data. Is there something I'm missing? I would greatly appreciate whatever advice you could give. Thank you Kind regards Michael Barry Library technician, Fitzroy North Primary School Fitzroy North, Australia. IMPORTANT - This email and any attachments may be confidential. If received in error, please contact us and delete all copies. Before opening or using attachments check them for viruses and defects. Regardless of any loss, damage or consequence, whether caused by the negligence of the sender or not, resulting directly or indirectly from the use of any attached files our liability is limited to resupplying any affected attachments. Any representations or opinions expressed are those of the individual sender, and not necessarily those of the Department of Education and Training.
Hi Michael, I don't see an obvious reason why it would not work. Which version of Koha are you using? Katrin On 08.06.2018 01:12, Barry, Michael M wrote:
Hi everyone,
I'm having some issues downloading the results of SQL reports. Running Koha 18.05.
I'm using the "overdues by homeroom" code from the Koha Wiki SQL Reports library, as below:
SELECT p.surname, p.firstname,
c.date_due, g.attribute AS grade,
(TO_DAYS(curdate())-TO_DAYS( date_due)) AS 'days overdue',
b.title, b.author, i.itemcallnumber,
i.barcode
FROM borrowers p
LEFT JOIN issues c ON (p.borrowernumber=c.borrowernumber)
LEFT JOIN items i ON (c.itemnumber=i.itemnumber)
LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber)
LEFT JOIN borrower_attributes g ON (p.borrowernumber=g.borrowernumber)
WHERE c.branchcode = '1490' AND (TO_DAYS(curdate())-TO_DAYS(date_due)) >= '' AND g.code="GRADE"
ORDER BY g.attribute, p.surname ASC
Which generates a report of overdue items on-screen as expected with no problems. BUT when I try to download the results as a CSV, OpenDocument, or tab-separated file, I only get the column heading labels, and none of the actual report data. Is there something I'm missing?
I would greatly appreciate whatever advice you could give.
Thank you
Kind regards
Michael Barry
Library technician, Fitzroy North Primary School Fitzroy North, Australia.
IMPORTANT - This email and any attachments may be confidential. If received in error, please contact us and delete all copies. Before opening or using attachments check them for viruses and defects. Regardless of any loss, damage or consequence, whether caused by the negligence of the sender or not, resulting directly or indirectly from the use of any attached files our liability is limited to resupplying any affected attachments. Any representations or opinions expressed are those of the individual sender, and not necessarily those of the Department of Education and Training. _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
Anyone who would like to host KohaCon19 (international Koha conference to be held in 2019) should add some brief information about their proposal to the proposals summary table in the page on the Koha Wiki which has been prepared following the model used for previous KohaCons, https://wiki.koha-community.org/wiki/KohaCon19_Proposals and for which there is already a proposal from Stockholm University Library. At the 6 June 2018 Koha General IRC meeting, people concluded that we should solicit bids June to 1 August 2018. Please link your summary proposal to a more detailed proposal preferably in a new page on the Koha wiki or alternately hosted on your own webserver. If you have submitted a proposal for hosting KohaCon in the past but had not been selected, please submit a new proposal if you are interested in hosting KohaCon19. Do not be discouraged that some other proposal had been selected over yours in some previous year. [In the interest of promoting regional diversity in Koha, we have rules against selecting KohaCon proposals from the same contintent within three years in case the most populous regions might come to excessively dominate voting for selecting a proposal. The rule would be set aside for a particular year if no proposal from a different continent has been introduced. See https://wiki.koha-community.org/wiki/Processes_for_KohaCons .] Anyone should be free to add additional relevant information to proposal information in the wiki, such as links and information for local hotel or other accommodations, attractions, etc. We want whatever information may be helpful for linking to a community wide ballot for selecting a particular proposal. As with all wiki content, it should be easy enough to edit by examining the form in which pre-existing content has been entered, when editing to add your own content. If you want more information about MediaWiki tables, see http://www.mediawiki.org/wiki/Help:Tables . Thomas Dukleth Agogme 109 E 9th Street, 3D New York, NY 10003 USA http://www.agogme.com +1 212-674-3783
I tried your query with the WHERE clause slightly simplified to give a non-empty result on my test installation of 18.05: WHERE (TO_DAYS(curdate())-TO_DAYS(date_due)) >= '' I was then able to download the CSV file and read it into LibreOffice Calc with no problems. So your query looks good; maybe take a look at the CSV file in an editor to see if there's anything strange about it. Or perhaps you have to tell your CSV import tool to use commas, and not semicolons or other delimiters (LibreOffice has several options for this).
Hi Mark, I tried running a different saved report (made with the report wizard, to produce a list of patrons by categorycode and sort1 fields) and the results downloaded into the CSV and OpenDocument files fine, so I think it might be something I'm missing in my SQL code. I replaced my WHERE line with your simplified version and the problem persisted. Opening the CSV file in Notepad yielded the same results: column headings were downloaded, the actual report results were not. Thanks for your help, Kind regards, Michael Barry -----Original Message----- From: Koha [mailto:koha-bounces@lists.katipo.co.nz] On Behalf Of Mark Alexander Sent: Wednesday, 13 June 2018 1:11 AM To: koha Subject: Re: [Koha] SQL Report not populating I tried your query with the WHERE clause slightly simplified to give a non-empty result on my test installation of 18.05: WHERE (TO_DAYS(curdate())-TO_DAYS(date_due)) >= '' I was then able to download the CSV file and read it into LibreOffice Calc with no problems. So your query looks good; maybe take a look at the CSV file in an editor to see if there's anything strange about it. Or perhaps you have to tell your CSV import tool to use commas, and not semicolons or other delimiters (LibreOffice has several options for this). _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha IMPORTANT - This email and any attachments may be confidential. If received in error, please contact us and delete all copies. Before opening or using attachments check them for viruses and defects. Regardless of any loss, damage or consequence, whether caused by the negligence of the sender or not, resulting directly or indirectly from the use of any attached files our liability is limited to resupplying any affected attachments. Any representations or opinions expressed are those of the individual sender, and not necessarily those of the Department of Education and Training.
I think I've found the source of the problem, I need to specify a ≥1 limitation on 'days overdue' in the box that comes up after running the report, leaving the box blank doesn't work. Thanks again for the assistance. Kind regards, Michael Barry. -----Original Message----- From: Koha [mailto:koha-bounces@lists.katipo.co.nz] On Behalf Of Barry, Michael M Sent: Wednesday, 13 June 2018 9:07 AM To: koha Subject: Re: [Koha] SQL Report not populating Hi Mark, I tried running a different saved report (made with the report wizard, to produce a list of patrons by categorycode and sort1 fields) and the results downloaded into the CSV and OpenDocument files fine, so I think it might be something I'm missing in my SQL code. I replaced my WHERE line with your simplified version and the problem persisted. Opening the CSV file in Notepad yielded the same results: column headings were downloaded, the actual report results were not. Thanks for your help, Kind regards, Michael Barry -----Original Message----- From: Koha [mailto:koha-bounces@lists.katipo.co.nz] On Behalf Of Mark Alexander Sent: Wednesday, 13 June 2018 1:11 AM To: koha Subject: Re: [Koha] SQL Report not populating I tried your query with the WHERE clause slightly simplified to give a non-empty result on my test installation of 18.05: WHERE (TO_DAYS(curdate())-TO_DAYS(date_due)) >= '' I was then able to download the CSV file and read it into LibreOffice Calc with no problems. So your query looks good; maybe take a look at the CSV file in an editor to see if there's anything strange about it. Or perhaps you have to tell your CSV import tool to use commas, and not semicolons or other delimiters (LibreOffice has several options for this). _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha IMPORTANT - This email and any attachments may be confidential. If received in error, please contact us and delete all copies. Before opening or using attachments check them for viruses and defects. Regardless of any loss, damage or consequence, whether caused by the negligence of the sender or not, resulting directly or indirectly from the use of any attached files our liability is limited to resupplying any affected attachments. Any representations or opinions expressed are those of the individual sender, and not necessarily those of the Department of Education and Training. _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha IMPORTANT - This email and any attachments may be confidential. If received in error, please contact us and delete all copies. Before opening or using attachments check them for viruses and defects. Regardless of any loss, damage or consequence, whether caused by the negligence of the sender or not, resulting directly or indirectly from the use of any attached files our liability is limited to resupplying any affected attachments. Any representations or opinions expressed are those of the individual sender, and not necessarily those of the Department of Education and Training.
This bug may be related as well: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=20945 On Tue, Jun 12, 2018 at 7:43 PM Barry, Michael M < Barry.Michael.M@edumail.vic.gov.au> wrote:
I think I've found the source of the problem, I need to specify a ≥1 limitation on 'days overdue' in the box that comes up after running the report, leaving the box blank doesn't work.
Thanks again for the assistance.
Kind regards,
Michael Barry.
-----Original Message----- From: Koha [mailto:koha-bounces@lists.katipo.co.nz] On Behalf Of Barry, Michael M Sent: Wednesday, 13 June 2018 9:07 AM To: koha Subject: Re: [Koha] SQL Report not populating
Hi Mark,
I tried running a different saved report (made with the report wizard, to produce a list of patrons by categorycode and sort1 fields) and the results downloaded into the CSV and OpenDocument files fine, so I think it might be something I'm missing in my SQL code.
I replaced my WHERE line with your simplified version and the problem persisted. Opening the CSV file in Notepad yielded the same results: column headings were downloaded, the actual report results were not.
Thanks for your help,
Kind regards, Michael Barry
-----Original Message----- From: Koha [mailto:koha-bounces@lists.katipo.co.nz] On Behalf Of Mark Alexander Sent: Wednesday, 13 June 2018 1:11 AM To: koha Subject: Re: [Koha] SQL Report not populating
I tried your query with the WHERE clause slightly simplified to give a non-empty result on my test installation of 18.05:
WHERE (TO_DAYS(curdate())-TO_DAYS(date_due)) >= ''
I was then able to download the CSV file and read it into LibreOffice Calc with no problems.
So your query looks good; maybe take a look at the CSV file in an editor to see if there's anything strange about it. Or perhaps you have to tell your CSV import tool to use commas, and not semicolons or other delimiters (LibreOffice has several options for this). _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
IMPORTANT - This email and any attachments may be confidential. If received in error, please contact us and delete all copies. Before opening or using attachments check them for viruses and defects. Regardless of any loss, damage or consequence, whether caused by the negligence of the sender or not, resulting directly or indirectly from the use of any attached files our liability is limited to resupplying any affected attachments. Any representations or opinions expressed are those of the individual sender, and not necessarily those of the Department of Education and Training. _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
IMPORTANT - This email and any attachments may be confidential. If received in error, please contact us and delete all copies. Before opening or using attachments check them for viruses and defects. Regardless of any loss, damage or consequence, whether caused by the negligence of the sender or not, resulting directly or indirectly from the use of any attached files our liability is limited to resupplying any affected attachments. Any representations or opinions expressed are those of the individual sender, and not necessarily those of the Department of Education and Training. _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
-- Nick Clemens Sonic Screwdriver (Development Support) ByWater Solutions IRC: kidclamp
participants (5)
-
Barry, Michael M -
Katrin Fischer -
Mark Alexander -
Nick Clemens -
Thomas Dukleth