Report Builder Components

Builder Components

1. Data

2. Layout

3. Object

4. Report

Parameter Form

6. Program

Attached Libraries

Data Model
The Report
Editor’s Data Model view enables you to define and modify the data model
objects for a report.

Data Model
Comprises tool Palette which Comes with several Options They are:

1. Select
2. Magnify
3. SQL Query
4. Ref Cursor Query
5. Express Query
6. Summary Column
7. Formula Column
8. Placeholder Column
9. Cross Product
10. Data Link

The tool
palette is a set of tools you can use to create and manipulate objects. Click a
tool to activate it for a single operation, or double-click a tool to
“lock” it for multiple operations. The tools in the tool palette vary
depending on the Report Editor view.

The Selector toolbar provides tools to help you select or arrange dimension
values that meet your criteria. The Selector toolbar is displayed at the top of
the Selector dialog box.

To magnify a hard-to-see portion of your report or reduce the image to get a
sense of your report’s overall layout.

SQL Query
The SQL Query Statement property is a SQL SELECT statement that retrieves data
from the database for your report. Enter a valid SELECT statement not to exceed
64K. The upper limit may vary between operating systems All features of the
SELECT statement are supported, except the INTO and FOR UPDATE clauses.

In building your SELECT statement, you can do the following:

1. Use the Tables and Columns dialog box
2. Insert comments
3. Insert bind and lexical references
4. Review SQL errors

Required/Optional : Required

Ref Cursor Query
A ref cursor query uses PL/SQL to fetch data. Each ref cursor query is
associated with a PL/SQL function that returns a strongly typed ref cursor. The
function must ensure that the ref cursor is opened and associated with a SELECT
statement that has a SELECT list that matches the type of the ref cursor.

You base a query on a ref cursor when you want to:

1. more easily administer SQL
2. avoid the use of lexical parameters in your reports
3. share data sources with other applications, such as Form Builder
4. encapsulate logic within a subprogram

Summary Column
A summary column performs a computation on another column’s data.
Using the Report Wizard or Data Wizard, you can create the following summaries:
sum, average, count, minimum, maximum, % total.

You can also create a summary column manually in the Data Model view, and use
the Property Palette to create the following additional summaries: first, last,
standard deviation, variance.

Formula Column
A formula column performs a user-defined computation on another column(s) data,
including placeholder columns.

Placeholder Column
A placeholder is a column for which you set the data type and value in PL/SQL
that you define.

You can set the value of a placeholder column in the following places:
1. The Before Report Trigger, if the placeholder is a report-level column
2. A report-level formula column, if the placeholder is a report-level column
3. A formula in the placeholder’s group or a group below it

Cross Product
The Cross Product Group property is the group that contains the source groups
of the Horizontal and Vertical Repeating Frames. The cross product group
correlates values between one or more groups to produce the data in the matrix.

Values Enter a valid cross product group name.
Applies to matrices
Required/Optional required
Default blank

Data Link
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.
When you create a data link in the Data Model view of your report, Report
Builder constructs a clause (as specified in the link’s Property Palette) that
will be added to the child query’s SELECT statement at runtime.

Layout Model
The Report Editor’s Layout Model view enables you to define and modify the
layout model objects for a report. In this view, objects and their property
settings are represented symbolically to highlight their types and

This is the view where you can design your Report Layout.

Object Navigator
The Object Navigator provides a hierarchical display of all major objects in a
report or template, including attached libraries and external queries. Using
this view, you can take such actions as select an object, bring up the Property
Palette for an object, edit an object’s PL/SQL, drag and drop PL/SQL program
units, and search for an object by name.

Report Triggers
Report triggers execute PL/SQL functions at specific times during the execution
and formatting of your report. Using the conditional processing capabilities of
PL/SQL for these triggers, you can do things such as customize the formatting
of your report, perform initialization tasks, and access the database.

To create or modify a report trigger, use Report Triggers in the Object
Navigator. Report triggers must explicitly return TRUE or FALSE. Report Builder
has five global report triggers which are fired in the following sequence.

1. Before Parameter Form trigger
2. After Parameter Form trigger
3. Before Report trigger
4. Between Pages trigger
5. After Report trigger

1. Before Parameter Form trigger
The Before Parameter Form trigger fires before the Runtime Parameter Form is
displayed. From this trigger, you can access and change the values of
parameters, PL/SQL global variables, and report-level columns. If the Runtime
Parameter Form is suppressed, this trigger still fires. Consequently, you can
use this trigger for validation of command line parameters.
Definition Level Report
On Failure:
Displays an error message and then returns to the place from which you ran the

2. After Parameter Form trigger
The After Parameter Form trigger fires after the Runtime Parameter Form is
displayed. From this trigger, you can access parameters and check their values.
This trigger can also be used to change parameter values or, if an error
occurs, return to the Runtime Parameter Form. Columns from the data model are
not accessible from this trigger. If the Runtime Parameter Form is suppressed,
the After Parameter Form trigger still fires. Consequently, you can use this
trigger for validation of command line parameters or other data.

Definition Level Report
On Failure:
Returns to the Runtime Parameter Form. If the Form is suppressed, then returns
to place from which you ran the report.

3. Before Report trigger
The Before Report trigger fires before the report is executed but after queries
is parsed and data is fetched.
Definition Level Report
On Failure:
Displays an error message and then returns to the place from which you ran the

4. Between Pages trigger
Between Pages trigger fires before each page of the report is formatted, except
the very first page. This trigger can be used for customized page formatting.
In the Runtime Previewer or Live Previewer, this trigger only fires the first
time that you go to a page. If you subsequently return to the page, the trigger
does not fire again.
Definition Level report
On Failure:
Displays an error message when you try to go to the page for which the trigger
returned FALSE. The Between Pages trigger does not fire before the first page.

5. After Report trigger
The After Report trigger fires after you exit the Runtime Previewer, or after
report output is sent to a specified destination, such as a file, a printer, or
a mailid. This trigger can be used to clean up any initial processing that was
done, such as deleting tables.
Note, however, that this trigger always fires, whether or not your report
completed successfully.
Definition Level Report
On Failure:
Does not affect formatting because the report is done. You can raise a message,
though, to indicate that the report did not run correctly

Note: The After-Report trigger does not fire when you are in the Live

Layout Model Properties

In this Layout model, we have several Options

Frames are used to surround other objects and protect them from being
overwritten or pushed by other objects.
For example, a frame might be used to surround all objects owned by a group, to
surround column headings, or to surround summaries.
When you default the layout for a report, Report Builder creates frames around
report objects as needed; you can also create a frame manually in the Layout
Model view.

Repeating Frames
Repeating frames surround all of the fields that are created for a group’s
columns. The repeating frame prints (is fired) once for each record of the
When you default the layout for a report, Report Builder creates repeating
frames around fields as needed; you can also create a repeating frame manually
in the Layout Model view.

This allows to embed the text in the layout view.

A field is the layout container for each column in the layout. A field is owned
by the object surrounding it, typically a repeating frame, unless the field is
a summary (in which case it is owned by a frame).

Anchors are used to determine the vertical and horizontal positioning of a
child object relative to its parent. The end of the anchor with a symbol on it
is attached to the parent object…

Parameter Form
The Report Editor’s Parameter Form view enables you to create a Runtime
Parameter Form for your report. You can select pre-defined system parameters
for your form using the Parameter Form Builder, or you can create your own.

Program Units
Program units are packages, functions, or procedures that you can reference
from any PL/SQL within the current report.

Stored program units (also known as stored subprograms) can be compiled
separately and stored permanently in an Oracle database, ready to be executed.
Once compiled and stored in the data dictionary, they are schema objects, which
can be referenced by any number of applications connected to that database.
Stored program units offer higher productivity, better performance, memory
savings, application integrity, and tighter security. For example, by designing
applications around a library of stored procedures and functions, you can avoid
redundant coding and increase your productivity.

Stored program units are stored in parsed, compiled form. So, when called, they
are loaded and passed to the PL/SQL engine immediately. Also, they take
advantage of shared memory. So, only one copy of a program unit need be loaded
into memory for execution by multiple users.

Attached Libraries
Attached libraries are external PL/SQL libraries that you have associated with
a report or another external library. When an external library is attached, you
can reference its packages, functions, and procedures from within your report.
For example, if you attached an external library name MYLIB to your report and
it contained a function named ADDXY, then you could reference ADDXY from any
PL/SQL in the report.

Report Customizations
You can customize business intelligence reports by both content and layout. You
also have the option to add columns, filter the data and sort the resulting
information. For example, you can add another column for a running total, or
insert grand totals and subtotals as you need them. In layout, you can change
column headings, report titles and the order of columns. You can also display
the data in graphical form, such as bar graphs or pie charts.

Customization is enhancement of new features to the Existing Report Content and
Layout depending on the new requirement.

  • January 12, 2015 | 22 views