Passing Parameter to Report

Example 1: Passing parameters to report

1) Go into the Data Model and then change the query:

select a.*, b.dname

from emp a, dept b

where a.deptno=b.deptno

and a.deptno = :p_deptno

2) Save

3) Re-run the report

4) Now the report will prompt the user to enter Department Number.



Example 2: Passing lexical parameter to report– example 1

1) Go into the Data Model and then change the query:

select a.*, b.dname

from emp a, dept b

where a.deptno=b.deptno

&order_by

2) Save

3) Re-run the report

4) Now the report will prompt the user to enter Lexical parameter. Let say you enter ‘order by dname’ and then run the report.



Example 3: Passing lexical parameter to report – example 2

5) Go into the Data Model and then change the query:

select a.*, b.dname

from emp a, dept b

where a.deptno=b.deptno

&and_cond

6) Save

7) Re-run the report

8) Now the report will prompt the user to enter Lexical parameter. Let say you enter ‘and a.deptno=10’ and then run the report.

Reports passing parameter

Create a manual data model with a query…select * from emp where sal between :low_sal and high_sal………

 
do proper layout model….
 

Give values

Go to sys admin in apps Register the concurrent executable and program

While Definig Concurrent Program,Go to Parameters Tab

copy Both of  token name from the object navigator…(Reports).Save it in as 2 different token id in parameter tab

save it

Go to Application Developer to create valuesets

Now while submiting reports these valuesets can be used.

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.