Recent Changes Printable View Page History Edit Page

Each report type is generated from a text file stored in openbiblio/reports/defs, nothing else is needed. Each report must be in its own file. Reports may be written in the RPT language, or directly in PHP. This document describes the RPT language; writing reports in PHP is not covered here.

This document is intended as a reference manual for the RPT language. For a gentler introduction to report writing, see Writing Reports.

Basic syntax

Every non-blank line in an RPT file contains either directives or SQL. Lines that contain directives start with a '.' in their first column, other lines must either be blank or contain SQL. On directive lines, the first word after the '.' is the directive. Syntax for the rest of the line depends on which directive is being used.

Some directives take a list of options. These directives either take a single value, a list of values and name/value pairs, or a single value followed by a list of values and name/value pairs. Individual values or name/value pairs are separated by spaces. A name/value pair is just two single values separated by '='. Strings and numbers are not treated differently.

Values must be quoted if they contain any character other than ASCII alphanumerics or underscore ('_'). Either single or double quotes may be used. To include a literal quote character in a quoted string, precede it with a backslash ('\'). To include a backslash, use two blackslashes. For example: 'Mrs. O\'Leary\'s cow', "Here's a backslash: \\".

Some directives start sections (.parameters, .sql, .if_set, etc.). These sections are ended with the .end directive. By convention, the name of the directive that started the section is used as a parameter to the end directive (e.g. .end sql), but this is only for clarity -- the language doesn't require it or check it for validity.

The RPT file can be thought of in three sections:

Here is an oversimplified example:

.title Example
.category Useless
.column param title=Parameter

.parameters
.	string param title=Parameter
.end parameters

.sql
	select %param% as param
.end sql

If you were to place this report in a file named example.rpt in the openbiblio/reports/defs directory, then a new report named 'Example' would appear in the 'Useless' report category on your reports list. When you ran that report, you'd be prompted for one parameter called 'Parameter'. On submitting the parameter form, you would be shown a single row of results with a single column called 'Parameter'. That column would display whatever you had typed in the box. In this example, we've separated the three sections by blank lines to make it easy to see them. That isn't required.

Each of the three sections is described in detail below.

Declarations

The declarations section tells OpenBiblio how the report should be listed and displayed. There are four declarations that can be made:

.title Title

This directive takes a single value, defining the name of the report as shown to users. If omitted it will default to the path of the .rpt file. It is not repeatable.

.category Category

This directive takes a single value, defining the category under which the report will be presented in the report list. If omitted it will default to 'Misc.'. If a report claims to be in a category that doesn't otherwise exist, a new category is created for it. This directive is not repeatable.

.layout layout_name [option=value ...]

This adds an available results view to the list. If omitted the only available views of the results will be the default report results table and the print view (PDF version of the table). Any additional layouts listed here should already be defined. It is repeatable. The options passed to the layout are specific to the layout class. See: Report Layouts.

.column name [option=value ...]

This defines a column in the default results table. There should be one '.column' entry for each column (or alias) returned by the SQL statement later in the file. If a column is not included here, it is hidden by default in the results.

Each column accepts a number of options:

hidden
Hides this column in default results
sort=name
Allows a column to be sorted in the default resultsaccording to the name sort-type, see below.
func=name
Performs a function on the results in the column, see below.
title=name
Used as a column header in the default results

Available func's

member_link
Links to the patron record (requires mbrid as a result column; use '.column mbrid hidden' to hide)
biblio_link
Links to the bibliographic record (requires bibid as a result column; use '.column bibid hidden' to hide)

Parameters

This section defines the parameters that are available to the SQL section and controls the form presented to the user when requesting a report. This section starts with a .parameters directive, and ends with the matching .end directive.

Each command in this section defines a parameter to be gathered in a form before running the report. These parameters are available to be used in the SQL section, see below.

There are two simple parameter types: string and date. String allows the user to enter anything in a text input field. Date also presents the user with a text input field, but it interprets any entry as a date and converts it to a format acceptable to MySQL. Date understands a number of common date formats as well as the words 'today', 'now', 'yesterday', and 'tomorrow'.

Each simple parameter is declared by naming its type, followed by the name that will be used to refer to it in the SQL section, possibly followed by any options. Here are some examples:

.string title title=Title
.date checkout_dt title="Checkout Date" default=today

These two lines declare a string parameter called 'title' that the user will see as 'Title' and a date parameter called 'checkout_dt' that the user will see as 'Checkout Date' with a default value of 'today'.

Here are the possible options:

title
The name of the parameter, as seen by the user. Defaults to the parameter's name.
default
The default value for the parameter. Defaults to empty.
repeatable
If this is specified, the parameter may be repeated. Defaults to off.

Repeatable parameters will appear four times on the form and may be used with the .foreach_parameter directive in the SQL section.

The other parameter types are select, order_by, session_id, and group. These each deserve a bit of discussion:

select

A select parameter creates a drop-down list. It is specified exactly like a simple parameter, except that its declaraton starts a section that must be ended with an end directive. The items to appear on the drop-down list are specified in the section in one of two ways: either as .item declaration or with .sql sections.

An item declaration is very much like a simple parameter declaration. The form is '.item value' followed by optional parameters. The specified value is the value that can be selected from the drop-down menu. If an optional 'title=' parameter is specified, then that is displayed in the menu instead of the value.

An SQL section is specified very similarly to the overall SQL sections described below, but it contains a query that is run to get option values (and possibly titles) for the drop-down box. Values must be in a column named 'value', and parameters (i.e. title) must be in columns named like the parameter.

Here is an example for a select declaration that would let the user choose a material type or 'Any':

.select material default=any
.	item any title=Any
.	sql
		SELECT code AS value, description AS title
		FROM material_type dm
.	end sql
.end select

Note: if the SQL section in a RPT .select parameter is wrapped to more than one line you will get error: Unexpected token "SQLCODE" expecting "end"
More info about preventing this error: discussion thread about this "silly little bug".

order_by

An order_by parameter defines the ways that a report's results can be sorted. It is very similar to a select, but it does not take a name as there can be only one order_by parameter per report, and its value is not used in the SQL section in the same way as other parameters' values. Each item in the order_by list actually generates two sort options, the one name (e.g. title) and the reverse, which has the same name, but with '!r' appended (e.g. title!r).

Normally, an order_by item specifies that the report may be ordered by the column with the same name as the item. Sometimes a more complicated SQL expression is needed to achieve the desired order. In that case, the order_by item is given an additional option, called expr, whose value is used instead of the column name in the ORDER BY clause. For example:

.order_by
.	item name title="Name" expr="concat(last_name, first_name)"
.end

This order_by item, instead of ordering by a column called 'name', which may or may not exist, orders by the concatenation of the last_name and first_name columns.

Order_by items may also take a type option that specifies how the sorting should be performed. Type can have the following values:

alnum
Alphanumeric sorting. This is the default. Leading numbers are sorted properly, i.e. 200 comes before 1000
numeric
Actually just a raw sort, useful on numeric fields
date
Actually just a raw sort, useful on date fields
MARC
Just like alnum, but requires an additional skip_indicator option on the sort item. Skip_indicator's value should be an SQL expression that yields the number of characters to skip when sorting the field. This is useful for mark fields like 245 where the number of non-filing characters is stored in a tag's indicator.
multi
Multi-column sort. Requires the expr option to be set to a comma-separated list of the names of other order_by items. It then sorts by each of those in order.

session_id

There can be only one session_id parameter in a parameter list. It does not take a name, and it does not cause a form element to appear on the report parameters page. It does make a parameter named 'session_id' available to the SQL section that contains the current user's PHP session ID. Its use is beyond the scope of this document.

group

Group allows for grouping parameters that are associated and may be repeated together. It is seldom used, and not yet documented.

SQL

SQL sections define the queries that return data for the report. An SQL section begins with the .sql directive and ends with the matching .end directive. Any number of SQL sections may be included in a report. Each of these sections should contain a single SQL statement. The report's results are the results of the last SELECT statement. Multiple SQL sections are useful if your report needs to create and delete temporary tables.

The query inside of an SQL section is never automatically modified by OpenBiblio. But when writing a report you can cause OpenBiblio to modify your queries by using variable substitution and control directives.

Variable Substitution

Variable substitution allows you to include parameter values in your queries. Each line in an SQL section that isn't a directive (i.e. doesn't begin with '.') is scanned for the following sequences. If one of those sequences is found, it is replaced with the value indicated. In this list, name is the name of a parameter defined in the parameters section above.

%name%
The value of the name parameter, quoted for use as a string
%#name%
The value of the name parameter, converted to a number
%.name%
The value of the name parameter, quoted for use as a table or column name
%"name%
Just like %name%, but the surrounding quotes are not added
%`name%
Just like %.name%, but the surrounding back-quotes are not added
%!name%
The value of the name parameter is placed directly in the query
DO NOT USE THIS unless you know exactly what you're doing.
%%
A literal '%' is placed in the query at this point

Control directives

Control directives modify the query or results in ways that simple variable substitution could not. The available directives are described below.

"If" directives

All of the directives that start with '.if_' behave similarly. Their syntax is as follows:

.	if_<whatever> <condition>
		<query to be used if condition is true>
.	else
		<query to be used if condition is false>
.	end

The else part is optional and may be omitted. The individual "if" directives are described below:

.if_set parameter
True if the named parameter has a non-empty value
.if_equal parameter value
True if the named parameter's value is equal to value
.if_not_equal parameter value
True if the named parameter's value is not equal to value

"Foreach" directives

The directives that start with '.foreach_' behave similarly. Their syntax is as follows:

.	foreach_<whatever> <parameter>
		<query part>
.	end

The query part is repeated a certain number of times with the named parameter set to a different value each time. The individual "foreach" directives are described below:

.foreach_parameter name
For a repeatable parameter name, runs the query part for each value of name with name set to that value.
.foreach_word name
Splits the value of the name parameter into words, honoring double quotes; runs the query part for each word with name set to the word

.order_by_expr

This directive is replaced by the SQL ORDER BY expression corresponding to the selected order_by parameter.

.subselect name

This directive may only appear at the end of an SQL section. It contains a separate SQL section that is run once for each row returned by the enclosing SQL section. It has access to the parameters defined for the report as well as an additional parameter for each column in the rows of enclosing SQL sections. The subselect query is run, and its reports are placed in an array as a column named name on each row of the enclosing SQL query's results. Subselects may be repeated and nested arbitrarily.

Subselects can sometimes be very useful for complex reports intended for custom layouts. Without special effort, their results do not display properly in the default views and layouts. Use with care.

SourceForge.net Logo
Edit Page - Page History - Printable View - Recent Changes - Search
Page last modified on October 30, 2007, at 08:19 PM