[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, concat(ExtractValue(`marcxml`,'//datafield[@tag

[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

r 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=&q

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

[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, ExtractValue(metadata,'//data

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 reports

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 Bradt

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 s

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 exp

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 ExtractVal

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 b.biblionumber=bm.b

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

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 sensitive

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 t

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

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

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

[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 borrower_attributes.attribut

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, '//datafield[@tag="100"]/subf

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 ExtractValu

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

[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, pleas

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 repor

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 substitute "marcxml" with > "bi

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 f

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

[Koha] Report help: count of certain subfield values

Hi all, I've been pondering how to do this report, but haven't found a good solution: I'd like to have a list of all subfield values for certain subfield from biblioitems, and have counts of the field contents. For example: SELECT count(*), ExtractValue(marcxml, '//datafield[@tag=<>]/su

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 "%KEYWO

[Koha] Report help

This must be simple to fix but I'm not fluent enough in Koha-speak to crack the problem. Would anyone be able to help amend this SQL report, so that the results show the title, author and call number for each entry, instead of a hyperlinked biblionumber? All suggestions gratefully received! Li

Re: [Koha] Report help needed

At 09:00 AM 9/28/2015 -0400, Barton Chittenden wrote: Elaine, This query should do what you're looking for: select biblionumber, from biblioitems where ExtractValue( marcxml, '//datafield[@tag=942]/subfield[@code="c"]' ) = ''; Depending on the number of bibs, this could take quite a while to r

Re: [Koha] Report help needed

Elaine, This query should do what you're looking for: select biblionumber, from biblioitems where ExtractValue( marcxml, '//datafield[@tag=942]/subfield[@code="c"]' ) = ''; Depending on the number of bibs, this could take quite a while to run -- I would try it after hours. Alternatively, if you

[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

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 (marcxml,'//datafield[@

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 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 > ',IFNULL(ExtractValue(m.marcxml,'//datafiel

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"][ position()=2]/sub

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 wrote: > You can also use the position marker in the xpath to pick an arbitrary > number of s

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"]'),'

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

[Koha] Report help

Hi all, I'm working on this report and I want there to be a BR between each distinct 650a - but for some reason it's not working. I worked on this in channel for a while and you can see the log here: http://irc.koha-community.org/koha/2015-04-14#i_1663170 Here's the report: Select b.biblionumbe

[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, '//datafield[@tag="264"]/subfield[@code

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) wrote: > Elaine, try the below

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

[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

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 it to a

Re: [Koha] Report help

Kerrie, You need to add the parameter of b.branchcode=<> 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, count(s.borrowernumber) AS checkouts FROM s

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

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

Re: [Koha] Report help- reading history

Hii, Visit koha report library for any sql report: http://wiki.koha-community.org/wiki/SQL_Reports_Library On Mon, Sep 22, 2014 at 12:38 PM, clint.deckard < clint.deck...@frontiers.co.nz> wrote: > A school I deal with would like a report that lists the reading history of > the top 10 borrowers

[Koha] Report help- reading history

A school I deal with would like a report that lists the reading history of the top 10 borrowers so that they can award a prize to each. As I am not a sql expert I would appreciate some assistance. Much appreciated, Clint. -- Clint Deckard Takapau Central Hawkes Bay, New Zealand.

Re: [Koha] Report help needed - modification date

Thanks, that helps! Elaine On Wed, Sep 3, 2014 at 12:08 PM, Nick Clemens 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 table directly > > >

[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 (items.biblioitemnu

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 SUBSTR(ExtractValue(m.marcxml,'//controlfield[@tag=

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

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 diff

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

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 biblio.c

Re: [Koha] Report help

Elaine Bradtke schreef op di 22-07-2014 om 17:35 [+0100]: > 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. > When my Elasticsearch work is finished, this'll be just a matter of finding an

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

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

hi, For item pull down list try this: Replace this: biblioitems.itemtype=‘BK’ with: items.itype=<> On Mon, Jun 9, 2014 at 5:49 AM, Robin Sheat wrote: > Elaine Bradtke schreef op vr 06-06-2014 om 21:42 [+0100]: > > WHERE biblioitems.itemtype=‘BK’ AND datecreated > > You have smartquotes aro

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"]'), Ex

Re: [Koha] Report help needed

Thanks, I'll try the various suggestions. I actually did the copy and paste in Koha. . . no text editor to blame. But there's the weirdness that goes on between a Mac and a PC (I use both on the same machine with VMware), and might possibly have copied from one to paste in the other. . . Sometim

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 u

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

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

-Original Message- From: Elaine Bradtke [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: SEL

[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.ma

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 fe

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

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

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('',biblio.biblionumber,'') AS biblionumbers, biblio.author, biblio.title, COUNT(items.itemnumber) AS ItemCount, ccode, itype FROM items LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber) GROUP BY items.biblionumb

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] < ml-nod

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 orde

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 woul

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

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 Friend

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] < ml-node+s10457

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

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

[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, b.

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

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 wrote: > Nick, > > I figured it out - we needed to limit to LOC authorized values: >

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

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

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

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

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 labele

[Koha] Report Help - With Rollup

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

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

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

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, ' (',g.cardnumber,

  1   2   >