Oracle Reports

Oracle Reports: Oracle Reports is a report generated by Oracle Report Builder tool using data stored in an Oracle database. 

• Way to provide a certain set of data in a specified format to an individual or a group of audience.
• Reports are often used to display the result of an experiment, investigation, or inquiry.
• The audience may be public or private, an individual or a group.

Report Builder:

Oracle Reports Builder is a powerful enterprise reporting tool used to build reports that dynamically retrieve data from Oracle database, format, display and print quality reports. When you first start Oracle Reports Developer, the Reports Wizard will open automatically. You can also access it by selecting File | New | Report from the menu.

 First, you need to select a report style from the following choices:

Tabular            : a simple spreadsheet-like report

 Form                : displays one form-like record

 Group Left      : Selected group fields are displayed on the left of the report

 Group Above : Selected group fields are displayed on top of the report

 Matrix : Summary report that will calculate values corresponding to a horizontal and a vertical grouping

Matrix with Group : A grouping field added to the matrix report

 Mailing Labels & Form Letter : similar to the Mail Merge capability.

     

      ** The difference between Mailing Labels and Form Letters is, Mailing Label shows multiple records on one page while Form Letter shows one record on each page.

Components in Report Builder:

Oracle Reports Builder comes with the following components.

       1. Object Navigator

2. Data Model Editor

3. Layout Model Editor

4. Parameter Form Editor

Object Navigator

The Object Navigator shows a hierarchical view of objects in the report. Each item listed is called a node and represents an object or type of object the report can contain or reference.

Data Model Editor: The Data Model contains the logical grouping of data for the report. This is the place where you define the source data, add additional queries, and link them.

A data model is composed of some or all of the following data definition objects.

Queries
Queries are SQL Select statements that fetch data from the oracle database. These statements are fired each time the report is run.

 Groups
Groups determine the hierarchy of data appearing in the report and are primarily used to group columns selected in the query. Oracle report automatically creates a group for each query.

Data Columns
Data columns contain the data values for a report. Default data columns, corresponding to the table columns included in each query’s SELECT list are automatically created by oracle reports. Each column is placed in the group associated with the query that selected the column.

Summary Columns
Summary columns are used for calculating summary information like sum, average etc. This column uses a set of predefined oracle aggregate functions. Summary columns are generally preceded by CS_ to distinguish them from other columns.

Data Links
Data Link: Join queries for complex data relationships.

Data links relate the results of multiple queries.  A data link (or parent-child relationship) causes the child query to be executed once for each instance of its parent group

Formula Columns

                Formula Column Performs a user-defined computation.

Characteristics:

. Executes a PL/SQL function
. Must return a value

n  Can be a Character, Number, Date

n  Returned value must match datatype

Example:

function CF_3Formula return Number is

begin

  return(:taxable_total+:tax_total);

end;

Placeholder:

n  An empty container at design time

n  Populated by another object at run time

n  Before report trigger

n  Formula column at report level

n  Formula column in same group or below placeholder

Bind Variable and Lexical Parameter:

n  Bind reference replaces a value

n  :parameter_name

n  parameter object is created by default

n  Lexical reference replaces a clause

n  &Lparameter_name

n  parameter object is never created by default

Use to substitute any part of the query.

                SELECT NAME, SALES_REP_ID

                FROM S_CUSTOMER

                &LP_WHERE_CLAUSE

                &LP_ORD_CLAUSE

LAYOUT MODEL: The Layout Model displays the physical layout of the data.

 A report layout editor contains the following layout objects

n  Frames : Frames surround other layout objects, enabling control of multiple objects simultaneously.

n  Repeating Frames : Repeating frames acts as placeholders for groups (I.e repeating values) and present rows of data retrieved from the database. Repeating frames repeat as often as the number of rows retrieved.

n  Fields : Fields acts as placeholders for columns values. They define the formatting attributes for all columns displayed in the report.

n  Boilerplate : Boilerplate consists of text (label of the column) and graphics that appear in a report each time it is run.

Parameter form is a runtime form used to accept inputs from the user.

Parameters : Parameters are variables for a report that accept input from the user at runtime. These parameter values can then be used in the SQL select statements to retrieve data conditionally. Oracle reports creates a set of system parameters at runtime namely report destination type, number of copies etc.

Commonly Used Field and Frame Properties

	Property	Usage
Field	Source	Select a column from the Data Model
	Vertical Elasticity	If the field content will expand beyond its physical width, the object can be truncated (Fixed), expanded vertically (Expand, Variable). If the object is smaller than the allocated, its height can be shrunk (Contract, Variable). In the Layout Model, the vertically expandable fields (and frames) are symbolized with an = sign on the vertical lines.
	Horizontal Elasticity	The same as the Vertical Elasticity, but the object will expand horizontally. This property is used less often than the Vertical Elasticity. Italic fonts might be truncated if you use the Variable or Contract setting
	Conditional Formatting	You can format or even hide the field depending upon various conditions Developer-friendly code writer.
Repeating Frame	Source	Select a group in the Data Model.
	Maximum Records per Page	Allows you to display a fixed number of records per page, usually “1” for Form-like reports
	Page Protect	If set to “Yes,” will not allow the separation of the field contained in the frame, unless the frame extends beyond one page
	Elasticity, Conditional Formatting	Similar to fields If the fields have variable lengths, it is reasonable to make the frames variable too.
Frames	Page Protect, Elasticity, Conditional Formatting	Similar to Repeating Frames

n  Format Triggers: Format triggers are PL/SQL functions executed before the object is formatted. These triggers are used to dynamically change the formatting attributes and used to conditionally print and not to print a report column value. These triggers return Boolean values TRUE or FALSE. If the return value of the format trigger is FALSE, the value is not displayed.

n  You can find these triggers in property Palette of layout objects

Example:

function F_TAX_CODE1FormatTrigger return boolean is

begin

    if cond ... then

                return (TRUE);

  else

                return (false);

  end;

Below are the list triggers which are available in report builder.

      Before Parameter Form:- Fires before the Runtime Parameter Form are displayed. Can access the PL/SQL global variables, report level columns and manipulate accordingly.

      After Parameter Form:- Fires after the Runtime Parameter form are displayed. Used to validate the parameter values.

      Before Report:- Fires before the report is executed but after the queries is parsed and date is fetched.

      Between Pages:- Fires before each page of the report are formatted, except the very first page. This page is used to customize page formatting.

      After Report:- Fires after the report previewer are exited, or after report output is sent to a specified destination.

Generating Warning and Errors:

Warning

WHEN <exception> THEN

 SRW.MESSAGE(999, ‘Warning: report continues’);

Error

WHEN <exception> THEN

 SRW.MESSAGE(999, ‘Error: report terminated’);

 RAISE SRW.PROGRAM_ABORT;

SRW.DO_SQL(‘CREATE TABLE SRW_LOG

                                                (RPT_NAME VARCHAR2(40),

                                                REC_NUM NUMBER,

                                                MSG_TEXT VARCHAR2(80))’);

SRW.DO_SQL(‘INSERT INTO SRW_LOG

                                (RPT_NAME REC_NUM, MSG_TEXT)

                                VALUES

                                (“PAY_REPORT”, TO_CHAR(:ID),

                                :LAST_NAME||”PAY REPORT RUN”)’);