Re: [Koha] Report help needed

[Alvaro Cornejo]

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="100"]/*'),
--), main_heading FROM `auth_header`WHERE datecreated SetASpecificDateHere

Regards

Alvaro
||
 Stay safe / Cuídate/  Reste sécurisé
*7* Switch off as you go / Apaga lo que no usas /  Débranchez au fur et à
mesure.
 *q *Recycle always / Recicla siempre / Recyclez toujours
 P Print only if absolutely necessary / Imprime solo si es necesario /
Imprimez seulement si nécessaire


Le mar. 10 mai 2022 à 12:43, Elaine Bradtke  a écrit :

> 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 line 1) isn't specific enough for my limited abilities.  I'd be
> grateful for your help.
>
> Here's the original:
> SELECT authid, datecreated, authtypecode,
> concat(ExtractValue(`marcxml`,'//datafield[@tag="100"]/*'), --
> PERSO_NAMEExtractValue(`marcxml`,'//datafield[@tag="110"]/*'), --
> CORPO_NAMEExtractValue(`marcxml`,'//datafield[@tag="111"]/*'), --
> MEETI_NAMEExtractValue(`marcxml`,'//datafield[@tag="130"]/*'), --
> UNIF_TITLEExtractValue(`marcxml`,'//datafield[@tag="148"]/*'), --
> CHRON_TERMExtractValue(`marcxml`,'//datafield[@tag="150"]/*'), --
> TOPIC_TERMExtractValue(`marcxml`,'//datafield[@tag="151"]/*'), --
> GEOGR_NAMEExtractValue(`marcxml`,'//datafield[@tag="155"]/*')  --
> GENRE/FORM) main_headingFROM `auth_header`WHERE datecreated BETWEEN <<(from
> -mm-dd)>> AND <<(to -mm-dd)>>ORDER BY datecreated, authtypecode,
> main_heading
>
> Elaine Bradtke
> VWML 
> English Folk Dance and Song Society 
> Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY
> Tel+44 (0) 20 7485 2206 (This number is for the English Folk Dance and
> Song Society in London, England. If you wish to phone me personally, send
> an e-mail first. I work off site)
> --
> Registered Company No. 297142
> Charity Registered in England and Wales No. 305999
> ___
>
> Koha mailing list  http://koha-community.org
> Koha@lists.katipo.co.nz
> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>
___

Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help needed

[Elaine Bradtke]

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 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 
> 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,'\">',biblionumber,'')
> AS bibnumber
> FROM
> (SELECT biblionumber,
> ExtractValue(metadata,'//datafield[@tag="300"]/subfield[@code>="a"]') AS
> suba,
> ExtractValue(metadata,'//datafield[@tag="300"]/subfield[@code>="b"]') AS
> subb,
> ExtractValue(metadata,'//datafield[@tag="300"]/subfield[@code>="3"]') AS
> sub3, FROM biblio_metadata) AS subfields WHERE suba = ""
> AND subb = ""
> AND sub3 = ""
>
> Elaine Bradtke
> VWML 
> Our income is severely diminished in these exceptionally uncertain times.
> Please help us to survive beyond this national crisis. Donate now <
> https://www.efdss.org/support-us>.
> English Folk Dance and Song Society  Cecil Sharp
> House, 2 Regent's Park Road, London NW1 7AY
> Tel+44 (0) 20 7485 2206 (This number is for the English Folk Dance and
> Song Society in London, England. If you wish to phone me personally, send
> an e-mail first. I work off site)
> --
> Registered Company No. 297142
> Charity Registered in England and Wales No. 305999
> ___
>
> Koha mailing list  http://koha-community.org Koha@lists.katipo.co.nz
> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>
>
>
___

Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help needed

[Lisette Scheer]

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 
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"]/subfield[@code>="a"]') AS suba,
ExtractValue(metadata,'//datafield[@tag="300"]/subfield[@code>="b"]') AS subb,
ExtractValue(metadata,'//datafield[@tag="300"]/subfield[@code>="3"]') AS sub3, 
FROM biblio_metadata) AS subfields WHERE suba = ""
AND subb = ""
AND sub3 = ""

Elaine Bradtke
VWML 
Our income is severely diminished in these exceptionally uncertain times.
Please help us to survive beyond this national crisis. Donate now 
.
English Folk Dance and Song Society  Cecil Sharp House, 
2 Regent's Park Road, London NW1 7AY
Tel+44 (0) 20 7485 2206 (This number is for the English Folk Dance and
Song Society in London, England. If you wish to phone me personally, send an 
e-mail first. I work off site)
--
Registered Company No. 297142
Charity Registered in England and Wales No. 305999 
___

Koha mailing list  http://koha-community.org Koha@lists.katipo.co.nz
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha


___

Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help needed

[Katrin Fischer]

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. Thanks!

SELECT CONCAT('',biblionumber,'')
AS bibnumber
FROM
(SELECT biblionumber,
ExtractValue(metadata,'//datafield[@tag="300"]/subfield[@code>="a"]') AS
suba,
ExtractValue(metadata,'//datafield[@tag="300"]/subfield[@code>="b"]') AS
subb,
ExtractValue(metadata,'//datafield[@tag="300"]/subfield[@code>="3"]') AS
sub3,
FROM biblio_metadata) AS subfields
WHERE suba = ""
AND subb = ""
AND sub3 = ""

Elaine Bradtke
VWML 
Our income is severely diminished in these exceptionally uncertain times.
Please help us to survive beyond this national crisis. Donate now
.
English Folk Dance and Song Society 
Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY
Tel+44 (0) 20 7485 2206 (This number is for the English Folk Dance and
Song Society in London, England. If you wish to phone me personally, send
an e-mail first. I work off site)
--
Registered Company No. 297142
Charity Registered in England and Wales No. 305999
___

Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha

___

Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help needed

[Michael Sutherland]

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
library  should
be and look to be updated for the new versions to help new and current
users.  I remember trying a number of reports in the library that did not
work for me.

Best,
M.
_
*Michael J. Sutherland*
University Libraries
Virginia Tech
sudrl...@vt.edu | 540.231.9669 <+15402319669>



On Wed, May 27, 2020 at 7:43 PM Mark Alexander  wrote:

> 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 XML header has a field called xsi:schemaLocation that contains
> a URL that might change someday.  I could very wrong about this,
> though; perhaps the format really is stable.
>
> This does the same job and seems less obscure (to me, anyway):
>
>   substring(ExtractValue(metadata, '//leader'), 7, 2)
>
> But perhaps it's not as fast?  I'm not a MySQL expert by any means.
> ___
>
> Koha mailing list  http://koha-community.org
> Koha@lists.katipo.co.nz
> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>
___

Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help needed

[Mark Alexander]

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 XML header has a field called xsi:schemaLocation that contains
a URL that might change someday.  I could very wrong about this,
though; perhaps the format really is stable.

This does the same job and seems less obscure (to me, anyway):

  substring(ExtractValue(metadata, '//leader'), 7, 2)

But perhaps it's not as fast?  I'm not a MySQL expert by any means.
___

Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help needed

[Michael Sutherland]

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 Bradtke  wrote:

> 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 set correctly for the format (still trying to verify
> that's what's going on).  I wanted to find any records that didn't have the
> appropriate Leader 06 for a given format as specified in the 942.
> Elaine Bradtke
> VWML
> English Folk Dance and Song Society | http://www.efdss.org
> Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY
> Tel+44 (0) 20 7485 2206 (This number is for the English Folk Dance and
> Song Society in London, England. If you wish to phone me personally, send
> an e-mail first. I work off site)
> --
> Registered Company No. 297142
> Charity Registered in England and Wales No. 305999
>
>
> On Wed, May 27, 2020 at 2:00 PM Michael Sutherland 
> wrote:
>
> > 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 expression - SUBSTRING(ExtractValue(metadata,'//leader'),7,1) AS
> > "Position06"
> >
> > you are wanting to extract a piece (substring) from the entire leader
> > string - metadata,'//leader' - the 7 is the value that specifies the
> > initial position from which the characters can be extracted. The first
> > position of the expression starts with 1. But, in MARC the first position
> > is 0 (zero). The next value is a positive integer value that specifies
> the
> > ending limit and determines how many characters are going to be
> > extracted from the given expression in this case '1', which will give you
> > the single letter code in the leader 06, or the seventh position, however
> > you choose to look at it.
> >
> > So, in collaboration with ByWater Solutions, I have co-written the
> > following report, which I use to report collection statistics by itemtype
> > to ACRL and ARL on our collection sans suppressed records from the OPAC.
> It
> > takes the combination of the leader 06 and 07 to determine itemtype and
> > count them.
> >
> > SELECT
> > CASE SUBSTR(metadata,282,2)
> >   WHEN ' m' THEN 'Book'
> >   WHEN '2m' THEN 'Book'
> >   WHEN 'am' THEN 'Book'
> >   WHEN 'aa' THEN 'Book'
> >   WHEN 'ac' THEN 'Book'
> >   WHEN 'ad' THEN 'Book'
> >   WHEN 'ai' THEN 'Book'
> >   WHEN 'as' THEN 'Journal'
> >   WHEN 'cc' THEN 'Music Score'
> >   WHEN 'cm' THEN 'Music Score'
> >   WHEN 'dm' THEN 'Music Score'
> >   WHEN 'ei' THEN 'Map'
> >   WHEN 'em' THEN 'Map'
> >   WHEN 'es' THEN 'Map'
> >   WHEN 'ga' THEN 'Video recording and motion pictures'
> >   WHEN 'gs' THEN 'Video recording and motion pictures'
> >   WHEN 'gc' THEN 'Video recording and motion pictures'
> >   WHEN 'gi' THEN 'Video recording and motion pictures'
> >   WHEN 'gm' THEN 'Video recording and motion pictures'
> >   WHEN 'ic' THEN 'Non-music sound recording'
> >   WHEN 'im' THEN 'Non-music sound recording'
> >   WHEN 'is' THEN 'Non-music sound recording'
> >   WHEN 'jm' THEN 'Music sound recording'
> >   WHEN 'jc' THEN 'Music sound recording'
> >   WHEN 'ji' THEN 'Music sound recording'
> >   WHEN 'kc' THEN '2D image'
> >   WHEN 'km' THEN '2D image'
> >   WHEN 'ma' THEN 'Computer file'
> >   WHEN 'mi' THEN 'Computer file'
> >   WHEN 'mm' THEN 'Computer file'
> >   WHEN 'ms' THEN 'Computer file'
> >   WHEN 'oc' THEN 'Kit'
> >   WHEN 'om' THEN 'Kit'
> >   WHEN 'pc' THEN 'Mixed material'
> >   WHEN 'pm' THEN 'Mixed material'
> >   WHEN 'rc' THEN '3D Object'
> >   WHEN 'rm' THEN '3D Object'
> >   WHEN 'tc' THEN 'Manuscript'
> >   WHEN 'tm' THEN 'Manuscript'
> >   ELSE 'unknown' END AS Type,
> > COUNT(DISTINCT biblionumber) AS Titles,
> > COUNT(itemnumber) AS Volumes
> > FROM biblio_metadata
> > LEFT JOIN items USING (biblionumber)
> > WHERE
> >  ExtractValue(metadata,'//datafield[@tag="942"]/subfield[@code="n"]')
> > != 1
> > GROUP BY Type
> > WITH ROLLUP
> >
> > which generates the following
> >
> > TypeTitlesVolumes
> > 2D image 65 92
> > 3D Object 21 21
> > Book 1193934 1329489
> > Computer file 630 1555
> > Journal 38568 542424
> > Kit 41 66
> > Manuscript 5461 14120
> > Map 4754 5770
> > Mixed 

Re: [Koha] Report help needed

[Elaine Bradtke]

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 set correctly for the format (still trying to verify
that's what's going on).  I wanted to find any records that didn't have the
appropriate Leader 06 for a given format as specified in the 942.
Elaine Bradtke
VWML
English Folk Dance and Song Society | http://www.efdss.org
Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY
Tel+44 (0) 20 7485 2206 (This number is for the English Folk Dance and
Song Society in London, England. If you wish to phone me personally, send
an e-mail first. I work off site)
--
Registered Company No. 297142
Charity Registered in England and Wales No. 305999


On Wed, May 27, 2020 at 2:00 PM Michael Sutherland  wrote:

> 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 expression - SUBSTRING(ExtractValue(metadata,'//leader'),7,1) AS
> "Position06"
>
> you are wanting to extract a piece (substring) from the entire leader
> string - metadata,'//leader' - the 7 is the value that specifies the
> initial position from which the characters can be extracted. The first
> position of the expression starts with 1. But, in MARC the first position
> is 0 (zero). The next value is a positive integer value that specifies the
> ending limit and determines how many characters are going to be
> extracted from the given expression in this case '1', which will give you
> the single letter code in the leader 06, or the seventh position, however
> you choose to look at it.
>
> So, in collaboration with ByWater Solutions, I have co-written the
> following report, which I use to report collection statistics by itemtype
> to ACRL and ARL on our collection sans suppressed records from the OPAC. It
> takes the combination of the leader 06 and 07 to determine itemtype and
> count them.
>
> SELECT
> CASE SUBSTR(metadata,282,2)
>   WHEN ' m' THEN 'Book'
>   WHEN '2m' THEN 'Book'
>   WHEN 'am' THEN 'Book'
>   WHEN 'aa' THEN 'Book'
>   WHEN 'ac' THEN 'Book'
>   WHEN 'ad' THEN 'Book'
>   WHEN 'ai' THEN 'Book'
>   WHEN 'as' THEN 'Journal'
>   WHEN 'cc' THEN 'Music Score'
>   WHEN 'cm' THEN 'Music Score'
>   WHEN 'dm' THEN 'Music Score'
>   WHEN 'ei' THEN 'Map'
>   WHEN 'em' THEN 'Map'
>   WHEN 'es' THEN 'Map'
>   WHEN 'ga' THEN 'Video recording and motion pictures'
>   WHEN 'gs' THEN 'Video recording and motion pictures'
>   WHEN 'gc' THEN 'Video recording and motion pictures'
>   WHEN 'gi' THEN 'Video recording and motion pictures'
>   WHEN 'gm' THEN 'Video recording and motion pictures'
>   WHEN 'ic' THEN 'Non-music sound recording'
>   WHEN 'im' THEN 'Non-music sound recording'
>   WHEN 'is' THEN 'Non-music sound recording'
>   WHEN 'jm' THEN 'Music sound recording'
>   WHEN 'jc' THEN 'Music sound recording'
>   WHEN 'ji' THEN 'Music sound recording'
>   WHEN 'kc' THEN '2D image'
>   WHEN 'km' THEN '2D image'
>   WHEN 'ma' THEN 'Computer file'
>   WHEN 'mi' THEN 'Computer file'
>   WHEN 'mm' THEN 'Computer file'
>   WHEN 'ms' THEN 'Computer file'
>   WHEN 'oc' THEN 'Kit'
>   WHEN 'om' THEN 'Kit'
>   WHEN 'pc' THEN 'Mixed material'
>   WHEN 'pm' THEN 'Mixed material'
>   WHEN 'rc' THEN '3D Object'
>   WHEN 'rm' THEN '3D Object'
>   WHEN 'tc' THEN 'Manuscript'
>   WHEN 'tm' THEN 'Manuscript'
>   ELSE 'unknown' END AS Type,
> COUNT(DISTINCT biblionumber) AS Titles,
> COUNT(itemnumber) AS Volumes
> FROM biblio_metadata
> LEFT JOIN items USING (biblionumber)
> WHERE
>  ExtractValue(metadata,'//datafield[@tag="942"]/subfield[@code="n"]')
> != 1
> GROUP BY Type
> WITH ROLLUP
>
> which generates the following
>
> TypeTitlesVolumes
> 2D image 65 92
> 3D Object 21 21
> Book 1193934 1329489
> Computer file 630 1555
> Journal 38568 542424
> Kit 41 66
> Manuscript 5461 14120
> Map 4754 5770
> Mixed material 253 2517
> Music Score 11948 15757
> Music sound recording 9193 9766
> Non-music sound recording 613 818
> unknown 1018 214
> Video recording and motion pictures 15246 17090
> 1281745 1939699
> titles is the number of bib records and volumes are the number of items
> attached to a bib record.
>
> Best regards,
> Michael
> _
> *Michael J. Sutherland*
> University Libraries
> Virginia Tech
> sudrl...@vt.edu | 540.231.9669 <+15402319669>
>
>
>
> On Wed, May 27, 2020 at 2:33 PM Elaine Bradtke  

Re: [Koha] Report help needed

[Michael Sutherland]

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 expression - SUBSTRING(ExtractValue(metadata,'//leader'),7,1) AS
"Position06"

you are wanting to extract a piece (substring) from the entire leader
string - metadata,'//leader' - the 7 is the value that specifies the
initial position from which the characters can be extracted. The first
position of the expression starts with 1. But, in MARC the first position
is 0 (zero). The next value is a positive integer value that specifies the
ending limit and determines how many characters are going to be
extracted from the given expression in this case '1', which will give you
the single letter code in the leader 06, or the seventh position, however
you choose to look at it.

So, in collaboration with ByWater Solutions, I have co-written the
following report, which I use to report collection statistics by itemtype
to ACRL and ARL on our collection sans suppressed records from the OPAC. It
takes the combination of the leader 06 and 07 to determine itemtype and
count them.

SELECT
CASE SUBSTR(metadata,282,2)
  WHEN ' m' THEN 'Book'
  WHEN '2m' THEN 'Book'
  WHEN 'am' THEN 'Book'
  WHEN 'aa' THEN 'Book'
  WHEN 'ac' THEN 'Book'
  WHEN 'ad' THEN 'Book'
  WHEN 'ai' THEN 'Book'
  WHEN 'as' THEN 'Journal'
  WHEN 'cc' THEN 'Music Score'
  WHEN 'cm' THEN 'Music Score'
  WHEN 'dm' THEN 'Music Score'
  WHEN 'ei' THEN 'Map'
  WHEN 'em' THEN 'Map'
  WHEN 'es' THEN 'Map'
  WHEN 'ga' THEN 'Video recording and motion pictures'
  WHEN 'gs' THEN 'Video recording and motion pictures'
  WHEN 'gc' THEN 'Video recording and motion pictures'
  WHEN 'gi' THEN 'Video recording and motion pictures'
  WHEN 'gm' THEN 'Video recording and motion pictures'
  WHEN 'ic' THEN 'Non-music sound recording'
  WHEN 'im' THEN 'Non-music sound recording'
  WHEN 'is' THEN 'Non-music sound recording'
  WHEN 'jm' THEN 'Music sound recording'
  WHEN 'jc' THEN 'Music sound recording'
  WHEN 'ji' THEN 'Music sound recording'
  WHEN 'kc' THEN '2D image'
  WHEN 'km' THEN '2D image'
  WHEN 'ma' THEN 'Computer file'
  WHEN 'mi' THEN 'Computer file'
  WHEN 'mm' THEN 'Computer file'
  WHEN 'ms' THEN 'Computer file'
  WHEN 'oc' THEN 'Kit'
  WHEN 'om' THEN 'Kit'
  WHEN 'pc' THEN 'Mixed material'
  WHEN 'pm' THEN 'Mixed material'
  WHEN 'rc' THEN '3D Object'
  WHEN 'rm' THEN '3D Object'
  WHEN 'tc' THEN 'Manuscript'
  WHEN 'tm' THEN 'Manuscript'
  ELSE 'unknown' END AS Type,
COUNT(DISTINCT biblionumber) AS Titles,
COUNT(itemnumber) AS Volumes
FROM biblio_metadata
LEFT JOIN items USING (biblionumber)
WHERE
 ExtractValue(metadata,'//datafield[@tag="942"]/subfield[@code="n"]')
!= 1
GROUP BY Type
WITH ROLLUP

which generates the following

TypeTitlesVolumes
2D image 65 92
3D Object 21 21
Book 1193934 1329489
Computer file 630 1555
Journal 38568 542424
Kit 41 66
Manuscript 5461 14120
Map 4754 5770
Mixed material 253 2517
Music Score 11948 15757
Music sound recording 9193 9766
Non-music sound recording 613 818
unknown 1018 214
Video recording and motion pictures 15246 17090
1281745 1939699
titles is the number of bib records and volumes are the number of items
attached to a bib record.

Best regards,
Michael
_
*Michael J. Sutherland*
University Libraries
Virginia Tech
sudrl...@vt.edu | 540.231.9669 <+15402319669>



On Wed, May 27, 2020 at 2:33 PM Elaine Bradtke  wrote:

> 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 ExtractValue( metadata, '//datafield[@tag="942"]/subfield[@code="c"]'
> ) = <>
> Elaine Bradtke
> VWML
> English Folk Dance and Song Society | http://www.efdss.org
> Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY
> Tel+44 (0) 20 7485 2206 (This number is for the English Folk Dance and
> Song Society in London, England. If you wish to phone me personally, send
> an e-mail first. I work off site)
> --
> Registered Company No. 297142
> Charity Registered in England and Wales No. 305999
>
>
> On Wed, May 27, 2020 at 10:50 AM Elaine Bradtke  wrote:
>
> > It doesn't throw up error messages, but what I really need is a list of
> > biblio numbers, with the info in position 6 of the leader and the
> itemtype
> > from the bib record, but also choose a specific itemtype (not books).
> I'm
> > going to try to mash up what you did with another 

Re: [Koha] Report help needed

[Elaine Bradtke]

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 ExtractValue( metadata, '//datafield[@tag="942"]/subfield[@code="c"]'
) = <>
Elaine Bradtke
VWML
English Folk Dance and Song Society | http://www.efdss.org
Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY
Tel+44 (0) 20 7485 2206 (This number is for the English Folk Dance and
Song Society in London, England. If you wish to phone me personally, send
an e-mail first. I work off site)
--
Registered Company No. 297142
Charity Registered in England and Wales No. 305999


On Wed, May 27, 2020 at 10:50 AM Elaine Bradtke  wrote:

> It doesn't throw up error messages, but what I really need is a list of
> biblio numbers, with the info in position 6 of the leader and the itemtype
> from the bib record, but also choose a specific itemtype (not books).  I'm
> going to try to mash up what you did with another report and see what
> happens.
> Elaine Bradtke
> VWML
> English Folk Dance and Song Society | http://www.efdss.org
> Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY
> Tel+44 (0) 20 7485 2206 (This number is for the English Folk Dance and
> Song Society in London, England. If you wish to phone me personally, send
> an e-mail first. I work off site)
> --
> Registered Company No. 297142
> Charity Registered in England and Wales No. 305999
>
>
> On Wed, May 27, 2020 at 1:51 AM Jonathan Druart <
> jonathan.dru...@bugs.koha-community.org> wrote:
>
>> Hi Elaine,
>>
>> The biblioitems.marcxml field has been moved to biblio_metadata.metadata.
>>
>> Does this query work as you want:
>> SELECT CONCAT('> href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=
>> ',b.biblionumber,'\">',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.biblionumber
>> WHERE bi.itemtype = 'BK' AND SUBSTR(bm.metadata,INSTR(bm.metadata,
>> "")+8+6,1) = 'a';
>>
>> Regards,
>> Jonathan
>>
>> Le mar. 26 mai 2020 à 20:35, Elaine Bradtke  a écrit :
>> >
>> > 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 following
>> > message: Unknown column 'marcxml' in 'field list'
>> > Please check the log for further details.
>> > Also, I don't understand how to indicate Leader position 06
>> >
>> >  SELECT CONCAT('> detail.pl?biblionumber=
>> > ',biblionumber,'\">',biblionumber,'')
>> >
>> > AS biblionumber
>> >
>> > FROM biblioitems,
>> >
>> > (SELECT biblioitemnumber, SUBSTR(marcxml,INSTR(marcxml,
>> "")+8+6,1)
>> >
>> > AS leader6 FROM biblioitems)
>> >
>> > AS leaders
>> >
>> > WHERE biblioitems.biblioitemnumber=leaders.biblioitemnumber AND
>> > leaders.leader6 = 'a'
>> >
>> > AND itemtype = <>
>> >
>> > Any help would be appreciated
>> >
>> > stay safe,
>> >
>> > Elaine Bradtke
>> > VWML
>> > English Folk Dance and Song Society | http://www.efdss.org
>> > Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY
>> > Tel+44 (0) 20 7485 2206 (This number is for the English Folk Dance
>> and
>> > Song Society in London, England. If you wish to phone me personally,
>> send
>> > an e-mail first. I work off site)
>> >
>> --
>> > Registered Company No. 297142
>> > Charity Registered in England and Wales No. 305999
>> > ___
>> >
>> > Koha mailing list  http://koha-community.org
>> > Koha@lists.katipo.co.nz
>> > Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>>
>
___

Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help needed

[Jonathan Druart]

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.biblionumber
WHERE bi.itemtype = 'BK' AND SUBSTR(bm.metadata,INSTR(bm.metadata,
"")+8+6,1) = 'a';

Regards,
Jonathan

Le mar. 26 mai 2020 à 20:35, Elaine Bradtke  a écrit :
>
> 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 following
> message: Unknown column 'marcxml' in 'field list'
> Please check the log for further details.
> Also, I don't understand how to indicate Leader position 06
>
>  SELECT CONCAT(' ',biblionumber,'\">',biblionumber,'')
>
> AS biblionumber
>
> FROM biblioitems,
>
> (SELECT biblioitemnumber, SUBSTR(marcxml,INSTR(marcxml, "")+8+6,1)
>
> AS leader6 FROM biblioitems)
>
> AS leaders
>
> WHERE biblioitems.biblioitemnumber=leaders.biblioitemnumber AND
> leaders.leader6 = 'a'
>
> AND itemtype = <>
>
> Any help would be appreciated
>
> stay safe,
>
> Elaine Bradtke
> VWML
> English Folk Dance and Song Society | http://www.efdss.org
> Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY
> Tel+44 (0) 20 7485 2206 (This number is for the English Folk Dance and
> Song Society in London, England. If you wish to phone me personally, send
> an e-mail first. I work off site)
> --
> Registered Company No. 297142
> Charity Registered in England and Wales No. 305999
> ___
>
> Koha mailing list  http://koha-community.org
> Koha@lists.katipo.co.nz
> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
___

Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help needed - lost items

[Cab Vinton]

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 to the order of these statements, but there
are probably other issues as well.

Hopefully someone else w/ better report-writing chops than me can take
it from there. Or if you found something that works, Elaine, please
share w/ the list :-)

All best,

Cab Vinton, Director
Plaistow Public Library
Plaistow, NH


On Sat, Aug 24, 2019 at 8:30 PM Elaine Bradtke  wrote:
>
> 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 last item is lost, or missing, or withdrawn (I can change that part of
> the query myself).  I have to limit it by format because we have articles
> and components in the catalogue that don't have item records attached.
> What I'm getting now lis a list of biblios with items that are missing but
> it includes biblios that still have one or more items that are on the shelf
> (but at least one item that is missing). Or in other words, I'm looking for
> biblios where the all the items are missing/lost/withdrawn.
>
> Many thanks for your help!
>
> Elaine Bradtke
> VWML
> English Folk Dance and Song Society | http://www.efdss.org
> Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY
> Tel+44 (0) 20 7485 2206 (This number is for the English Folk Dance and
> Song Society in London, England. If you wish to phone me personally, send
> an e-mail first. I work off site)
> --
> Registered Company No. 297142
> Charity Registered in England and Wales No. 305999
>
>
> On Fri, Aug 23, 2019 at 12:30 PM Caroline Cyr-La-Rose <
> caroline.cyr-la-r...@inlibro.com> wrote:
>
> > 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 there
> > twice).
> >
> > First, instead of selecting from the biblio table, I would go the other
> > way and select from the items table. That way, you are sure of getting
> > only things that have an item.
> >
> > If you want to limit by itemtype, you can add WHERE itype = < > type|itemtypes>> in your query to have the ability to choose your
> > itemtype upon executing your report.
> >
> > I cleaned your query up a little bit, but like I mentioned before, as I
> > don't know the purpose, it might not be what you need.
> >
> > SELECT
> >  CONCAT(
> >  ' >  biblionumber,
> >  '\">',
> >  title,
> >  ''
> >  ) AS Title,
> >  biblionumber,
> >  barcode,
> >  ccode,
> >  onloan AS 'Checkout date'
> > FROM
> >  items
> >  JOIN biblio USING (biblionumber)
> > WHERE itemlost != 0
> >AND itype = <>
> >
> > Let us know if this works.
> > Caroline
> >
> >
> > On 19-08-23 15 h 00, Mark Alexander wrote:
> > > 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
> > >> don't have items and it's returning all of them:
> > >>
> > >> SELECT
> > >>  CONCAT(
> > >>  ' > ',
> > >>  bib.biblionumber,
> > >>  '\">',
> > >>  bib.title,
> > >>  ''
> > >>  ) AS Title,
> > >>  bib.title,
> > >>  bib.biblionumber,
> > >>  lostitems.barcode,
> > >>  lostitems.ccode,
> > >>  lostitems.onloan AS 'Checkout date'
> > >> FROM
> > >>  biblio bib
> > >>  LEFT JOIN items ON (bib.biblionumber = items.biblionumber AND
> > >> items.itemlost = 0)
> > >>  LEFT JOIN items AS lostitems ON (bib.biblionumber =
> > >> lostitems.biblionumber AND lostitems.itemlost != 0)
> > >> GROUP BY bib.biblionumber
> > >> HAVING count(items.itemnumber) = 0
> > > While I'm not an SQL expert by any means, I think I was able to get
> > > this to work on my catalog by removing the HAVING clause and
> > > putting "WHERE items.itemnumber IS NULL" before the GROUP clause:
> > >
> > > SELECT
> > >  CONCAT(
> > >  ' > ',
> > >  bib.biblionumber,
> > >  '\">',
> > >  bib.title,
> > >   

Re: [Koha] Report help needed - lost items

[Elaine Bradtke]

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 last item is lost, or missing, or withdrawn (I can change that part of
the query myself).  I have to limit it by format because we have articles
and components in the catalogue that don't have item records attached.
What I'm getting now lis a list of biblios with items that are missing but
it includes biblios that still have one or more items that are on the shelf
(but at least one item that is missing). Or in other words, I'm looking for
biblios where the all the items are missing/lost/withdrawn.

Many thanks for your help!

Elaine Bradtke
VWML
English Folk Dance and Song Society | http://www.efdss.org
Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY
Tel+44 (0) 20 7485 2206 (This number is for the English Folk Dance and
Song Society in London, England. If you wish to phone me personally, send
an e-mail first. I work off site)
--
Registered Company No. 297142
Charity Registered in England and Wales No. 305999


On Fri, Aug 23, 2019 at 12:30 PM Caroline Cyr-La-Rose <
caroline.cyr-la-r...@inlibro.com> wrote:

> 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 there
> twice).
>
> First, instead of selecting from the biblio table, I would go the other
> way and select from the items table. That way, you are sure of getting
> only things that have an item.
>
> If you want to limit by itemtype, you can add WHERE itype = < type|itemtypes>> in your query to have the ability to choose your
> itemtype upon executing your report.
>
> I cleaned your query up a little bit, but like I mentioned before, as I
> don't know the purpose, it might not be what you need.
>
> SELECT
>  CONCAT(
>  '  biblionumber,
>  '\">',
>  title,
>  ''
>  ) AS Title,
>  biblionumber,
>  barcode,
>  ccode,
>  onloan AS 'Checkout date'
> FROM
>  items
>  JOIN biblio USING (biblionumber)
> WHERE itemlost != 0
>AND itype = <>
>
> Let us know if this works.
> Caroline
>
>
> On 19-08-23 15 h 00, Mark Alexander wrote:
> > 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
> >> don't have items and it's returning all of them:
> >>
> >> SELECT
> >>  CONCAT(
> >>  ' ',
> >>  bib.biblionumber,
> >>  '\">',
> >>  bib.title,
> >>  ''
> >>  ) AS Title,
> >>  bib.title,
> >>  bib.biblionumber,
> >>  lostitems.barcode,
> >>  lostitems.ccode,
> >>  lostitems.onloan AS 'Checkout date'
> >> FROM
> >>  biblio bib
> >>  LEFT JOIN items ON (bib.biblionumber = items.biblionumber AND
> >> items.itemlost = 0)
> >>  LEFT JOIN items AS lostitems ON (bib.biblionumber =
> >> lostitems.biblionumber AND lostitems.itemlost != 0)
> >> GROUP BY bib.biblionumber
> >> HAVING count(items.itemnumber) = 0
> > While I'm not an SQL expert by any means, I think I was able to get
> > this to work on my catalog by removing the HAVING clause and
> > putting "WHERE items.itemnumber IS NULL" before the GROUP clause:
> >
> > SELECT
> >  CONCAT(
> >  ' ',
> >  bib.biblionumber,
> >  '\">',
> >  bib.title,
> >  ''
> >  ) AS Title,
> >  bib.title,
> >  bib.biblionumber,
> >  lostitems.barcode,
> >  lostitems.ccode,
> >  lostitems.onloan AS 'Checkout date'
> > FROM
> >  biblio bib
> >  LEFT JOIN items ON (bib.biblionumber = items.biblionumber AND
> > items.itemlost = 0)
> >  LEFT JOIN items AS lostitems ON (bib.biblionumber =
> > lostitems.biblionumber AND lostitems.itemlost != 0)
> > WHERE items.itemnumber IS NULL
> > GROUP BY bib.biblionumber
> > ___
> > Koha mailing list  http://koha-community.org
> > Koha@lists.katipo.co.nz
> > https://lists.katipo.co.nz/mailman/listinfo/koha
>
> --
> Caroline Cyr La Rose, M.S.I.
> Bibliothécaire | Responsable de produit
>
> Tél. : 1-833-465-4276, poste 221
> caroline.cyr-la-r...@inlibro.com 

Re: [Koha] Report help needed - lost items

[Caroline Cyr-La-Rose]

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 there twice).


First, instead of selecting from the biblio table, I would go the other 
way and select from the items table. That way, you are sure of getting 
only things that have an item.


If you want to limit by itemtype, you can add WHERE itype = > in your query to have the ability to choose your 
itemtype upon executing your report.


I cleaned your query up a little bit, but like I mentioned before, as I 
don't know the purpose, it might not be what you need.


SELECT
CONCAT(
'',
title,
''
) AS Title,
biblionumber,
barcode,
ccode,
onloan AS 'Checkout date'
FROM
items
JOIN biblio USING (biblionumber)
WHERE itemlost != 0
  AND itype = <>

Let us know if this works.
Caroline


On 19-08-23 15 h 00, Mark Alexander wrote:

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
don't have items and it's returning all of them:

SELECT
 CONCAT(
 '',
 bib.title,
 ''
 ) AS Title,
 bib.title,
 bib.biblionumber,
 lostitems.barcode,
 lostitems.ccode,
 lostitems.onloan AS 'Checkout date'
FROM
 biblio bib
 LEFT JOIN items ON (bib.biblionumber = items.biblionumber AND
items.itemlost = 0)
 LEFT JOIN items AS lostitems ON (bib.biblionumber =
lostitems.biblionumber AND lostitems.itemlost != 0)
GROUP BY bib.biblionumber
HAVING count(items.itemnumber) = 0

While I'm not an SQL expert by any means, I think I was able to get
this to work on my catalog by removing the HAVING clause and
putting "WHERE items.itemnumber IS NULL" before the GROUP clause:

SELECT
 CONCAT(
 '',
 bib.title,
 ''
 ) AS Title,
 bib.title,
 bib.biblionumber,
 lostitems.barcode,
 lostitems.ccode,
 lostitems.onloan AS 'Checkout date'
FROM
 biblio bib
 LEFT JOIN items ON (bib.biblionumber = items.biblionumber AND
items.itemlost = 0)
 LEFT JOIN items AS lostitems ON (bib.biblionumber =
lostitems.biblionumber AND lostitems.itemlost != 0)
WHERE items.itemnumber IS NULL
GROUP BY bib.biblionumber
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha


--
Caroline Cyr La Rose, M.S.I.
Bibliothécaire | Responsable de produit

Tél. : 1-833-465-4276, poste 221
caroline.cyr-la-r...@inlibro.com 

INLiBRO | Spécialistes en technologies documentaires | www.inLibro.com 


___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help needed - lost items

[Mark Alexander]

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
> don't have items and it's returning all of them:
> 
> SELECT
> CONCAT(
> ' bib.biblionumber,
> '\">',
> bib.title,
> ''
> ) AS Title,
> bib.title,
> bib.biblionumber,
> lostitems.barcode,
> lostitems.ccode,
> lostitems.onloan AS 'Checkout date'
> FROM
> biblio bib
> LEFT JOIN items ON (bib.biblionumber = items.biblionumber AND
> items.itemlost = 0)
> LEFT JOIN items AS lostitems ON (bib.biblionumber =
> lostitems.biblionumber AND lostitems.itemlost != 0)
> GROUP BY bib.biblionumber
> HAVING count(items.itemnumber) = 0

While I'm not an SQL expert by any means, I think I was able to get
this to work on my catalog by removing the HAVING clause and
putting "WHERE items.itemnumber IS NULL" before the GROUP clause:

SELECT
CONCAT(
'',
bib.title,
''
) AS Title,
bib.title,
bib.biblionumber,
lostitems.barcode,
lostitems.ccode,
lostitems.onloan AS 'Checkout date'
FROM
biblio bib
LEFT JOIN items ON (bib.biblionumber = items.biblionumber AND
items.itemlost = 0)
LEFT JOIN items AS lostitems ON (bib.biblionumber =
lostitems.biblionumber AND lostitems.itemlost != 0)
WHERE items.itemnumber IS NULL 
GROUP BY bib.biblionumber
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help

[David Hughes]

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 August 2018 at 18:29, Caroline Cyr-La-Rose <
caroline.cyr-la-r...@inlibro.com> wrote:

> 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.
>
> Caroline
> Caroline Cyr La Rose, M.S.I.
> Bibl. prof. / Chargée de la formation et du soutien
>
> Tél.  : 1 (833) 465-4276
> caroline.cyr-la-r...@inlibro.com
> inLibro | pour esprit libre | www.inLibro.com
> Le 2018-08-03 à 11:57, David Hughes a écrit :
>
> 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.attribute that have these codes.  So if
> borrower_attributes.code CC has values 1, 2, 3 etc and
> borrower_attributes.code CD has values A, B C  I'd want to output the grand
> totals of all records having
>
> CC value "1" CD value "A"
> CC value "1" CD value "B"
> CC value "1" CD value "C"
> ..
> CC value "2" CD value "A"
> CC value "2" CD value "B"
>
> etc
>
> This is outwith the scope of my very limited SQL, so I'd appreciate any
> help that the community would be able to offer.  Thanks.
>
> 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  
> *
>
>
>
>

-- 


*Data Protection: *The protection of your data is important to us.  
Please see our Privacy Notice  for more 
information on how we handle your data.




___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help

[Caroline Cyr-La-Rose]

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.


Caroline

Caroline Cyr La Rose, M.S.I.
Bibl. prof. / Chargée de la formation et du soutien

Tél.  : 1 (833) 465-4276
caroline.cyr-la-r...@inlibro.com 

inLibro | pour esprit libre | www.inLibro.com 
Le 2018-08-03 à 11:57, David Hughes a écrit :

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.attribute that have these codes.  So if
borrower_attributes.code CC has values 1, 2, 3 etc and
borrower_attributes.code CD has values A, B C  I'd want to output the grand
totals of all records having

CC value "1" CD value "A"
CC value "1" CD value "B"
CC value "1" CD value "C"
..
CC value "2" CD value "A"
CC value "2" CD value "B"

etc

This is outwith the scope of my very limited SQL, so I'd appreciate any
help that the community would be able to offer.  Thanks.

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 *



___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help needed

[Elaine Bradtke]

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"]/subfield[@code="a"]')) != 0 AND
  length(ExtractValue(metadata,
'//datafield[@tag="100"]/subfield[@code="9"]')) = 0
UNION
 SELECT biblionumber, ExtractValue(metadata,
'//datafield[@tag="110"]/subfield[@code="a"]') AS heading
 FROM biblio_metadata
 WHERE
  length(ExtractValue(metadata,
'//datafield[@tag="110"]/subfield[@code="a"]')) != 0 AND
  length(ExtractValue(metadata,
'//datafield[@tag="110"]/subfield[@code="9"]')) = 0
UNION
 SELECT biblionumber, ExtractValue(metadata,
'//datafield[@tag="111"]/subfield[@code="a"]') AS heading
 FROM biblio_metadata
 WHERE
  length(ExtractValue(metadata,
'//datafield[@tag="111"]/subfield[@code="a"]')) != 0 AND
  length(ExtractValue(metadata,
'//datafield[@tag="111"]/subfield[@code="9"]')) = 0
UNION
 SELECT biblionumber, ExtractValue(metadata,
'//datafield[@tag="130"]/subfield[@code="a"]') AS heading
 FROM biblio_metadata
 WHERE
  length(ExtractValue(metadata,
'//datafield[@tag="130"]/subfield[@code="a"]')) != 0 AND
  length(ExtractValue(metadata,
'//datafield[@tag="130"]/subfield[@code="9"]')) = 0
) AS heads
ORDER BY heading

Elaine Bradtke
VWML
English Folk Dance and Song Society | http://www.efdss.org
Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY
Tel+44 (0) 20 7485 2206 (This number is for the English Folk Dance and
Song Society in London, England. If you wish to phone me personally, send
an e-mail first. I work off site)
--
Registered Company No. 297142
Charity Registered in England and Wales No. 305999
---
"Writing about music is like dancing about architecture"
--Elvis Costello (Musician magazine No. 60 (October 1983), p. 52)

On Fri, Jul 13, 2018 at 11:53 AM, Paul Hoffman  wrote:

> 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 as well as the name?
> >
> > SELECT DISTINCT heading FROM (
> >  SELECT ExtractValue(metadata,
> > '//datafield[@tag="100"]/subfield[@code="a"]') AS heading
> >  FROM biblio_metadata
> >  WHERE
> >   length(ExtractValue(metadata,
> > '//datafield[@tag="100"]/subfield[@code="a"]')) != 0 AND
> >   length(ExtractValue(metadata,
> > '//datafield[@tag="100"]/subfield[@code="9"]')) = 0
> > UNION
> > [...]
>
> I assume that you can just add biblionumber to the output everywhere
> that a heading appears, something like this (untested!):
>
> 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"]/subfield[@code="a"]'))
> != 0
> ANDlength(ExtractValue(metadata, 
> '//datafield[@tag="100"]/subfield[@code="9"]'))
> = 0
> UNION
> [...]
>
> Paul.
>
> --
> Paul Hoffman 
> Software Manager
> Fenway Libraries Online
> c/o Wentworth Institute of Technology
> 550 Huntington Ave.
> Boston, MA 02115
> (617) 442-2384 (FLO main number)
> ___
> Koha mailing list  http://koha-community.org
> Koha@lists.katipo.co.nz
> https://lists.katipo.co.nz/mailman/listinfo/koha
>
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help needed

[Paul Hoffman]

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 as well as the name?
> 
> SELECT DISTINCT heading FROM (
>  SELECT ExtractValue(metadata,
> '//datafield[@tag="100"]/subfield[@code="a"]') AS heading
>  FROM biblio_metadata
>  WHERE
>   length(ExtractValue(metadata,
> '//datafield[@tag="100"]/subfield[@code="a"]')) != 0 AND
>   length(ExtractValue(metadata,
> '//datafield[@tag="100"]/subfield[@code="9"]')) = 0
> UNION
> [...]

I assume that you can just add biblionumber to the output everywhere 
that a heading appears, something like this (untested!):

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"]/subfield[@code="a"]')) != 0
ANDlength(ExtractValue(metadata, 
'//datafield[@tag="100"]/subfield[@code="9"]')) = 0
UNION
[...]

Paul.

-- 
Paul Hoffman 
Software Manager
Fenway Libraries Online
c/o Wentworth Institute of Technology
550 Huntington Ave.
Boston, MA 02115
(617) 442-2384 (FLO main number)
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help

[Eric Phetteplace]

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, but would instead
require web analytics that track how often a search URL is visited. If you
have Google Analytics set up for your catalog, you can see how often URLs
like "cgi-bin/koha/opac-search.pl?q=" were accessed.

2) can be very simply or rather lengthy depending on what you want to
include. At its simplest, you can just count the number of bibliographic
records in your Koha database:

SELECT COUNT(*)
FROM biblio

If you want something a bit more refined, for instance only biblios that
have attached items or perhaps the number items with a "BOOK" type, then
that has to be modified but it's hard to know what you want based on your
original message.

Best,

ERIC PHETTEPLACE

Systems Librarian


libraries.cca.edu | vault.cca.edu | 510.594.3660

5212 Broadway, Oakland, CA 94618

 8th St., San Francisco, CA 94107

Preferred Pronoun(s): he/him

:(){ :|: & };:

2018-03-19 0:04 GMT-07:00 Mutasem Al Shami :

> 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, please contact the sender immediately and delete this message
> and its attachment, if any, from your system. You should not copy this
> message or disclose its contents to any other person or use it for any
> purpose. Statements and opinions expressed in this e-mail are those of the
> sender, and do not necessarily reflect those of Al Maarefa College (MCST).
> All opinions and/or views expressed in this email are solely those of the
> author and do not necessarily represent those of MCST, MCSTaccepts no
> liability for damage caused by any virus transmitted by this email.
>
>   تنويه: هذه الرسالة ومرفقاتها (إن وجدت) تمثل وثيقة سرية قد تحتوي على
> معلومات تتمتع بحماية وحصانة قانونية. إذا لم تكن الشخص المعني بهذه الرسالة
> يجب عليك تنبيه المُرسل بخطأ وصولها إليك، وحذف الرسالة ومرفقاتها. ولا يجوز
> لك نسخ هذه الرسالة أو مرفقاتها أو أي جزء منها، أو البوح بمحتوياتها لأي شخص
> أو استعمالها لأي غرض. علماً بأن الآراء والإفادات التي تحويها هذه الرسالة
> تعبر فقط عن رأي المُرسل وليس بالضرورة رأي كلية المعرفة، ولا تتحمل كلية
> المعرفة أي مسئولية عن الأضرار الناتجة عن أي فيروسات قد يحملها هذا البريد.
>
>
> ___
> Koha mailing list  http://koha-community.org
> Koha@lists.katipo.co.nz
> https://lists.katipo.co.nz/mailman/listinfo/koha
>
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help

[Katharina Penner]

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 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
>> "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 following error:
>> Unknown column 'biblio_metadata' in 'field list'
>> Please check the log for further details.
>> Return to previous page
>>
>> So somehow probably more needs to happen than just substitution of words.
>> The 700's subfields are fine, thank you.
>>
>> Katharina
>>
>> On Wed, Feb 21, 2018 at 3:08 PM, Lee Jamison <
>> ldjami...@maryu.marywood.edu>
>> wrote:
>>
>> > Hello Katharina, I believe something like this would work. Please note
>> > this should work on versions up to 16.11. It appears that starting in
>> 17.05
>> > the marcxml attribute was removed from the biblioitems table, so the
>> > accurate query would depend on your Koha version if you'd care to
>> specify
>> > what that would be. I am not familiar with the associated descriptions
>> of
>> > the 700s subfields, so I referenced what appeared to be an old
>> cataloging
>> > index to gather the identifiers.
>> >
>> > SELECT
>> > ExtractValue(marcxml,'//datafield[@tag="700"]/
>> subfield[@code>="a"]')
>> > AS Surname,
>> > ExtractValue(marcxml,'//datafield[@tag="700"]/
>> subfield[@code>="b"]')
>> > AS Firstname,
>> > ExtractValue(marcxml,'//datafield[@tag="700"]/
>> subfield[@code>="c"]')
>> > AS 'Additions',
>> > ExtractValue(marcxml,'//datafield[@tag="700"]/
>> subfield[@code>="f"]')
>> > AS Dates,
>> > ExtractValue(marcxml,'//datafield[@tag="700"]/
>> subfield[@code>="g"]')
>> > AS Expansion,
>> > ExtractValue(marcxml,'//datafield[@tag="700"]/
>> subfield[@code>="4"]')
>> > AS 'Relator Code'
>> > FROM biblioitems;
>> >
>> > On Wed, Feb 21, 2018 at 3:14 AM, Katharina Penner <
>> > katharina.pen...@gmail.com> wrote:
>> >
>> >> 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,
>> c,
>> >> f,
>> >> g, $4. So far the data come up all in the same field.
>> >>
>> >> Please help!
>> >> Katharina Penner
>> >> ___
>> >> Koha mailing list  http://koha-community.org
>> >> Koha@lists.katipo.co.nz
>> >> https://lists.katipo.co.nz/mailman/listinfo/koha
>> >>
>> >
>> >
>> >
>> > --
>> > Lee Jamison
>> > Automated Library Technician
>> > Marywood University
>> > 570-961-4586 <(570)%20961-4586> <(570)%20961-4586>
>> > ldjami...@marywood.edu
>> >
>> ___
>> Koha mailing list  http://koha-community.org
>> Koha@lists.katipo.co.nz
>> https://lists.katipo.co.nz/mailman/listinfo/koha
>>
>
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help

[Jonathan Druart]

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
> "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 following error:
> Unknown column 'biblio_metadata' in 'field list'
> Please check the log for further details.
> Return to previous page
>
> So somehow probably more needs to happen than just substitution of words.
> The 700's subfields are fine, thank you.
>
> Katharina
>
> On Wed, Feb 21, 2018 at 3:08 PM, Lee Jamison  >
> wrote:
>
> > Hello Katharina, I believe something like this would work. Please note
> > this should work on versions up to 16.11. It appears that starting in
> 17.05
> > the marcxml attribute was removed from the biblioitems table, so the
> > accurate query would depend on your Koha version if you'd care to specify
> > what that would be. I am not familiar with the associated descriptions of
> > the 700s subfields, so I referenced what appeared to be an old cataloging
> > index to gather the identifiers.
> >
> > SELECT
> > ExtractValue(marcxml,'//datafield[@tag="700"]/subfield[@code>="a"]')
> > AS Surname,
> > ExtractValue(marcxml,'//datafield[@tag="700"]/subfield[@code>="b"]')
> > AS Firstname,
> > ExtractValue(marcxml,'//datafield[@tag="700"]/subfield[@code>="c"]')
> > AS 'Additions',
> > ExtractValue(marcxml,'//datafield[@tag="700"]/subfield[@code>="f"]')
> > AS Dates,
> > ExtractValue(marcxml,'//datafield[@tag="700"]/subfield[@code>="g"]')
> > AS Expansion,
> > ExtractValue(marcxml,'//datafield[@tag="700"]/subfield[@code>="4"]')
> > AS 'Relator Code'
> > FROM biblioitems;
> >
> > On Wed, Feb 21, 2018 at 3:14 AM, Katharina Penner <
> > katharina.pen...@gmail.com> wrote:
> >
> >> 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, c,
> >> f,
> >> g, $4. So far the data come up all in the same field.
> >>
> >> Please help!
> >> Katharina Penner
> >> ___
> >> Koha mailing list  http://koha-community.org
> >> Koha@lists.katipo.co.nz
> >> https://lists.katipo.co.nz/mailman/listinfo/koha
> >>
> >
> >
> >
> > --
> > Lee Jamison
> > Automated Library Technician
> > Marywood University
> > 570-961-4586 <(570)%20961-4586>
> > ldjami...@marywood.edu
> >
> ___
> Koha mailing list  http://koha-community.org
> Koha@lists.katipo.co.nz
> https://lists.katipo.co.nz/mailman/listinfo/koha
>
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help

[Katharina Penner]

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 following error:
Unknown column 'biblio_metadata' in 'field list'
Please check the log for further details.
Return to previous page

So somehow probably more needs to happen than just substitution of words.
The 700's subfields are fine, thank you.

Katharina

On Wed, Feb 21, 2018 at 3:08 PM, Lee Jamison 
wrote:

> Hello Katharina, I believe something like this would work. Please note
> this should work on versions up to 16.11. It appears that starting in 17.05
> the marcxml attribute was removed from the biblioitems table, so the
> accurate query would depend on your Koha version if you'd care to specify
> what that would be. I am not familiar with the associated descriptions of
> the 700s subfields, so I referenced what appeared to be an old cataloging
> index to gather the identifiers.
>
> SELECT
> ExtractValue(marcxml,'//datafield[@tag="700"]/subfield[@code>="a"]')
> AS Surname,
> ExtractValue(marcxml,'//datafield[@tag="700"]/subfield[@code>="b"]')
> AS Firstname,
> ExtractValue(marcxml,'//datafield[@tag="700"]/subfield[@code>="c"]')
> AS 'Additions',
> ExtractValue(marcxml,'//datafield[@tag="700"]/subfield[@code>="f"]')
> AS Dates,
> ExtractValue(marcxml,'//datafield[@tag="700"]/subfield[@code>="g"]')
> AS Expansion,
> ExtractValue(marcxml,'//datafield[@tag="700"]/subfield[@code>="4"]')
> AS 'Relator Code'
> FROM biblioitems;
>
> On Wed, Feb 21, 2018 at 3:14 AM, Katharina Penner <
> katharina.pen...@gmail.com> wrote:
>
>> 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, c,
>> f,
>> g, $4. So far the data come up all in the same field.
>>
>> Please help!
>> Katharina Penner
>> ___
>> Koha mailing list  http://koha-community.org
>> Koha@lists.katipo.co.nz
>> https://lists.katipo.co.nz/mailman/listinfo/koha
>>
>
>
>
> --
> Lee Jamison
> Automated Library Technician
> Marywood University
> 570-961-4586 <(570)%20961-4586>
> ldjami...@marywood.edu
>
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help

[Liz Rea]

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 "%KEYWORD%";


On 06/10/15 23:45, Charlotte Stock wrote:
> 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!
>
>
>
> Charlotte Stock
> Librarian
> charlo...@bu-london.co.uk
>
> Boston University Study Abroad London
> 43 Harrington Gardens
> London, SW7 4JU
> T 0207 244 6255   F 0207 373 9430
> http://www.bu.edu/london/
>
> Registered company in England no. FC020819
> ---
> This email message has been delivered safely and archived online by Mimecast.
> For more information please visit http://www.mimecast.com 
> ---
>
> ___
> Koha mailing list  http://koha-community.org
> Koha@lists.katipo.co.nz
> https://lists.katipo.co.nz/mailman/listinfo/koha

-- 
--
Liz Rea
Catalyst.Net Limited
Level 6, Catalyst House, 
150 Willis Street, Wellington.
P.O Box 11053, Manners Street, 
Wellington 6142

GPG: B149 A443 6B01 7386 C2C7 F481 B6c2 A49D 3726 38B7




signature.asc
Description: OpenPGP digital signature
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help

[Nick Clemens]

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, Apr 14, 2015 at 11:05 AM, Nicole Engard neng...@gmail.com wrote:

 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.biblionumber, GROUP_CONCAT(DISTINCT ExtractValue(m.marcxml,
 '//datafield[@tag=650]/subfield[@code=a]') SEPARATOR ' BR ') AS
 'Subject',i.itype AS 'IType'
 FROM biblio b
 LEFT JOIN biblioitems m using (biblionumber)
 left join items i using (biblioitemnumber)
 WHERE i.location in

 ('ARCHIVES','ARCHSIZE','HISTORYMED','HISTMEDOVZ','HISTMEDREF','RAREBKROOM','RAREOVRSIZ')
 and i.itype != 'JOURNAL' and ExtractValue(m.marcxml,
 '//datafield[@tag=650]/subfield[@code=a]') is not null and
 ExtractValue(m.marcxml, '//datafield[@tag=650]/subfield[@code=a]')
 != ''
 group by i.biblionumber
 ORDER BY b.biblionumber



 Thanks in advance!
 Nicole
 ___
 Koha mailing list  http://koha-community.org
 Koha@lists.katipo.co.nz
 http://lists.katipo.co.nz/mailman/listinfo/koha




-- 
Nick Clemens
Quechee  Wilder Libraries
n...@quecheelibrary.org
http://www.QuecheeLibrary.org
Q (802) 295-1232 W (802) 295-6341
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help

[schnydszch]

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[@tag=653]/subfield[@code=a][3]') as Keywords
but the subjects/keywords are in different columns and I have arbitrarily
put in up to 40 columns for this keywords. whatever report we arrive will be
great and could be use by a greater number of Koha users. Cheers  ya'll! :)



--
View this message in context: 
http://koha.1045719.n5.nabble.com/Report-help-tp5835592p5835643.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

Re: [Koha] Report help

[Nicole Engard]

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
 ',IFNULL(ExtractValue(m.marcxml,'//datafield[@tag=650][position()=1]/subfield[@code=a]',''),IFNULLExtractValue(m.marcxml,'//datafield[@tag=650][position()=2]/subfield[@code=a]'),''))
 to keep out extra BRs

 I don't know of a way you can easily get around guessing on the number, that
 seems to require creating a function like in the stack overflow link.  If
 someone does know how I would to know too ;-)

 On Tue, Apr 14, 2015 at 4:09 PM, Nicole Engard neng...@gmail.com wrote:

 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 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
 
  ',ExtractValue(m.marcxml,'//datafield[@tag=650][position()=3]/subfield[@code=a]')
  SEPARATOR ' BR ') AS 'Subject'
 
  On Tue, Apr 14, 2015 at 1:17 PM, Nick Clemens n...@quecheelibrary.org
  wrote:
 
  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, Apr 14, 2015 at 11:05 AM, Nicole Engard neng...@gmail.com
  wrote:
 
  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.biblionumber, GROUP_CONCAT(DISTINCT ExtractValue(m.marcxml,
  '//datafield[@tag=650]/subfield[@code=a]') SEPARATOR ' BR ') AS
  'Subject',i.itype AS 'IType'
  FROM biblio b
  LEFT JOIN biblioitems m using (biblionumber)
  left join items i using (biblioitemnumber)
  WHERE i.location in
 
 
  ('ARCHIVES','ARCHSIZE','HISTORYMED','HISTMEDOVZ','HISTMEDREF','RAREBKROOM','RAREOVRSIZ')
  and i.itype != 'JOURNAL' and ExtractValue(m.marcxml,
  '//datafield[@tag=650]/subfield[@code=a]') is not null and
  ExtractValue(m.marcxml, '//datafield[@tag=650]/subfield[@code=a]')
  != ''
  group by i.biblionumber
  ORDER BY b.biblionumber
 
 
 
  Thanks in advance!
  Nicole
  ___
  Koha mailing list  http://koha-community.org
  Koha@lists.katipo.co.nz
  http://lists.katipo.co.nz/mailman/listinfo/koha
 
 
 
 
  --
  Nick Clemens
  Quechee  Wilder Libraries
  n...@quecheelibrary.org
  http://www.QuecheeLibrary.org
  Q (802) 295-1232 W (802) 295-6341
 
 
 
 
  --
  Nick Clemens
  Quechee  Wilder Libraries
  n...@quecheelibrary.org
  http://www.QuecheeLibrary.org
  Q (802) 295-1232 W (802) 295-6341
  ___
  Koha mailing list  http://koha-community.org
  Koha@lists.katipo.co.nz
  http://lists.katipo.co.nz/mailman/listinfo/koha




 --
 Nick Clemens
 Quechee  Wilder Libraries
 n...@quecheelibrary.org
 http://www.QuecheeLibrary.org
 Q (802) 295-1232 W (802) 295-6341
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help

[Nick Clemens]

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
',ExtractValue(m.marcxml,'//datafield[@tag=650][position()=3]/subfield[@code=a]')
SEPARATOR ' BR ') AS 'Subject'

On Tue, Apr 14, 2015 at 1:17 PM, Nick Clemens n...@quecheelibrary.org
wrote:

 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, Apr 14, 2015 at 11:05 AM, Nicole Engard neng...@gmail.com wrote:

 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.biblionumber, GROUP_CONCAT(DISTINCT ExtractValue(m.marcxml,
 '//datafield[@tag=650]/subfield[@code=a]') SEPARATOR ' BR ') AS
 'Subject',i.itype AS 'IType'
 FROM biblio b
 LEFT JOIN biblioitems m using (biblionumber)
 left join items i using (biblioitemnumber)
 WHERE i.location in

 ('ARCHIVES','ARCHSIZE','HISTORYMED','HISTMEDOVZ','HISTMEDREF','RAREBKROOM','RAREOVRSIZ')
 and i.itype != 'JOURNAL' and ExtractValue(m.marcxml,
 '//datafield[@tag=650]/subfield[@code=a]') is not null and
 ExtractValue(m.marcxml, '//datafield[@tag=650]/subfield[@code=a]')
 != ''
 group by i.biblionumber
 ORDER BY b.biblionumber



 Thanks in advance!
 Nicole
 ___
 Koha mailing list  http://koha-community.org
 Koha@lists.katipo.co.nz
 http://lists.katipo.co.nz/mailman/listinfo/koha




 --
 Nick Clemens
 Quechee  Wilder Libraries
 n...@quecheelibrary.org
 http://www.QuecheeLibrary.org
 Q (802) 295-1232 W (802) 295-6341




-- 
Nick Clemens
Quechee  Wilder Libraries
n...@quecheelibrary.org
http://www.QuecheeLibrary.org
Q (802) 295-1232 W (802) 295-6341
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help

[Nicole Engard]

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 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
 ',ExtractValue(m.marcxml,'//datafield[@tag=650][position()=3]/subfield[@code=a]')
 SEPARATOR ' BR ') AS 'Subject'

 On Tue, Apr 14, 2015 at 1:17 PM, Nick Clemens n...@quecheelibrary.org
 wrote:

 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, Apr 14, 2015 at 11:05 AM, Nicole Engard neng...@gmail.com wrote:

 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.biblionumber, GROUP_CONCAT(DISTINCT ExtractValue(m.marcxml,
 '//datafield[@tag=650]/subfield[@code=a]') SEPARATOR ' BR ') AS
 'Subject',i.itype AS 'IType'
 FROM biblio b
 LEFT JOIN biblioitems m using (biblionumber)
 left join items i using (biblioitemnumber)
 WHERE i.location in

 ('ARCHIVES','ARCHSIZE','HISTORYMED','HISTMEDOVZ','HISTMEDREF','RAREBKROOM','RAREOVRSIZ')
 and i.itype != 'JOURNAL' and ExtractValue(m.marcxml,
 '//datafield[@tag=650]/subfield[@code=a]') is not null and
 ExtractValue(m.marcxml, '//datafield[@tag=650]/subfield[@code=a]')
 != ''
 group by i.biblionumber
 ORDER BY b.biblionumber



 Thanks in advance!
 Nicole
 ___
 Koha mailing list  http://koha-community.org
 Koha@lists.katipo.co.nz
 http://lists.katipo.co.nz/mailman/listinfo/koha




 --
 Nick Clemens
 Quechee  Wilder Libraries
 n...@quecheelibrary.org
 http://www.QuecheeLibrary.org
 Q (802) 295-1232 W (802) 295-6341




 --
 Nick Clemens
 Quechee  Wilder Libraries
 n...@quecheelibrary.org
 http://www.QuecheeLibrary.org
 Q (802) 295-1232 W (802) 295-6341
 ___
 Koha mailing list  http://koha-community.org
 Koha@lists.katipo.co.nz
 http://lists.katipo.co.nz/mailman/listinfo/koha
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help

[Nick Clemens]

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]/subfield[@code=a]'),'')) to keep out extra BRs

I don't know of a way you can easily get around guessing on the number,
that seems to require creating a function like in the stack overflow link.
If someone does know how I would to know too ;-)

On Tue, Apr 14, 2015 at 4:09 PM, Nicole Engard neng...@gmail.com wrote:

 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 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
 
 ',ExtractValue(m.marcxml,'//datafield[@tag=650][position()=3]/subfield[@code=a]')
  SEPARATOR ' BR ') AS 'Subject'
 
  On Tue, Apr 14, 2015 at 1:17 PM, Nick Clemens n...@quecheelibrary.org
  wrote:
 
  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, Apr 14, 2015 at 11:05 AM, Nicole Engard neng...@gmail.com
 wrote:
 
  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.biblionumber, GROUP_CONCAT(DISTINCT ExtractValue(m.marcxml,
  '//datafield[@tag=650]/subfield[@code=a]') SEPARATOR ' BR ') AS
  'Subject',i.itype AS 'IType'
  FROM biblio b
  LEFT JOIN biblioitems m using (biblionumber)
  left join items i using (biblioitemnumber)
  WHERE i.location in
 
 
 ('ARCHIVES','ARCHSIZE','HISTORYMED','HISTMEDOVZ','HISTMEDREF','RAREBKROOM','RAREOVRSIZ')
  and i.itype != 'JOURNAL' and ExtractValue(m.marcxml,
  '//datafield[@tag=650]/subfield[@code=a]') is not null and
  ExtractValue(m.marcxml, '//datafield[@tag=650]/subfield[@code=a]')
  != ''
  group by i.biblionumber
  ORDER BY b.biblionumber
 
 
 
  Thanks in advance!
  Nicole
  ___
  Koha mailing list  http://koha-community.org
  Koha@lists.katipo.co.nz
  http://lists.katipo.co.nz/mailman/listinfo/koha
 
 
 
 
  --
  Nick Clemens
  Quechee  Wilder Libraries
  n...@quecheelibrary.org
  http://www.QuecheeLibrary.org
  Q (802) 295-1232 W (802) 295-6341
 
 
 
 
  --
  Nick Clemens
  Quechee  Wilder Libraries
  n...@quecheelibrary.org
  http://www.QuecheeLibrary.org
  Q (802) 295-1232 W (802) 295-6341
  ___
  Koha mailing list  http://koha-community.org
  Koha@lists.katipo.co.nz
  http://lists.katipo.co.nz/mailman/listinfo/koha




-- 
Nick Clemens
Quechee  Wilder Libraries
n...@quecheelibrary.org
http://www.QuecheeLibrary.org
Q (802) 295-1232 W (802) 295-6341
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help needed

[Elaine Bradtke]

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:

 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 select statement has a comma after
 it, except for the last on before the from statement.

 Hope that helps!  -- it ran on my system.

 SELECT
 biblio.biblionumber,biblio.title,biblio.notes,biblioitems.publishercode,biblioitems.itemtype,

 ExtractValue(biblioitems.marcxml,
 '//datafield[@tag=780]/subfield[@code=t]') as 780t,
 ExtractValue(biblioitems.marcxml,
 '//datafield[@tag=780]/subfield[@code=w]') as 780w field,
 ExtractValue(biblioitems.marcxml,
 '//datafield[@tag=866]/subfield[@code=a]') as 866a,
 ExtractValue(biblioitems.marcxml,
 '//datafield[@tag=866]/subfield[@code=z]') as 866z
 FROM biblioitems LEFT JOIN biblio USING (biblionumber)
 WHERE biblioitems.itemtype='CR'

 Heather Braum
 NExpress Coordinator
 Resource Sharing Librarian
 Northeast Kansas Library System
 hbr...@nekls.org

 The illiterate of the 21st century will not be those who cannot read
 and write, but those who cannot learn, unlearn, and relearn. ~Alvin
 Toffler, *Rethinking the Future*




 On Mon, Nov 17, 2014 at 5:38 PM, Elaine Bradtke e...@efdss.org wrote:

 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)   WHERE
 biblioitems.itemtype='CR'

 But I need to add in more columns that involve the MARC XML

 Specifically
 780 t (previous title)
 780 w (previous control no.)
 866 a (holdings)
 866 z (note)
 867 e (source)

 When I try to add in:
   Extract value ExtractValue(marcxml,
 '//datafield[@tag=780]/subfield[@code=t]')
 I manage to break the above query and it complains of syntax errors.
 Does it matter where this Extract phrase comes in?
 How do you handle multiple subfields (780 t and w for instance)
 For that matter, how do you handle multiple fields?  And how can I label
 the columns

 The following (and a few variations on the theme) did not work:

 SELECT

  
 biblio.biblionumber,biblio.title,biblio.notes,biblioitems.publishercode,biblioitems.itemtype
 ExtractValue(marcxml, '//datafield[@tag=780]/subfield[@code=t]')
 ExtractValue(marcxml, '//datafield[@tag=780]/subfield[@code=w]')
 ExtractValue(marcxml, '//datafield[@tag=866]/subfield[@code=a]')
 ExtractValue(marcxml, '//datafield[@tag=866]/subfield[@code=z]')
 FROM biblioitems LEFT JOIN biblio USING (biblionumber)   WHERE
 biblioitems.itemtype='CR'


 As always I am very, very grateful for whatever help you can give.
 Thanks
 --
 Elaine Bradtke
 Data Wrangler
 VWML
 English Folk Dance and Song Society | http://www.efdss.org
 Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY
 Tel+44 (0) 20 7485 2206 (This number is for the English Folk Dance
 and
 Song Society in London, England. If you wish to phone me personally, send
 an e-mail first. I work off site)
 --
 Registered Company No. 297142
 Charity Registered in England and Wales No. 305999

 ---
 Writing about music is like dancing about architecture
 --Elvis Costello (Musician magazine No. 60 (October 1983), p. 52)
 ___
 Koha mailing list  http://koha-community.org
 Koha@lists.katipo.co.nz
 http://lists.katipo.co.nz/mailman/listinfo/koha





-- 
Elaine Bradtke
Data Wrangler
VWML
English Folk Dance and Song Society | http://www.efdss.org
Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY
Tel+44 (0) 20 7485 2206 (This number is for the English Folk Dance and
Song Society in London, England. If you wish to phone me personally, send
an e-mail first. I work off site)
--
Registered Company No. 297142
Charity Registered in England and Wales No. 305999
---
Writing about music is like dancing about architecture
--Elvis Costello (Musician magazine No. 60 (October 1983), p. 52)
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help needed

[Heather Braum (NEKLS)]

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 select statement has a comma after
it, except for the last on before the from statement.

Hope that helps!  -- it ran on my system.

SELECT
biblio.biblionumber,biblio.title,biblio.notes,biblioitems.publishercode,biblioitems.itemtype,

ExtractValue(biblioitems.marcxml,
'//datafield[@tag=780]/subfield[@code=t]') as 780t,
ExtractValue(biblioitems.marcxml,
'//datafield[@tag=780]/subfield[@code=w]') as 780w field,
ExtractValue(biblioitems.marcxml,
'//datafield[@tag=866]/subfield[@code=a]') as 866a,
ExtractValue(biblioitems.marcxml,
'//datafield[@tag=866]/subfield[@code=z]') as 866z
FROM biblioitems LEFT JOIN biblio USING (biblionumber)
WHERE biblioitems.itemtype='CR'

Heather Braum
NExpress Coordinator
Resource Sharing Librarian
Northeast Kansas Library System
hbr...@nekls.org

The illiterate of the 21st century will not be those who cannot read
and write, but those who cannot learn, unlearn, and relearn. ~Alvin
Toffler, *Rethinking the Future*




On Mon, Nov 17, 2014 at 5:38 PM, Elaine Bradtke e...@efdss.org wrote:

 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)   WHERE
 biblioitems.itemtype='CR'

 But I need to add in more columns that involve the MARC XML

 Specifically
 780 t (previous title)
 780 w (previous control no.)
 866 a (holdings)
 866 z (note)
 867 e (source)

 When I try to add in:
   Extract value ExtractValue(marcxml,
 '//datafield[@tag=780]/subfield[@code=t]')
 I manage to break the above query and it complains of syntax errors.
 Does it matter where this Extract phrase comes in?
 How do you handle multiple subfields (780 t and w for instance)
 For that matter, how do you handle multiple fields?  And how can I label
 the columns

 The following (and a few variations on the theme) did not work:

 SELECT

  
 biblio.biblionumber,biblio.title,biblio.notes,biblioitems.publishercode,biblioitems.itemtype
 ExtractValue(marcxml, '//datafield[@tag=780]/subfield[@code=t]')
 ExtractValue(marcxml, '//datafield[@tag=780]/subfield[@code=w]')
 ExtractValue(marcxml, '//datafield[@tag=866]/subfield[@code=a]')
 ExtractValue(marcxml, '//datafield[@tag=866]/subfield[@code=z]')
 FROM biblioitems LEFT JOIN biblio USING (biblionumber)   WHERE
 biblioitems.itemtype='CR'


 As always I am very, very grateful for whatever help you can give.
 Thanks
 --
 Elaine Bradtke
 Data Wrangler
 VWML
 English Folk Dance and Song Society | http://www.efdss.org
 Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY
 Tel+44 (0) 20 7485 2206 (This number is for the English Folk Dance and
 Song Society in London, England. If you wish to phone me personally, send
 an e-mail first. I work off site)
 --
 Registered Company No. 297142
 Charity Registered in England and Wales No. 305999
 ---
 Writing about music is like dancing about architecture
 --Elvis Costello (Musician magazine No. 60 (October 1983), p. 52)
 ___
 Koha mailing list  http://koha-community.org
 Koha@lists.katipo.co.nz
 http://lists.katipo.co.nz/mailman/listinfo/koha

___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help

[Steven Nickerson]

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 BETWEEN Top checkouts BETWEEN (-mm-dd)|date AND
and (-mm-dd)|date AND s.branch =Branch|branches GROUP BY
s.borrowernumber ORDER BY count(s.borrowernumber) DESC LIMIT 20

That is, assuming each campus library is defined as a separate branch in
Koha, of course.   Hope this helps!
Steve

-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 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 with this?
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 BETWEEN Top checkouts BETWEEN (-mm-dd)|date AND
and (-mm-dd)|date GROUP BY s.borrowernumber ORDER BY
count(s.borrowernumber) DESC LIMIT 20 Thank you!
Kerrie Stevens
COLLEGE LIBRARIAN
Harvest Bible College


___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help

[Heather Braum (NEKLS)]

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, count(s.borrowernumber)
AS checkouts
FROM statistics s
LEFT JOIN borrowers b
USING (borrowernumber)
WHERE b.branchcode=chopse branch|branchces AND s.datetime BETWEEN Top
checkouts BETWEEN (-mm-dd)|date AND and (-mm-dd)|date
GROUP BY s.borrowernumber
ORDER BY count(s.borrowernumber) DESC
LIMIT 20

Hope it helps and works!

Heather Braum
NExpress Coordinator
Resource Sharing Librarian
Northeast Kansas Library System
hbr...@nekls.org

The illiterate of the 21st century will not be those who cannot read
and write, but those who cannot learn, unlearn, and relearn. ~Alvin
Toffler, *Rethinking the Future*




On Mon, Oct 13, 2014 at 11:29 PM, Kerrie Stevens kstev...@harvest.edu.au
wrote:

 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 with this?
 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 BETWEEN Top checkouts BETWEEN (-mm-dd)|date AND
 and (-mm-dd)|date
 GROUP BY s.borrowernumber
 ORDER BY count(s.borrowernumber) DESC
 LIMIT 20
 Thank you!
 Kerrie Stevens
 COLLEGE LIBRARIAN
 Harvest Bible College
 ___
 Koha mailing list  http://koha-community.org
 Koha@lists.katipo.co.nz
 http://lists.katipo.co.nz/mailman/listinfo/koha

___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help- reading history

[clint.deckard]

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 intervention.
I should have provided more information in my initial request for help. 
My apologies.


I appreciate you taking the time to help.
Best wishes,
Clint.

vishal patil wrote:

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 mailto: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 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.


_
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz mailto:Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/__mailman/listinfo/koha
http://lists.katipo.co.nz/mailman/listinfo/koha




--
*Warm Regards,
Vishal Patil
*Software Engineer - Nucsoft Ltd.
phone: 022 32080161 | mob: 08767505528
www.osslabs.biz http://www.osslabs.biz/
www.nucsoft.com http://www.nucsoft.com/
vishal.pa...@osslabs.biz mailto:vishal.pa...@osslabs.biz



___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help- reading history

[Katelyn Browne]

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
otherwise it should do basically what you're asking.

SELECT topten.name, biblio.title, biblio.author
FROM statistics
LEFT JOIN items ON (items.itemnumber = statistics.itemnumber)
LEFT JOIN biblio ON (items.biblionumber = biblio.biblionumber)
LEFT JOIN (SELECT CONCAT(p.surname, , , p.firstname) AS name,
p.borrowernumber
FROM statistics s
LEFT JOIN borrowers p ON (s.borrowernumber=p.borrowernumber)
WHERE DATE(s.datetime) BETWEEN Top checkouts BETWEEN (-mm-dd)|date
AND and (-mm-dd)|date
  AND s.itemnumber IS NOT NULL
GROUP BY p.borrowernumber
ORDER BY COUNT(s.borrowernumber) DESC
LIMIT 10) topten ON (topten.borrowernumber = statistics.borrowernumber)
WHERE topten.name IS NOT NULL AND statistics.datetime BETWEEN Enter same
start date (-mm-dd)|date AND and same end date (-mm-dd)|date
AND statistics.type = issue
ORDER BY topten.name ASC

Katelyn Browne
Middle/High School Librarian
Capital City Public Charter School
100 Peabody Street NW
Washington, DC 20011
(202) 387-0309 x1745
kbro...@ccpcs.org
http://www.ccpcs.org/library/

On Mon, Sep 22, 2014 at 3:58 PM, clint.deckard 
clint.deck...@frontiers.co.nz wrote:

 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 intervention.
 I should have provided more information in my initial request for help. My
 apologies.

 I appreciate you taking the time to help.
 Best wishes,
 Clint.

 vishal patil wrote:

 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 mailto: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 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.


 _
 Koha mailing list  http://koha-community.org
 Koha@lists.katipo.co.nz mailto:Koha@lists.katipo.co.nz
 http://lists.katipo.co.nz/__mailman/listinfo/koha
 http://lists.katipo.co.nz/mailman/listinfo/koha




 --
 *Warm Regards,
 Vishal Patil
 *Software Engineer - Nucsoft Ltd.
 phone: 022 32080161 | mob: 08767505528
 www.osslabs.biz http://www.osslabs.biz/
 www.nucsoft.com http://www.nucsoft.com/
 vishal.pa...@osslabs.biz mailto:vishal.pa...@osslabs.biz


 ___
 Koha mailing list  http://koha-community.org
 Koha@lists.katipo.co.nz
 http://lists.katipo.co.nz/mailman/listinfo/koha

___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help needed - modification date

[Elaine Bradtke]

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 table directly


 On Tue, Sep 2, 2014 at 6:39 PM, Elaine Bradtke e...@efdss.org wrote:

 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.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio on
 (biblioitems.biblionumber=biblio.biblionumber)   WHERE biblio.timestamp =
 '2014-05-01' AND biblio.timestamp = '2014-05-30' ORDER BY
 biblio.biblionumber asc

 In the guided report the option I chose was Modification date /
 biblio.timestamp

 But after testing this I've discovered that  it isn't using the date in
 the
 005.  I'm not sure where the  Modification date / biblio.timestamp
 information is coming from, it's neither the creation date, nor the
 modification date, but some other date entirely.

 I'm trying to hunt down a batch of records modified on a certain date.
 Thanks for any help.

 --
 Elaine Bradtke
 Data Wrangler
 VWML
 English Folk Dance and Song Society | http://www.efdss.org
 Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY
 Tel+44 (0) 20 7485 2206 (This number is for the English Folk Dance
 and
 Song Society in London, England. If you wish to phone me personally, send
 an e-mail first. I work off site)
 --
 Registered Company No. 297142
 Charity Registered in England and Wales No. 305999

 ---
 Writing about music is like dancing about architecture
 --Elvis Costello (Musician magazine No. 60 (October 1983), p. 52)
 ___
 Koha mailing list  http://koha-community.org
 Koha@lists.katipo.co.nz
 http://lists.katipo.co.nz/mailman/listinfo/koha




 --
 Nick Clemens
 Quechee  Wilder Libraries
 n...@quecheelibrary.org
 http://www.QuecheeLibrary.org
 Q (802) 295-1232 W (802) 295-6341




-- 
Elaine Bradtke
Data Wrangler
VWML
English Folk Dance and Song Society | http://www.efdss.org
Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY
Tel+44 (0) 20 7485 2206 (This number is for the English Folk Dance and
Song Society in London, England. If you wish to phone me personally, send
an e-mail first. I work off site)
--
Registered Company No. 297142
Charity Registered in England and Wales No. 305999
---
Writing about music is like dancing about architecture
--Elvis Costello (Musician magazine No. 60 (October 1983), p. 52)
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help

[Elaine Bradtke]

 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=008]'),8,2)
WHEN '15' THEN '16th'
WHEN '16' THEN '17th'
WHEN '17' THEN '18th'
WHEN '18' THEN '19th'
WHEN '19' THEN '20th'
WHEN '20' THEN '21st'
  ElSE 'Unknown' END
   AS Century, count(DISTINCT biblionumber) AS bibs FROM biblioitems m
GROUP BY century


On Tue, Jul 29, 2014 at 12:08 PM, BWS Johnson abesottedphoe...@yahoo.com
wrote:

 Salve!

 Way late response since I can't check my personal email at work.


 Ahrgh
 not enough sleep last night
 1900s=20th century. .
 

 Even if it's by Century, the initial ones had


   WHEN '18' THEN 'XVIII'
   WHEN '17' THEN 'XVII'

 So either way, it's flubbed, since that ought be XIX and XVIII
 respectively.


 Cheers,
 Brooke




-- 
Elaine Bradtke
Data Wrangler
VWML
English Folk Dance and Song Society | http://www.efdss.org
Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY
Tel+44 (0) 20 7485 2206 (This number is for the English Folk Dance and
Song Society in London, England. If you wish to phone me personally, send
an e-mail first. I work off site)
--
Registered Company No. 297142
Charity Registered in England and Wales No. 305999
---
Writing about music is like dancing about architecture
--Elvis Costello (Musician magazine No. 60 (October 1983), p. 52)
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help

[Elaine Bradtke]

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.copyrightdate asc

I also used an expanded version with more fields so I could see what the
numbers really mean.


   - It doesn't pick up everything.  A lot of our 18th and 19th century
   publications have partial or conjectural dates in square brackets. These
   seem to be ignored.
   - It gives a count of the items.  Not the biblios.  So this skews the
   count further (we have a lot of 18th century pamphlets bound together -
   many biblios, but only one item record for the bound volume).

Is there any way we can be more precise?
Would a query on the MARCXML for the  260 c produce a more accurate result?
Can we to force it to count biblios instead of items?
It may be we have to settle for doing the counting in the spreadsheet
rather than via Koha. But it would be nice to have some control over what
is counted.

I know next to nothing about SQL, any help would be greatly appreciated.

Thanks



On Wed, Jul 23, 2014 at 12:31 AM, Robin Sheat ro...@catalyst.net.nz wrote:

 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 elasticsearch visualisation tool that lets you explore your
 data, and telling it to do what you want.

 The way I'd do it currently would be to script something that pulls the
 date from the MARC of each entry and exports that. Then you could pop it
 into libreoffice calc and make graphs or whatever you need.

 --
 Robin Sheat
 Catalyst IT Ltd.
 ✆ +64 4 803 2204
 GPG: 5FA7 4B49 1E4D CAA4 4C38  8505 77F5 B724 F871 3BDF

 ___
 Koha mailing list  http://koha-community.org
 Koha@lists.katipo.co.nz
 http://lists.katipo.co.nz/mailman/listinfo/koha




-- 
Elaine Bradtke
Data Wrangler
VWML
English Folk Dance and Song Society | http://www.efdss.org
Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY
Tel+44 (0) 20 7485 2206 (This number is for the English Folk Dance and
Song Society in London, England. If you wish to phone me personally, send
an e-mail first. I work off site)
--
Registered Company No. 297142
Charity Registered in England and Wales No. 305999
---
Writing about music is like dancing about architecture
--Elvis Costello (Musician magazine No. 60 (October 1983), p. 52)
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help

[ramirouvia .]

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 bibs FROM biblioitems m
GROUP BY bibtype

I made this report based on two reports from the library by Nicole C.
Engard.

Ramiro



2014-07-23 15:57 GMT-03:00 Elaine Bradtke e...@efdss.org:

 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.copyrightdate asc

 I also used an expanded version with more fields so I could see what the
 numbers really mean.


- It doesn't pick up everything.  A lot of our 18th and 19th century
publications have partial or conjectural dates in square brackets. These
seem to be ignored.
- It gives a count of the items.  Not the biblios.  So this skews the
count further (we have a lot of 18th century pamphlets bound together -
many biblios, but only one item record for the bound volume).

 Is there any way we can be more precise?
 Would a query on the MARCXML for the  260 c produce a more accurate result?
 Can we to force it to count biblios instead of items?
 It may be we have to settle for doing the counting in the spreadsheet
 rather than via Koha. But it would be nice to have some control over what
 is counted.

 I know next to nothing about SQL, any help would be greatly appreciated.

 Thanks



 On Wed, Jul 23, 2014 at 12:31 AM, Robin Sheat ro...@catalyst.net.nz
 wrote:

  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 elasticsearch visualisation tool that lets you explore your
  data, and telling it to do what you want.
 
  The way I'd do it currently would be to script something that pulls the
  date from the MARC of each entry and exports that. Then you could pop it
  into libreoffice calc and make graphs or whatever you need.
 
  --
  Robin Sheat
  Catalyst IT Ltd.
  ✆ +64 4 803 2204
  GPG: 5FA7 4B49 1E4D CAA4 4C38  8505 77F5 B724 F871 3BDF
 
  ___
  Koha mailing list  http://koha-community.org
  Koha@lists.katipo.co.nz
  http://lists.katipo.co.nz/mailman/listinfo/koha
 



 --
 Elaine Bradtke
 Data Wrangler
 VWML
 English Folk Dance and Song Society | http://www.efdss.org
 Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY
 Tel+44 (0) 20 7485 2206 (This number is for the English Folk Dance and
 Song Society in London, England. If you wish to phone me personally, send
 an e-mail first. I work off site)
 --
 Registered Company No. 297142
 Charity Registered in England and Wales No. 305999
 ---
 Writing about music is like dancing about architecture
 --Elvis Costello (Musician magazine No. 60 (October 1983), p. 52)
 ___
 Koha mailing list  http://koha-community.org
 Koha@lists.katipo.co.nz
 http://lists.katipo.co.nz/mailman/listinfo/koha

___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help

[Elaine Bradtke]

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
difficulties in producing reliable statistics!

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


On Wed, Jul 23, 2014 at 8:35 PM, ramirouvia . ramirou...@gmail.com wrote:

 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 bibs FROM biblioitems m
 GROUP BY bibtype

 I made this report based on two reports from the library by Nicole C.
 Engard.

 Ramiro



 2014-07-23 15:57 GMT-03:00 Elaine Bradtke e...@efdss.org:

 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.copyrightdate asc

 I also used an expanded version with more fields so I could see what the
 numbers really mean.


- It doesn't pick up everything.  A lot of our 18th and 19th century

publications have partial or conjectural dates in square brackets.
 These
seem to be ignored.
- It gives a count of the items.  Not the biblios.  So this skews the

count further (we have a lot of 18th century pamphlets bound together -
many biblios, but only one item record for the bound volume).

 Is there any way we can be more precise?
 Would a query on the MARCXML for the  260 c produce a more accurate
 result?
 Can we to force it to count biblios instead of items?
 It may be we have to settle for doing the counting in the spreadsheet
 rather than via Koha. But it would be nice to have some control over what
 is counted.

 I know next to nothing about SQL, any help would be greatly appreciated.

 Thanks



 On Wed, Jul 23, 2014 at 12:31 AM, Robin Sheat ro...@catalyst.net.nz
 wrote:

  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 elasticsearch visualisation tool that lets you explore your
  data, and telling it to do what you want.
 
  The way I'd do it currently would be to script something that pulls the
  date from the MARC of each entry and exports that. Then you could pop it
  into libreoffice calc and make graphs or whatever you need.
 
  --
  Robin Sheat
  Catalyst IT Ltd.
  ✆ +64 4 803 2204
  GPG: 5FA7 4B49 1E4D CAA4 4C38  8505 77F5 B724 F871 3BDF
 
  ___
  Koha mailing list  http://koha-community.org
  Koha@lists.katipo.co.nz
  http://lists.katipo.co.nz/mailman/listinfo/koha
 



 --
 Elaine Bradtke
 Data Wrangler
 VWML
 English Folk Dance and Song Society | http://www.efdss.org
 Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY
 Tel+44 (0) 20 7485 2206 (This number is for the English Folk Dance
 and
 Song Society in London, England. If you wish to phone me personally, send
 an e-mail first. I work off site)
 --
 Registered Company No. 297142
 Charity Registered in England and Wales No. 305999

 ---
 Writing about music is like dancing about architecture
 --Elvis Costello (Musician magazine No. 60 (October 1983), p. 52)
 ___
 Koha mailing list  http://koha-community.org
 Koha@lists.katipo.co.nz
 http://lists.katipo.co.nz/mailman/listinfo/koha





-- 
Elaine Bradtke
Data Wrangler
VWML
English Folk Dance and Song Society | http://www.efdss.org
Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY
Tel+44 (0) 20 7485 2206 (This number is for the English Folk Dance and
Song Society in London, England. If you wish to phone me personally, send
an e-mail first. I work off site)
--
Registered Company No. 297142
Charity Registered in England and Wales No. 305999
---
Writing about music is like dancing 

Re: [Koha] Report help

[BWS Johnson]

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 bibtype

I made this report based on two reports from the library by Nicole C.
Engard.




    Should prolly be

  WHEN '19' THEN 'X1X'
  WHEN '20' THEN 'XX'
  WHEN '18' THEN 'XVIII'
  WHEN '17' THEN 'XVII'



    yeah?

Cheers,
Brooke

___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

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

[vishal patil]

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’ 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 mailing list  http://koha-community.org
 Koha@lists.katipo.co.nz
 http://lists.katipo.co.nz/mailman/listinfo/koha




-- 


*Warm Regards,Vishal Patil*Software Engineer - Nucsoft Ltd.
phone: 022 32080161
www.osslabs.biz
www.nucsoft.com
vishal.pa...@osslabs.biz
 http://nucsoft.com/nucweb/images/OSS_labs.jpg
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

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

[Robin Sheat]

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 mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help needed

[Steven Nickerson]

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 copy/paste (thank you smart 
programs like MS Word).   Again, just a guess, and maybe those characters were 
correct when you executed the MySQL in the report and only got converted when 
entered into your email application.   They just look suspect to me.

-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:
SELECT ExtractValue(b.marcxml,
'//datafield[@tag=245]/subfield[@code=a]'), ExtractValue(b.marcxml, 
'//datafield[@tag=260]/subfield[@code=a]'),ExtractValue(b.marcxml,
'//datafield[@tag=260]/subfield[@code=c]'), monthname(datecreated) AS 
month, year(datecreated) AS year, biblionumber AS biblionumber FROM biblio LEFT 
JOIN biblioitems b USING (biblionumber) WHERE datecreated BETWEEN Between 
(-mm-dd) AND and (-mm-dd)

I tried to add some fields as per a request from one of our staff who would 
like a rather thorough list of items added to the catalogue within a time frame.

SELECT ExtractValue(b.marcxml,
'//datafield[@tag=“100”]/subfield[@code=a]'), ExtractValue(b.marcxml, 
'//datafield[@tag=245]/subfield[@code=a]'), ExtractValue(b.marcxml, 
'//datafield[@tag=245]/subfield[@code=“b”]’), ExtractValue(b.marcxml, 
'//datafield[@tag=245]/subfield[@code=“c”]’), ExtractValue(b.marcxml, 
'//datafield[@tag=260]/subfield[@code=a]'), ExtractValue(b.marcxml, 
'//datafield[@tag=“773”]/subfield[@code=“t”]’), ExtractValue(b.marcxml, 
'//datafield[@tag=“773”]/subfield[@code=“g”]’), ExtractValue(b.marcxml, 
'//datafield[@tag=260]/subfield[@code=“b”]’), ExtractValue(b.marcxml, 
'//datafield[@tag=260]/subfield[@code=c]'), ExtractValue(b.marcxml, 
'//datafield[@tag=“300”]/subfield[@code=“a”]’), ExtractValue(b.marcxml, 
'//datafield[@tag=“020”]/subfield[@code=“a”]’), ExtractValue(b.marcxml, 
'//datafield[@tag=“022”]/subfield[@code=“a”]’), ExtractValue(b.marcxml,
monthname(datecreated) AS month, year(datecreated) AS year, biblionumber AS 
biblionumber FROM biblio LEFT JOIN biblioitems b USING (biblionumber) WHERE 
datecreated BETWEEN Between (-mm-dd) AND and (-mm-dd)


The first report works.  The second doesn't.  I'm sure it's a syntax error, or 
something like that, but I'm blundering around in the dark.


Her request was for the following info: 100 field = Author | 245 = Title proper 
(including subtitles and statement of responsibility) | 773(?) = Periodical 
title |260 = place, publisher, and date | 300 = Extent | 020 = ISBN / 022 = ISSN


Can anyone help?

--
Elaine Bradtke
Data Wrangler
VWML
English Folk Dance and Song Society | http://www.efdss.org Cecil Sharp House, 2 
Regent's Park Road, London NW1 7AY
Tel+44 (0) 20 7485 2206 (This number is for the English Folk Dance and
Song Society in London, England. If you wish to phone me personally, send an 
e-mail first. I work off site)
--
Registered Company No. 297142
Charity Registered in England and Wales No. 305999
---
Writing about music is like dancing about architecture
--Elvis Costello (Musician magazine No. 60 (October 1983), p. 52)


___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help needed

[Bernardo Gonzalez Kriegel]

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, '//datafield[@tag=245]/subfield[@code=b]'),
ExtractValue(b.marcxml, '//datafield[@tag=245]/subfield[@code=c]'),
ExtractValue(b.marcxml, '//datafield[@tag=260]/subfield[@code=a]'),
ExtractValue(b.marcxml, '//datafield[@tag=773]/subfield[@code=t]'),
ExtractValue(b.marcxml, '//datafield[@tag=773]/subfield[@code=g]'),
ExtractValue(b.marcxml, '//datafield[@tag=260]/subfield[@code=b]'),
ExtractValue(b.marcxml, '//datafield[@tag=260]/subfield[@code=c]'),
ExtractValue(b.marcxml, '//datafield[@tag=300]/subfield[@code=a]'),
ExtractValue(b.marcxml, '//datafield[@tag=020]/subfield[@code=a]'),
ExtractValue(b.marcxml, '//datafield[@tag=022]/subfield[@code=a]'),
monthname(datecreated) AS month,
year(datecreated) AS year,
biblionumber AS biblionumber
FROM biblio
LEFT JOIN biblioitems b USING (biblionumber)
WHERE datecreated
BETWEEN Between (-mm-dd) AND and (-mm-dd)


Bernardo

-- 
Bernardo Gonzalez Kriegel
bgkrie...@gmail.com


On Tue, May 20, 2014 at 1:57 PM, Elaine Bradtke e...@efdss.org wrote:

 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,
 '//datafield[@tag=260]/subfield[@code=c]'), monthname(datecreated) AS
 month, year(datecreated) AS year, biblionumber AS biblionumber
 FROM biblio
 LEFT JOIN biblioitems b USING (biblionumber)
 WHERE datecreated BETWEEN Between (-mm-dd) AND and (-mm-dd)

 I tried to add some fields as per a request from one of our staff who would
 like a rather thorough list of items added to the catalogue within a time
 frame.

 SELECT ExtractValue(b.marcxml,
 '//datafield[@tag=“100”]/subfield[@code=a]'), ExtractValue(b.marcxml,
 '//datafield[@tag=245]/subfield[@code=a]'), ExtractValue(b.marcxml,
 '//datafield[@tag=245]/subfield[@code=“b”]’), ExtractValue(b.marcxml,
 '//datafield[@tag=245]/subfield[@code=“c”]’), ExtractValue(b.marcxml,
 '//datafield[@tag=260]/subfield[@code=a]'), ExtractValue(b.marcxml,
 '//datafield[@tag=“773”]/subfield[@code=“t”]’), ExtractValue(b.marcxml,
 '//datafield[@tag=“773”]/subfield[@code=“g”]’), ExtractValue(b.marcxml,
 '//datafield[@tag=260]/subfield[@code=“b”]’), ExtractValue(b.marcxml,
 '//datafield[@tag=260]/subfield[@code=c]'), ExtractValue(b.marcxml,
 '//datafield[@tag=“300”]/subfield[@code=“a”]’), ExtractValue(b.marcxml,
 '//datafield[@tag=“020”]/subfield[@code=“a”]’), ExtractValue(b.marcxml,
 '//datafield[@tag=“022”]/subfield[@code=“a”]’), ExtractValue(b.marcxml,
 monthname(datecreated) AS month, year(datecreated) AS year, biblionumber AS
 biblionumber
 FROM biblio
 LEFT JOIN biblioitems b USING (biblionumber)
 WHERE datecreated BETWEEN Between (-mm-dd) AND and (-mm-dd)


 The first report works.  The second doesn't.  I'm sure it's a syntax error,
 or something like that, but I'm blundering around in the dark.


 Her request was for the following info: 100 field = Author | 245 = Title
 proper (including subtitles and statement of responsibility) | 773(?) =
 Periodical title |260 = place, publisher, and date | 300 = Extent | 020 =
 ISBN / 022 = ISSN


 Can anyone help?

 --
 Elaine Bradtke
 Data Wrangler
 VWML
 English Folk Dance and Song Society | http://www.efdss.org
 Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY
 Tel+44 (0) 20 7485 2206 (This number is for the English Folk Dance and
 Song Society in London, England. If you wish to phone me personally, send
 an e-mail first. I work off site)
 --
 Registered Company No. 297142
 Charity Registered in England and Wales No. 305999
 ---
 Writing about music is like dancing about architecture
 --Elvis Costello (Musician magazine No. 60 (October 1983), p. 52)
 ___
 Koha mailing list  http://koha-community.org
 Koha@lists.katipo.co.nz
 http://lists.katipo.co.nz/mailman/listinfo/koha

___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help needed

[Paul A]

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/20/2014 +0100, Elaine Bradtke wrote:

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,
'//datafield[@tag=260]/subfield[@code=c]'), monthname(datecreated) AS
month, year(datecreated) AS year, biblionumber AS biblionumber
FROM biblio
LEFT JOIN biblioitems b USING (biblionumber)
WHERE datecreated BETWEEN Between (-mm-dd) AND and (-mm-dd)

I tried to add some fields as per a request from one of our staff who would
like a rather thorough list of items added to the catalogue within a time
frame.

SELECT ExtractValue(b.marcxml,
'//datafield[@tag=“100”]/subfield[@code=a]'), ExtractValue(b.marcxml,
'//datafield[@tag=245]/subfield[@code=a]'), ExtractValue(b.marcxml,
'//datafield[@tag=245]/subfield[@code=“b”]’), ExtractValue(b.marcxml,
'//datafield[@tag=245]/subfield[@code=“c”]’), ExtractValue(b.marcxml,
'//datafield[@tag=260]/subfield[@code=a]'), ExtractValue(b.marcxml,
'//datafield[@tag=“773”]/subfield[@code=“t”]’), 
ExtractValue(b.marcxml,
'//datafield[@tag=“773”]/subfield[@code=“g”]’), 
ExtractValue(b.marcxml,

'//datafield[@tag=260]/subfield[@code=“b”]’), ExtractValue(b.marcxml,
'//datafield[@tag=260]/subfield[@code=c]'), ExtractValue(b.marcxml,
'//datafield[@tag=“300”]/subfield[@code=“a”]’), 
ExtractValue(b.marcxml,
'//datafield[@tag=“020”]/subfield[@code=“a”]’), 
ExtractValue(b.marcxml,
'//datafield[@tag=“022”]/subfield[@code=“a”]’), 
ExtractValue(b.marcxml,

monthname(datecreated) AS month, year(datecreated) AS year, biblionumber AS
biblionumber
FROM biblio
LEFT JOIN biblioitems b USING (biblionumber)
WHERE datecreated BETWEEN Between (-mm-dd) AND and (-mm-dd)


The first report works.  The second doesn't.  I'm sure it's a syntax error,
or something like that, but I'm blundering around in the dark.


Her request was for the following info: 100 field = Author | 245 = Title
proper (including subtitles and statement of responsibility) | 773(?) =
Periodical title |260 = place, publisher, and date | 300 = Extent | 020 =
ISBN / 022 = ISSN


Can anyone help?

--
Elaine Bradtke
Data Wrangler
VWML
English Folk Dance and Song Society | http://www.efdss.org
Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY
Tel+44 (0) 20 7485 2206 (This number is for the English Folk Dance and
Song Society in London, England. If you wish to phone me personally, send
an e-mail first. I work off site)
--
Registered Company No. 297142
Charity Registered in England and Wales No. 305999
---
Writing about music is like dancing about architecture
--Elvis Costello (Musician magazine No. 60 (October 1983), p. 52)
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha


---
Maritime heritage and history, preservation and conservation,
research and education through the written word and the arts.
http://NavalMarineArchive.com and http://UltraMarine.ca

___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help needed

[Daniel Sanford]

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 use


//datafield[@tag=“022”]/
subfield[@code=“a”]’),
monthname(datecreated) AS month, year(datecreated) AS year, biblionumber AS

You've also got a mix of ’ and ' which are slightly different and causing
MySQL to misread the sections.

Likewise on the line
'//datafield[@tag=245]/subfield[@code=a]'), ExtractValue(b.marcxml,
'//datafield[@tag=245]/subfield[@code=“b”]’), ExtractValue(b.marcxml,
you'll note the a and b subfield codes have different quotation marks, the
one around a are right and the ones around b are not, a lot of the tags and
codes are using those and MySQL often errors out due to the that. The first
query you sent has all of those properly formatted.

Here's a cleaned up copy

 SELECT
ExtractValue(b.marcxml, '//datafield[@tag=100]/subfield[@code=a]'),
ExtractValue(b.marcxml, '//datafield[@tag=245]/subfield[@code=a]'),
ExtractValue(b.marcxml, '//datafield[@tag=245]/subfield[@code=b]'),
ExtractValue(b.marcxml, '//datafield[@tag=245]/subfield[@code=c]'),
ExtractValue(b.marcxml, '//datafield[@tag=260]/subfield[@code=a]'),
ExtractValue(b.marcxml, '//datafield[@tag=773]/subfield[@code=t]'),
ExtractValue(b.marcxml, '//datafield[@tag=773]/subfield[@code=g]'),
ExtractValue(b.marcxml, '//datafield[@tag=260]/subfield[@code=b]'),
ExtractValue(b.marcxml, '//datafield[@tag=260]/subfield[@code=c]'),
ExtractValue(b.marcxml, '//datafield[@tag=300]/subfield[@code=a]'),
ExtractValue(b.marcxml, '//datafield[@tag=020]/subfield[@code=a]'),
ExtractValue(b.marcxml, '//datafield[@tag=022]/subfield[@code=a]'),
monthname(datecreated) AS month, year(datecreated) AS year, biblionumber AS
biblionumber
FROM biblio
LEFT JOIN biblioitems b USING (biblionumber)
WHERE datecreated BETWEEN Between (-mm-dd) AND and (-mm-dd)



Daniel Sanford
Assistant Technical Librarian for Digital Infrastructure
American Philosophical Society
105 South 5th Street
Philadelphia, PA 19106
Telephone: (215)-599-4313



On Tue, May 20, 2014 at 12:57 PM, Elaine Bradtke e...@efdss.org wrote:

 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,
 '//datafield[@tag=260]/subfield[@code=c]'), monthname(datecreated) AS
 month, year(datecreated) AS year, biblionumber AS biblionumber
 FROM biblio
 LEFT JOIN biblioitems b USING (biblionumber)
 WHERE datecreated BETWEEN Between (-mm-dd) AND and (-mm-dd)

 I tried to add some fields as per a request from one of our staff who would
 like a rather thorough list of items added to the catalogue within a time
 frame.

 SELECT ExtractValue(b.marcxml,
 '//datafield[@tag=“100”]/subfield[@code=a]'), ExtractValue(b.marcxml,
 '//datafield[@tag=245]/subfield[@code=a]'), ExtractValue(b.marcxml,
 '//datafield[@tag=245]/subfield[@code=“b”]’), ExtractValue(b.marcxml,
 '//datafield[@tag=245]/subfield[@code=“c”]’), ExtractValue(b.marcxml,
 '//datafield[@tag=260]/subfield[@code=a]'), ExtractValue(b.marcxml,
 '//datafield[@tag=“773”]/subfield[@code=“t”]’), ExtractValue(b.marcxml,
 '//datafield[@tag=“773”]/subfield[@code=“g”]’), ExtractValue(b.marcxml,
 '//datafield[@tag=260]/subfield[@code=“b”]’), ExtractValue(b.marcxml,
 '//datafield[@tag=260]/subfield[@code=c]'), ExtractValue(b.marcxml,
 '//datafield[@tag=“300”]/subfield[@code=“a”]’), ExtractValue(b.marcxml,
 '//datafield[@tag=“020”]/subfield[@code=“a”]’), ExtractValue(b.marcxml,
 '//datafield[@tag=“022”]/subfield[@code=“a”]’), ExtractValue(b.marcxml,
 monthname(datecreated) AS month, year(datecreated) AS year, biblionumber AS
 biblionumber
 FROM biblio
 LEFT JOIN biblioitems b USING (biblionumber)
 WHERE datecreated BETWEEN Between (-mm-dd) AND and (-mm-dd)


 The first report works.  The second doesn't.  I'm sure it's a syntax error,
 or something like that, but I'm blundering around in the dark.


 Her request was for the following info: 100 field = Author | 245 = Title
 proper (including subtitles and statement of responsibility) | 773(?) =
 Periodical title |260 = place, publisher, and date | 300 = Extent | 020 =
 ISBN / 022 = ISSN


 Can anyone help?

 --
 Elaine Bradtke
 Data Wrangler
 VWML
 English Folk Dance and Song Society | http://www.efdss.org
 Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY
 Tel+44 (0) 20 7485 2206 (This number is for the English Folk Dance and
 Song Society in London, England. If you wish to phone me personally, send
 an e-mail first. I work off site)
 --
 Registered Company No. 297142
 Charity 

Re: [Koha] Report help needed 999$c

[Robin Sheat]

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]') AS comp FROM
 biblioitems

This is totally untested, but you might be able to use the XPath count()
function in your ExtractValue. So,:

ExtractValue(marcxml, 'count(//datafield[@tag=999]/subfield[@code=c])')

An example here:

http://dev.mysql.com/doc/refman/5.1/en/xml-functions.html#function_extractvalue

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.

-- 
Robin Sheat
Catalyst IT Ltd.
✆ +64 4 803 2204
GPG: 5FA7 4B49 1E4D CAA4 4C38  8505 77F5 B724 F871 3BDF

___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help needed 999$c

[Elaine Bradtke]

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 cases I've seen like the one I posted, it appears they are multiple
editions of the same book, in which case the cataloguer probably duplicated
the record.  I haven't tested this theory out, but I could see where it
might just add in another biblionumber to the string already there.



On Mon, May 5, 2014 at 11:57 PM, Robin Sheat ro...@catalyst.net.nz wrote:

 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]') AS comp FROM
  biblioitems

 This is totally untested, but you might be able to use the XPath count()
 function in your ExtractValue. So,:

 ExtractValue(marcxml, 'count(//datafield[@tag=999]/subfield[@code=c])')

 An example here:


 http://dev.mysql.com/doc/refman/5.1/en/xml-functions.html#function_extractvalue

 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.

 --
 Robin Sheat
 Catalyst IT Ltd.
 ✆ +64 4 803 2204
 GPG: 5FA7 4B49 1E4D CAA4 4C38  8505 77F5 B724 F871 3BDF

 ___
 Koha mailing list  http://koha-community.org
 Koha@lists.katipo.co.nz
 http://lists.katipo.co.nz/mailman/listinfo/koha




-- 
Elaine Bradtke
Data Wrangler
VWML
English Folk Dance and Song Society | http://www.efdss.org
Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY
Tel+44 (0) 20 7485 2206 (This number is for the English Folk Dance and
Song Society in London, England. If you wish to phone me personally, send
an e-mail first. I work off site)
--
Registered Company No. 297142
Charity Registered in England and Wales No. 305999
---
Writing about music is like dancing about architecture
--Elvis Costello (Musician magazine No. 60 (October 1983), p. 52)
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report Help

[Chitralekha]

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+s1045719n5793704...@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=nodenode=5793704i=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.
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report Help

[Chitralekha]

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+s1045719n5793161...@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 
 herehttp://koha.1045719.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_codenode=3047918code=Y2hpdHJhbGVraGFjaGl0YWxlQGdtYWlsLmNvbXwzMDQ3OTE4fDE5NDY2MzM0MjE=
 .
 NAMLhttp://koha.1045719.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewerid=instant_html%21nabble%3Aemail.namlbase=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespacebreadcrumbs=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.
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report Help

[manoj382093]

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: manojmisrasrm...@gmail.com


On Thu, Apr 10, 2014 at 2:06 AM, Chitralekha [via Koha] 
ml-node+s1045719n5793620...@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=nodenode=5793620i=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.
 NAMLhttp://koha.1045719.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewerid=instant_html%21nabble%3Aemail.namlbase=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespacebreadcrumbs=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 
 herehttp://koha.1045719.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_codenode=5793161code=bWFub2prdW1hcm1pc3JhQGdtYWlsLmNvbXw1NzkzMTYxfC0xMDkzNzg1MDg0
 .
 NAMLhttp://koha.1045719.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewerid=instant_html%21nabble%3Aemail.namlbase=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespacebreadcrumbs=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.
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report Help

[Katelyn Browne]

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
kbro...@ccpcs.org
http://www.ccpcs.org/library/


On Fri, Apr 11, 2014 at 3:47 AM, manoj382093 manojkumarmi...@gmail.comwrote:

 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: manojmisrasrm...@gmail.com


 On Thu, Apr 10, 2014 at 2:06 AM, Chitralekha [via Koha] 
 ml-node+s1045719n5793620...@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=nodenode=5793620i=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_viewerid=instant_html%21nabble%3Aemail.namlbase=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespacebreadcrumbs=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_codenode=5793161code=bWFub2prdW1hcm1pc3JhQGdtYWlsLmNvbXw1NzkzMTYxfC0xMDkzNzg1MDg0
 
  .
  NAML
 http://koha.1045719.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewerid=instant_html%21nabble%3Aemail.namlbase=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespacebreadcrumbs=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.
 ___
 Koha mailing list  http://koha-community.org
 Koha@lists.katipo.co.nz
 http://lists.katipo.co.nz/mailman/listinfo/koha

___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report Help

[manoj382093]

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-node+s1045719n5793778...@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=nodenode=5793778i=0
 http://www.ccpcs.org/library/


 On Fri, Apr 11, 2014 at 3:47 AM, manoj382093 [hidden 
 email]http://user/SendEmail.jtp?type=nodenode=5793778i=1wrote:


  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=nodenode=5793778i=2
 
 
  On Thu, Apr 10, 2014 at 2:06 AM, Chitralekha [via Koha] 
  [hidden email] http://user/SendEmail.jtp?type=nodenode=5793778i=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=nodenode=5793620i=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_viewerid=instant_html%21nabble%3Aemail.namlbase=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespacebreadcrumbs=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
 
 
   .
   NAML
 
 http://koha.1045719.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewerid=instant_html%21nabble%3Aemail.namlbase=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespacebreadcrumbs=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.
  ___
  Koha mailing list  http://koha-community.org
  [hidden email] http://user/SendEmail.jtp?type=nodenode=5793778i=4
  http://lists.katipo.co.nz/mailman/listinfo/koha
 
 ___
 Koha mailing list  http://koha-community.org
 [hidden email] http://user/SendEmail.jtp?type=nodenode=5793778i=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 
 herehttp://koha.1045719.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_codenode=5793161code=bWFub2prdW1hcm1pc3JhQGdtYWlsLmNvbXw1NzkzMTYxfC0xMDkzNzg1MDg0
 .
 

Re: [Koha] Report Help

[Liz Rea]

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



___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report Help

[manoj382093]

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: manojmisrasrm...@gmail.com


On Sun, Apr 6, 2014 at 5:09 PM, Liz Rea-3 [via Koha] 
ml-node+s1045719n579322...@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=nodenode=5793227i=0
  http://lists.katipo.co.nz/mailman/listinfo/koha



 ___
 Koha mailing list  http://koha-community.org
 [hidden email] http://user/SendEmail.jtp?type=nodenode=5793227i=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 
 herehttp://koha.1045719.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_codenode=5793161code=bWFub2prdW1hcm1pc3JhQGdtYWlsLmNvbXw1NzkzMTYxfC0xMDkzNzg1MDg0
 .
 NAMLhttp://koha.1045719.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewerid=instant_html%21nabble%3Aemail.namlbase=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespacebreadcrumbs=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.
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report Help

[Arslan Farooq]

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

[manoj382093]

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+s1045719n5793162...@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=nodenode=5793162i=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 
 herehttp://koha.1045719.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_codenode=5793161code=bWFub2prdW1hcm1pc3JhQGdtYWlsLmNvbXw1NzkzMTYxfC0xMDkzNzg1MDg0
 .
 NAMLhttp://koha.1045719.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewerid=instant_html%21nabble%3Aemail.namlbase=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespacebreadcrumbs=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.
___
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

[Nicole Engard]

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 (borrowernumber) where s.type in ('issue','renew')
and p.categorycode != 'LIBRARYUSE' and s.branch=Check out/renewal
branch|branches and s.datetime between
Checked out or renewed between (-mm-dd)|date and and
(-mm-dd)|date group by i.location with rollup ) AS thingy LEFT
JOIN authorised_values a on thingy.loction=a.authorised_value
where a.category='LOC'

On Tue, Mar 25, 2014 at 4:16 PM, Nick Clemens n...@quecheelibrary.org wrote:
 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 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 (borrowernumber) where s.type in ('issue','renew')
 and p.categorycode != 'LIBRARYUSE' and s.branch=Check out/renewal
 branch|branches and s.datetime between
 Checked out or renewed between (-mm-dd)|date and and
 (-mm-dd)|date group by i.location with rollup ) AS thingy LEFT
 JOIN authorised_values a on thingy.loction=a.authorised_value
 where a.category='LOC'

 On Tue, Mar 25, 2014 at 1:08 PM, Nick Clemens n...@quecheelibrary.org
 wrote:
  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 throw an error, and I can't see that value in the listing, but
  it is
  then 'Adult Fiction' is duplicated in my report
 
  Is the issue clear?
 
 
 
 
  On Thu, Mar 20, 2014 at 7:04 PM, Nicole Engard neng...@gmail.com
  wrote:
 
  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
 
 
 
 
  --
  Nick Clemens
  Quechee  Wilder Libraries
  n...@quecheelibrary.org
  http://www.QuecheeLibrary.org
  Q (802) 295-1232 W (802) 295-6341




 --
 Nick Clemens
 Quechee  Wilder Libraries
 n...@quecheelibrary.org
 http://www.QuecheeLibrary.org
 Q (802) 295-1232 W (802) 295-6341
___
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

[Nick Clemens]

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 throw an error, and I can't see that value in the listing, but it
is then 'Adult Fiction' is duplicated in my report

Is the issue clear?




On Thu, Mar 20, 2014 at 7:04 PM, Nicole Engard neng...@gmail.com wrote:

 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




-- 
Nick Clemens
Quechee  Wilder Libraries
n...@quecheelibrary.org
http://www.QuecheeLibrary.org
Q (802) 295-1232 W (802) 295-6341
___
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

[Nicole Engard]

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 (borrowernumber) where s.type in ('issue','renew')
and p.categorycode != 'LIBRARYUSE' and s.branch=Check out/renewal
branch|branches and s.datetime between
Checked out or renewed between (-mm-dd)|date and and
(-mm-dd)|date group by i.location with rollup ) AS thingy LEFT
JOIN authorised_values a on thingy.loction=a.authorised_value
where a.category='LOC'

On Tue, Mar 25, 2014 at 1:08 PM, Nick Clemens n...@quecheelibrary.org wrote:
 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 throw an error, and I can't see that value in the listing, but it is
 then 'Adult Fiction' is duplicated in my report

 Is the issue clear?




 On Thu, Mar 20, 2014 at 7:04 PM, Nicole Engard neng...@gmail.com wrote:

 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




 --
 Nick Clemens
 Quechee  Wilder Libraries
 n...@quecheelibrary.org
 http://www.QuecheeLibrary.org
 Q (802) 295-1232 W (802) 295-6341
___
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

[Nick Clemens]

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 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 (borrowernumber) where s.type in ('issue','renew')
 and p.categorycode != 'LIBRARYUSE' and s.branch=Check out/renewal
 branch|branches and s.datetime between
 Checked out or renewed between (-mm-dd)|date and and
 (-mm-dd)|date group by i.location with rollup ) AS thingy LEFT
 JOIN authorised_values a on thingy.loction=a.authorised_value
 where a.category='LOC'

 On Tue, Mar 25, 2014 at 1:08 PM, Nick Clemens n...@quecheelibrary.org
 wrote:
  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 throw an error, and I can't see that value in the listing, but
 it is
  then 'Adult Fiction' is duplicated in my report
 
  Is the issue clear?
 
 
 
 
  On Thu, Mar 20, 2014 at 7:04 PM, Nicole Engard neng...@gmail.com
 wrote:
 
  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
 
 
 
 
  --
  Nick Clemens
  Quechee  Wilder Libraries
  n...@quecheelibrary.org
  http://www.QuecheeLibrary.org
  Q (802) 295-1232 W (802) 295-6341




-- 
Nick Clemens
Quechee  Wilder Libraries
n...@quecheelibrary.org
http://www.QuecheeLibrary.org
Q (802) 295-1232 W (802) 295-6341
___
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

[Nick Clemens]

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 Grand Total' and should be fixed before running the report.

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 (borrowernumber)
where s.type in ('issue','renew') and p.categorycode != 'LIBRARYUSE' and
s.branch=Check out/renewal
branch|branches and s.datetime between
Checked out or renewed between (-mm-dd)|date and and
(-mm-dd)|date
group by i.location with rollup ) AS thingy
LEFT JOIN authorised_values a on thingy.loction=a.authorised_value



On Thu, Mar 20, 2014 at 9:11 AM, Nicole Engard neng...@gmail.com wrote:

 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.datetime) as
 circs from statistics s left join items i using (itemnumber) left join
 borrowers p using (borrowernumber) LEFT JOIN authorised_values a ON
 (i.location=a.authorised_value) where s.type in ('issue','renew') and
 p.categorycode != 'LIBRARYUSE' and s.branch=Check out/renewal
 branch|branches and a.category='LOC' and s.datetime between
 Checked out or renewed between (-mm-dd)|date and and
 (-mm-dd)|date group by i.location with rollup

 This runs and is right, but at the end it repeats the last shelving
 location instead of showing 'Grand Total'.  Next I tried:

 select if(i.location is null, '*GRAND TOTAL*', a.lib) as location,
 count(s.datetime) as circs
 from statistics s
 left join items i using (itemnumber)
 left join borrowers p using (borrowernumber)
 LEFT JOIN authorised_values a ON (i.location=a.authorised_value)
 where s.type in ('issue','renew') and p.categorycode != 'LIBRARYUSE'
 and s.branch='BAILEYCOVE'
 and a.category='LOC' and s.datetime between '2014-03-19'
 and '2014-03-20'
 group by i.location with rollup


 This repeated the same location over and over.  So then I tried this:

 select ifnull(i.location, 'GRAND TOTAL') as code, a.lib as location,
 count(s.datetime) as circs
 from statistics s
 left join items i using (itemnumber)
 left join borrowers p using (borrowernumber)
 LEFT JOIN authorised_values a ON (i.location=a.authorised_value)
 where s.type in ('issue','renew') and p.categorycode != 'LIBRARYUSE'
 and s.branch=Check out/renewal branch|branches
 and a.category='LOC' and s.datetime between Checked out or
 renewed between (-mm-dd)|date
 and and (-mm-dd)|date
 group by i.location with rollup

 Which shows grand total, but still repeats the darn location name again.

 And this which shows only one location over and over:

 select case a.location when null 'grand total' else a.lib as location,
 count(s.datetime) as circs
 from statistics s
 left join items i using (itemnumber)
 left join borrowers p using (borrowernumber)
 LEFT JOIN authorised_values a ON (i.location=a.authorised_value)
 where s.type in ('issue','renew') and p.categorycode != 'LIBRARYUSE'
 and s.branch=Check out/renewal branch|branches
 and a.category='LOC' and s.datetime between Checked out or
 renewed between (-mm-dd)|date
 and and (-mm-dd)|date
 group by i.location with rollup


 I'm up for any suggestion that gets me to have the final line just
 have the total and not a false location name.
 ___
 Koha mailing list  http://koha-community.org
 Koha@lists.katipo.co.nz
 http://lists.katipo.co.nz/mailman/listinfo/koha




-- 
Nick Clemens
Quechee  Wilder Libraries
n...@quecheelibrary.org
http://www.QuecheeLibrary.org
Q (802) 295-1232 W (802) 295-6341
___
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

[Nicole Engard]

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

[Nicole Engard]

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 mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report Help

[Nicole Engard]

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 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, ')'),'')
 AS guarantor,
   IFNULL(concat(p.surname, ', ', p.firstname, ' (',p.cardnumber, ')'),'')
 AS guarantee
 FROM
 (
   SELECT *
   FROM borrowers
   WHERE guarantorid  IS NOT NULL
 ) AS p
 LEFT JOIN borrowers AS g
   ON p.guarantorid  = g.borrowernumber
 ORDER BY g.borrowernumber
 ;

 Bernardo

 --
 Bernardo Gonzalez Kriegel
 bgkrie...@gmail.com


 On Mon, Apr 22, 2013 at 4:18 PM, Nicole Engard neng...@gmail.com wrote:

 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, ', ', g.firstname, ' (',
 g.cardnumber, ')'),'') AS guarantor, p.relationship,
 ifnull(FORMAT(SUM(a.amountoutstanding),2),'0.00') AS due
 FROM borrowers p
 LEFT JOIN accountlines a USING (borrowernumber)
 LEFT JOIN borrowers g ON (p.guarantorid=g.borrowernumber)
 WHERE p.dateexpiry  NOW()
 GROUP BY p.borrowernumber
 ORDER BY p.dateexpiry ASC



 Nicole
 ___
 Koha mailing list  http://koha-community.org
 Koha@lists.katipo.co.nz
 http://lists.katipo.co.nz/mailman/listinfo/koha



___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report Help

[Bernardo Gonzalez Kriegel]

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, ')'),'')
AS guarantor,
  IFNULL(concat(p.surname, ', ', p.firstname, ' (',p.cardnumber, ')'),'')
AS guarantee
FROM
(
  SELECT *
  FROM borrowers
  WHERE guarantorid  IS NOT NULL
) AS p
LEFT JOIN borrowers AS g
  ON p.guarantorid  = g.borrowernumber
ORDER BY g.borrowernumber
;

Bernardo

-- 
Bernardo Gonzalez Kriegel
bgkrie...@gmail.com


On Mon, Apr 22, 2013 at 4:18 PM, Nicole Engard neng...@gmail.com wrote:

 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, ', ', g.firstname, ' (',
 g.cardnumber, ')'),'') AS guarantor, p.relationship,
 ifnull(FORMAT(SUM(a.amountoutstanding),2),'0.00') AS due
 FROM borrowers p
 LEFT JOIN accountlines a USING (borrowernumber)
 LEFT JOIN borrowers g ON (p.guarantorid=g.borrowernumber)
 WHERE p.dateexpiry  NOW()
 GROUP BY p.borrowernumber
 ORDER BY p.dateexpiry ASC



 Nicole
 ___
 Koha mailing list  http://koha-community.org
 Koha@lists.katipo.co.nz
 http://lists.katipo.co.nz/mailman/listinfo/koha

___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report Help

[Manos PETRIDIS]

As the original request was I'm trying to get a report to show patrons with
overdues and if there is a guarantor I want that to show too, might I
propose the following modification to Bernardo Gonzalez Kriegel's reply, so
that all patrons are shown in a single report:

select * from
(
SELECT
patron.borrowernumber   as borrower, 
patron.surname  as Surname, 
patron.firstnameas Firstname, 
patron.relationship as relationship, 
guarantor.borrowernumberas guarantor, 
guarantor.surname   as guarantor_Surname, 
guarantor.firstname as guarantor_Firstname
FROM
borrowers as patron
LEFT JOIN
borrowers as guarantor
ON ( patron.guarantorid = guarantor.borrowernumber ) WHERE
patron.guarantorid IS NOT NULL

UNION

SELECT
patron.borrowernumber   as borrower, 
patron.surname  as Surname, 
patron.firstnameas Firstname, 
patron.relationship as relationship, 
'-' as guarantor, 
'-' as guarantor_Surname, 
'-' as guarantor_Firstname
FROM
borrowers as patron
WHERE patron.guarantorid IS NULL
)   tmp
order by tmp.Surname, tmp.Firstname;


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 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
patron.surname, patron.firstname, patron.relationship,
patron.guarantorid,
guarantor.borrowernumber, guarantor.surname, guarantor.firstname FROM
borrowers as patron
LEFT JOIN
borrowers as guarantor
ON ( patron.guarantorid = guarantor.borrowernumber ) WHERE
patron.guarantorid IS NOT NULL;


Regards,
Bernardo

--
Bernardo Gonzalez Kriegel
bgkrie...@gmail.com


On Wed, Apr 10, 2013 at 2:28 PM, Nicole Engard neng...@gmail.com wrote:

 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 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 p.categorycode, p.dateofbirth, p.cardnumber, p.surname, 
  p.firstname, p.dateexpiry,  IFNULL(concat(g.surname, ', ', g.firstname,
'
  (', g.cardnumber, ')'),'') as guarantor, p.relationship,
  FORMAT(SUM(a.amountoutstanding),2) as due FROM borrowers p LEFT JOIN 
  accountlines a USING (borrowernumber) left join borrowers g on 
  (g.guarantorid=p.borrowernumber) WHERE p.dateexpiry  NOW() and 
  p.surname = 'Meave'
  group by p.borrowernumber, g.borrowernumber ORDER BY p.dateexpiry 
  asc ;
 
 
 
 
 
 

+--+-++-+---+---
-+-+--++
  | categorycode | dateofbirth | cardnumber | surname | firstname |
  dateexpiry | guarantor   | relationship | due|
 
 

+--+-++-+---+---
-+-+--++
  | TRES | 1996-08-24  | P0012129   | Meave   | Melina-Maria  |
  2008-12-04 | | parent   | 5.90   |
  | TRES | 2000-08-30  | P0012128   | Meave   | Phoenix-Jonas |
  2008-12-04 | | parent   | 2.30   |
  | STRICT   | 1956-09-23  | P0012127   | Meave   | Jose  |
  2008-12-04 | Meave, Melina-Maria (P0012129)  |  | 351.53 |
  | STRICT   | 1956-09-23  | P0012127   | Meave   | Jose  |
  2008-12-04 | Meave, Phoenix-Jonas (P0012128) |  | 351.53 |
 
 

+--+-++-+---+---
-+-+--++
 
 ___
 Koha mailing list  http://koha-community.org Koha@lists.katipo.co.nz 
 http://lists.katipo.co.nz/mailman/listinfo/koha

___
Koha mailing list  http://koha-community.org Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report Help

[Nicole Engard]

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 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 p.categorycode, p.dateofbirth, p.cardnumber, p.surname,
 p.firstname, p.dateexpiry,  IFNULL(concat(g.surname, ', ', g.firstname, '
 (', g.cardnumber, ')'),'') as guarantor, p.relationship,
 FORMAT(SUM(a.amountoutstanding),2) as due
 FROM borrowers p
 LEFT JOIN accountlines a USING (borrowernumber)
 left join borrowers g on (g.guarantorid=p.borrowernumber)
 WHERE p.dateexpiry  NOW() and p.surname = 'Meave'
 group by p.borrowernumber, g.borrowernumber
 ORDER BY p.dateexpiry asc ;





 +--+-++-+---++-+--++
 | categorycode | dateofbirth | cardnumber | surname | firstname |
 dateexpiry | guarantor   | relationship | due|

 +--+-++-+---++-+--++
 | TRES | 1996-08-24  | P0012129   | Meave   | Melina-Maria  |
 2008-12-04 | | parent   | 5.90   |
 | TRES | 2000-08-30  | P0012128   | Meave   | Phoenix-Jonas |
 2008-12-04 | | parent   | 2.30   |
 | STRICT   | 1956-09-23  | P0012127   | Meave   | Jose  |
 2008-12-04 | Meave, Melina-Maria (P0012129)  |  | 351.53 |
 | STRICT   | 1956-09-23  | P0012127   | Meave   | Jose  |
 2008-12-04 | Meave, Phoenix-Jonas (P0012128) |  | 351.53 |

 +--+-++-+---++-+--++

___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report Help

[Bernardo Gonzalez Kriegel]

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
patron.surname, patron.firstname, patron.relationship,
patron.guarantorid,
guarantor.borrowernumber, guarantor.surname, guarantor.firstname
FROM
borrowers as patron
LEFT JOIN
borrowers as guarantor
ON ( patron.guarantorid = guarantor.borrowernumber )
WHERE patron.guarantorid IS NOT NULL;


Regards,
Bernardo

-- 
Bernardo Gonzalez Kriegel
bgkrie...@gmail.com


On Wed, Apr 10, 2013 at 2:28 PM, Nicole Engard neng...@gmail.com wrote:

 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 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 p.categorycode, p.dateofbirth, p.cardnumber, p.surname,
  p.firstname, p.dateexpiry,  IFNULL(concat(g.surname, ', ', g.firstname, '
  (', g.cardnumber, ')'),'') as guarantor, p.relationship,
  FORMAT(SUM(a.amountoutstanding),2) as due
  FROM borrowers p
  LEFT JOIN accountlines a USING (borrowernumber)
  left join borrowers g on (g.guarantorid=p.borrowernumber)
  WHERE p.dateexpiry  NOW() and p.surname = 'Meave'
  group by p.borrowernumber, g.borrowernumber
  ORDER BY p.dateexpiry asc ;
 
 
 
 
 
 
 +--+-++-+---++-+--++
  | categorycode | dateofbirth | cardnumber | surname | firstname |
  dateexpiry | guarantor   | relationship | due|
 
 
 +--+-++-+---++-+--++
  | TRES | 1996-08-24  | P0012129   | Meave   | Melina-Maria  |
  2008-12-04 | | parent   | 5.90   |
  | TRES | 2000-08-30  | P0012128   | Meave   | Phoenix-Jonas |
  2008-12-04 | | parent   | 2.30   |
  | STRICT   | 1956-09-23  | P0012127   | Meave   | Jose  |
  2008-12-04 | Meave, Melina-Maria (P0012129)  |  | 351.53 |
  | STRICT   | 1956-09-23  | P0012127   | Meave   | Jose  |
  2008-12-04 | Meave, Phoenix-Jonas (P0012128) |  | 351.53 |
 
 
 +--+-++-+---++-+--++
 
 ___
 Koha mailing list  http://koha-community.org
 Koha@lists.katipo.co.nz
 http://lists.katipo.co.nz/mailman/listinfo/koha

___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report Help

[Nicole Engard]

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 you to get an inverted list.

 For example this query will list all guarantees with corresponding
 guarantor (if not null),

 SELECT
 patron.surname, patron.firstname, patron.relationship,
 patron.guarantorid,
 guarantor.borrowernumber, guarantor.surname, guarantor.firstname
 FROM
 borrowers as patron
 LEFT JOIN
 borrowers as guarantor
 ON ( patron.guarantorid = guarantor.borrowernumber )
 WHERE patron.guarantorid IS NOT NULL;


 Regards,
 Bernardo

 --
 Bernardo Gonzalez Kriegel
 bgkrie...@gmail.com


 On Wed, Apr 10, 2013 at 2:28 PM, Nicole Engard neng...@gmail.com wrote:

 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 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 p.categorycode, p.dateofbirth, p.cardnumber, p.surname,
  p.firstname, p.dateexpiry,  IFNULL(concat(g.surname, ', ', g.firstname,
 '
  (', g.cardnumber, ')'),'') as guarantor, p.relationship,
  FORMAT(SUM(a.amountoutstanding),2) as due
  FROM borrowers p
  LEFT JOIN accountlines a USING (borrowernumber)
  left join borrowers g on (g.guarantorid=p.borrowernumber)
  WHERE p.dateexpiry  NOW() and p.surname = 'Meave'
  group by p.borrowernumber, g.borrowernumber
  ORDER BY p.dateexpiry asc ;
 
 
 
 
 
 
 +--+-++-+---++-+--++
  | categorycode | dateofbirth | cardnumber | surname | firstname |
  dateexpiry | guarantor   | relationship | due|
 
 
 +--+-++-+---++-+--++
  | TRES | 1996-08-24  | P0012129   | Meave   | Melina-Maria  |
  2008-12-04 | | parent   | 5.90   |
  | TRES | 2000-08-30  | P0012128   | Meave   | Phoenix-Jonas |
  2008-12-04 | | parent   | 2.30   |
  | STRICT   | 1956-09-23  | P0012127   | Meave   | Jose  |
  2008-12-04 | Meave, Melina-Maria (P0012129)  |  | 351.53 |
  | STRICT   | 1956-09-23  | P0012127   | Meave   | Jose  |
  2008-12-04 | Meave, Phoenix-Jonas (P0012128) |  | 351.53 |
 
 
 +--+-++-+---++-+--++
 
 ___
 Koha mailing list  http://koha-community.org
 Koha@lists.katipo.co.nz
 http://lists.katipo.co.nz/mailman/listinfo/koha



___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help

[Nicole Engard]

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 biblioitemnumber,SUBSTR(marcxml,INSTR(marcxml, leader)+8+6,1) AS
leader6,SUBSTR(marcxml,INSTR(marcxml, leader)+8+7,1) AS leader7 FROM
biblioitems) AS leaders WHERE
biblioitems.biblioitemnumber=leaders.biblioitemnumber AND (leaders.leader6 =
'a' AND (leader7 IN ('a','c','d','n')) OR leader6 = 't') AND itemtype = 'PHOTO'


On Tue, Apr 3, 2012 at 8:43 PM, Chris Cormack ch...@bigballofwax.co.nz wrote:
 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 like this then

 SELECT * FROM biblioitems,
 (SELECT biblioitemnumber,SUBSTR(marcxml,INSTR(marcxml,
 leader)+8+6,1) AS leader6,SUBSTR(marcxml,INSTR(marcxml,
 leader)+8+7,1) AS leader7 FROM biblioitems) AS leaders
 WHERE biblioitems.biblioitemnumber=leaders.biblioitemnumber AND
 (leaders.leader6 = 'a' AND (leader7 IN ('a','c','d','n')) OR leader6 =
 't') AND itemtypes = 'your_itemtype goes here'

 Chris
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help

[Nicole Engard]

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 CONCAT('a
 href=\/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblionumber,'\',biblionumber,'/a')
 AS biblionumber FROM biblioitems,
   (SELECT biblioitemnumber,SUBSTR(marcxml,INSTR(marcxml, leader)+8+6,1) AS
 leader6,SUBSTR(marcxml,INSTR(marcxml, leader)+8+7,1) AS leader7 FROM
 biblioitems) AS leaders WHERE
 biblioitems.biblioitemnumber=leaders.biblioitemnumber AND (leaders.leader6 =
 'a' AND (leader7 IN ('a','c','d','n')) OR leader6 = 't') AND itemtype = 
 'PHOTO'


 On Tue, Apr 3, 2012 at 8:43 PM, Chris Cormack ch...@bigballofwax.co.nz 
 wrote:
 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 like this then

 SELECT * FROM biblioitems,
 (SELECT biblioitemnumber,SUBSTR(marcxml,INSTR(marcxml,
 leader)+8+6,1) AS leader6,SUBSTR(marcxml,INSTR(marcxml,
 leader)+8+7,1) AS leader7 FROM biblioitems) AS leaders
 WHERE biblioitems.biblioitemnumber=leaders.biblioitemnumber AND
 (leaders.leader6 = 'a' AND (leader7 IN ('a','c','d','n')) OR leader6 =
 't') AND itemtypes = 'your_itemtype goes here'

 Chris
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help

[Chris Cormack]

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 that shows item type as book?

 I know that the 94$c is mapped to biblioitems.itemtype - so that's not
 the issue, it's the Leader bit I need help with.


What chars in the leader tell me if its a book?

Chris
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help

[Nicole Engard]

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 wrote:
 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 that shows item type as book?

 I know that the 94$c is mapped to biblioitems.itemtype - so that's not
 the issue, it's the Leader bit I need help with.


 What chars in the leader tell me if its a book?

 Chris
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help

[Chris Cormack]

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 like this then

SELECT * FROM biblioitems,
(SELECT biblioitemnumber,SUBSTR(marcxml,INSTR(marcxml,
leader)+8+6,1) AS leader6,SUBSTR(marcxml,INSTR(marcxml,
leader)+8+7,1) AS leader7 FROM biblioitems) AS leaders
WHERE biblioitems.biblioitemnumber=leaders.biblioitemnumber AND
(leaders.leader6 = 'a' AND (leader7 IN ('a','c','d','n')) OR leader6 =
't') AND itemtypes = 'your_itemtype goes here'

Chris
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] report help - total amount writeoff

[Wagner, Jane]

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
payments)



Jane Wagner

Senior Project Manager

LibLime, a division of PTFS

Content Management and Library Solutions

11501 Huff Court

North Bethesda, MD  20895

(301) 654-8088 x 151

jwag...@liblime.com jwag...@ptfs.com



*From:* koha-boun...@lists.katipo.co.nz [mailto:
koha-boun...@lists.katipo.co.nz] *On Behalf Of *Peel Lisa
*Sent:* Wednesday, October 19, 2011 3:08 PM
*To:* 'koha@lists.katipo.co.nz'
*Subject:* [Koha] report help - total amount writeoff



Hello



Report help again I am afraid!



Could anyone supply me with the SQL for a report which will show me the
amount of fines being written off at a specific library for a given period
of time (for example the last three months?)



Regards and thanks in advance



Lisa





[image: Description: Description: Description: CR_logo_POS_COULEUR b]**

* *

* *

*LISA PEEL, PGDip, MA.***

Librarian

César Ritz Colleges Switzerland

1897 Le Bouveret - Switzerland

Tel: +41 24 482 82 82 Fax: +41 24 482 82 80

E-mail: *lisa.p...@ritz.edu hortense.ho...@ritz.edu*

http://www.ritz.edu - http://www.culinaryarts.ch
image001.jpg___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report Help

[Nicole Engard]

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, i.datelastborrowed, i.issues as totalcheckouts,

 i.dateaccessioned

 FROM items i

 LEFT JOIN issues

 USING (itemnumber)

 LEFT JOIN biblio b

 USING (biblionumber)

 WHERE i.itemnumber NOT IN (SELECT issues.itemnumber FROM issues) AND
 YEAR(NOW())-YEAR(i.datelastborrowed)  5

 UNION SELECT b.title, b.author, b.copyrightdate, i.itemcallnumber,

 i.barcode, i.datelastborrowed, i.issues as totalcheckouts,

 i.dateaccessioned

 FROM items i

 LEFT JOIN issues

 USING (itemnumber)

 LEFT JOIN biblio b

 USING (biblionumber)

 WHERE i.itemnumber NOT IN (SELECT old_issues.itemnumber FROM old_issues) AND
 YEAR(NOW())-YEAR(i.datelastborrowed)  5;
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report Help - Extracting XML

[Nicole Engard]

:) No problem! I actually had to remove them to test on the command line anyway.

You are right! It does work :) thanks - will share on the wiki with a
dual credit :)

Nicole

On Thu, Jul 28, 2011 at 8:21 PM, Grobani, Daniel
dgrob...@samuelmerritt.edu wrote:
 Hmm, it works fine for me. It looks like it might have choked on the first 
 tab--try replacing all the tabs I introduced to spaces.

 Thanks for your reminder about the  and sorry I removed them. I remember 
 reading about that now, but because that works differently in our, um, 
 flavor, it didn't 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=\/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\',biblio.title,'/a')
 AS Title, CONCAT('a
 href=\http://catalog.mysiteca.gov/cgi-bin/koha/opac-detail.pl?biblionumber=',biblio.biblionumber,'\http://catalog.mysiteca.gov/cgi-bin/koha/opac-detail.pl?biblionumber=',biblio.biblionumber,'/a')
 as URL, biblio.author, items.location,
 extractValue(biblioitems.marcxml,collection/record/datafield[@tag='520'][1]/subfield[@code='a'])
 FROM            statistics
 JOIN            items ON (items.itemnumber=statistics.itemnumber)
 LEFT JOIN       biblio ON (biblio.biblionumber=items.biblionumber)
 LEFT JOIN       biblioitems ON (biblioitems.biblioitemnumber =
 items.biblioitemnumber)
 WHERE           DATE(statistics.datetime) BETWEEN '2011-01-01' AND 
 '2011-07-01'
                       AND statistics.itemnumber IS NOT NULL
                       AND location = 'Shelving Location Code'
 GROUP BY        biblio.biblionumber
 ORDER BY        circs DESC
 LIMIT           10

 Gets this:

 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual 
 that corresponds to your MySQL server version for the right syntax to use 
 near '       statistics JOIN            items ON (items.itemnumber=statistic' 
 at line 4


 Also, a tip - those  sections I had in there are runtime parameters for 
 Koha (a neat trick that is documented on the wiki and in the manual - in case 
 you removed them cause you didn't know what they were).


 On Thu, Jul 28, 2011 at 7:41 PM, Daniel Grobani dgrob...@samuelmerritt.edu 
 wrote:
 Try this (I also did some light cleanup):

 SELECT          COUNT(statistics.datetime) AS circs,
                CONCAT(' \/cgi- ',biblio.title,' ') AS Title,
                CONCAT(' \http://mycatalogv/cgi-
 http://catalog.losgatosca.gov/cgi-bin/koha/opac-detail.pl?biblionumber=',biblio.biblionumber,'
 ') AS URL,
                biblio.author,
                items.location,

 extractValue(biblioitems.marcxml,collection/record/datafield[@tag='52
 0'][1]/subfield[@code='a'])
 FROM            statistics
 JOIN            items ON (items.itemnumber=statistics.itemnumber)
 LEFT JOIN       biblio ON (biblio.biblionumber=items.biblionumber)
 LEFT JOIN       biblioitems ON (biblioitems.biblioitemnumber =
 items.biblioitemnumber)
 WHERE           DATE(statistics.datetime) BETWEEN '-mm-dd' AND 
 '-mm-dd'
                        AND statistics.itemnumber IS NOT NULL
                        AND location = 'Shelving Location Code'
 GROUP BY        biblio.biblionumber
 ORDER BY        circs DESC
 LIMIT           10


 -
 Cheers,
 Daniel Grobani
 Library Technology Specialist
 John A. Graziano Memorial Library
 Samuel Merritt University

 --
 View this message in context:
 http://koha.1045719.n5.nabble.com/Report-Help-Extracting-XML-tp4640604
 p4644634.html Sent from the Koha - Discuss 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


___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report Help - Extracting XML

[Nicole Engard]

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,
 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 Merritt University

 --
 View this message in context: 
 http://koha.1045719.n5.nabble.com/Report-Help-Extracting-XML-tp4640604p4640701.html
 Sent from the Koha - Discuss 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

___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report Help - Extracting XML

[Nicole Engard]

If you don't mind, put your two pieces in to my report in the right
places - my problem is that I'm not sure the Join goes where I would
normally put it.

Nicole

On Thu, Jul 28, 2011 at 12:45 PM, Grobani, Daniel
dgrob...@samuelmerritt.edu wrote:
 How about LEFT JOIN biblioitems 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 :) 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,
 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 Merritt University

 --
 View this message in context:
 http://koha.1045719.n5.nabble.com/Report-Help-Extracting-XML-tp4640604
 p4640701.html Sent from the Koha - Discuss 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


___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report Help - Extracting XML

[Daniel Grobani]

Try this (I also did some light cleanup):

SELECT  COUNT(statistics.datetime) AS circs,
CONCAT(' \/cgi- ',biblio.title,' ') AS Title,
CONCAT(' \http://mycatalogv/cgi-
http://catalog.losgatosca.gov/cgi-bin/koha/opac-detail.pl?biblionumber=',biblio.biblionumber,'
') AS URL,
biblio.author,
items.location,

extractValue(biblioitems.marcxml,collection/record/datafield[@tag='520'][1]/subfield[@code='a'])
FROMstatistics
JOINitems ON (items.itemnumber=statistics.itemnumber)
LEFT JOIN   biblio ON (biblio.biblionumber=items.biblionumber)
LEFT JOIN   biblioitems ON (biblioitems.biblioitemnumber =
items.biblioitemnumber)
WHERE   DATE(statistics.datetime) BETWEEN '-mm-dd' AND '-mm-dd'
AND statistics.itemnumber IS NOT NULL
AND location = 'Shelving Location Code'
GROUP BYbiblio.biblionumber
ORDER BYcircs DESC
LIMIT   10


-
Cheers,
Daniel Grobani
Library Technology Specialist
John A. Graziano Memorial Library
Samuel Merritt University

--
View this message in context: 
http://koha.1045719.n5.nabble.com/Report-Help-Extracting-XML-tp4640604p4644634.html
Sent from the Koha - Discuss 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

Re: [Koha] Report Help - Extracting XML

[Nicole Engard]

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
href=\http://catalog.mysiteca.gov/cgi-bin/koha/opac-detail.pl?biblionumber=',biblio.biblionumber,'\http://catalog.mysiteca.gov/cgi-bin/koha/opac-detail.pl?biblionumber=',biblio.biblionumber,'/a')
as URL, biblio.author, items.location,
extractValue(biblioitems.marcxml,collection/record/datafield[@tag='520'][1]/subfield[@code='a'])
FROMstatistics
JOINitems ON (items.itemnumber=statistics.itemnumber)
LEFT JOIN   biblio ON (biblio.biblionumber=items.biblionumber)
LEFT JOIN   biblioitems ON (biblioitems.biblioitemnumber =
items.biblioitemnumber)
WHERE   DATE(statistics.datetime) BETWEEN '2011-01-01' AND '2011-07-01'
   AND statistics.itemnumber IS NOT NULL
   AND location = 'Shelving Location Code'
GROUP BYbiblio.biblionumber
ORDER BYcircs DESC
LIMIT   10

Gets this:

ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near '       statistics
JOIN            items ON (items.itemnumber=statistic' at line 4


Also, a tip - those  sections I had in there are runtime
parameters for Koha (a neat trick that is documented on the wiki and
in the manual - in case you removed them cause you didn't know what
they were).


On Thu, Jul 28, 2011 at 7:41 PM, Daniel Grobani
dgrob...@samuelmerritt.edu wrote:
 Try this (I also did some light cleanup):

 SELECT          COUNT(statistics.datetime) AS circs,
                CONCAT(' \/cgi- ',biblio.title,' ') AS Title,
                CONCAT(' \http://mycatalogv/cgi-
 http://catalog.losgatosca.gov/cgi-bin/koha/opac-detail.pl?biblionumber=',biblio.biblionumber,'
 ') AS URL,
                biblio.author,
                items.location,

 extractValue(biblioitems.marcxml,collection/record/datafield[@tag='520'][1]/subfield[@code='a'])
 FROM            statistics
 JOIN            items ON (items.itemnumber=statistics.itemnumber)
 LEFT JOIN       biblio ON (biblio.biblionumber=items.biblionumber)
 LEFT JOIN       biblioitems ON (biblioitems.biblioitemnumber =
 items.biblioitemnumber)
 WHERE           DATE(statistics.datetime) BETWEEN '-mm-dd' AND 
 '-mm-dd'
                        AND statistics.itemnumber IS NOT NULL
                        AND location = 'Shelving Location Code'
 GROUP BY        biblio.biblionumber
 ORDER BY        circs DESC
 LIMIT           10


 -
 Cheers,
 Daniel Grobani
 Library Technology Specialist
 John A. Graziano Memorial Library
 Samuel Merritt University

 --
 View this message in context: 
 http://koha.1045719.n5.nabble.com/Report-Help-Extracting-XML-tp4640604p4644634.html
 Sent from the Koha - Discuss 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

___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report Help - Extracting XML

[Grobani, Daniel]

Hmm, it works fine for me. It looks like it might have choked on the first 
tab--try replacing all the tabs I introduced to spaces.

Thanks for your reminder about the  and sorry I removed them. I remember 
reading about that now, but because that works differently in our, um, flavor, 
it didn't 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=\/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\',biblio.title,'/a')
AS Title, CONCAT('a
href=\http://catalog.mysiteca.gov/cgi-bin/koha/opac-detail.pl?biblionumber=',biblio.biblionumber,'\http://catalog.mysiteca.gov/cgi-bin/koha/opac-detail.pl?biblionumber=',biblio.biblionumber,'/a')
as URL, biblio.author, items.location,
extractValue(biblioitems.marcxml,collection/record/datafield[@tag='520'][1]/subfield[@code='a'])
FROMstatistics
JOINitems ON (items.itemnumber=statistics.itemnumber)
LEFT JOIN   biblio ON (biblio.biblionumber=items.biblionumber)
LEFT JOIN   biblioitems ON (biblioitems.biblioitemnumber =
items.biblioitemnumber)
WHERE   DATE(statistics.datetime) BETWEEN '2011-01-01' AND '2011-07-01'
   AND statistics.itemnumber IS NOT NULL
   AND location = 'Shelving Location Code'
GROUP BYbiblio.biblionumber
ORDER BYcircs DESC
LIMIT   10

Gets this:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that 
corresponds to your MySQL server version for the right syntax to use near '     
  statistics JOIN            items ON (items.itemnumber=statistic' at line 4


Also, a tip - those  sections I had in there are runtime parameters for 
Koha (a neat trick that is documented on the wiki and in the manual - in case 
you removed them cause you didn't know what they were).


On Thu, Jul 28, 2011 at 7:41 PM, Daniel Grobani dgrob...@samuelmerritt.edu 
wrote:
 Try this (I also did some light cleanup):

 SELECT          COUNT(statistics.datetime) AS circs,
                CONCAT(' \/cgi- ',biblio.title,' ') AS Title,
                CONCAT(' \http://mycatalogv/cgi- 
 http://catalog.losgatosca.gov/cgi-bin/koha/opac-detail.pl?biblionumber=',biblio.biblionumber,'
 ') AS URL,
                biblio.author,
                items.location,

 extractValue(biblioitems.marcxml,collection/record/datafield[@tag='52
 0'][1]/subfield[@code='a'])
 FROM            statistics
 JOIN            items ON (items.itemnumber=statistics.itemnumber)
 LEFT JOIN       biblio ON (biblio.biblionumber=items.biblionumber)
 LEFT JOIN       biblioitems ON (biblioitems.biblioitemnumber =
 items.biblioitemnumber)
 WHERE           DATE(statistics.datetime) BETWEEN '-mm-dd' AND 
 '-mm-dd'
                        AND statistics.itemnumber IS NOT NULL
                        AND location = 'Shelving Location Code'
 GROUP BY        biblio.biblionumber
 ORDER BY        circs DESC
 LIMIT           10


 -
 Cheers,
 Daniel Grobani
 Library Technology Specialist
 John A. Graziano Memorial Library
 Samuel Merritt University

 --
 View this message in context: 
 http://koha.1045719.n5.nabble.com/Report-Help-Extracting-XML-tp4640604
 p4644634.html Sent from the Koha - Discuss 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

___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report Help - Extracting XML

[Daniel Grobani]

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 Merritt University

--
View this message in context: 
http://koha.1045719.n5.nabble.com/Report-Help-Extracting-XML-tp4640604p4640701.html
Sent from the Koha - Discuss 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

Re: [Koha] Report Help

[Chris Nighswonger]

 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]')
 AS lcsh FROM biblioitems)
 AS subjects
 WHERE lcsh
 LIKE College%
 OR lcsh LIKE Seminary%
 GROUP BY lcsh


 What's happening is if there is more than one 610 on a bib record it's
 showing them both on one line.  I tried using DISTINCT, but that's
 didn't do the trick, so I'm up for ideas.


I did some work with ExtractValue and marcxml a while back and IIRC this has
to do with the way ExtractValue returns the data from the xml. I was unable
to find a workaround at the time, but perhaps someone else has found one.

Kind Regards,
Chris
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report Help

[Ian Walls]

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 differentiating between the multiple
fields aside from their order, there isn't much that can be done here.  And
if the desired 610 field isn't the first in the series of them, the LIKE
clause won't match; you'd need % in front, as well.

MARC for the fail.. :(


-Ian

2011/7/1 Chris Nighswonger cnighswon...@foundations.edu

 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]')
 AS lcsh FROM biblioitems)
 AS subjects
 WHERE lcsh
 LIKE College%
 OR lcsh LIKE Seminary%
 GROUP BY lcsh


 What's happening is if there is more than one 610 on a bib record it's
 showing them both on one line.  I tried using DISTINCT, but that's
 didn't do the trick, so I'm up for ideas.


 I did some work with ExtractValue and marcxml a while back and IIRC this
 has to do with the way ExtractValue returns the data from the xml. I was
 unable to find a workaround at the time, but perhaps someone else has found
 one.

 Kind Regards,
 Chris

 ___
 Koha mailing list  http://koha-community.org
 Koha@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.wa...@bywatersolutions.com
Twitter: @sekjal
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report Help

[Chris Nighswonger]

 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 a single,
 space-delimited string.


The sad thing is if it was comma or otherwise delimited we could do some
splitting foo. But spaces will inevitably appear in the date in the
specified fields.

Kind Regards,
Chris
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report Help

[Frédéric Demians]

  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 example, on Linux Shell:

   mysql my_koha_db_name -e 'SELECT marc FROM biblioitems' | \
   yaz-marcdump | grep ^610 | sort | uniq

After grep ^610, you can add a text filter to extract just $a without
field name and other subfields.

-- 
Frédéric DEMIANS
http://www.tamil.fr/u/fdemians.html

___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report Help

[Chris Nighswonger]

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 date in the
  specified fields.

 Chris, in this library's case (cause they do have professional
 catalogers) the subject headings all end in periods ... could we use
 that instead of the comma/space?


Assuming there were no periods embedded anywhere else in the data its
possible. The 'foo' I mentioned amounts to writing a stored procedure in
your db do handle the splitting since MySQL does not have a splitting
function. So it would require some coding apart from the reports tool in
Koha.

Kind Regards,
Chris
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help

[Church, Beverly]

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:

 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 bchu...@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: bchu...@liblime.com
  skype: beverlychurch
 
 
  On Wed, May 25, 2011 at 3:08 PM, Nicole Engard neng...@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 neng...@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 
 bchu...@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: bchu...@liblime.com
   skype: beverlychurch
  
  
   On Wed, May 25, 2011 at 12:10 PM, Nicole Engard neng...@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@lists.katipo.co.nz
   http://lists.katipo.co.nz/mailman/listinfo/koha
  
  
  
 
 

___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [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 bchu...@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: bchu...@liblime.com
 skype: beverlychurch


 On Wed, May 25, 2011 at 4:23 PM, Nicole Engard neng...@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 bchu...@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: bchu...@liblime.com
  skype: beverlychurch
 
 
  On Wed, May 25, 2011 at 3:08 PM, Nicole Engard neng...@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 neng...@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 
 bchu...@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: bchu...@liblime.com
   skype: beverlychurch
  
  
   On Wed, May 25, 2011 at 12:10 PM, Nicole Engard neng...@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@lists.katipo.co.nz
   http://lists.katipo.co.nz/mailman/listinfo/koha
  
  
  
 
 



 ___
 Koha mailing list  http://koha-community.org
 Koha@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.wa...@bywatersolutions.com
Twitter: @sekjal
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help

[Daniel Grobani]

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 worked fine, but it bombs when I run
it as a report in our production instance. I suspect LLEK's production
version is still pre-5.1.

Cheers,
Daniel Grobani
Library Technology Specialist
John A. Graziano Memorial Library
Samuel Merritt University


--
View this message in context: 
http://koha.1045719.n5.nabble.com/Report-help-tp4425796p4426913.html
Sent from the Koha - Discuss 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