Make your own free website on

Oracle Report
Back Home Up Next





 Written Books


Technical Report

Survey Reports





Contact Me


For 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

wpe17.jpg (2487 bytes)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

wpe18.jpg (3736 bytes)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

wpe19.jpg (3035 bytes)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

wpe1A.jpg (3850 bytes)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

wpe1B.jpg (3220 bytes)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

wpe1C.jpg (2748 bytes)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

wpe1D.jpg (4069 bytes)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

wpe1E.jpg (3490 bytes)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

wpe1F.jpg (2723 bytes)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

wpe20.jpg (3645 bytes)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:

Attempt to layout the report on paper. This will assist with the development of the data model as well as the final layout. Understand where subtotals should be provided to create the data breaks up front.

When the default layout is used, define the page size to be excessively wide.

If possible, formulate the data retrieval in a single query.

Complete the data model before attempting to finalize the layout. The addition of a single column in a query might necessitate a redesign of the layout and thus a misuse of time.

When adding an additional break level to an existing report, 90 percent of the time it is faster to redo the default layout. Adding another intermediate level frame is one of the most difficult tasks to be done.

Rather than trying to resize or reposition objects in the Layout Editor, use the Size Objects and Align Objects tools. Several columns can be selected at once and all made the same custom size quickly, and they can be aligned and spaced with minimal effort.

To lock the relative position of multiple objects, select them and create a group to join them together.

Use the Magnify tool to zoom in to view the relative positions of the objects or to zoom out to view the total report structure.

When you make a mistake in the editor, use the Edit | Undo menu option to reverse the action rather than trying to correct it with the mouse.

Before running any report, save it in a file to make sure that it can be recovered. Also, save different versions to facilitate recovery.