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.
Hi, Did you have a look in "5 Catalog/Bibliographic Reports" here?: http://wiki.koha-community.org/wiki/SQL_Reports_Library Arslan
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: manojmisrasrmgpc@gmail.com On Sat, Apr 5, 2014 at 12:39 AM, arslan [via Koha] < ml-node+s1045719n5793162h93@n5.nabble.com> wrote:
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 [hidden email] <http://user/SendEmail.jtp?type=node&node=5793162&i=0> http://lists.katipo.co.nz/mailman/listinfo/koha
------------------------------ If you reply to this email, your message will be added to the discussion below: http://koha.1045719.n5.nabble.com/Report-Help-tp5793161p5793162.html To unsubscribe from Report Help, click here<http://koha.1045719.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=5793161&code=bWFub2prdW1hcm1pc3JhQGdtYWlsLmNvbXw1NzkzMTYxfC0xMDkzNzg1MDg0> . NAML<http://koha.1045719.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>
-- View this message in context: http://koha.1045719.n5.nabble.com/Report-Help-tp5793161p5793169.html Sent from the Koha-general mailing list archive at Nabble.com.
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, 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 mailing list http://koha-community.org Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
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. No.: 09415422579 Alternate E-mail: manojmisrasrmgpc@gmail.com On Sun, Apr 6, 2014 at 5:09 PM, Liz Rea-3 [via Koha] < ml-node+s1045719n5793227h8@n5.nabble.com> wrote:
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, 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 mailing list http://koha-community.org [hidden email] <http://user/SendEmail.jtp?type=node&node=5793227&i=0> http://lists.katipo.co.nz/mailman/listinfo/koha
_______________________________________________ Koha mailing list http://koha-community.org [hidden email] <http://user/SendEmail.jtp?type=node&node=5793227&i=1> http://lists.katipo.co.nz/mailman/listinfo/koha
------------------------------ If you reply to this email, your message will be added to the discussion below: http://koha.1045719.n5.nabble.com/Report-Help-tp5793161p5793227.html To unsubscribe from Report Help, click here<http://koha.1045719.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=5793161&code=bWFub2prdW1hcm1pc3JhQGdtYWlsLmNvbXw1NzkzMTYxfC0xMDkzNzg1MDg0> . NAML<http://koha.1045719.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>
-- View this message in context: http://koha.1045719.n5.nabble.com/Report-Help-tp5793161p5793244.html Sent from the Koha-general mailing list archive at Nabble.com.
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 JOIN biblio ON (items.biblionumber=biblio.biblionumber) GROUP BY items.biblionumber HAVING (COUNT(*)>=1) ORDER BY ItemCount DESC On Sat, Apr 5, 2014 at 2:01 PM, manoj382093 [via Koha] < ml-node+s1045719n5793161h33@n5.nabble.com> wrote:
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.
------------------------------ If you reply to this email, your message will be added to the discussion below: http://koha.1045719.n5.nabble.com/Report-Help-tp5793161.html To unsubscribe from Koha-general, click here<http://koha.1045719.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=3047918&code=Y2hpdHJhbGVraGFjaGl0YWxlQGdtYWlsLmNvbXwzMDQ3OTE4fDE5NDY2MzM0MjE=> . NAML<http://koha.1045719.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>
-- View this message in context: http://koha.1045719.n5.nabble.com/Report-Help-tp5793161p5793620.html Sent from the Koha-general mailing list archive at Nabble.com.
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 would have to remove 4 copies and save it as different bib record. *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: manojmisrasrmgpc@gmail.com On Thu, Apr 10, 2014 at 2:06 AM, Chitralekha [via Koha] < ml-node+s1045719n5793620h91@n5.nabble.com> wrote:
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 JOIN biblio ON (items.biblionumber=biblio.biblionumber) GROUP BY items.biblionumber HAVING (COUNT(*)>=1) ORDER BY ItemCount DESC
On Sat, Apr 5, 2014 at 2:01 PM, manoj382093 [via Koha] <[hidden email]<http://user/SendEmail.jtp?type=node&node=5793620&i=0>
wrote:
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.
------------------------------ If you reply to this email, your message will be added to the discussion below: http://koha.1045719.n5.nabble.com/Report-Help-tp5793161.html To unsubscribe from Koha-general, click here. NAML<http://koha.1045719.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>
------------------------------ If you reply to this email, your message will be added to the discussion below: http://koha.1045719.n5.nabble.com/Report-Help-tp5793161p5793620.html To unsubscribe from Report Help, click here<http://koha.1045719.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=5793161&code=bWFub2prdW1hcm1pc3JhQGdtYWlsLmNvbXw1NzkzMTYxfC0xMDkzNzg1MDg0> . NAML<http://koha.1045719.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>
-- View this message in context: http://koha.1045719.n5.nabble.com/Report-Help-tp5793161p5793704.html Sent from the Koha-general mailing list archive at Nabble.com.
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 order: GROUP BY i.itype, biblionumber). --Katelyn. Katelyn Browne Middle/High School Librarian Capital City Public Charter School 100 Peabody Street NW Washington, DC 20011 (202) 387-0309 x1745 kbrowne@ccpcs.org http://www.ccpcs.org/library/ On Fri, Apr 11, 2014 at 3:47 AM, manoj382093 <manojkumarmisra@gmail.com>wrote:
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 would have to remove 4 copies and save it as different bib record.
*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: manojmisrasrmgpc@gmail.com
On Thu, Apr 10, 2014 at 2:06 AM, Chitralekha [via Koha] < ml-node+s1045719n5793620h91@n5.nabble.com> wrote:
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 JOIN biblio ON (items.biblionumber=biblio.biblionumber) GROUP BY items.biblionumber HAVING (COUNT(*)>=1) ORDER BY ItemCount DESC
On Sat, Apr 5, 2014 at 2:01 PM, manoj382093 [via Koha] <[hidden email]< http://user/SendEmail.jtp?type=node&node=5793620&i=0>
wrote:
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.
------------------------------ If you reply to this email, your message will be added to the discussion below: http://koha.1045719.n5.nabble.com/Report-Help-tp5793161.html To unsubscribe from Koha-general, click here. NAML< http://koha.1045719.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml
------------------------------ If you reply to this email, your message will be added to the discussion below: http://koha.1045719.n5.nabble.com/Report-Help-tp5793161p5793620.html To unsubscribe from Report Help, click here< http://koha.1045719.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=5793161&code=bWFub2prdW1hcm1pc3JhQGdtYWlsLmNvbXw1NzkzMTYxfC0xMDkzNzg1MDg0
-- View this message in context: http://koha.1045719.n5.nabble.com/Report-Help-tp5793161p5793704.html Sent from the Koha-general mailing list archive at Nabble.com. _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
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: manojmisrasrmgpc@gmail.com On Fri, Apr 11, 2014 at 4:48 AM, Katelyn Browne [via Koha] < ml-node+s1045719n5793778h89@n5.nabble.com> wrote:
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 order: GROUP BY i.itype, biblionumber).
--Katelyn.
Katelyn Browne Middle/High School Librarian Capital City Public Charter School 100 Peabody Street NW Washington, DC 20011 (202) 387-0309 x1745 [hidden email] <http://user/SendEmail.jtp?type=node&node=5793778&i=0> http://www.ccpcs.org/library/
On Fri, Apr 11, 2014 at 3:47 AM, manoj382093 <[hidden email]<http://user/SendEmail.jtp?type=node&node=5793778&i=1>>wrote:
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 would have to remove 4 copies and save it as different bib record.
*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: [hidden email]<http://user/SendEmail.jtp?type=node&node=5793778&i=2>
On Thu, Apr 10, 2014 at 2:06 AM, Chitralekha [via Koha] < [hidden email] <http://user/SendEmail.jtp?type=node&node=5793778&i=3>> wrote:
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 JOIN biblio ON (items.biblionumber=biblio.biblionumber) GROUP BY items.biblionumber HAVING (COUNT(*)>=1) ORDER BY ItemCount DESC
On Sat, Apr 5, 2014 at 2:01 PM, manoj382093 [via Koha] <[hidden email]< http://user/SendEmail.jtp?type=node&node=5793620&i=0>
wrote:
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.
------------------------------ If you reply to this email, your message will be added to the discussion below: http://koha.1045719.n5.nabble.com/Report-Help-tp5793161.html To unsubscribe from Koha-general, click here. NAML<
------------------------------ If you reply to this email, your message will be added to the
discussion
below: http://koha.1045719.n5.nabble.com/Report-Help-tp5793161p5793620.html To unsubscribe from Report Help, click here<
. NAML<
-- View this message in context: http://koha.1045719.n5.nabble.com/Report-Help-tp5793161p5793704.html Sent from the Koha-general mailing list archive at Nabble.com. _______________________________________________ Koha mailing list http://koha-community.org [hidden email] <http://user/SendEmail.jtp?type=node&node=5793778&i=4> http://lists.katipo.co.nz/mailman/listinfo/koha
_______________________________________________ Koha mailing list http://koha-community.org [hidden email] <http://user/SendEmail.jtp?type=node&node=5793778&i=5> http://lists.katipo.co.nz/mailman/listinfo/koha
------------------------------ If you reply to this email, your message will be added to the discussion below: http://koha.1045719.n5.nabble.com/Report-Help-tp5793161p5793778.html To unsubscribe from Report Help, click here<http://koha.1045719.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=5793161&code=bWFub2prdW1hcm1pc3JhQGdtYWlsLmNvbXw1NzkzMTYxfC0xMDkzNzg1MDg0> . NAML<http://koha.1045719.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>
-- View this message in context: http://koha.1045719.n5.nabble.com/Report-Help-tp5793161p5793912.html Sent from the Koha-general mailing list archive at Nabble.com.
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 4 attached items to BCABKS is not getting reflected in this report. I am sorry even I am not sure how to make entry of one biblio-record having different itemtypes. I will explore and let you know as soon as I get solution. Thanks and regards. Chitralekha Mahesh Chitale Librarian, Lokmanya Tilak College of Engineering, Navi Mumbai. On Fri, Apr 11, 2014 at 1:17 PM, manoj382093 [via Koha] < ml-node+s1045719n5793704h66@n5.nabble.com> wrote:
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 would have to remove 4 copies and save it as different bib record.
*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: [hidden email]<http://user/SendEmail.jtp?type=node&node=5793704&i=0>
-- View this message in context: http://koha.1045719.n5.nabble.com/Report-Help-tp5793161p5794300.html Sent from the Koha-general mailing list archive at Nabble.com.
participants (5)
-
Arslan Farooq -
Chitralekha -
Katelyn Browne -
Liz Rea -
manoj382093