OpenBiblio | Main / WritingReports browse

Note: This page only applies to OpenBiblio 0.6.0 and later. OpenBiblio versions 0.5.2 and earlier use a completely different syntax for writing reports.

So you want some information from OpenBiblio. You know the information is there, you just don't know how to get at it. This how-to is for you.

If you've already got the information in a report, but you want to know how to format it into letters or labels or some other format, what you need is a layout. See: Report Layouts.

If you already know basically how to write reports, but you need more information about one of the report language's features, see RPT Syntax.

Jump Start

Someone may already have written the report you need or one that you can modify to fit. This tutorial will help you start from scratch, but you may not have to. Take a look at the reports that have already been written.

Prerequisites

In order for this tutorial to be much help to you, there are a few things you should understand first. You need to know how to make a plain text file and place it in a folder inside your OpenBiblio installation. You also should have at least a basic understanding of SQL and OpenBiblio's database structure. You can Google "sql tutorial" for a start on SQL. For OpenBiblio's database structure, you'll either have to figure it out on your own or ask questions on the Forums. There isn't any real documentation for the database format yet.

Getting started

First, we need to know what information we want from the database. For this how-to, we'll write a report that shows us the average age of the items in our collections. We can guage the age of an item by it's publication date which should be stored in MARC field 260$c. Here's an SQL query to select all the publication dates in the database:

SELECT bibid, field_data
    FROM biblio_field
    WHERE tag="260" AND subfield_cd="c"

Now the average date:

SELECT AVG(field_data)
    FROM biblio_field
    WHERE tag="260" AND subfield_cd="c"

But we want the dates listed by collection, so we need to pull in the biblio and collection_dm tables:

SELECT c.code, c.description collection, AVG(f.field_data) date
    FROM biblio b, biblio_field f, collection_dm c
    WHERE f.bibid=b.bibid AND c.code=b.collection_cd
        AND f.tag="260" AND f.subfield_cd="c"
    GROUP BY c.code, c.description

So, having the basic idea of the report and some SQL to back it up, we can create a basic report for OpenBiblio:

.title "Average Publication Date"
.column code
.column collection
.column date
.sql
    SELECT c.code, c.description collection, AVG(f.field_data) date
        FROM biblio b, biblio_field f, collection_dm c
        WHERE f.bibid=b.bibid AND c.code=b.collection_cd
            AND f.tag="260" AND f.subfield_cd="c"
        GROUP BY c.code, c.description
.end

If you put this into a file with a .rpt extension and save it in the openbiblio/reports/defs folder, you should see the new report on your report list called "Average Publication Date". Clicking on that report, you'll be asked in what format you want the results. For now, leave it at the default, "HTML (page-by-page)". Hitting "Submit", you should be shown a table with three columns and one row per collection. (If a collection doesn't have any items with a 260$c field, it won't be shown.)

So what have we done here? The SQL query itself is exactly the same one we wrote initially, but we've use the RPT language to tell OpenBiblio a little about how to run it and how to show us the results. To do this, we added directives to describe the report to OpenBiblio. Directives in the RPT language are entered on a line that starts with a period.

The first directive in this report, title, tells OpenBiblio what to call the report. After the .title directive, we have a space and the title itself. The title usually needs to be placed in quotes -- if it contained only ASCII letters, numbers, and underscores (_), we wouldn't need the quotes.

Next we tell OpenBiblio which columns to display. To do this, we have a line for each column that begins .column, and then has the SQL name or alias of the column. After this, we have the SQL itself, preceded by a line .sql and followed by a .end line.

That's all you need for a very basic report.

Prettifying

There are a few aesthetic improvements we could make on this report. First, it appears in a category on the reports list called "Misc." That probably isn't what we want. We'd like it to appear in the "Cataloging" category, or perhaps a new one called "Statistics". To move the report into a different category, we just have to add the following directive after the title line:

.category Statistics

That will put our report in the "Statistics" category, which will be created if it doesn't exist already. You'll notice we didn't put "Statistics" in quotes. We could have, and we would have to if the category name had spaces or non-alphanumeric characters in it. But "Statistics" is just one word, so the quotes are optional.

Next, we really don't need to see the collection code in the report results. Why do we care what the code number for the collection is? We don't. The simplest way to remove that column is just to delete the '.column code' line. OpenBiblio doesn't care if there are more columns in the SQL results than you specify, it only shows the ones you tell it to.

Now the remaining columns need more readable titles. Right now, the columns in the results table are labeled 'collection' and 'date'. We'd like prettier names, so we'll change those column lines like this:

.column collection title=Collection
.column date title="Avg. Pub. Date"

We added options to the column directive. A column can have a number of options, but the one called 'title' changes the column's title shown in the report results. Again, notice that we didn't have to use quotes around "Collection", but they were necessary around "Avg. Pub. Date".

So here's the report so far:

.title "Average Publication Date"
.category Statistics
.column collection title=Collection
.column date title="Avg. Pub. Date"
.sql
    SELECT c.code, c.description collection, AVG(f.field_data) date
        FROM biblio b, biblio_field f, collection_dm c
        WHERE f.bibid=b.bibid AND c.code=b.collection_cd
            AND f.tag="260" AND f.subfield_cd="c"
        GROUP BY c.code, c.description
.end

Sorting

The last improvement we'd like to make to this report is to make it sortable. Odds are, there aren't that many rows in the results here, but you'd still like them to be in order. You could simply add 'ORDER BY c.description' to the end of the SQL, but that wouldn't be too flexible. In order to allow the user to change the sort order, we need to add a parameter to the report. Parameters allow the user to change the report a bit when it's run. They're defined in a section of the RPT file called parameters. Here is a simple parameter section:

.parameters
.    order_by
.        item collection
.    end
.end

Place this code before the .sql directive. Now, when you run the report, you'll see an additional drop-down menu above the 'Format' option. It will let you select between sorting by 'collection' or 'collection (Reverse)'. But OpenBiblio never modifies your SQL without being told to explicity, so this sorting won't work without some way of getting the ORDER BY clause into the SQL statement. To do this, we need to add a '.order_by_expr' directive to the '.sql' section where you want the ORDER BY clause to be inserted. Here's the updated report:

.title "Average Publication Date"
.category Statistics
.column collection title=Collection
.column date title="Avg. Pub. Date"
.parameters
.    order_by
.        item collection
.    end
.end
.sql
    SELECT c.code, c.description collection, AVG(f.field_data) date
        FROM biblio b, biblio_field f, collection_dm c
        WHERE f.bibid=b.bibid AND c.code=b.collection_cd
            AND f.tag="260" AND f.subfield_cd="c"
        GROUP BY c.code, c.description
.   order_by_expr
.end

To make sorting more convenient, we can add sort buttons to a column. To add the buttons for the 'collection' sort type to the 'collection' column, we add a sort option to the column like this:

.column collection title=Collection sort=collection

We'd also like to sort by the date column, but the obvious solution doesn't always work. Adding a sort on the date column identical to the one on the collection column may not work because some versions of MySQL don't support sorting on aliases for functions. So we can't have ORDER BY date in the SQL query, we need ORDER BY AVG(f.field_data). To accomplish this, we add the sort item with an expr option:

.       item date expr="AVG(f.field_data)"

There's still a problem, though. By default, OpenBiblio's sort does some processing on the column value to make alphanumeric fields with leading numbers sort correctly. When dealing with straight numeric values, though, it doesn't work properly. So we need to tell OpenBiblio that we're not sorting an alphanumeric value, but a straight numeric one, like this:

.       item date expr="AVG(f.field_data)" type=numeric

Finally, we add sort buttons to the date column. Here's the final report:

.title "Average Publication Date"
.category Statistics
.column collection title=Collection sort=collection
.column date title="Avg. Pub. Date" sort=date
.parameters
.    order_by
.        item collection
.        item date expr="AVG(f.field_data)" type=numeric
.    end
.end
.sql
    SELECT c.code, c.description collection, AVG(f.field_data) date
        FROM biblio b, biblio_field f, collection_dm c
        WHERE f.bibid=b.bibid AND c.code=b.collection_cd
            AND f.tag="260" AND f.subfield_cd="c"
        GROUP BY c.code, c.description
.   order_by_expr
.end

More complicated reports

FIXME Need to introduce these topics: