Week 3

Day 15

Developer/2000: Developing Reports with Oracle Reports

In this lesson, you'll learn about some basic features of Oracle Reports. You'll read about two basic report types—a tabular report based on a single table and a master-detail report. Oracle Reports is chock-full of features, and this lesson is intended to be an introduction to the most important of them.

Elements of an Oracle Report

Like Oracle Forms, Oracle Reports also has an Object Navigator that you can use to create and modify a report's objects. The Object Navigator displays objects in a hierarchical manner. For each report, the Object Navigator displays a set of nodes:

• Data Model
• Layout

• Parameter Form
• Report Triggers
• Program Units
• Attached Libraries

For directions on the use of the Object Navigator, refer to Day 12, "Developer/2000: Introduction to Oracle Forms." The next section looks more closely at some of the objects in a typical report.

Data Model

Every report must have a data model. The data model describes the queries that retrieve records from the database. A data model can be composed of one or more queries. A query can consist of any valid SELECT statement; in other words, you can select from multiple tables or even from a view. You also can use an ORDER BY clause to specify the order in which the rows are to be retrieved.

Layout

Every report must also have a layout. You can actually describe up to four layout components—header, body, footer, and margin. The examples in this chapter only define the body layout. However, you can define a layout for a report header that is printed only once at the very beginning of a report. An example of a report header might be a description of the report contents and a warning regarding the accuracy of the information contained within the body of the report.

The report layout may consist of many different elements, including frames, fields, text items, and graphical elements such as lines and rectangles. A frame is used to describe the characteristics of the items contained within it—for example, when they should be printed and in what direction. A frame can contain multiple fields. Each field may be associated
with a column from a query—this is referred to as the source of the field. The field source also may be the current date or physical page number.

Parameter Form

Oracle Reports enables you to design a custom Runtime Parameter Form that may contain parameters whose values are specified by the user at runtime. For example, you can design a parameter form that enables the user to specify a Department ID that restricts the report output to that department. If you don't specify a parameter form, Oracle Reports displays the default Runtime Parameter Form.

Oracle Reports Programs

Oracle Reports contains two tools that are relevant to this lesson—Reports Designer and Reports Runtime. The Reports Designer enables you to create and modify three types of modules: reports, external queries, and libraries.

By default, the Reports Designer saves the modules that you design to either a file or to an Oracle database. To save a module to the database, you or your DBA need to install the database schema needed by Developer/2000. To learn more about this, read the Administrative Notes that are in the Developer/2000 Admin for Win95.

The Reports Designer saves a report as an RDF file: Reports Definition File. This is a binary file that is portable; that is to say, it can be read by the Reports Designer, whether on Windows 95, Mac OS, or UNIX. The Reports Designer generates an REP file that is a binary file used by the Reports Runtime program to actually run a report. An RDF file also can be converted to an REX file, which is a complete text description of the report that can be placed under source code control.

Building a Tabular Report Based on a Single Table

Let's begin using Oracle Reports to design a tabular report based on a single table. You will build a report that will present course information. To invoke the Reports Designer, from the Start button, click Oracle Designer for Win95 | Reports Designer. The Reports Designer program first displays the Object Navigator (see Figure 15.1).

Notice that a report titled UNTITLED is displayed in the Object Navigator. Before going any further, connect to the Oracle database.

Connecting to an Oracle Database

To connect Reports Designer to an Oracle database, follow these steps:

1. Select File | Connect from the menu. A dialog box is displayed, prompting you for the username, password, and database to use for establishing an Oracle database connection.
2. If you are using Personal Oracle for development, use flugle for the username and password. If you're using an Oracle server for development, use flugle for the username, password, and database.
3. For both cases, click the Connect button. If there are no error messages, you can assume that you have successfully connected to the database.

Figure 15.1.
Reports Designer first
displays Object
Navigator.

Creating a Data Model

The first step in creating any report is specifying the data model for the report. The Data Model Editor is used for this purpose. You can invoke the Data Model Editor by either clicking the icon to the left of the Data Model node in the Object Navigator or by selecting Tools | Data Model Editor from the menu. Maximize the window.

To create a query, click the button labeled SQL on the toolbar on the left-hand side of the window. Move the mouse to the main part of the window and hold down the left mouse button. You will see the outline of a rectangle. When the rectangle is a reasonable size, release the mouse button. You should now see a rounded black rectangle, labeled Q_1 (see Figure 15.2).

Double-click the rectangle that represents query Q_1. A window in which the SELECT statement for the query can be entered is displayed. Change the name of the query to Q_COURSE. Click the button labeled Tables/Columns. Another window, titled Table and Column Names, is displayed. This window enables you to choose the table and columns used for the query. In the listbox labeled Database Objects, scroll down to the COURSE table and select it. Select all of the columns in the Columns listbox (see Figure 15.3).