Editing a Report
This section briefly describes the most common actions performed when
editing a report. For a complete description of the DataVision GUI, see The DataVision User Interface.
Fields have popup menus. Sections have popup menus. The section labels
on the left side have popup menus.
Adding Fields
To add a database field, formula, parameter, subtotal, or special field
to a report, select the corresponding menu item from the
Insert menu. The Field Picker window will open, allowing
you to select and drag a field from that window onto the report.
The Field Picker window also lets you create new formulas and parameter
fields. Use the New Formula... or New
Parameter... menu items from the Field
menu.
To add a static text label, select Text from
the Insert menu. The cursor will turn into a text
cursor. When you click the mouse in any section, a new editable text label
will appear.
See Subtotals for adding running
subtotal fields.
To add an image, select Image... from the
Insert menu. A dialog box will open, letting you type in
the URL or file path for the image. (If you type in a relative file path
like ``example/postgres.xml'' or URL like ``file:example/postgres.xml'', it
must be relative to the DataVision directory, not to the report XML file.)
When you enter the URL, the image is inserted into the report in the top
left corner of the Report Header. From there, you may move the image
anywhere else in the report. See image fields for more
information.
Only image formats understood by the Java Swing interface (GIF, JPEG) may
be created using the DataVision GUI. That's because I haven't yet written
the code that will visually represent images that Swing can't understand.
To change how a field is displayed, select Format
Field... from the Format menu or from a
field's popup menu. See The Format Dialog for
details.
Selecting Fields
To select a field, single-click it. To select more than one field, hold
down the shift key while selecting the fields.
Not yet implemented: You may also select fields by clicking
and dragging a selection box with the mouse.
Removing Fields
To remove a single field, select it and hit the delete or backspace key.
You may also right-click on the field and select
Delete from the popup menu.
To remove a formula or parameter from the report, open the Field Picker
window, select the formula or parameter, and select
Cut from the Edit menu. You
can only remove formulas and parameters that are not used somewhere in the
report. If the Cut menu item is not enabled when
you select a formula or parameter, that means it is being used somewhere in
the report.
Moving and Re-sizing Fields
Click and drag a field to move it. Drag the edges to re-size it. Moving
and dragging operate on the current selection, so if multiple fields are
selected they will all be moved or re-sized.
Subtotals
To create a new subtotal field, either right-click on a field and select
Subtotal... from the popup menu or select a
field by single-clicking it and then select Running Total
Field... from the Insert menu. A dialog
box appears that lets you select where you would like subtotals to appear
for this field. The choices are Grand Total (appearing in the report
footer), All (appearing in every group footer and the report footer), and
Group (appearing in one group that you select from a menu of groups).
The field you want to subtotal must be in the details section. (It can be
hidden, if you don't want to display its value.) It must be a numeric
column, a formula, a user column, or a numeric parameter.
Parameters
Double-clicking a parameter field, either in the Report Design window or
the Field Picker window, opens the dialog box appropriate for the parameter
type (string, number, or date). Alternately, you can right-click on the
parameter field in the Report Design window and select
Edit... from the popup menu or you can select
Edit Parameter... from the
Field menu in the Field Picker window.
To be written.
Formulas
Formulas are Ruby scripts that can contain database fields, parameters, and
more.
Double-clicking a formula field, either in the Report Design window or
the Field Picker window, opens the formula editor dialog box. Alternately,
you can right-click on the formula in the Report Design window and select
Edit... from the popup menu or you can select
Edit Formula... from the
Field menu in the Field Picker window.
The main text pane in the dialog box that is opened lets you edit the
text of the formula. For more information about this dialog see The Formula Editor
Dialog.
For more information about Ruby, see Ruby.
Groups
The sort order of a group determines the order in which a group's values
are selected. For example, if the ``office.name'' group of a report has
three possible values (New York, New Jersey, and Chicago) then the sort
order determines which office's detail records are displayed first. When
sorted ascending, Chicago's records will be first, then New Jersey and
finally New York. When sorted descending, New York will be first. This is
not the same as the sort order of the detail records within a group. (See
Sorting Records for information about
sorting detail records.)
To edit the sort order of a group's values or the nesting order of groups
within a report, open the Group By... dialog
from the Report menu. See The ``Group By'' Dialog for
more information about this dialog.
Sorting Records
Detail records may be sorted using any combination of database columns.
The order in which columns are sorted is significant. In the example
database, job records have an id and a date. Sorting the records by date
and then id will return the records in a different order than sorting them
by id and then date.
A column may be sorted in ascending or descending order. Ascending order
means lower values are first.
To edit the list of sorted columns and their order, open the
Sort By... dialog from the
Report menu. See The ``Sort By'' Dialog for more
information about this dialog.
Columns used by groups do not appear in the ``Sort By'' dialog. To change
the sort order of groups, use The
``Group By'' Dialog. See Groups for more information about
editing groups.
Selecting Records
By default, running a report retrieves and displays all of the records
from all of the tables in your report. It is often desirable to limit the
records that are retrieved.
When reading records from a database, you use a SQL WHERE clause to specify
which records you want to retrieve. (The WHERE clause is also used to
specify how tables in your SQL query are joined, but we are not concerned
about that right now. For information about joining tables, see Joining Tables.) The SQL specification is
beyond the scope of this document. See your database's documentation.
When reading records from a comma- or tab-separated file, you select
records use a Ruby script. The script must return a boolean (true/false)
value. If the returned value is true, the record will be included in the
report.
To edit the text of the WHERE clause or Ruby script, use the Select Records dialog.
The text you enter may contain columns, formulas, and parameters. You can
type their names (using the proper format) or simply drag them in from the
Field Picker window. Formulas look like
``{@My Formula}'' and parameters look like ``{?My Parameter}''.
Database Column Names
You can enter database column names with or without the curly braces. (When
you drag them from the Field Picker window, the curly braces are used.) The
curly braces make a difference when the database column's name is not all
lower case (or all upper case, depending upon your database). Column names
without curly braces will not be quoted. That means the JDBC driver may
automatically convert the name to all lower case or all upper case. This
causes problems when your database was created using mixed-case names, for
example. See Database Name Case
Sensitivity for details.
To summarize: when you need to enter a mixed-case name because the
table or column name is mixed-case, use quotes around the name.
The quotes go around the parts of the name that need them, not the whole
name. For example, if the table name is ``table'' and the column name is
``MyCol'', the quotes don't go around the entire name ``table.MyCol''.
Instead, you would need to type
table.``MyCol''
Parameters
When using a parameter that is a string, do not enclose the
parameter in quotes. If you do, you will see an error message like
``Parameter index out of range'' when you run your report.
If you want to combine a ``like'' string comparison that uses wildcards
with a parameter value you need to concatenate the parameter with the
wildcard characters. Here's an example. Let's say you want to run a report
that only displays records where the ``name'' column starts with a
user-selected prefix string. You would create a parameter called "Start of
Name" that will be used to ask the user to enter the start of the name.
Next you use the Select Records dialog to set the SQL WHERE clause to
upper(table.name) like upper({?Start of Name} || '%')
The characters ``||'' are the string concatenation operator, at least for
Oracle and PostgreSQL. The string '%' is the wildcard character that we
are appending to the end of the user's string. To search for the user's
string anywhere in the name column, add wildcards to the beginning and the
end, like this:
upper(table.name) like upper('%' || {?Start of Name} || '%')
Joining Tables
When two or more tables appear in your report, you probably want to join
them together by one or more common columns. To do so, use the Visual Table Linker dialog.
Startup Script
The startup script runs at the beginning of the report, before any data is
read. The script is Ruby, not SQL. Edit the startup script by selecting
Startup Script from the
Report menu. Any Ruby variables you set in this script
are available to all of the rest of your formulas.
For more information about Ruby, see Ruby.
Page Breaks
Each section can force a page break before it outputs data. To turn a
section's page break on or off, open its popup menu and select
New Page Before. The menu item will be checked
when this feature is on and unchecked when it is off.
Database Name Case Sensitivity
Some databases support case-sensitive names. In those database, the column
named ``mycol'' is different from the column named ``MyCol''.
Unfortunately, the programs that access databases such as JDBC drivers and
command-line tools like PostgreSQL's psql often confuse the issue by
changing all names to lower case or upper case internally before sending
the query to the database. When you type ``select MyCol from MyTable'' it
sends ``select mycol from my table'' to the database.
If your database was created using names with only lower case (or upper
case) letters, you may not see any problem. Your queries always work
because no matter what case you use, the names are converted to all lower
case (or upper case) and everything is fine.
In order to access a column named ``MyCol'' using a tool that converts the
names, you have to quote the mixed-case names. For example, the query
``select MyCol from MyTable'' must be entered as
select "MyCol" from "MyTable"
The quotes go around the parts of the name that need them, not the whole
name. For example, the quotes don't go around the entire name
``MyTable.MyCol''. Instead, you would need to type
select ``MyTable''.``MyCol''
As of version 0.6.0 of DataVision, database table and column names are
case-sensitive. All table and column names are quoted if their case does
not match the case the JDBC driver uses when executing SQL queries
DataVision's internal searches for columns (for example, when loading a
report XML file) now perform case-sensitive searches. This may break
reports designed manually, especially if you used all upper-case for your
table and column names.
Using previous versions of DataVision along with many (most) JDBC drivers,
it was impossible to query a case-sensitive database. Since DataVision did
not quote table or column names (unless the name contained a space), the
JDBC driver would convert all of the names.
Use the -q command line flag to disable this new behavior. Table and
column names will not be quoted when sent to the database (unless the name
contains a space) and internal searches for columns will be
case-insensitive.
Not that if you only used the DataVision GUI to create your report, you
probably won't see any difference. The table and column names were
retrieved from the database and saved to the report XML file using the
correct case.
Any column name within curly braces is automatically quoted by DataVision
(unless you use -q), so you don't need to add the quotes. Also, if
you drag a column from the Field Picker window to another window like the
``Select Records...'' window, the column's name is surrounded by curly
braces and you don't have to add any quotes.