Each report type is generated from a text file stored in
This document is intended as a reference manual for the RPT language. For a gentler introduction to report writing, see Writing Reports.
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
Each of the three sections is described in detail below.
The declarations section tells OpenBiblio how the report should be listed and displayed. There are four declarations that can be made:
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.
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:
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:
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:
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"
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 . 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:
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 allows for grouping parameters that are associated and may be repeated together. It is seldom used, and not yet documented.
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
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 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.
Control directives modify the query or results in ways that simple variable substitution could not. The available directives are described below.
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:
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:
This directive is replaced by the SQL
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.