Monday, September 15, 2008

Oracle 6i Reports/Report Builder - Part I

About Report Builder
Report Builder enables you to develop production-quality reports in a client/server or Web environment. You can deploy reports on the Web using the Reports Server and Reports Web CGI or Reports Cartridge. Reports can be run standalone, or you can run them from forms or Graphics Builder displays.

Major concepts
A report is a collection of objects defining its data, layout, and runtime interface. To quickly create new reports, you use the Report Wizard. The wizard guides you through the process of choosing a report type, defining a data model, and laying out the data. You can reenter the Report Wizard after the initial creation of a report, so you may re-invoke it on an existing report, make changes, and re default the report, even if the report was not originally created with the Report Wizard.

Major Features
1. Data model and layout editors in which you can create the structure and format of your report
2. Object navigator to help you navigate among the data and layout objects in your report
3. Packaged functions for creating computations
4. Fully-integrated Previewer for viewing your report output
5. Support for fonts, colors, and graphics

Additional Features :
1. Context-sensitive online help system
2. Conditional printing capabilities
3. Non-procedural Approach: The unique non-procedural approach of Oracle Reports lets you concentrate on design improvements instead of programming. Its' easy- to-use, fill-in-the-form interface and powerful defaults make developing and maintaining even the most complex reports fast and simple.
4. Full Integration with Other Oracle Products: One can integrate Oracle Reports with other Oracle products such as Oracle Forms, Oracle Graphics, and Oracle Mail.
5. Open Architecture: Oracle Reports' open architecture enables incorporation of user-defined routines written in COBOL, C, and most other programming languages, as well as the powerful PL/SQL language.

Object Navigator
1. The Data Model editor, in which you define the data for the report.
Data model is composed of some or all of the following data definition objects:
a. Queries: Queries are ANSI-standard SQL SELECT statements that fetch data from a standard database.
b. Groups: Groups determine the hierarchy of the data appearing in a report, and are used primarily to create breaks. In addition to break reports, you also create groups to define the cross products of matrix reports.
c. Columns: Columns contain the data values for a report. Default report columns corresponding to the table columns included in each query's SELECT list are automatically created by Oracle Reports, then each column is placed in the group associated with the query that selected the column. If you want to perform summaries and computations on database column values, you can create new columns.
d. Parameters: Parameters are variables for your report that enable you to change selection criteria at runtime.
e. Links: Data links are used to establish parent-child relationships between queries and groups via column-matching. A data link (or parent-child relationship) causes the child query to be executed once for each instance of its parent group. You can create links with various SQL clauses (i.e., WHERE, HAVING, or START WITH) and conditions. When a report with a data link is executed, the data link is converted into a SQL clause and appended to the child query. The child query is executed with the values of the primary key used by the parent.

2. The Layout editor, in which you create the report layout. Layout objects define a report's format; i.e., the positioning and appearance of data, text, and graphics in the report output.

3. The Parameter Form editor, in which you customize the appearance of the Runtime Parameter Form, a window that optionally appears at runtime and enables you to enter parameter values that affect report execution.
4. The Report Editor's Live Previewer view enables you to preview and inspect your report output page by page.
5. Report triggers execute PL/SQL functions at specific times during the execution and formatting of your report.
6. Program units are packages, functions, or procedures that you can reference from any PL/SQL within the current report.
7. Attached libraries are external PL/SQL libraries that you have associated with a report or another external library.

Data Model – Groups
There are two types of groups in Oracle Reports:
1. Default (created by Oracle Reports)
2. User-created (created by you)
Break groups are created to produce subtotals, print columns in a different direction, create breaks, and so on. A break group suppresses duplicate values in sequential records Cross product groups perform mathematical cross products, which are generally used to create matrix reports.

Data Model – Parameter
Parameter values can be specified in these ways:
1. Accepting the default parameter values (default values are set in the Parameter property sheet, and you can control whether the values are displayed at runtime on the Runtime Parameter Form)
2. Typing the parameter value(s) as arguments on the command line (where applicable)
3. Choosing from a list or entering the parameter value(s) in the Runtime Parameter Form

User Parameters
1. Bind References: Use a bind reference when you want the parameter to substitute only one value at runtime. Precede a bind reference with a colon (:).

2. Lexical References: Use a lexical reference when you want the parameter to substitute multiple values at runtime. Precede a lexical reference with an ampersand (&).

The remaining part would be continued in Part II shortly

No comments: