or many years, the predictors of the future
have envisioned the concept of the paperless office wherein all correspondence and
corporate records are stored online and all paper reporting has been eliminated. Reality,
however, has shown that hard-copy reports continue to be required at an ever-increasing
rate.
Oracle Reports is the Developer/2000 tool that has been provided to
produce reports of data in the Oracle database. These reports can be previewed on the user
screen before being printed or can be printed directly. Optionally, report output may also
be saved in a file to be used at a later date.
The Oracle Reports Environment
Oracle Reports operates in a Graphical User Interface (GUI) environment
such as Microsoft Windows. Functions may be performed by clicking iconic buttons or via
menu picks. The menus used by Reports dynamically change based on the current context of
the tool and are fairly intuitive as to their specific purpose. The Oracle Reports
Designer interface consists of three primary components or tools
The Data Model Editor
The Data Model Editor is
used to define all of the data elements that are to be included in the report. These data
elements are queries, links, and miscellaneous columns that are organized into sets called
groups. The Data Model Editor is operated using a set of iconic buttons that can be
selected with the mouse.
The Layout Editor
The Layout Editor is used
to construct the format for the report. The editor presents each of the layout elements
exactly as it will appear on the final report. Like the Data Model Editor, the Layout
Editor is controlled through a set of iconic buttons as well as from the menus. The
buttons along the left side of the editor window are used to manipulate the layout
objects.
Initializing the Reports Environment
Before you develop any
reports, the development environment should be set up based on the individual preferences
as well as any standards defined by the programming organization. Use the menu pick Tools
| Options to present the Tools Options dialog Box.
The preferences tab is used to set the specific user preferences for
the developer. These options define the parameters used by the reports designer. The first
checkbox is used to disable PL/SQL compilation. Typically, Oracle Reports compiles each
PL/SQL program unit it is closed. When you disable compilation, all program units will be
compiled when the report is generated.
The Color Palette Mode is used to define how color palettes should be
handled in Oracle Reports. The options for the list box are Editable, Read Only - Shared,
and Read Only - Private. Editable means that the color palette of the active report will
replace the system color palette causing the active report to be shown accurately while
the appearance of any inactive reports may not be accurate.
Creating a Report
To create a new report,
either select File | New | Report from the menu or type Ctrl+E. This will create a report
named "Untitled" in the Object Navigator. After the report is created, its
properties should be defined using the Tools | Properties menu selection. The Report
Properties Dialog Box defines the dimensions for the report. The first selection in this
form is for the unit of measure for the report.
Creating a Master-Detail Report
The Master-Detail report is
a report that organizes the data according to specific break groups Break groups are
present in the Data Model Editor as separate boxes containing the data columns belonging
to the individual break group.
Constructing a Tabular Report
The first and most basic
report that can be developed using Reports is the tabular report. The tabular report
appears as a columnar listing of the selected rows. To build this report, create a new
report using the menu pick File | New | Report. This will create a new report called
"Untitled" in the Object Navigator. After setting the development environment
open the Data Model Editor using the Tools | Data Model Editor menu pick.
Customizing Reports with Boilerplate Text and Graphics
The reports that have been
explored in this chapter thus far utilize the powerful, intelligent default capabilities
of Oracle Reports. Many times, however, it becomes necessary to enhance the report with
explanatory text and graphical objects.
Summary Column Box
The important field needed for report is the subtotal, which is
calculated as the sum of all the item totals. To create this column, use the Summary
Column tool. Double-click the desire column to display the Summary Column dialog box.
The Mailing Label Report
The mailing label report is
used to print address labels on special paper, which is usually perforated to print labels
for multiple address records. To build this report, create a new report with the proper
page size for the label paper. In the Data Model Editor, create a simple query from the
any table ‘table name’. After the query has been created, select the Default
Layout tool, select mailing label as the report type, delete all column headings in the
Data Selection tab, and then click the OK button. The label will be formatted with all the
fields within the label.
The Form Letter Report
The form letter report applies database data to a specific text format.
After the query has been entered, select Form Letter style from the Default Layout tool
and click the OK button. The field boxes that appear within the report frame are hidden
when the report is run. The values in the fields can be displayed using the &fieldname
lexical parameter embedded in the text of the letter.
The Matrix Report
A matrix report appears
like a columnar report, except that the column headings are retrieved as part of the
database query. This report groups the data horizontally, based on the values in the
matrix columns. In cases where no data exists the column will appear as blank unless the
developer specifies a default value if the field is null.
Creating Dynamic Report
There are certain techniques for producing reports that define their
queries based on runtime selections by the operator. This feature can be used through
dynamic query parameters for runtime modification of a query with lexical constructs.
Using Dynamic Query Parameters
The developer can define a
user parameter for a report that defines the query. To accomplish this, open the
filename.rdf document report that was created earlier. Select the User Parameters heading
in the Object Navigator and create a new user parameter using the Add Object tool at the
left of the Object Navigator window. The new user parameter will appear under the User
Parameters heading. Double-click the icon to the left of this parameter to display the
properties sheet for the parameter.
Ten Top Tips for Oracle Reports
Oracle Reports is a powerful tool that can be used to generate useful
reports against Oracle databases. While there are no hard and fast rules regarding how the
tool should be used, experience has borne out a number of useful tips that make a
developer's utilization of this tool much easier:
