Oracle Reports 6i - Introduction

In this tutorial you will learn about Introduction to Oracle Reports Builder, Report 

file storage formats, Oracle Reports Builder Tools, Report Wizard, Triggers in Reports, Types of Triggers and Case Study - Creating a Tabular report.

Introduction to Oracle Reports Builder

Oracle Reports Builder is a powerful enterprise reporting tool used to build reports that dynamically retrieve data from the database, format, display and print quality reports. Reports can be stored in File or Database (Report Builder Tables).

Report file storage formats

.rdf Report 
• Binary File Full report definition (includes source code and comments)
• Modifiable through Builder. Binary, executable Portable if transferred as binary.
• PL/SQL recompiles on Open/Run

.rep Report • Binary Run-Only File
• No source code or comments. Not modifiable binary, executable.
• Report Executables

RWBLD60 Report Builder 
RWRUN60 Report Runtime
RWCON60 Report Converter/Compiler [File => Administration => Compile (rdf to rep)/Convert]

Oracle Reports Builder Tools

Oracle Reports Builder comes with the following components
• Object Navigator
• Property Palette
• Data Model Editor
• Layout Model Editor
• 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.

Property Palette A Property Palette is a window that displays the settings for defining an Oracle reports object.

Data Model Editor 
To specify data for a report, a data model should be defined. 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.

Formula Columns Formulas can be entered in formula columns to create computed columns. Formulas can be written using PL/SQL syntax. Formula columns are generally preceded by CF_ to distinguish from other columns.

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 links are used to establish parent-child relationships between queries and groups via column matching.

Layout Model Editor A report layout editor contains the following layout objects

Frames 
Frames surround other layout objects, enabling control of multiple objects simultaneously

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

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

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

Parameter Form Editor 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.

Report Wizard

• When we create a default Tabular Report using report wizard, the wizard will take you through the below mentioned pages
• Report Style Tabular, Form-Like, Mailing Label, Form Letter, Group Left, Group Above, Matrix, Matrix with Group
• Query Type Choose whether to build a SQL query or an Express query.
• Data Enter a SELECT statement to retrieve the report data
• Displayed Fields Select the fields that you want to display in the output.
• Fields to Total Select the fields that you want to summarize.
• Labels for Fields Alter the labels that appear for each field and the width of each field.
• Template Select the template that you want to use for this report. A template contains standard information such as company logo, date, and so on.

Note: The above steps are different for each report style.

Group Left & Have an additional page: ‘Groups’
Group Above styles
Matrix Reports styles Have 3 additional pages: ‘Matrix Rows’ ‘Columns’ ‘Cells’ 
Mailing Label & Have 4 pages: ‘Report Style’ ‘Data’
Form Letter styles ‘Text’ ‘Template’

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.

Triggers in Reports

Types of Triggers

Formula Triggers: Formula triggers are PL/SQL functions that populate columns of type Formula.

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.

Action Triggers: Action triggers are used to perform user-defined action. These triggers do not return any value.

Validation Triggers: Validation triggers are PL/SQL functions that are executed when a parameter value is entered and the cursor moves to the next parameter. These triggers return Boolean value TRUE / FALSE.

Report Triggers: Report triggers enable execution of PL/SQL functions at specific time during execution and formatting of report.

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.

Case Study - Create a Tabular report

After invoking the report builder and connecting to the database invoke Report wizard.

Click on Tools… Report Wizard… to start the report wizard for a new report. Report wizard shows the following tab pages to enter information required for report.

Style Totals Data
Labels Fields Template

In the Style tab select ‘Tabular’ as the report style and Click Next

Ads

The Data tab allows creation of an SQL statement using Query Builder or to enter the SQL statement in the multi line edit box provided in the Data tab.
Click Next

This will take you to the next tab if your SQL statement syntax is correct.

Fields tab is used to specify the fields that must be displayed in tabular format. Select all fields by clicking on >> icon.
Click Next

The Totals tab is displayed that allows creation of Summary columns using aggregate functions.
This report does not include totals for the selected fields and thus Click Next.

The Labels tab is displayed that enable us to change the labels of the columns.
Click Next

The Templates tab is displayed that enable us to create report from templates. There are number of pre-determined templates available in Oracle Reports Builder.

Select Corporate 1 as the template. Click Finish

Click on File. Save to save the report, specify the report name and click OK.

Run the report!!

Tuning Oracle Reports 6i - II

Efficient SQL

Oracle Reports uses the Structured Query Language (SQL) to retrieve data from the relational database. So an efficient SQL increases the performance of the report execution.

There are many sql editors which show the performance of an SQL by generating the explain plan of the sequel. So based on the EXPLAIN PLAN, we can identify the time consuming (full table scan) sql and create indexes where needed.

Calculations

When performing calculations within a report (either through summary or formula columns), the general thumb rule is the more calculations that can be performed within the SQL of the report queries, the better.

If the calculations are included in the SQL, then they are performed before the

data is retrieved by the database, rather than the performed on the retrieved data by the Report.

Database-stored user-defined PL/SQL functions can also be included in the query select list. This is more efficient then using a local PL/SQL function (e.g. in a formula column), since the calculated data is returned as part of the result set from the database.

So, the more the number of formula or summary columns used in the report query, the less efficient is the report performance.

Redundant Queries

Ideally a report should have no redundant queries (queries which return data which is not required in the report), since they will clearly have an effect on performance.

Break Groups

Limiting the number of break groups can improve the performance of a report. For each column in the data model that has the break order property set

The creation of a break group may make an ORDER BY clause defined as part of the query redundant. If this is the case then the redundant ORDER BY should be removed, since this will require extra processing on the database.

Group Filter

The main use for group filters in the database is to reduce the number of records retrieved from the database. When using a group filter, the query is still passed to the database and all the data is returned to reports, where the filtering will take place.

Therefore, even if the filter is defined to only displays the top five records, the result set returned to reports will contain all the records returned by the query.

So it is always suggested to use condition ‘WHERE’ clause in the report sql query, instead of using the group filters.

Data Link

When designing the data model in the report, it is preferable to minimize the actual number of queries by using fewer, larger (multi-table) queries, rather than several simpler (single-table) queries.

Each time a query is issued, Oracle Reports needs to parse, bind and execute a cursor. A single query report is therefore able to return all the required data in a single cursor rather than many.

Also be aware that with master-detail queries, the detail query will be re-parsed, re-bound and re-executed for each master record retrieved. In this instance it is often more efficient to merge the two queries and use break groups to create the master-detail effect.

Credit goes to the below website(s) :

http://oracleapps4u.blogspot.com/search/label/Tuning%20Oracle%20Reports%206i

Tuning Oracle Reports 6i - I

Introduction 

There are a number of distinct areas to focus on when tuning Oracle Reports. It's helpful to have a clear understanding of these areas which can provide both perceived and measured improvements.

Consideration must be given to the costs involved, computing environment

complexity and the trade-offs that may occur in improving performance in a single area such as reports.

Investigation of some of these areas can result in significant performance improvements, some may result in minor performance improvements and yet others may have no effect on the actual report performance, but can improve the perceived execution time.

Performance analysis tools

Report Profile

The first step in analyzing the performance of a report by identifying the time taken by the report in various phases

1. Time spent in fetching the data (Report Sql)
2. Time spent in formatting the data (Report Layout)

Profile can be set in the report builder to identify the time consumed in each phase of the report.

Go to Report Builder

Navigate Tools -> Preferences -> Runtime settings -> Profile (Provide the path where the log file has to be saved)

The Profile log file gets saved only after the session. A sample profile file will look like this

----------------------------------- Sample Profile file ------------------------------------

LOG :

      Logged onto server: vis

      Username: apps

                                     +-------------------------------------+

                                    | Report Builder Profiler statistics |

                                    +-------------------------------------+

            TOTAL ELAPSED Time:     314.00 seconds

            Reports Time:     311.00 seconds (99.04% of TOTAL)

            ORACLE Time:       3.00 seconds ( 0.95% of TOTAL)

                            UPI:       2.00 seconds

                            SQL:       1.00 seconds

 TOTAL CPU Time used by process: N/A

Where

Total Elapsed Time:          Execution time of the report

Report Time:                    The amount of time that was spent formatting the retrieved data

Oracle Time:                    The amount of time spent waiting for the data to be retrieved

UPI time:                         The time spent establishing the database connection and parsing and executing the SQL.

SQL time                          The time spent while the database server fetches the data (percent of time spent executing SRW.DO_SQL() statements, EXEC_SQL statements, PL/SQL Cursors, etc.).

From the above example we can understand that the majority of the time is spent in formatting the retrieved data. So we have to concentrate much of our tuning effort on report layout

  

We can even trace the report profile when executing as command line argument.

The preferred method is to set the TRACEFILE (i.e. TRACEFILE=<filename>), with the additional command line arguments of TRACEMODE and TRACEOPTS.

TRACEMODE indicates whether to replace or append to the existing trace log file. TRACEOPTS is used to build the event list within the log file with TRACE_PRF being the specific profile option.

The following command line:

c:\rwrun60 report=emp.rdf userid=scott/tiger@orcl desformat=pdf tracemode=replace tracefile=emp.lis traceopts=trace_prf

Was used to produce the trace file for the Reports Profile Statistics.

Reports Trace (For Tuning and Debuging Report)

The reports trace option produces a file that describes the series of steps completed during the execution of the report.

The trace option can be set so that either all events are logged in the file or only a subset of those events (e.g., only SQL execution steps).

The trace file can provide an abundance of information that is not only useful for performance tuning, but also helps to debug reports.

The trace option can be set either from the main menu (Tools -> Trace) or from the command line argument TRACEFILE (i.e. TRACEFILE=<filename>.

Additional command line arguments

for this option include TRACEMODE and TRACEOPTS.

TRACEMODE specifies whether to replace or append to the existing trace log file. TRACEOPTS is used to specify the events to record within the log file.

Trace from Report builder

Go to Report Builder

Navigate to Tools -> Trace

Now provide trace file name with the trace options & trace mode.

  

A sample trace file may look like this:

22:04:04  MSG MSG-01000: Sequence of execution

22:04:05  MSG MSG-01001: BeforePForm

22:04:10  MSG MSG-01003: AfterPForm

22:04:11  MSG MSG-01004: Input: RESEARCH

22:04:11  APP  (  Database Column          P_DNAME

22:04:11  APP  )  Database Column          P_DNAME

22:04:11  PLS  (  Function:       beforereport

22:04:11  PLS  )  Function:       beforereport

22:04:11  APP  (  Frame                   

22:04:11  APP . (  Frame                    M_1

22:04:11  APP .. (  Repeating Frame          R_1

22:04:11  APP ... (  Group                    G_dname  Local Break:  0  Global Break:  0

22:04:11  APP .... (  Query                    Q_1

22:04:11  SQL         EXECUTE QUERY : select dname, deptno, loc from scott.dept where dname = :p_dname

                      Bind Variable               Value

                      --------------------------- -----------------------------

                      P_DNAME                     RESEARCH

22:04:11  APP .... )  Query                    Q_1

22:04:11  APP ... )  Group                    G_dname

22:04:11  APP ... (  Text Boilerplate         B_6

22:04:11  APP ... )  Text Boilerplate         B_6

22:04:11  APP ... (  Text Boilerplate         B_5

22:04:11  APP ... )  Text Boilerplate         B_5