<= Previous | Next => | Table of Contents DataVision User's Manual

6 The Anatomy of a Report

The data a report retrieves from the database is only part of a report. A report also includes headers and footers and may include groups of data, formulas to perform calculations, run-time parameters for which you are prompted, and running subtotals and totals.

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 that can be turned on or off (suppressed).

The parts that make up a report are:

Each section 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 fields that allows you to specify values at report run time.

6.1 Report Parts

The Report Header is printed once at the beginning of the report on the first page. Typically, a report title is 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 place a database column in 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.

6.2 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 Value

A special value field contains one of the following values:

Database Column

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 a file path (either relative or absolute).

Currently, only image fields containing image formats understood by the Java Swing interface are creatable using the DataVision GUI. In the future, you will be able to add any image file format. (You can manually edit the report XML and add or edit any image field, of course.)

When a layout engine capable of doing so generates an image, it uses the file path contained in the image field. When you create the image field using the DataVision GUI, the file path will be absolute and the generated report will be able to find the image file. If you add an image to the report XML manually and use a relative file path, the relative path will be exported. That means if you save the report in another directory the generated report may not be able to find the image file. You can edit the generated report and change the image path, of course. I plan to fix this behavior by copying the image to the exported report directory.

6.3 Formulas

A formula can execute code that you write. This code write must be in a simple RPN mini-language (see The RPN Mini-Language). Note: this language will change drastically in the future; hopefully to something more understandable such as embedded scripting language like Ruby.

Before being evaluated, the following substitutions are made to the formula text:

The RPN Mini-Language

The language used for formulas is extremely simple. It uses Reverse Polish Notation (RPN), long familiar to HP calculator users. Note: this formula language will change drastically in the future; hopefully to something more understandable such as embedded scripting language like Ruby.

After substituting the values of columns, formulas, and special values, only three things are allowed in a formula: numbers, strings, and operators. Numbers All numbers are treated as floating-point numbers.

Strings Anything contained in double quotes or obtained from a column, formula, or special value that holds a string.

Operators The available operators are:

+
Adds the two previous values, if they are both numbers. If either one is a string, they are both treated as strings and are concatenated. For example, "1 " banana" +" returns "1 banana".
-
Subtracts the previous value from the value before that. Both values must be numbers. For example, "5 3 -" subtracts 3 from 5, leaving 2.
*
Multiplies the two previous values. If the second previous value is a string and the previous value is a number, returns the string repeated number times. For example, ""banana" 2 *" returns "bananabanana".
/
Divides the second previous value by the first. Both values must be numbers. For example, "10 2 /" returns 5.
floor
Returns the largest (closest to positive infinity) double value that is not greater than the argument and is equal to a mathematical integer.
ceil
Returns the smallest (closest to negative infinity) double value that is not less than the argument and is equal to a mathematical integer.
round
Returns the closest integer to the argument.
sqrt
Returns the correctly rounded positive square root of a double value.
abs
Returns the absolute value of a double value.
pow
Returns of value of the first argument raised to the power of the second argument.
**
(Same as "pow".) Returns of value of the first argument raised to the power of the second argument.
max
Returns the greater of two double values.
min
Returns the smaller of two double values.
sin
Returns the trigonometric sine of an angle in radians.
cos
Returns the trigonometric cosine of an angle in radians.
tan
Returns the trigonometric tangent of an angle in radians.

6.4 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, and 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".

6.5 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 colum text:

Formulas are not allowed within user columns because their values may be undefined when the query is run.


<= Previous | Next => | Table of Contents Valid XHTML 1.0! DataVision User's Manual