[Koha] Report help needed

Forwarding this to the list. Joel's suggestion fixed the problems. Elaine VWML <https://vwml.org> -- Forwarded message - From: Coehoorn, Joel Date: Tue, May 10, 2022 at 11:20 AM Subject: Re: [Koha] Report help needed To: Elaine Bradtke It could just be my email

Re: [Koha] Report help needed

Hi Elaine, I guess there is a space missing before FROM in ...main_headingFROM `auth_header`... And also a colon after GENRE/FORM) Else you can try your SQL by parts and start by the basics. Something like SELECT authid, datecreated, authtypecode,

[Koha] Report help needed

I imported the following report from Mana, but it doesn't work as is. I tried changing `marcxml` to `metadata` but it still didn't work. The error message (You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at

Re: [Koha] Report help needed

fter AS sub3 that is not needed. > I was able to get the report to run on my system after taking out the , > Lisette > -Original Message- > From: Koha [mailto:koha-boun...@lists.katipo.co.nz] On Behalf Of Elaine > Bradtke > Sent: Tuesday, January 5, 2021 11:10 AM > To: koha >

Re: [Koha] Report help needed

Subject: [Koha] Report help needed I'm trying to find records lacking 300 fields. The following keeps failing. Any help would be appreciated. Thanks! SELECT CONCAT('',biblionumber,'') AS bibnumber FROM (SELECT biblionumber, ExtractValue(metadata,'//datafield[@tag="300"]/subf

Re: [Koha] Report help needed

Hi Elaine, I notice that you compare to >= so it will include other subfields bigger than... . Can you try changing this to read just =? Katrin On 05.01.21 20:10, Elaine Bradtke wrote: I'm trying to find records lacking 300 fields. The following keeps failing. Any help would be appreciated.

[Koha] Report help needed

I'm trying to find records lacking 300 fields. The following keeps failing. Any help would be appreciated. Thanks! SELECT CONCAT('',biblionumber,'') AS bibnumber FROM (SELECT biblionumber, ExtractValue(metadata,'//datafield[@tag="300"]/subfield[@code>="a"]') AS suba,

Re: [Koha] Report help needed

I do not know about the speed and I'm not an expert either. The beauty of the reports is that we can borrow them from others, save them and change them as needed, right? It is the same as the former query 'ExtractValue(marcxml' changing to ExtractValue(metadata'. All of the reports in the

Re: [Koha] Report help needed

Excerpts from Michael Sutherland's message of 2020-05-27 16:59:50 -0400: > CASE SUBSTR(metadata,282,2) I tried this myself, and it does seem to work. But I worry that the 282 value might not be correct in the future, should the XML format of the metadata change even slightly. In particular, the

Re: [Koha] Report help needed

Thank you. I'm unfamiliar with that particular bug and will check it out for our records. Best, Michael _ *Michael J. Sutherland* University Libraries Virginia Tech sudrl...@vt.edu | 540.231.9669 <+15402319669> On Wed, May 27, 2020 at 7:05 PM Elaine

Re: [Koha] Report help needed

Not condescending at all, the things I don't know about reports would fill a book. Interesting report you have there by the way. The purpose for mine is to double check the position 06 against the 942 $c because there is a bug 19419 that seems to cause trouble with the 008 if the Leader 06 is not

Re: [Koha] Report help needed

Elaine, Maybe this would be helpful for those codes - https://www.loc.gov/marc/bibliographic/bdleader.html And, to break it down for those that do not know and are interested, (at the risk of condescending, which is not my intention, please, please, I hope you do not take it that way) in the

Re: [Koha] Report help needed

Yes, I think I've got it now. Thanks for your help. The hard part was figuring out where to find the itemtype in the bib record. SELECT biblio.biblionumber, SUBSTRING(ExtractValue(metadata,'//leader'),7,1) AS "Position06" FROM biblio LEFT JOIN biblio_metadata USING (biblionumber) WHERE

Re: [Koha] Report help needed

Hi Elaine, The biblioitems.marcxml field has been moved to biblio_metadata.metadata. Does this query work as you want: SELECT CONCAT('',b.biblionumber,'') AS biblionumber FROM biblio b LEFT JOIN biblioitems bi ON b.biblionumber= bi.biblionumber LEFT JOIN biblio_metadata bm ON

[Koha] Report help needed

What I'd like it to do - give a list of biblio numbers for records with a particular itemtype, and the information found in the Leader *06 - Type of record *field. I found the following in the reports library as something that could be modified to fit my needs. But it doesn't work I get the

Re: [Koha] Report help needed - lost items

Very late to the party, but maybe this could provide a clue. The following throws an error, but I believe the basic idea of finding records where the lowest itemlost value isn't zero is on the right track: WHERE itype = <> GROUP BY biblionumber HAVING MIN(itemlost) > 0 I suspect SQL is

Re: [Koha] Report help needed - lost items

Mark, that still didn't do the trick, but thanks! Caroline, this is much closer to what I need. But still not quite right. It's messy because I've altered a report that I found in the report library rather inelegantly. What I really need is a list of titles and biblio numbers of biblios where the

Re: [Koha] Report help needed - lost items

Hello Elaine, from what I understand, you want bibliographic information on lost items? I'd need to know exactly the purpose of the report to help you more as there are things in there I don't understand (like why select barcode, when afterwards you group by biblionumber, or why the title is

Re: [Koha] Report help needed - lost items

Excerpts from Elaine Bradtke's message of 2019-08-23 11:32:44 -0700: > I've got the following adapted from one in the reports library. But I need > to limit it by item type, or not return everything that doesn't have an > item record attached. We've got lots of components in our catalogue that >

[Koha] Report help needed - lost items

I've got the following adapted from one in the reports library. But I need to limit it by item type, or not return everything that doesn't have an item record attached. We've got lots of components in our catalogue that don't have items and it's returning all of them: SELECT CONCAT(

Re: [Koha] Report help

Thanks so much Caroline. Perhaps I should look at the statistics wizards more often! Kind regards David Hughes Systems Librarian Dublin Business School 13-14 Aungier St. Dublin 2 Ireland 00 353 1 417 8744 david.hug...@dbs.ie *http://library.dbs.ie * On 3

Re: [Koha] Report help

Hi David, Did you try with the statistics wizards? If you go in Reports > Statistics wizards > Patrons and then choose your first attribute "CC" as a row and "CD" as a column, it should give you a table with all the different combinations and the count of patrons with that combination.

[Koha] Report help

Hi, I want to create a report that gives a grand total of records with various combinations of two different patron attributes. I have two different borrower_attributes.code values "CC" and "CD" and I want to output the grand totals of records with all permutations of

Re: [Koha] Report help needed

Yes, that did the trick! Thanks This is the revised report: SELECT DISTINCT biblionumber, heading FROM ( SELECT biblionumber, ExtractValue(metadata, '//datafield[@tag="100"]/subfield[@code="a"]') AS heading FROM biblio_metadata WHERE length(ExtractValue(metadata,

Re: [Koha] Report help needed

On Fri, Jul 13, 2018 at 10:06:56AM -0700, Elaine Bradtke wrote: > This report finds Authors not linked to authorities. But it only lists > their names (some of them are quite prolific, and sometimes it's only one > biblio that needs checking). > How can I make the following give the biblio number

[Koha] Report help needed

This report finds Authors not linked to authorities. But it only lists their names (some of them are quite prolific, and sometimes it's only one biblio that needs checking). How can I make the following give the biblio number as well as the name? SELECT DISTINCT heading FROM ( SELECT

Re: [Koha] Report help

Hi Mutasem, Can you clarify what you mean by "number of search book in koha"? I can think of a few different interpretations, including: 1) number of times someone searched the OPAC for a book 2) number of books that be retrieved by a search #1 cannot be answered with a report, as far as I

[Koha] Report help

Hi, any one can help me, i need report to count number of search book in koha. thank, Mutasem -- -- Disclaimer: This message and its attachment, if any, are confidential and may contain legally privileged information. If you are not the intended recipient,

Re: [Koha] Report help

Yes, sorry, I had done this before for other reports and now forgot to return to it. That did the trick!! Thank you! Katharina On Wed, Feb 21, 2018 at 6:16 PM, Jonathan Druart < jonathan.dru...@bugs.koha-community.org> wrote: > Hello Katharina, > > Do you see the "Update SQL" button on the

Re: [Koha] Report help

Hello Katharina, Do you see the "Update SQL" button on the report list? Did you try it? It is supposed to update the query with the new table structure. Regards, Jonathan On Wed, 21 Feb 2018 at 13:24 Katharina Penner wrote: > We use 17.05, and so I have tried to

Re: [Koha] Report help

We use 17.05, and so I have tried to substitute "marcxml" with "biblio_metadata", like recommended at the Wiki page: https://wiki.koha-community.org/wiki/SQL_Reports_Library#Query_MARC. But I keep getting the following error message: The following error was encountered: The database returned the

[Koha] Report help

Dear all, I need to create a report to extract all metadata on all authors. I want to have each aspect - surname, name (or initials), dates, expansion of initials, other possible fields - appear in a separate field/column, in a delimited way. We're using UniMarc so these would be fields 700 a, b,

Re: [Koha] Report help

Hi, Try this: SELECT biblio.title, biblio.author, items.itemcallnumber, lcsh FROM (SELECT biblionumber, ExtractValue(marcxml,'//datafield[@tag="650"]/subfield[@code>="a"]') AS lcsh FROM biblioitems) AS subjects JOIN biblio USING(biblionumber) JOIN items USING(biblionumber) WHERE lcsh LIKE

[Koha] Report help needed

We have one record in the catalogue that is missing a 942 $c. but I can't find it. I'm looking for a 942$c that is empty. If I could get the biblio number of the problem record, I could fix it. Thanks in advance -- Elaine Bradtke VWML English Folk Dance and Song Society | http://www.efdss.org

Re: [Koha] Report help

I think the problem is that return from ExtractValue returns only one item so the GROUP_CONCAT is only seeing one thing come in and doesn't add the separator: See this: http://stackoverflow.com/questions/10808149/mysql-use-extractvaluexml-value-values-to-get-all-multiple-values-split-on On Tue,

Re: [Koha] Report help

Hi Nicole Nick! I'm particularly interested with this report. What I did before was this: ExtractValue (marcxml,'//datafield[@tag=653]/subfield[@code=a][1]') as Keywords, ExtractValue (marcxml,'//datafield[@tag=653]/subfield[@code=a][2]') as Keywords, ExtractValue

Re: [Koha] Report help

Well this is awesome - thanks so much for giving me an option! On Tue, Apr 14, 2015 at 3:39 PM, Nick Clemens n...@quecheelibrary.org wrote: Yes to both. It's not a perfect workaround by far, but thought it was worth mentioning You could something like CONCAT_WS(' BR

Re: [Koha] Report help

You can also use the position marker in the xpath to pick an arbitrary number of subject headings: GROUP_CONCAT(DISTINCT ExtractValue(m.marcxml,'//datafield[@tag=650][position()=1]/subfield[@code=a]'),' BR ',ExtractValue(m.marcxml,'//datafield[@tag=650][position()=2]/subfield[@code=a]'),' BR

Re: [Koha] Report help

Don't I have to guess at what the max number of subjects would be then? and then won't I have a bunch of BRs all over the place if there is only one subject? On Tue, Apr 14, 2015 at 2:02 PM, Nick Clemens n...@quecheelibrary.org wrote: You can also use the position marker in the xpath to pick an

Re: [Koha] Report help

Yes to both. It's not a perfect workaround by far, but thought it was worth mentioning You could something like CONCAT_WS(' BR ',IFNULL(ExtractValue(m.marcxml,'// datafield[@tag=650][position()=1]/subfield[@code=a]',''),IFNULL ExtractValue(m.marcxml,'//datafield[@tag=650][

[Koha] Report help

Hi all, I'm looking for a report to give the average age of the collection in a few item types. This report runs but isn't right: SELECT i.itype, avg(b.pubdate REGEXP '^[0-9]+$') as average FROM items i left join (select biblionumber, ExtractValue(marcxml,

Re: [Koha] Report help needed

Thanks Heather, and double thanks for the hint about commas. It only takes one little missing piece of the puzzle for the whole thing to fail. I could really use something like SQL queries for Dummies. Elaine On Mon, Nov 17, 2014 at 11:47 PM, Heather Braum (NEKLS) hbr...@nekls.org wrote:

[Koha] Report help needed

I have a request for a report that is beyond my paltry abilities and that of the report builder. I have this, and it works (thanks Liz!): SELECT biblio.biblionumber,biblio.title,biblio.notes,biblioitems.publishercode,biblioitems.itemtype FROM biblioitems LEFT JOIN biblio USING (biblionumber)

Re: [Koha] Report help needed

Elaine, try the below. It looks like you were missing some commas -- that was what was causing the syntax errors; you can then name the columns like I did below or however you want using as ___ single-word (as 780t) or as two or more words (as 780w field_). Make sure every column of data in the

Re: [Koha] Report help

-Original Message- From: Kerrie Stevens [mailto:kstev...@harvest.edu.au] Sent: Tuesday, October 14, 2014 12:29 AM To: koha@lists.katipo.co.nz Subject: [Koha] Report help Hi Everyone, I've been using the report 'Patrons with most checkouts in date range' with success, but when I try to modify

[Koha] Report help

Hi Everyone, I've been using the report 'Patrons with most checkouts in date range' with success, but when I try to modify it to allow me to select which campus library the patrons are from so I can get a top 20 list for any of our campus libraries, I can't get it to work - can anyone help me

Re: [Koha] Report help

Kerrie, You need to add the parameter of b.branchcode=choose branch|branches in the WHERE part of your SQL statement, like I did below. The {choose branch} part is actually arbitrary and can be whatever you want. Try this: SELECT concat(b.surname,', ',b.firstname) AS name,

Re: [Koha] Report help- reading history

Dear Vishal Patil, thank you for the assistance. The school did use the 'Patrons checking out the most' report and then copied and paste the reading history into a spreadsheet to produce the information they needed, it's just that they were hoping for a solution that required a little less

Re: [Koha] Report help- reading history

Clint, This is SUPER rough and cobbled-together, but it's the best I could do in a few minutes. If I have time later, I'll try to clean it up a bit (or others are welcome to do so). I have NOT tested it very extensively! It doesn't show multiple checkouts of the same item by the same patron, but

Re: [Koha] Report help needed - modification date

Thanks, that helps! Elaine On Wed, Sep 3, 2014 at 12:08 PM, Nick Clemens n...@quecheelibrary.org wrote: To get the '005' data, I think you have to pull from the marcxml in biblioitems: ExtractValue(marcxml,'//controlfield[@tag=005]') I don't think it is accessible through any of the

[Koha] Report help needed - modification date

How do I filter records based on the MARC 005? DATE AND TIME OF LATEST TRANSACTION biblio.timestamp doesn't do it. This is what I have: SELECT biblio.biblionumber,items.barcode,biblio.author,biblio.title,biblio.timestamp,biblio.datecreated FROM items LEFT JOIN biblioitems on

Re: [Koha] Report help

I rewrote the report to make it user friendly to my colleagues who aren't familiar with Roman numerals. . . little tweaks like this I can do. It's the syntax and commands that I don't know how to use properly that's the real problem. SELECT CASE

Re: [Koha] Report help

This is the basic report: SELECT count(biblio.copyrightdate), biblio.copyrightdate,items.itype FROM items LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber) GROUP BY biblio.copyrightdate ORDER BY

Re: [Koha] Report help

Elaine: I think you should use 008 date: SELECT CASE SUBSTR(ExtractValue(m.marcxml,'//controlfield[@tag=008]'),8,2) WHEN '19' THEN 'XX' WHEN '20' THEN 'XXI' WHEN '18' THEN 'XVIII' WHEN '17' THEN 'XVII' ElSE 'OLDER' END AS bibtype, count(DISTINCT biblionumber) AS

Re: [Koha] Report help

Ramiro That's very useful, thanks. I've expanded it a little to accommodate our date range (yes we have some old items) and changed it to say Unknown rather than older, because I suspect these are questionable dates or missing data. Quite a few dates are missing in the 008 field. One of the

Re: [Koha] Report help

Salvete! SELECT CASE SUBSTR(ExtractValue(m.marcxml,'//controlfield[@tag=008]'),8,2)       WHEN '19' THEN 'XX'       WHEN '20' THEN 'XXI'       WHEN '18' THEN 'XVIII'       WHEN '17' THEN 'XVII'       ElSE 'OLDER' END    AS bibtype, count(DISTINCT biblionumber) AS bibs FROM biblioitems m GROUP BY

[Koha] Report help

One of my colleagues was asking if I could give statistics on what we have from the 17th, 18th and first half of the 19th century. Preferably by year. So I guess I'm asking for a count by publication year. Any thoughts on the best way to approach this? -- Elaine Bradtke Data Wrangler VWML

Re: [Koha] Report help needed - filter by itemtype

hi, For item pull down list try this: Replace this: biblioitems.itemtype=‘BK’ with: items.itype=Select Itemtype|itemtypes On Mon, Jun 9, 2014 at 5:49 AM, Robin Sheat ro...@catalyst.net.nz wrote: Elaine Bradtke schreef op vr 06-06-2014 om 21:42 [+0100]: WHERE biblioitems.itemtype=‘BK’

Re: [Koha] Report help needed - filter by itemtype

Elaine Bradtke schreef op vr 06-06-2014 om 21:42 [+0100]: WHERE biblioitems.itemtype=‘BK’ AND datecreated You have smartquotes around the BK. You can't have smartquotes anywhere. -- Robin Sheat Catalyst IT Ltd. ✆ +64 4 803 2204 GPG: 5FA7 4B49 1E4D CAA4 4C38 8505 77F5 B724 F871 3BDF

[Koha] Report help needed - filter by itemtype

I can't seem to get the report to filter on item type. Probably another typo somewhere. it works if I take out this line: biblioitems.itemtype=‘BK’ But I want to filter by books (and eventually other item types). SELECT ExtractValue(b.marcxml, '//datafield[@tag=100]/subfield[@code=a]'),

[Koha] Report help needed

I'll admit, I'm the queen of cut and paste. What I don't know about SQL would fill a book. Using a report I already have on hand: SELECT ExtractValue(b.marcxml, '//datafield[@tag=245]/subfield[@code=a]'), ExtractValue(b.marcxml, '//datafield[@tag=260]/subfield[@code=a]'),ExtractValue(b.marcxml,

Re: [Koha] Report help needed

[mailto:e...@efdss.org] Sent: Tuesday, May 20, 2014 12:58 PM To: koha Subject: [Koha] Report help needed I'll admit, I'm the queen of cut and paste. What I don't know about SQL would fill a book. Using a report I already have on hand: SELECT ExtractValue(b.marcxml, '//datafield[@tag=245

Re: [Koha] Report help needed

Hi Elaine, yes, copy paste :) You paste an extra 'ExtractValue(b.marcxml,' before monthname(datecreated) AS month Try SELECT ExtractValue(b.marcxml, '//datafield[@tag=100]/subfield[@code=a]'), ExtractValue(b.marcxml, '//datafield[@tag=245]/subfield[@code=a]'), ExtractValue(b.marcxml,

Re: [Koha] Report help needed

It could be just the way that my email client is formatting characters, but you may have a problem with the and ' (double and single quote marks.) I read them below as “, ”, and ’ Maybe you used a text editor that allows curlies rather than straights? Best -- Paul At 05:57 PM

Re: [Koha] Report help needed

It looks like the issue is '//datafield[@tag=“022”]/ subfield[@code=“a”]’), ExtractValue(b.marcxml, monthname(datecreated) AS month, year(datecreated) AS year, biblionumber AS The last ExtractValue(b.marcxml, doesn't have a closing ) and you've stopped extracting XML so that is causing one error

[Koha] Report help needed 999$c

I've got something that extracts the biblio numbers from the 999c. Is there any way to ask it to only show the ones that contain multiple 999c? SELECT biblionumber, ExtractValue(marcxml, '//datafield[@tag=999]/subfield[@code=c]') AS comp FROM biblioitems -- Elaine Bradtke Data Wrangler VWML

Re: [Koha] Report help needed 999$c

Elaine Bradtke schreef op ma 05-05-2014 om 22:55 [+0100]: I've got something that extracts the biblio numbers from the 999c. Is there any way to ask it to only show the ones that contain multiple 999c? SELECT biblionumber, ExtractValue(marcxml, '//datafield[@tag=999]/subfield[@code=c]')

Re: [Koha] Report help needed 999$c

Note that it looked like your data also had multiple entries in one 999 $c field, separated with '|'. That's weird, and this won't find that. On the other hand, you can just do WHERE field LIKE %|% to catch those. Yes, that is weird. I'm trying to figure out where it is coming from. In a few

Re: [Koha] Report Help

Manojji, Your one book has one biblio record say biblionumber 1234. For this record in 942c tag (Koha [default] item type) you have entered / selected item type as BTECHBKS. If all 12 items are attached to this biblionumber the report will show 12 copies to BTECHBKS. Changing 952y item type for

Re: [Koha] Report Help

Please try this SQL report. This is sorted by count. SELECT CONCAT('a href=\/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\',biblio.biblionumber,'/a') AS biblionumbers, biblio.author, biblio.title, COUNT(items.itemnumber) AS ItemCount, ccode, itype FROM items LEFT

Re: [Koha] Report Help

Dear Chitralekha, Take this example: I have one book with 12 copies, 8copies with item type BTECHBKS and 4 copies with item type BCABKS. When i generate report it shows 12 copies for itemtype BTECHBKS and none for BCABKS. Is there any way of reporting so that i can get the correct report or i

Re: [Koha] Report Help

Liz's report should do what you want if you change the GROUP BY to say GROUP BY biblionumber, i.itype. (That will list, say, the 14 copies of Book A that are type x, then the 3 copies that are type y. If you'd rather have all the type x's list together, then all the type y's, just change the

Re: [Koha] Report Help

Thank you very much Katelyn, it worked. *Regards!* *Manoj Kumar Misra* M.Phil., M.L.I.Sc., B.L.I.Sc., M.Sc.(Phy), B.Sc. (P.C.M.) Deputy Librarian SRMGPC Lucknow Mob. No.: 09415422579 Alternate E-mail: manojmisrasrm...@gmail.com On Fri, Apr 11, 2014 at 4:48 AM, Katelyn Browne [via Koha]

Re: [Koha] Report Help

Give this a try: SELECT b.title, count(i.itemnumber) AS number of items, i.itype, i.ccode FROM biblio b JOIN items i USING(biblionumber) GROUP BY b.biblionumber If it works you can add it to the report library. ;) Cheers, Liz Rea Catalyst IT On 05/04/14 21:31, manoj382093 wrote: Dear Friends,

Re: [Koha] Report Help

Thanks Liz, it is working but i want list with specific item type and collection code. As the bib record may be attached to more than 1 item type so it should be distinct. *Regards!* *Manoj Kumar Misra* M.Phil., M.L.I.Sc., B.L.I.Sc., M.Sc.(Phy), B.Sc. (P.C.M.) Deputy Librarian SRMGPC Lucknow

[Koha] Report Help

Dear Friends, I am looking for a report which has list of titles(biblio) their number of copies(items) by item type and collection code. -- View this message in context: http://koha.1045719.n5.nabble.com/Report-Help-tp5793161.html Sent from the Koha-general mailing list archive at Nabble.com.

Re: [Koha] Report Help

Hi, Did you have a look in 5 Catalog/Bibliographic Reports here?: http://wiki.koha-community.org/wiki/SQL_Reports_Library Arslan ___ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report Help

Dear Arslan, I could not find it there *Regards!* *Manoj Kumar Misra* M.Phil., M.L.I.Sc., B.L.I.Sc., M.Sc.(Phy), B.Sc. (P.C.M.) Deputy Librarian SRMGPC Lucknow Mob. No.: 09415422579 Alternate E-mail: manojmisrasrm...@gmail.com On Sat, Apr 5, 2014 at 12:39 AM, arslan [via Koha]

[Koha] Report help

I'm looking for a report that shows items that have holds on them where there are no other items available at the branch. Available being checked in and not lost. I was using this report as a starting point. SELECT i.itemnumber, i.reserves, i.issues, i.datelastseen, i.dateaccessioned, i.ccode,

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

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

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

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

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

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] Report help needed for List of Items added by cataloger with link to biblio title

Dear Friends, I made some changes in the report with title Titles added by cataloger from SQL Report Library. My aim is to build a report which list of biblios added by cataloguer. Now this report display time stamp and biblio number with link. I request your help to add bibio.title in this

[Koha] Report Help

Hi all, I have a library that wants to alter this report to allow us to limit on the item's home branch: SELECT concat(b.title, ' ', ExtractValue(m.marcxml, '//datafield[@tag=245]/subfield[@code=b]')) AS title, b.author, count(h.reservedate) AS 'holds' FROM biblio b LEFT JOIN biblioitems m USING

Re: [Koha] Report Help

Thank you! That's a step in the right direction :) I have shared it (in your name) on the Koha wiki for others to benefit from. Nicole On Mon, Apr 22, 2013 at 9:19 PM, Bernardo Gonzalez Kriegel bgkrie...@gmail.com wrote: Nicole, you want for each guarantor it's guarantees, Q: it's ok to

[Koha] Report Help

I'm looking to change this report around. It shows patrons with their guarantor information. I want a report that shows me the guarantees for each guarantor instead. SELECT p.categorycode, p.dateofbirth, p.cardnumber, p.surname, p.firstname, p.dateexpiry, IFNULL(concat(g.surname, ', ',

Re: [Koha] Report Help

Nicole, you want for each guarantor it's guarantees, Q: it's ok to repeat guarantor information on each guarantee tuple? you could use something like this (put any data you want in first select, and change order by if needed) SELECT IFNULL(concat(g.surname, ', ', g.firstname, '

Re: [Koha] Report Help

; Kind regards, Manos Petridis -Original Message- From: koha-boun...@lists.katipo.co.nz [mailto:koha-boun...@lists.katipo.co.nz] On Behalf Of Bernardo Gonzalez Kriegel Sent: Wednesday, April 10, 2013 9:45 PM To: Nicole Engard Cc: Koha Subject: Re: [Koha] Report Help Nicole, what you need

[Koha] Report Help

I'm trying to get a report to show patrons with overdues and if there is a guarantor I want that to show too. But what's happening is the Guarantees are showing as the Guarantors and the Guarantor is showing 2 times cause he has 2 Guarantees - can someone help me with my SQL: SELECT

Re: [Koha] Report Help

Sorry - I was wrong - not patrons with overdues, but expired patrons - that's what I'm looking for. On Wed, Apr 10, 2013 at 1:25 PM, Nicole Engard neng...@gmail.com wrote: I'm trying to get a report to show patrons with overdues and if there is a guarantor I want that to show too. But what's

Re: [Koha] Report Help

Nicole, what you need to match is p(atron).guarantorid with g(uarantor).borrowernumber, not g(uarantor).guarantorid with p(atron).borrowernumber that is what causing you to get an inverted list. For example this query will list all guarantees with corresponding guarantor (if not null), SELECT

Re: [Koha] Report Help

Thank you so much - so simple!! :) On Wed, Apr 10, 2013 at 2:45 PM, Bernardo Gonzalez Kriegel bgkrie...@gmail.com wrote: Nicole, what you need to match is p(atron).guarantorid with g(uarantor).borrowernumber, not g(uarantor).guarantorid with p(atron).borrowernumber that is what causing

Re: [Koha] Report help

Nope :( No results, and we know there are some problem records. I only made the following changes: SELECT CONCAT('a href=\/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblionumber,'\',biblionumber,'/a') AS biblionumber FROM biblioitems, (SELECT

Re: [Koha] Report help

I found out that we're looking at too much in this. I removed a bit and now it all seems to work :) Nicole On Wed, Apr 4, 2012 at 9:44 AM, Nicole Engard neng...@gmail.com wrote: Nope :( No results, and we know there are some problem records. I only made the following changes: SELECT

[Koha] Report help

I think this requires some regex magic :) So, if anyone can help me out that would be great: How do I create a report for all records that have both of the following items? A 942|c field that says Photographs and a leader fields that shows item type as book? I know that the 94$c is mapped to

Re: [Koha] Report help

On 4 April 2012 14:38, Nicole Engard neng...@gmail.com wrote: I think this requires some regex magic :) So, if anyone can help me out that would be great: How do I create a report for all records that have both of the following items? A 942|c field that says Photographs and a leader fields

  1   2   >