The Anatomy of a Report
A report is made up of a set of standard headers and footers as well as
the report detail rows. Each part except the details is optional. Each part
contains one or more sections. Each section can be turned on or off
(suppressed) and can force a page break.
The parts that make up a report are:
- Report Header
- Page Header
- Group Header
- Detail
- Group Footer
- Report Footer
- Page Footer
Each group has an associated column. Records are
grouped by that column's value. Column subtotals may appear in group
footers and in the report footer.
Parts are made up of one or more sections.
Unfortunately, the word ``section'' is used to mean both a section and a
part containing multiple sections. Usually, either the meaning is clear or
the distinction is not important.
Each section may contains fields. A field may be a
database column, a calculated formula, static text, a running subtotal, a
special value like the report's title or the current page number, or a
parameter field that allows you to specify its value at report run time.
Report Parts
The Report Header is printed once at the beginning
of the report on the first page. Report titles are typically placed here.
The Page Header is printed at the top of each page
(but after the Report Header on the first page). Often, column titles are
placed here. In fact, when you drag a database column into the Detail
section, a title is automatically placed in the Page Header for you.
The Group Header is printed before the first row
of data and again each time a group's value changes. Since groups can be
nested, the group header for each group is printed in order.
The Detail section is printed once for each row
of data.
The Group Footer is printed just before a group's
value changes, and after the last row of data in the report. Since groups
can be nested, the group footer for each group is printed in reverse order.
Subtotals may be added here.
The Report Footer is printed once at the end of
the report on the last page. This is where end-of-report grand totals
belong.
The Page Footer is printed at the bottom of every
page.
Section Suppression Procs
A suppression proc determines if a section should be displayed or not.
Sections are normally displayed always. If you select ``Always hide'' (see
The Suppression Proc
Dialog) then the section is always hidden.
A suppression proc can contain a formula. The formula contains Ruby code that must return a boolean
(true/false) value. For every row, the code is run to determine if the
entire section should be hidden for that row only. When the code returns
true, the section is hidden.
For example, the Ruby code
({jobs.ID} & 1) == 0
will return true when jobs.ID is an even number. If attached to a detail
section, then all detail records whose jobs.ID is an even number would be
hidden. That is, only records with odd-numbered job IDs would be displayed.
Note: Subtotals are updated even for hidden or suppressed
detail rows. If you want to avoid subtotaling rows, don't retrieve them
from the database or use a formula that returns 0 for rows you want to
ignore and subtotal that row instead.
Fields
A field is a single unit of information, whether a database column,
text, or an image.
Database Column
A database column field displays the value of a
column in the database.
Static Text
A static text field displays a text label.
Formula Field
A formula field displays the value of a formula.
Formulas contains chunks of code that are evaluated when the report runs.
Formulas may use the values of any other fields, including other formula.
For more information, see Formulas.
Running Subtotal
A running subtotal field displays the total of a
series of values. The values may come from any numeric database column or
formula.
Special Values
A special value field contains one of the
following values:
- Report name
- The report's name, as specified in the ``Summary'' Dialog..
- Report title
- The report's title, as specified in the
``Summary'' Dialog.
- Author's name
- The report's author, as specified in the
``Summary'' Dialog.
- Report description
- The report's description, as specified
in the ``Summary'' Dialog.
- Report run date
- The current date.
- Report row number
- The current row number.
- Page number
- The current page number.
- Group record count
- When put into the detail section, the
group count is the number of the current record within the innermost
report group (if there are no groups, it will be the record number
within the entire report.) When in a group footer, it's the number of
rows in the group. When in the report footer, it's the total number of
rows in the report.
Parameter Field
A parameter field displays the value of a
parameter. Parameters allows you to specify values at report run time.
Parameters may be used anywhere: in formulas, in the report (as a parameter
field), or for limiting the records selected from the data (the
Select Records... menu item).
Image
An image field displays an image. The value of the
field is either a file path or a URL. Note that a URL of the form
``file:examples/postgresql.xml'' specifies a relative file path. The path
is relative to the DataVision runtime working directory, not relative to
the report XML file.
Currently, only image fields containing image formats understood by the
Java Swing interface (GIF, JPEG) are editable using the DataVision GUI. In
the future you will be able to add any image file format, though it may
display as a simple rectangle if Swing can't display it. (You can manually
edit the report XML and add or edit any image field, of course.)
Formulas
A formula can execute Ruby code. Before
being evaluated, the following substitutions are made to the formula text:
- {table_name.column_name} is replaced by the current
value of the column table_name.column_name.
- {@id_number} is replaced by the results of evaluating
the formula whose id is id_number. (When editing a formula
the referenced formula's name is displayed, not its id number. The id
number is used internally.)
- {?id_number} is replaced by the value of the parameter
whose id is id_number. (When editing a formula the
parameter's name is displayed, not its id number. The id number is used
internally.)
- {%special_value_name} is replaced by a special value
(report name, report title, author's name, report description, report
run date, page number, data row number, or group record count).
special_value_name is one of the values listed in Fields and described in Special Values.
- {!id_number} is replaced by the value of the user column
whose id is id_number. (When editing a formula the user
column's name is displayed, not its id number. The id number is used
internally.) See User Columns.
Ruby
The language used for formulas is Ruby. Ruby is a scripting language
invented by Yukihiro ``Matz'' Matsumoto. See the Ruby home page for more
information.
This document is not an appropriate place for an introduction to Ruby. I
highly recommend ``Programming Ruby'' by David Thomas & Andrew Hunt,
published by Addison Wesley Longman. This book is online and is published
under the Open Publication license.
Startup Script
There is one special formula that is evaluated at the beginning of each
report run. It is called the startup script, and can be edited by opening
the Startup Script dialog
from the Report menu. Any Ruby variables you set in this
script are available to all of the rest of your formulas.
Parameters
More needs to be written.
Create parameters using the Field Picker window. Select New
Parameter... from the Field menu.
Drag parameters into the ``Select Records'' dialog or onto the report to
use them. When a report that contains parameters is run, you will be asked
to supply values for those parameters.
Example
Open the example report, then select Select
Records... from the Report menu. Type
``office.name = {?String Param}'' and click OK. The
``{?String Param}'' is the name of one of the parameters defined in the
report XML file. (Alternately, you can open the Field Picker window and
drag the ``office.name'' database field and the ``{?String Param}''
parameter from the list of items.)
Run the report. You will be asked to supply a string value. Accept the
default value, ``Chicago''. The report will only display records whose
office name matches ``Chicago''.
User Columns
A user column is arbitrary SQL text. The text is insert into the SQL query
as a column to be retrieved. For example, if you define a user column with
the text ``my_stored_proc({?My Parameter})'' then the SQL query will look
something like
select col1, col2, ..., my_stored_proc(the param value)
from table1, table2, ...
where ...
Before being inserted into the SQL statement, the following
substitutions are made to the user column text:
- {table_name.column_name} is replaced by
table_name.column_name. Though you can simply add the column
name, you shouldn't. DataVision needs that extra hint to figure out
what tables and columns are used in the report.
- {?id_number} is replaced by the value of the parameter
whose id is id_number. (When editing a formula the
parameter's name is displayed, not its id number. The id number is used
internally.)
- {%special_value_name} is replaced by a special value
(report name, report title, author's name, report description, report
run date, page number, data row number, or group record count).
special_value_name is one of the values listed in Fields and described in Special Values.
Formulas are not allowed within user columns because their values may be
undefined when the query is run.