Oracle Apps – Custom Reports Development & Customization Process

Report Builder Components

Report Builder Components are

1. Data Model
2. Layout Model
3. Object Navigator
4. Report Triggers
5. Parameter Form
6. Program Units
7. 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

Express Query

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 relationships.

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 report.

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 report.

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 Previewer.

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 group.
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.


One thought on “Oracle Apps – Custom Reports Development & Customization Process

Want to give some comment to author ( Shivmohan Purohit )

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s