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