Reports ... Reports ... Reports

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>

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>

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

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 = '...';";

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>

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???

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

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. 

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