Reports

2007-09-05
2013-05-30
  • Joe Hagerty
    Joe Hagerty
    2007-09-05

    I am porting all the reports in the "Reports...Reports...Reports..." thread to 0.60 and have a question.  I have a report def:

    .title "Public Domain Texts"
    .category Cataloging

    .column bibid hidden
    .column title title=Title func=biblio_link sort=title
    .column year title=Year sort=year
    .sql
            SELECT biblio.bibid AS bibid, biblio.title, biblio_field.field_data AS year
            FROM biblio_field
            JOIN biblio ON biblio_field.bibid=biblio.bibid
            WHERE biblio_field.tag='260' AND biblio_field.subfield_cd='c'
            AND biblio_field.field_data< YEAR(NOW())-75
            AND LOCATE("[",biblio_field.field_data)<1
    .end sql

    But the sorts are not working.  Is it because they have to be added as parameters?

     
    • I hope you're not porting ALL the reports, because some (especially those submitted by me) are probable candidates for improving SQL ...
      OR ... have been improved already ... :-)

      See also:
      http://obiblio.sourceforge.net/index.php/Reports/Reports

      In response to your question regarding sorting on the "Public Domain Texts" report, the code below works for me; the difference with your version are sections starting with string .    order_by"

      .title "Public Domain Texts"
      .category Cataloging
      .column bibid hidden
      .column title title=Title func=biblio_link sort=title
      .column year title=Year sort=year
      .parameters
      .    order_by default=title
      .        item title title="Title"
      .        item year title="Date of Publication" type=date
      .    end order_by
      .end parameters
      .sql
      SELECT biblio.bibid AS bibid, biblio.title, biblio_field.field_data AS year
      FROM biblio_field
      JOIN biblio ON biblio_field.bibid=biblio.bibid
      WHERE biblio_field.tag='260' AND biblio_field.subfield_cd='c'
      AND biblio_field.field_data< YEAR(NOW())-75
      AND LOCATE("[",biblio_field.field_data)<1
      .    order_by_expr
      .end sql

      One more thing... my testing data returns every "year" starting with "cop.", or any other alphabetical string entered in the '260 c' field. Perhaps this is not a problem with your data.

      Regards,

      Hans.

       
      • Joe Hagerty
        Joe Hagerty
        2007-09-06

        1)  I was going to port everything I couldn't already find.  As for the SQL, I know mine is horrible and should be redone, can I assume that you have volunteered to review all the SQL statements if I convert everything else ;0)

        2)  And thanks for the order_by info.  That worked for me as well.

        3)  Yeah... I have pruned all my 260c fields to just represent years, or have it enclosed in square brackets.

        Thanks again!
        RevJoe

         
        • > can I assume that you have volunteered to review all the SQL statements [...]

          :-D

          Serious: volunteer work for OpenBiblio is not possible currently, sorry. At this time I can only accept work for pay.

           
    • Hi,
      One Question:

      What is the Report syntax for the
      .column line
      for a field like '260 c' ?

      Thank
      Bastor

       
      • Micah Stetson
        Micah Stetson
        2007-11-07

        It all depends on what you name the column in your SQL results.  Supose you have SQL like this:

        SELECT b.bibid, b.title, f.field_data
        FROM biblio b, biblio_field f
        WHERE f.bibid=b.bibid AND f.tag='260' AND f.subfield_cd='c'

        Then your column lines could look like this:

        .column bibid hidden
        .column title title="Title"
        .column field_data title="Publication date"

        The point is that the name after .column has to be the name of the field as returned by MySQL.  Suppose you said "SELECT ... f.field_data AS pubdate ..." instead, then you'd need to do ".column pubdate ...".  I hope that clears things up,

        Micah