Oracle Application Server 10g Web Development (Oracle Press)

Reports Builder

Reports Builder is the program that allows developers to create and test all of the objects that constitute an Oracle Report. These report objects include:

• Data model objects

• Layout objects

• Parameter Form objects

• PL/SQL objects

• References to external PL/SQL libraries

• Code shown in the Web Source view (for JSP-based web reports)

Data Model Objects

These objects provide the data that will be displayed on the report. They include:

• Queries Queries are used to query database(s) and provide the base data for your reports. They can be built using report or data wizards or can be constructed manually. As mentioned before, multiple data sources are available including Oracle (SQL and PL/SQL), XML, JDBC, etc.

• Groups Groups are used to organize columns in a report. They can be used to either separate a query s data into sets producing break levels in a report, or filter a query s data, conditionally removing records from result sets. This is a very powerful feature of Oracle Reports as it allows queries run against the database to be as simple as possible and shifts complex reporting requirements (summaries, grouping, etc.) to the reports engine, distributing the load more evenly among your hardware components .

• Groups When you define a query for your report, Reports Builder automatically creates a corresponding column in the data model. The real power of columns is in their flexibility. Developers can:

  • Create new columns manually for summary and formula columns

  • Reassign columns to a different group

  • Define a column as a graphic column, allowing graphics to be read out of the database (or from a file on the server) and displayed directly on reports

  • Define Formula columns ( user -created columns that get data from a PL/SQL function or expression, a SQL statement, or a combination), Summary columns (columns that perform a computation on another column s data), and Placeholder columns (columns for which the datatype and value are set by the developer in PL/SQL)

• Links Links are used to link data on a report made up of a master-detail query. The query that provides the detail data is executed once for each record in the parent group matching the criteria.

Layout Objects

These objects are used to control the look of your report:

• Frames Used to protect objects on your report from being overwritten or moved by other objects. Frames are commonly used to protect column headings and all objects in a group to ensure they maintain their relative positions during printing.

• Repeating frames Used on the fields that are created for a group s columns to protect them from being overwritten or moved by other objects. Repeating frames fire once for each record of the group. Repeating frames can enclose any layout object, including other repeating frames.

• Fields Used as placeholders for parameters, columns, etc. The most common usage for fields is to display data queried from the database, but they are also useful for displaying information that does not come directly from the database or is derived from data queried from the database. Examples of this include values specified on a parameter form, page number of the report, date report was executed, user executing the report, etc. Formulas (PL/SQL functions) are commonly used to populate fields.

• Boilerplate Used to print text or draw graphics that appear every time a report is executed. In the paper layout of the report editor, there are numerous tools in the tool palette to help developers draw the necessary boilerplate objects to be used in their reports. The report editor is discussed later in this chapter. Boilerplate objects can also be links to a file or even to a URL where the image is located.

• Anchors Used to align objects. Anchors fasten an edge of one object to an edge of another object, ensuring that they maintain their relative positions. When data is queried, it is possible that the size of some layout objects may be altered by the data being returned. Anchors maintain the relative positions of objects. Implicit anchors are generated that determine which objects, if any, can overwrite an object. By default, objects are anchored to the upper-left corner of their enclosing object. Developers can also create explicit anchors, which will automatically override any implicit anchoring for an object.

Parameter Form Objects

Reports Builder includes a tool called the Parameter Form Builder that allows the developer to define which parameters the end user can enter before running a report. You are not limited, however, to the standard parameter display generated by the Parameter Form Builder tool. Additionally, the developer can create boilerplate text with HTML tags for adding hyperlinks or any other HTML tagged text to your Paper Parameter Form, insert parameter fields with JavaScript (for defining input or select events), or create a Parameter Form header or footer for placing a logo or link in the header or footer of the HTML Parameter Form.

PL/SQL Objects

The PL/SQL objects supported by Oracle Reports 10 g can be broken down into two categories:

• Program units Reports Builder includes the PL/SQL editor. This editor allows developers to create PL/SQL objects (packages, functions, or procedures) stored and executed in the report. These PL/SQL objects are sometimes referred to as local program units. PL/SQL objects created with this editor have no corresponding PL/SQL object created in the database. By embedding the PL/SQL code in the report, the execution of the PL/SQL program handled by the Reports Server and the load is more evenly distributed ( assuming , of course, that the reports server is on a machine other than the database). Local program units can be used by other reports by placing them into a library. These libraries can then be attached to other reports, giving the report access to all packages, functions, and procedures contained within the library. Reports Builder also includes the Stored PL/SQL editor, which allows the creation and modification of PL/SQL program units stored in the database, sometimes referred to as stored program units.

• Triggers Report triggers execute PL/SQL functions at specific times during the execution and formatting of your report. There are five report triggers available (developers cannot create report triggers), the names of which should be self-explanatory:

  • Before Report trigger Fires before the data is fetched and the report is executed but after the queries are parsed.

  • After Report trigger Fires after report output is sent to a specified destination. (There is a Java API included with Reports for writing your own destination.)

  • Between Pages trigger Fires before each page of the report is formatted, except the very first page.

  • Before Parameter Form trigger Fires before the Runtime Parameter Form is displayed.

  • After Parameter Form trigger Fires after the Runtime Parameter Form is displayed.