[Koha] Report help

[Ian Walls]

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 <bchurch at liblime.com>

> 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: bchurch at liblime.com
> skype: beverlychurch
>
>
> On Wed, May 25, 2011 at 4:23 PM, Nicole Engard <nengard at gmail.com> wrote:
>
>> I did try it and it does work. You need a more recent version of MySQL
>> for it to work ... 5. something ... It's in Debian Squueze.
>>
>> Nicole
>>
>> On Wed, May 25, 2011 at 3:21 PM, Church, Beverly <bchurch at liblime.com>
>> wrote:
>> > Have you tried it? I can't get it to work.
>> >
>> > Bev
>> >
>> > * * * *
>> > Beverly Church
>> > LibLime Project Manager
>> >
>> > phone: 1-888-564-2457 ext. 717
>> >             1-301-654-8088 ext. 292
>> > email: bchurch at liblime.com
>> > skype: beverlychurch
>> >
>> >
>> > On Wed, May 25, 2011 at 3:08 PM, Nicole Engard <nengard at gmail.com>
>> wrote:
>> >>
>> >> For those who are curious, cait found my answer and it's on the wiki:
>> >>
>> >>
>> http://wiki.koha-community.org/wiki/SQL_Reports_Library#Title.2FSubtitle_List
>> >>
>> >> Nicole
>> >>
>> >> On Wed, May 25, 2011 at 1:49 PM, Nicole Engard <nengard at gmail.com>
>> wrote:
>> >> > Bev,
>> >> >
>> >> > I was thinking more along the lines of this type of thing:
>> >> > http://wiki.koha-community.org/wiki/SQL_Reports_Library#Query_MARC
>> >> > which I can do with the version of MySQL that I have installed. Just
>> >> > wants sure how to fit it in to the report.
>> >> >
>> >> > Nicole
>> >> >
>> >> > On Wed, May 25, 2011 at 11:59 AM, Church, Beverly <
>> bchurch at liblime.com>
>> >> > wrote:
>> >> >> Hi Nicole,
>> >> >>
>> >> >> This query should work:
>> >> >>
>> >> >> select title, IF( LOCATE('<datafield tag="245"',
>> biblioitems.marcxml) =
>> >> >> 0 OR
>> >> >> LOCATE('<subfield code="b">', biblioitems.marcxml,
>> LOCATE('<datafield
>> >> >> tag="245"', biblioitems.marcxml)) = 0 OR LOCATE('<subfield
>> code="b">',
>> >> >> biblioitems.marcxml, LOCATE('<datafield tag="245"',
>> >> >> biblioitems.marcxml)) >
>> >> >> LOCATE('</datafield>', biblioitems.marcxml, LOCATE('<datafield
>> >> >> tag="245"',
>> >> >> biblioitems.marcxml)), '', SUBSTRING( biblioitems.marcxml,
>> >> >> LOCATE('<subfield
>> >> >> code="b">', biblioitems.marcxml, LOCATE('<datafield tag="245"',
>> >> >> biblioitems.marcxml)) + 19, LOCATE('</subfield>',
>> biblioitems.marcxml,
>> >> >> LOCATE('<subfield code="b">', biblioitems.marcxml,
>> LOCATE('<datafield
>> >> >> tag="245"', biblioitems.marcxml)) + 19) - (LOCATE('<subfield
>> >> >> code="b">',
>> >> >> biblioitems.marcxml, LOCATE('<datafield tag="245"',
>> >> >> biblioitems.marcxml)) +
>> >> >> 19))) AS 'Sub-title'  FROM biblioitems, biblio where
>> >> >> biblioitems.biblionumber = biblio.biblionumber and
>> LOCATE('<datafield
>> >> >> tag="245"', biblioitems.marcxml) != 0 ORDER BY title asc
>> >> >>
>> >> >>
>> >> >>
>> >> >> * * * *
>> >> >> Beverly Church
>> >> >> LibLime Project Manager
>> >> >>
>> >> >> phone: 1-888-564-2457 ext. 717
>> >> >>             1-301-654-8088 ext. 292
>> >> >> email: bchurch at liblime.com
>> >> >> skype: beverlychurch
>> >> >>
>> >> >>
>> >> >> On Wed, May 25, 2011 at 12:10 PM, Nicole Engard <nengard at gmail.com>
>> >> >> wrote:
>> >> >>>
>> >> >>> I'm looking for some help with using the marc xml reports trick :)
>> >> >>>
>> >> >>> I need a report that shows the title and the subtitle, how would I
>> do
>> >> >>> that?
>> >> >>>
>> >> >>> Thanks in advance!
>> >> >>> Nicole
>> >> >>> _______________________________________________
>> >> >>> Koha mailing list  http://koha-community.org
>> >> >>> Koha at lists.katipo.co.nz
>> >> >>> http://lists.katipo.co.nz/mailman/listinfo/koha
>> >> >>
>> >> >>
>> >> >
>> >
>> >
>>
>
>
> _______________________________________________
> Koha mailing list  http://koha-community.org
> Koha at lists.katipo.co.nz
> http://lists.katipo.co.nz/mailman/listinfo/koha
>
>


-- 
Ian Walls
Lead Development Specialist
ByWater Solutions
ALA Booth 732
Phone # (888) 900-8944
http://bywatersolutions.com
ian.walls at bywatersolutions.com
Twitter: @sekjal
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.katipo.co.nz/pipermail/koha/attachments/20110525/385d160f/attachment.htm