Work at SourceForge, help us to make it a better place! We have an immediate need for a Support Technician in our San Francisco or Denver office.

Close

Reports ... Reports ... Reports

2004-10-15
2013-05-30
1 2 > >> (Page 1 of 2)
  • Joe Hagerty
    Joe Hagerty
    2004-10-15

    I for one really like the reporting system that we have in openbiblio.  I think Dave did a bang up job.  And I think that others have made reports definitions for reports that are not included in the distribution.... So why not share them....

    Either post them here or email them to me and I will post them for ya...

    Joe

     
    • Joe Hagerty
      Joe Hagerty
      2004-10-15

      I'll go first.....

      Here is a report for biblio's by material type:

      <!--
        *********************************************************************************
        *  sql syntax rules:
        *  1.  Do not code column aliases.  This will mess up the selection criteria page.
        *  2.  Do not specify the sort clause in your sql.  The sort order will be specified
        *      by the user.
        *  3.  You may, but are not required to specify the where clause
        *********************************************************************************
      -->

      <report>
        <id>Material Types</id>
        <title>Biblios By Material Type</title>
        <sql>
      select title, description
      from biblio
      join material_type_dm on biblio.material_cd=material_type_dm.code
        </sql>
      </report>

       
      • These are fabulous reports, and I've searched high and low for one that gives the following list: books added in the previous calendar year. I tried thinking about modifying the recently added items, but I just... can't... seem to figure it out.

        Any help out there?

         
    • Joe Hagerty
      Joe Hagerty
      2004-10-17

      Nother report I wrote to list just the title and the date of publication....

      <!--
        *********************************************************************************
        *  sql syntax rules:
        *  1.  Do not code column aliases.  This will mess up the selection criteria page.
        *  2.  Do not specify the sort clause in your sql.  The sort order will be specified
        *      by the user.
        *  3.  You may, but are not required to specify the where clause
        *********************************************************************************
      -->

      <report>
      <id>PublicDomainTexts</id>
      <title>Possible Public Domain Texts</title>
      <sql>
      SELECT biblio.title, biblio_field.field_data
      FROM biblio_field
      JOIN biblio ON biblio_field.bibid=biblio.bibid
      WHERE biblio_field.tag='260' AND biblio_field.subfield_cd='c'
      </sql><!--  AND biblio_field.field_data&lt;'1911'   -->
      </report>

       
    • Joe Hagerty
      Joe Hagerty
      2004-10-17

      Here's another one I needed tonight.  I do all my filing by collection and author's last name... So I wrote a quick report to check my filing list (helps me make sure I have all the books in obiblio)...

      It might be worth modifying this to include call numbers... could be useful for auditing....

      <!--
        *********************************************************************************
        *  sql syntax rules:
        *  1.  Do not code column aliases.  This will mess up the selection criteria page.
        *  2.  Do not specify the sort clause in your sql.  The sort order will be specified
        *      by the user.
        *  3.  You may, but are not required to specify the where clause
        *********************************************************************************
      -->

      <report>
        <id>shelving</id>
        <title>Shelving Report</title>
        <sql>
      SELECT author, title, collection_dm.description
      FROM biblio
      JOIN biblio_copy ON biblio.bibid=biblio_copy.bibid
      JOIN collection_dm ON biblio.collection_cd=collection_dm.code
      <!--ORDER BY author ASC-->
        </sql>
      </report>

      enjoy

       
      • When report title is 'Shelving Report' my non-English language background says this is the same as inventory. I'm not sure auditing is exactly the same.

        I'm wondering if it is possible to have one standard inventory report that fits all or most libraries. I'll describe here how my library uses inventory. Of course I hope for reactions, suggestions...

        My library's inventory report is similar to the one below.
        Call numbers included. One reason for this is because items belonging to a collection can be shelved in different areas, for example when items have differing dimensions. Also included: copy barcode number, status code, material type, collection description.

        <!--
          *********************************************************************************
          *  sql syntax rules:
          *  1.  Do not code column aliases.  This will mess up the selection criteria page.
          *  2.  Do not specify the sort clause in your sql.  The sort order will be specified
          *      by the user.
          *  3.  You may, but are not required to specify the where clause
          *********************************************************************************
        -->

        <report>
          <id>inventory</id>
          <title>Inventory Report</title>
          <sql>
            select biblio.call_nmbr1
                   ,biblio.call_nmbr2
                   ,biblio.call_nmbr3
                   ,biblio_copy.barcode_nmbr
                   ,biblio_copy.status_cd
                   ,biblio.author
                   ,biblio.title
                   ,material_type_dm.description
                   ,collection_dm.description
                   ,biblio_copy.status_begin_dt
            from   biblio left join biblio_copy on biblio.bibid=biblio_copy.bibid left join collection_dm on biblio.collection_cd=collection_dm.code left join material_type_dm on biblio.material_cd = material_type_dm.code
          </sql>
        </report>

        When printing report above one might change paper orientation to landscape. Or try alternative report below.

        <!--
          *********************************************************************************
          *  sql syntax rules:
          *  1.  Do not code column aliases.  This will mess up the selection criteria page.
          *  2.  Do not specify the sort clause in your sql.  The sort order will be specified
          *      by the user.
          *  3.  You may, but are not required to specify the where clause
          *********************************************************************************
        -->

        <report>
          <id>inventory2</id>
          <title>Inventory Report (Author/Title limited to 20 chars)</title>
          <sql>
            select biblio.call_nmbr1
                   ,biblio.call_nmbr2
                   ,biblio.call_nmbr3
                   ,biblio_copy.barcode_nmbr
                   ,biblio_copy.status_cd
                   ,SUBSTRING(biblio.author,1,20) as Author
                   ,SUBSTRING(biblio.title,1,20) as Title
                   ,material_type_dm.description
                   ,collection_dm.description
                   ,biblio_copy.status_begin_dt
            from   biblio left join biblio_copy on biblio.bibid=biblio_copy.bibid left join collection_dm on biblio.collection_cd=collection_dm.code left join material_type_dm on biblio.material_cd = material_type_dm.code
          </sql>
        </report>

        Example
        Another reason for using call number is because we use the collection field to distinguish between reading levels for Dutch fiction (Ned.). The collection field for Dutch fiction bibliographies can be: Ned. A, Ned. B, Ned. C, etc. They have in common field Call 1 = NED because they are shelved in one area.

        Example inventory report criteria for subdivided collection
        Criteria 1:  Call 1 = NED

        Report Sort Order (optional) 
        Sort 1:  Call 3 ascending
        In most cases Call 3 is first 4 letters from author name, in some cases (for example biography of an author) first 4 letters of the subject. This conforms to our shelving rule that an authors biography should be shelved together with the books he has written.
        Sort 2:  Author ascending
        Sort 3:  Title  ascending

        Not covered here: how to ignore title initial articles in sorting.

         
    • Thanks Dave; by making creative use of the reporting system we can have some of the functionality already that is to be expected in the release version much later.

      This report checks for duplicate biblio entries.

      <!--
        *********************************************************************************
        *  sql syntax rules:
        *  1.  Do not code column aliases.  This will mess up the selection criteria page.
        *  2.  Do not specify the sort clause in your sql.  The sort order will be specified
        *      by the user.
        *  3.  You may, but are not required to specify the where clause
        *********************************************************************************
      -->

      <report>
        <id>duplicateBiblio</id>
        <title>Duplicate Title - Author combinations</title>
        <sql>
          select biblio.title
                 ,biblio.author
                 ,count(*) as 'Duplicates'
          from biblio
          group by biblio.title, biblio.author
          having count(*) > 1
        </sql>
      </report>

       
    • Check for duplicate member entries

      <!--
        *********************************************************************************
        *  sql syntax rules:
        *  1.  Do not code column aliases.  This will mess up the selection criteria page.
        *  2.  Do not specify the sort clause in your sql.  The sort order will be specified
        *      by the user.
        *  3.  You may, but are not required to specify the where clause
        *********************************************************************************
      -->

      <report>
        <id>duplicateMember</id>
        <title>Duplicate Member Names</title>
        <sql>
          select member.last_name
                 ,member.first_name
                 ,count(*) as 'Duplicates'
          from member
          group by member.last_name, member.first_name
          having count(*) > 1
        </sql>
      </report>

       
    • Because I think the original 'Popular Bibliographies' lists Popular Copies I propose this code change. The original report can be renamed Popular Items or something...

      <!--
        *********************************************************************************
        *  sql syntax rules:
        *  1.  Do not code column aliases.  This will mess up the selection criteria page.
        *  2.  Do not specify the sort clause in your sql.  The sort order will be specified
        *      by the user.
        *  3.  You may, but are not required to specify the where clause
        *********************************************************************************
      -->

      <report>
        <id>popularBiblios</id>
        <title>Popular Bibliographies</title>
        <sql>
          select biblio.title
                 ,biblio.author
                 ,count(*) checkoutCount
          from   biblio_status_hist, biblio_copy, biblio
          where  biblio_status_hist.bibid = biblio_copy.bibid
                 and biblio_status_hist.copyid = biblio_copy.copyid
                 and biblio_status_hist.bibid = biblio.bibid
                 and biblio_status_hist.status_cd = 'out'
          group by biblio.title
                 ,biblio.author
        </sql>
      </report>

       
    • And Popular authors, of course...

      <!--
        *********************************************************************************
        *  sql syntax rules:
        *  1.  Do not code column aliases.  This will mess up the selection criteria page.
        *  2.  Do not specify the sort clause in your sql.  The sort order will be specified
        *      by the user.
        *  3.  You may, but are not required to specify the where clause
        *********************************************************************************
      -->

      <report>
        <id>popularAuthors</id>
        <title>Popular Authors</title>
        <sql>
          select biblio.author
                 ,count(*) checkoutCount
          from   biblio_status_hist, biblio_copy, biblio
          where  biblio_status_hist.bibid = biblio_copy.bibid
                 and biblio_status_hist.copyid = biblio_copy.copyid
                 and biblio_status_hist.bibid = biblio.bibid
                 and biblio_status_hist.status_cd = 'out'
          group by biblio.author
        </sql>
      </report>

       
    • Why not Popular collections?

      <!--
        *********************************************************************************
        *  sql syntax rules:
        *  1.  Do not code column aliases.  This will mess up the selection criteria page.
        *  2.  Do not specify the sort clause in your sql.  The sort order will be specified
        *      by the user.
        *  3.  You may, but are not required to specify the where clause
        *********************************************************************************
      -->

      <report>
        <id>popularCollecs</id>
        <title>Popular Collections</title>
        <sql>
          select collection_dm.description
                 ,count(*) checkoutCount
          from   biblio_status_hist, biblio_copy, biblio
          left join  collection_dm on biblio.collection_cd = collection_dm.code
          where  biblio_status_hist.bibid = biblio_copy.bibid
                 and biblio_status_hist.copyid = biblio_copy.copyid
                 and biblio_status_hist.bibid = biblio.bibid
                 and biblio_status_hist.status_cd = 'out'
          group by biblio.collection_cd
        </sql>
      </report>

       
    • Browse a list of bibliographies ordered by classification number. Title is a link to OPAC.

      Code to be changed for your library:
      - First line starting with ,concat for your library's OPAC URL.
      - Where clause. The most common classification system in the Netherlands is SISO. My library stores it in local MARC field 964 a. Change this for your classification system.

      Thanks to Joe for the hint on '&lt;' in the 'new Arrivals' thread, very useful.

      The presentation would be a little cleaner if the report could be grouped by biblio_field.field_data (one line for each classification number in the first column) and the rest of the columns would be multiple lines for each biblio categorized under this classification number. Failed there...

      <!--
        *********************************************************************************
        *  sql syntax rules:
        *  1.  Do not code column aliases.  This will mess up the selection criteria page.
        *  2.  Do not specify the sort clause in your sql.  The sort order will be specified
        *      by the user.
        *  3.  You may, but are not required to specify the where clause
        *********************************************************************************
      -->

      <report>
        <id>classificationLink</id>
        <title>Links to Bibliographies with Classification Number: 1) Save as html 2) Delete from html source Section Tabs until this title in Main Body 3) Edit Column Titles and Report Title  4) Place file on webserver  </title>
        <sql>
          select biblio_field.field_data
                 ,concat('&lt;a href=//localhost/openbiblio/shared/biblio_view.php?bibid=',biblio.bibid,'&amp;tab=opac', '&gt;',biblio.title,'&lt;/a&gt;') as Title
                 ,material_type_dm.description
                 ,collection_dm.description
                 ,concat_ws(' ',biblio.call_nmbr1, biblio.call_nmbr2, biblio.call_nmbr3) as Call_Number
          from   biblio left join biblio_field on biblio.bibid = biblio_field.bibid left join collection_dm on biblio.collection_cd = collection_dm.code left join material_type_dm on  biblio.material_cd = material_type_dm.code
          where  biblio_field.tag = '964' and biblio_field.subfield_cd = 'a'
        </sql>
      </report>

       
    • List new entries. Modified a script that was posted as 'new Arrivals' on the Feature Requests, Joe might recognize some of the code ;-)
      Changed the where clause because the original also listed entries that were new a year ago.

      More or less by accident I discovered how to list new entries made in the current month and previous months, not simply from the past n days.

      <!--
      *********************************************************************************
      * sql syntax rules:
      * 1. Do not code column aliases. This will mess up the selection criteria page.
      * 2. Do not specify the sort clause in your sql. The sort order will be specified
      * by the user.
      * 3. You may, but are not required to specify the where clause
      *********************************************************************************
      -->

      <report>
        <id>biblioNew</id>
        <title>New Entries for this month and previous two months</title>
        <sql>
          SELECT concat('&lt;a href=//localhost/openbiblio/shared/biblio_view.php?bibid=',biblio.bibid,'&amp;tab=opac', '&gt;',biblio.title,'&lt;/a&gt;') as Title
                 , author
                 , material_type_dm.description
                 , collection_dm.description
                 , create_dt
          FROM biblio
          LEFT JOIN collection_dm ON collection_dm.code = biblio.collection_cd LEFT JOIN material_type_dm ON biblio.material_cd = material_type_dm.code
          WHERE MONTH( create_dt ) BETWEEN MONTH( CURRENT_DATE )  - 2 AND ( MONTH( CURRENT_DATE )  )
          AND TO_DAYS(sysdate()) - TO_DAYS(create_dt) &lt;= 300
        </sql>
      </report>

       
    • When I find the time I'll use this report to make order in my libraries topics.

      <!--
        *********************************************************************************
        *  sql syntax rules:
        *  1.  Do not code column aliases.  This will mess up the selection criteria page.
        *  2.  Do not specify the sort clause in your sql.  The sort order will be specified
        *      by the user.
        *  3.  You may, but are not required to specify the where clause
        *********************************************************************************
      -->

      <report>
        <id>topics</id>
        <title>Topic Fields Occurence</title>
        <sql>
          SELECT biblio.topic1, biblio.topic2, biblio.topic3, biblio.topic4, biblio.topic5, count( biblio.bibid ) row_count
          FROM biblio
          GROUP BY topic1, topic2, topic3, topic4, topic5
        </sql>
      </report>

       
    • Extra translation strings needed for reports

      $trans["biblio_copy.status_cd"]     = "\$text = 'St.';";
      $trans["collection_dm.description"]     = "\$text = 'Collection';";
      $trans["material_type_dm.description"]     = "\$text = 'Material Type';";
      $trans["biblio_field.field_data"]     = "\$text = '...';";

       
    • Micah Stetson
      Micah Stetson
      2004-11-11

      I really like this Reports thread of Joe's, and I've been meaning to post some reports, but I haven't had time until now.  Thanks for starting this, Joe.

      Here's a report that shows what fines would be due for items in each collection a member has checkouts in, if those items were returned today.  Combined with the standard Balance Due Member List, it can give some idea of the total amount owed to the library.  For a bit more discussion, see the end of this forum post:

      http://sf.net/forum/message.php?msg_id=2848403

      <report>
        <id>late_fees</id>
        <title>Potential Late Fees</title>
        <sql>
          select member.barcode_nmbr,
                 member.last_name,
                 member.first_name,
                 collection_dm.description,
                 sum(greatest(0, to_days(sysdate())-to_days(biblio_copy.due_back_dt)))*collection_dm.daily_late_fee late_fee
            from biblio_copy,
                 member,
                 biblio,
                 collection_dm
            where biblio_copy.bibid=biblio.bibid
                  and collection_dm.code=biblio.collection_cd
                  and biblio_copy.mbrid=member.mbrid
            group by member.mbrid, biblio.collection_cd
        </sql>
      </report>

       
    • Micah Stetson
      Micah Stetson
      2004-11-11

      Here's a report to show the checkout history.  You can limit it to a particular copy barcode or title using the report criteria.

      <report>
        <id>checkout_history</id>
        <title>Checkout History</title>
        <sql>
          select biblio_copy.barcode_nmbr,
                 biblio.title,
                 member.barcode_nmbr,
                 member.last_name,
                 member.first_name,
                 biblio_status_hist.status_begin_dt,
                 biblio_status_hist.due_back_dt
            from biblio_copy,
                 biblio,
                 biblio_status_hist,
                 member
            where biblio_copy.bibid=biblio.bibid
                  and biblio_copy.bibid=biblio_status_hist.bibid
                  and biblio_copy.copyid=biblio_status_hist.copyid
                  and member.mbrid=biblio_status_hist.mbrid
                  and biblio_status_hist.status_cd='out'
        </sql>
      </report>

      Micah

       
    • So basically all I have to do to get any of these reports to show up in the openbiblio/reports/report_list.php page is to create the appropriate xml file and simply drop it into the reportdefs folder in OpenBiblior???

       
      • Micah Stetson
        Micah Stetson
        2004-12-02

        Exactly.

        Micah

         
    • Newtron35
      Newtron35
      2004-12-08

      OK. I tried to install a few of these and I get a white page that says "Error running report".  I checked the file composition...verbatim.  I also checked the /var/log/httpd/error_log and there's some PHP warning about Call-time pass-by reference has been deprecated - argument passed by value.  Don't know if that has anything to do with it or not.

      Also Hans....where does the extra translation strings get inserted??

      Everything else works great.

      Does anyone out there have a report for member list by school, grade, teacher, state, juvinile, adult...etc?

      Thanks, In advance.
      Nate

       
      • There is more information on the 'Error Running Report' page, but it's in HTML comments. View the source, and you can see the actual SQL and the MySQL error message.

        Insert the translation strings in /locale/[2 letter language code]/reports.php
        Find the section #*  Column Text

         
      • Report for members
        Delete fields you don't need in line starting with Select.

        <!--
          *********************************************************************************
          *  sql syntax rules:
          *  1.  Do not code column aliases.  This will mess up the selection criteria page.
          *  2.  Do not specify the sort clause in your sql.  The sort order will be specified
          *      by the user.
          *  3.  You may, but are not required to specify the where clause
          *********************************************************************************

          -->
        - <report>
          <id>members</id>
          <title>reportMembers</title>
          <sql>select member.barcode_nmbr ,member.last_name ,member.first_name ,member.address1 ,member.address2 ,member.city ,member.state ,member.home_phone ,member.work_phone ,member.email ,member.classification ,member.school_grade ,member.school_teacher from member</sql>
          </report>

         
    • Thanks again for the help.  What I was doing was creating the .xml file in emacs.  When I created the same code in Dreamweaver MX as an .xml file, it worked great. 

       
    • Newtron35
      Newtron35
      2004-12-08

      I ammended an earlier entry to also compare the Title remainder of a biblio.  Some MARC records can have Vol II or such in this field. 

      Amend Hans's earlier post for Duplicate Biblio entries to also compare Title remainder fields:

      original:
      group by biblio.title, biblio.author

      to:
      group by biblio.title ,biblio.title_remainder ,biblio.author

      I know this may seem minor, but for those of us who don't deal alot (or very little) with SQL, this may save them some time in the "cut and paste" process of adding this report to their OpenBiblio.

      Thanks,
      Nate

       
    • Any idea why <> in where condition is not acceptable (probably need to escape it.)? I just work around it using not in ....

       
1 2 > >> (Page 1 of 2)