| <= Previous | Next => | Table of Contents | DataVision User's Manual |
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 section areas that make up a report are:
Each group has an associated user column or database column. Records are grouped by that column's value. Column aggregates (sum, min, max, count, average, standard deviation) may appear in group footers and in the report footer.
Section areas (or more simply, "areas") are made up of one or more sections. For example, the Group Header area may contain one or more Group Header sections.
Each section may contains fields. A field may be a database column, a calculated formula, static text, an aggregate, 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.
Reports can contain other reports, called subreports. The subreport's query runs each time its field needs to be output. See Subreports below.
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 area 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. Aggregates 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.
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 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: Aggregates are updated even for hidden or suppressed detail rows. If you want to avoid aggregating rows, don't retrieve them from the database or use a formula that returns 0 or some other appropriate value for rows you want to ignore and aggregate that value instead.
A field is a single unit of information, whether a database column, text, or an image.
A database column field displays the value of a column in the database.
A static text field displays a text label.
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.
An aggregate field displays an aggregate (sum, min, max, count, average, or standard deviation) of all of the values a field takes on, either within a group or over the entire report. The values may come from any numeric database column or formula.
A special value field contains one of the following values:
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 menu item).
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.)
A formula can execute any code supported by the Bean Scripting Framework (BSF). Before being evaluated, the following substitutions are made to the formula text:
The Bean Scripting Framework (BSF) is a Java framework for executing scripting languages that can access Java objects.
A report is exposed to all formulas as a global object called "report". More information about what you can do with the report object will appear in future documentation, and some changes to the report object may be made to allow for access to interesting bits of information.
Each report has a default language, and each formula can use a different language if it wishes. When a report is first created, its default language is Ruby. All formulas that do not specify a language use the report's default language.
DataVision ships with JRuby, which is a BSF language that implements Ruby. Ruby is a scripting language invented by Yukihiro "Matz" Matsumoto. See the Ruby home page and the JRuby page for more information.
Reports created before DataVision 0.9.0 (when the BSF was introduced) will continue to work with Ruby and no changes are necessary to continue using Ruby.
In Ruby scripts, the BSF report object is in a global variable called
$report ("$" identifies global variables in Ruby).
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. The second edition of the book, though not available for free, is an excellent follow-up well worth the money.
If you want to use a different language you will have to take a few steps.
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 menu. Any BSF variables you set in this script are available to all of the rest of your formulas. Note: for the same language only, I think, not all other BSF languages.
More needs to be written.
Create parameters using the Field Picker window. Select from the 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.
Open the example report, then select from the menu. Type "office.name = {?String Param}" and click . 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".
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:
Formulas are not allowed within user columns because their values may be undefined when the query is run.
A subreport is a report within a report. The subreport's query runs each time the subreport's field needs to be output. Subreports are only allowed in reports that have database data sources.
When you import a subreport, you define one or more joins that link the master report to the subreport. The first column of each join will be filled with the current value of that column in the master report. The second will be a SELECT column for the sub-report. For example, selecting "office.name <= office.name" will fill the first office.name with the current value of the office name from the master report. Let's say the master report's office name value is currently 'Chicago'. The sub-report's query will then contain a WHERE clause that ends with "... AND 'Chicago' <= office.name".
See Subreports in Editing a Report for instructions on adding a subreport to a report.
Many thanks to Aaron Kardell and Altona Ed, LLC, who funded development of subreports in DataVision.
| <= Previous | Next => | Table of Contents |
|