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,

Re: [Koha] Report help needed

Removing the comma fixed it! It was built off another report with a lot more fields. I'm a proud graduate of the cut and paste school of report writing. ;-) Elaine VWML On Tue, Jan 5, 2021 at 11:38 AM Lisette Scheer wrote: > Elaine, > There is a comma after AS sub3 that is

Re: [Koha] Report help needed

Elaine, There is a comma after 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

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.

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

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 >

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.

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

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

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

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

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

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:

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

Hi Kerrie, I think adding AND s.branch =Branch|branches will do what you're looking for. So the whole SQL statement would be: SELECT concat(b.surname,', ',b.firstname) AS name, count(s.borrowernumber) AS checkouts FROM statistics s LEFT JOIN borrowers b USING (borrowernumber) WHERE s.datetime

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

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

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

Re: [Koha] Report help needed

Hi Elaine, I'm really not positive this is the issue, but I would double-check to make sure that all of your single and double quotes are standard ASCII quote marks. It looks like several of them are special characters, perhaps automatically converted somewhere along the way in your

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

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

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]

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

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

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

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

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

Re: [Koha] Report help

From here: http://manual.koha-community.org/3.6/en/XSLTiTypes.html leader6 = a (and one of the leader7 values below) leader7 = a leader7 = c leader7 = d leader7 = m leader6 = t On Tue, Apr 3, 2012 at 7:43 PM, Chris Cormack ch...@bigballofwax.co.nz

Re: [Koha] Report help

On 4 April 2012 15:19, Nicole Engard neng...@gmail.com wrote: From here: http://manual.koha-community.org/3.6/en/XSLTiTypes.html    leader6 = a (and one of the leader7 values below)        leader7 = a        leader7 = c        leader7 = d        leader7 = m    leader6 = t Something

Re: [Koha] report help - total amount writeoff

Try something like this: SELECT round(Sum(accountlines.amount),2) AS 'Writeoffs Last 3 Months' FROM accountlines WHERE accounttype = 'W' and date like concat(date_format(LAST_DAY(now() - interval 3 month),'%Y-%m-%')) (haven’t tested this variant, but I run similar reports for 1 month and

Re: [Koha] Report Help

Thanks a bunch! This appears to work. I'll post this to the wiki in your name so that you get credit for the work and so that others can benefit from this. Nicole 2011/8/17 Msgr. Marion J. Makarewicz marmak3...@att.net: SELECT b.title, b.author, b.copyrightdate, i.itemcallnumber, i.barcode,

Re: [Koha] Report Help - Extracting XML

Message- From: Nicole Engard [mailto:neng...@gmail.com] Sent: Thursday, July 28, 2011 5:02 PM To: Grobani, Daniel Cc: koha@lists.katipo.co.nz Subject: Re: [Koha] Report Help - Extracting XML Okay - this: select count(statistics.datetime) as circs, CONCAT('a href=\/cgi-bin/koha/catalogue

Re: [Koha] Report Help - Extracting XML

Daniel, That part I knew :) it was getting it in to the query (the joining) I was having trouble with :) Nicole On Wed, Jul 27, 2011 at 8:00 PM, Daniel Grobani dgrob...@samuelmerritt.edu wrote: Nicole, This should give you the 520a: extractValue(biblioitems.marcxml,

Re: [Koha] Report Help - Extracting XML

ON (biblioitems.biblioitemnumber = items.biblioitemnumber)? Daniel -Original Message- From: Nicole Engard [mailto:neng...@gmail.com] Sent: Thursday, July 28, 2011 4:36 AM To: Grobani, Daniel Cc: koha@lists.katipo.co.nz Subject: Re: [Koha] Report Help - Extracting XML Daniel, That part I knew

Re: [Koha] Report Help - Extracting XML

Try this (I also did some light cleanup): SELECT COUNT(statistics.datetime) AS circs, CONCAT(' \/cgi- ',biblio.title,' ') AS Title, CONCAT(' \http://mycatalogv/cgi-

Re: [Koha] Report Help - Extracting XML

Okay - this: select count(statistics.datetime) as circs, CONCAT('a href=\/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\',biblio.title,'/a') AS Title, CONCAT('a

Re: [Koha] Report Help - Extracting XML

stick. Daniel -Original Message- From: Nicole Engard [mailto:neng...@gmail.com] Sent: Thursday, July 28, 2011 5:02 PM To: Grobani, Daniel Cc: koha@lists.katipo.co.nz Subject: Re: [Koha] Report Help - Extracting XML Okay - this: select count(statistics.datetime) as circs, CONCAT('a href

Re: [Koha] Report Help - Extracting XML

Nicole, This should give you the 520a: extractValue(biblioitems.marcxml, collection/record/datafield[@tag='520'][1]/subfield[@code='a']) Of course, you'll have to do a join on biblioitems. - Cheers, Daniel Grobani Library Technology Specialist John A. Graziano Memorial Library Samuel

Re: [Koha] Report Help

Hi Nicole, On Fri, Jul 1, 2011 at 9:33 AM, Nicole Engard neng...@gmail.com wrote: Hi all, I wrote this report to get all subject headings in a Koha system that start with key phrases: SELECT lcsh FROM (SELECT biblionumber, ExtractValue(marcxml,'//datafield[@tag=610]/subfield[@code=a]')

Re: [Koha] Report Help

Unfortunately, that's just the way that ExtractValue works. From the manual: If multiple matches are found, the content of the first child text node of each matching element is returned (in the order matched) as a single, space-delimited string. Since MARC doesn't give us any means of

Re: [Koha] Report Help

On Fri, Jul 1, 2011 at 9:52 AM, Ian Walls ian.wa...@bywatersolutions.comwrote: Unfortunately, that's just the way that ExtractValue works. From the manual: If multiple matches are found, the content of the first child text node of each matching element is returned (in the order matched) as

Re: [Koha] Report Help

I wrote this report to get all subject headings in a Koha system that start with key phrases: You can get your headings with a Perl script. You can also export all your biblio records in ISO2709 and then combine various standard Linux commands to extract and select your headings. For

Re: [Koha] Report Help

On Fri, Jul 1, 2011 at 10:19 AM, Nicole Engard neng...@gmail.com wrote: On Fri, Jul 1, 2011 at 10:09 AM, Chris Nighswonger cnighswon...@foundations.edu wrote: The sad thing is if it was comma or otherwise delimited we could do some splitting foo. But spaces will inevitably appear in the

Re: [Koha] Report help

Is this run at the MySQL level or in the reports module? * * * * Beverly Church LibLime Project Manager phone: 1-888-564-2457 ext. 717 1-301-654-8088 ext. 292 email: bchu...@liblime.com skype: beverlychurch On Wed, May 25, 2011 at 4:23 PM, Nicole Engard neng...@gmail.com wrote:

Re: [Koha] Report help

MySQL 5.1 or greater is required. The queries should work either from the reports module, or directly on the MySQL command line. -Ian 2011/5/25 Church, Beverly bchu...@liblime.com Is this run at the MySQL level or in the reports module? * * * * Beverly Church LibLime Project Manager

Re: [Koha] Report help

Bev, I wrote that gnarly marcxml-parsing query you offered below because at the time, LLEK was running on a pre-5.1 version of MySQL and I couldn't use the simpler ExtractValue(). I just ran cait's query that Nicole offered from the SQL Reports Library against our LLEK report server and it