Oracle® Fusion Middleware Oracle Reports User's Guide to Building Reports 11g Release 1 (11.1.1) Part Number B32122-02 |
|
|
PDF · Mobi · ePub |
In this chapter, you will learn about reports that include PL/SQL. By following the steps in this chapter, you can generate the report output shown in Figure 40-1.
Concepts
There are a variety of ways to incorporate PL/SQL into your reports. You have already created formula columns that used simple PL/SQL expressions to compute their values, and format triggers that used PL/SQL to conditionally determine the formatting of mailing labels. Here, you will create external libraries and local functions and procedures.
External PL/SQL libraries are modules that contain named PL/SQL functions and procedures. They may be stored either in the database or in a file, and can be referenced from not only any report, but from other Oracle products. External libraries eliminate the need to re-enter commonly-used PL/SQL constructs, whether in reports, forms, or graphs. This, in turn, eliminates the problem of maintaining several versions of the same PL/SQL code.
Local PL/SQL consists of named PL/SQL functions and procedures that are saved in a report definition. Local PL/SQL may be referenced only by objects within the report (for example, group filters, formula columns, format triggers, and so on). However, the usefulness of storing PL/SQL in a single location still applies.
Data Relationships
This report uses one query. You will add a function stored in an external library, a report-level function, two formula columns, and a parameter governing the number of records to display before inserting a space.
Layout
This report uses the tabular layout style, with minor modifications.
Example Scenario
In this example, you will use an external PL/SQL library and PL/SQL within a report to modify formatting to add spacing between records, and calculate the total compensation for each employee. You will manually create a query in the Data Model view, then modify the layout of the report in the Paper Layout view. You will create formula columns, a summary column, and a format trigger that uses a summary column and a user parameter.
As you build this example report, you will:
Create a New PL/SQL Library that you will use in this report.
To see a sample PL/SQL report, open the examples folder named plsql
, then open the Oracle Reports example named plsql.rdf
. For details on how to open it, see "Accessing the Example Reports" in the Preface.