repots10g

Triggers in Report Data Source and Report Style

Triggers in Reports

Types of Triggers

Global triggers called the Report Triggers :

(1) Before Parameter Form

(2) After Parameter Form

(3) Before Report

(4) After Report

(5) Between Pages

Other Triggers :

(1) Validation Triggers

(2) Format Triggers

(3) Action 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.

The Firing sequence for report triggers is

• Before Parameter Form
• After Parameter Form 
• Before Report 
• Between Pages 
• After Report.

Data sources

It is a good idea to prepare your data even before starting to develop the report. OracleReports Developer comes with its own Query Builder that is fairly useful, but the query statement editing box is too small for complex queries and its font is not a fixed size, so that it is not possible to format code using indentation. Large queries are so difficult to read that it is easier to copy the text into an SQL editor, modify it and copy it back. Building the query in an SQL Editor will also allow you to run and optimize it. The report will never run faster than the query it is based on.

SELECT  PAPF.EMPLOYEE_NUMBER

        ,PAPF.FULL_NAME EMPLOYEE_NAME

        ,PAPF.DATE_OF_BIRTH

        ,PAPF.ORIGINAL_DATE_OF_HIRE

        ,PJ.NAME JOB

        ,PP.NAME POSITION

        ,PA.ADDRESS_LINE1 || ',' ||

        PA.ADDRESS_LINE2 || ',' ||

        PA.ADDRESS_LINE3 ADDRESS

         ,PA.TOWN_OR_CITY

        ,PA.POSTAL_CODE

FROM    PER_ALL_PEOPLE_F PAPF

        ,PER_ALL_ASSIGNMENTS_F PAAF

        ,PER_JOBS PJ

        ,PER_POSITIONS PP

        ,PER_ADDRESSES PA

WHERE   PAPF.PERSON_ID=PAAF.PERSON_ID

AND     SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE

AND     SYSDATE BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE

AND     PAAF.JOB_ID=PJ.JOB_ID

AND     PAAF.POSITION_ID=PP.POSITION_ID

AND     PAPF.PERSON_ID=PA.PERSON_ID

AND     PJ.NAME=:JOB

Report Styles

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:

v      Tabular – a simple spreadsheet-like report

v      Form – displays one form-like record

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

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

v      Matrix – Special summary report that will calculate values corresponding to a horizontal and a vertical grouping;

v      Matrix with Group – A grouping field added to the matrix report

v      Mailing Labels

v      Form Letter – similar to the Mail Merge capability

Report

We will see, how to develop the report. 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.

We can develop the report following two ways

            Use the Report Wizard

            Build a new report manually

Oracle Report Builder Tools

How to open Report Builder:

Navigation :

Start Menu From the Destop -> All Program -> Oracle Developer Suit  10g -> Reports Developer -> Reports Builder

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

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. The Layout Model (shown in Figure 5) displays the physical layout of the data. You can have multiple frames based on the same logical groups as defined in the Data Model.

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 the database. 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. 

About Report

What is report?

To get answers to questions like these, you'll want to generate a report. There are lots of ways you can query, filter, sort and group your data to give you the answers you're looking for. Then output those results in any format that works best. For example, you're not stuck displaying tasks in a chronological list. Try presenting them in a calendar or timeline instead.

Reports let you:

v      Specify which records you want to see. Filter out some of your records (by specifying matching values to use). Visiting the in-laws in Woonsocket? UseWoonsocket or nearby area codes as matching criteria that will return a list of the clients in the area you could visit while you're there.

v      Order those records exactly as you want. Specify how records are sorted or grouped. You could order that list of clients alphabetically or by how much each one has purchased from you.

v      Design the layout and set colors to suit your purpose. Not only can you choose from a variety of formats (see list of links below) you can customize the layout and look of each of those formats. Rearrange and resize columns. Change the colors and labeling of charts in a snap.

v      Ask the user what information they seek. You don't need to decide everything. Perhaps you'd like to give your application users an opportunity to choose what company's data they want to see. Or what quarter's sales they need to track. If so, look into creating a report that prompts users to enter criteria, then displays the results.

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

This discusses the basic techniques that a successful Oracle Reports developer should know. It is intended for the novice user with strong SQL-PL/SQL knowledge who is interested in learning to use Oracle’s Reports Developer.

Reports Developer Basic Tools

To start using Reports Developer, you need the software, a database, and a set of templates. Although Oracle provides a set of templates.

Report file storage formats

.rdf Report 

.rep Report

.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