Report Triggers

Trigger types in Report:


1. Report Trigger.
2. Data Model Trigger.
3. Layout Trigger.

Report Trigger:

This Enable you to use PL/SQL functions in the Reports during execution and formatting.

You can

1) Customize the Formatting of your report.

2) Initialize Certain Tasks Before Running Report.

3) Access The Database.

4) Create Dynamic WHERE Clause.

Using Report Triggers.

Types:

Types Fires

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

1. BEFORE PARAMETER FORM.
Before Runtime Parameter form is displayed.

2. AFTER PARAMETER FORM.
After Parameter form is displayed.

3. BEFORE REPORT.
After Queries are parsed before records are fetched.

4. BETWEEN PAGES.
For every Pages except first page.

5. AFTER REPORT.
After exiting Live Previewer.


Creating Dynamic Where Clause:

We Can Create Dynamic Where Clause Depending Upon the Value Entered by The User in the Runtime.

Here two possibilities are there

1. Bind Parameter is Not Null

2. Bind Parameter is Null

Example:-

1. Query:- SELECT * FROM EMP &PWHERE;

2. Create one User Parameter P_DNO. Set Data type to NUMBER and Width to 20.

3. Write Following Code in AFTER PARAMETER FORM Trigger.

FUNCTION AFTERPFORM RETURN BOOLEN IS
BEGIN
IF : P_DNO IS NULL THEN
:PWHERE := ‘’;
ELSE
:PWHERE := ‘WHERE DEPTNO=:P_DNO’;
END IF;
END;

Data Model Trigger:

It Consists Of

• PL/SQL Group Filter Triggers.
• Parameter Validation Triggers.

Using PL/SQL Group Filter:

Can be used for

• Restricting the Records in a Specific Group, depending on other processing in the Report.

• Performing PL/SQL Processing for each Record in a Report.

• Restrict The Value Of Lower Group in the Hierarchy.


Example:-

1. Query:-SELECT * FROM EMP;

2. Set Property Palette of G_EMP as Given Below:

Group Filter : PL/SQL Filter.

3. Write Following Query in Group Filter Program Unit Editor

FUNCTION G_EMPGROUPFILTER RETURN BOOLEAN IS
BEGIN
IF: DEPTNO= 10 THEN
RETURN (TRUE);
ELSE
RETURN (FALSE);
END IF;
END;


Validating a Parameter Value:

You Can Validate both System and User Parameters Using Validation Trigger Property.

Example:-

Query: - SELECT * FROM EMP WHERE DEPTNO=: P_DNO;

Select Validation Trigger Property in the Property Palette of User Parameter P_DNO and Write Following Code:

FUNCTION P_DNOVALIDTRIGGER RETURN BOOLEAN IS
BEGIN
IF : P_DNO IN (10,20,30) THEN
RETURN (TRUE);
ELSE
RETURN (FALSE);
END IF;
END;


Layout Model Trigger:

It Consists Of Format Triggers.

Used For:

1. Suppressing Entire Layout for a Query.

2. Suppressing Individual Records.


Example:-

1. Query:- SELECT * FROM EMP E, DEPT D WHERE E.DEPTNO(+)=D.DEPTNO;

2. Create a Summary Column to count the number of Members in a Particular Dept.

3. In the Layout Model, Create a Piece of Boiler Plate Text ‘NO EMPLOYEES’.

4. Write The Format Trigger on Column Heading:

FUNCTION RETURN BOOLEAN IS
BEGIN
IF :CS_1=0 THEN
RETURN (FALSE);
ELSE
RETURN (TRUE);
END IF;
END;

5. Write the Format Trigger on Boiler Plate Text Created:

FUNCTION RETURN BOOLEAN IS
BEGIN
IF :CS_1=0 THEN
RETURN (TRUE);
ELSE
RETURN (FALSE);
END IF;
END;

6. Compile the Trigger Codes and Run The Report.