1)Report Builder is one of the Oracle GUI Tool
Graphical user Interface
2)By using this we can develop simple and Complex reports
3)There are two ways we can develop the report
1)Wizard base
2)Manuval
4) 1)Data Model
2)Layout model
DataModel will be used to select the data from Database,
SELECT Statemen and Formulas,Summary functions and so on.......
Layout model will used to displays the datamodel fields in the desired
format.
Layout Model Objects:
1)Text : Will be used to display the standard text information
like titles,prompts,headings,Address...............
2)Frame : Will be used to display the layout objects only one time
like totals,titles,Headings and so on
3)Repeating Frame: Will be used to display the objects multiple times
like database columns,Sub Totals,Page Totals.......
4)Field : Will be used to display the Data base columns,variables and
so on.
First page : Employee Information Report
Second Page : Empno Ename Sal hiredate Comm
Last page : End of the report
Summary Column : If we want to apply summary functions then we will
go for summary columns like Average,count,min,max and so on.
1)Group level : Executed for every record in the group
2)Report Level : executed only once for entire report.
Empno Ename Sal HireDate
Total Emp:
Total Sal:
Min Salary :
Max Salary :
Formula Columns : When we want implement some logic and return the
value then we will use formula columns.
It is a PL/SQL Function. where we can write PL/SQL code
we can return only one value.
1)Group Level
2)Report Level
Empno Ename Sal Grade (If sal >5000 'A'
(If sal<=5000 and >3000 'B')
(if sal<=3000 then 'C')
Total Emp:
Total Salary:
Average Emp Salary : (Total salary/TotalEmp)
PlaceHolder Columns:
A datatype which stores some value and we can use this across the
report.
It will work lika Global variable in the reports.
If we want to return more than one value from the Formula Columns then
it will be used.
Empno Ename Sal HireDate Comm TotalSal(SAl+Comm) Tax
Tax : if (totalsal>5000 then 7% tax
(Totalsal>3000 and <=5000 then 4%tax
(Totalsal<=3000 then 2% tax)
Empno Ename Sal Comm Tax Netsalary
Comm := 15% sal
tax : 7 % (sal+comm)
netsal : sal+comm-tax
Triggers :
Format Trigger
Validation Trigger
Action Trigger
Report Triggers
Format Trigger : To Hide or display the layout objects dynamically it will be
used.
layout objects means frame,repeating frame,field,............
Action Triggers: If we want to develop drill down reports then we will use
Action Triggers.
Place the Button in the layout when user press the button we can
execute some PL/SQL code.
Report Triggers: 5 Types which will fired automatically when we run the
report
Sequence Order
1)Before Parameter Form
2)after Parameter Form
3)Before report
4)Between pages
5)After REport
Before Parameter Form : Will Execute before the parameter form is going to
open.
To assign any default values for the parameter.
After Parameter Form : After the Parameters are entered in the form and
submit it will be executed.
To change the Parameter values dynamically
And To populate the Lexical parameter values.
Before Report : It will be executed before report is executing and select
data from database.
Between Pages : When cursor moves from one page to another page it will be
executed .
It will be executed (n-1) times.
After Report : after completion of the report. once the output is reach the
destination (Printer,file,fax,e-mail) then it will be executed.
Parameters :
1)System Parameters
2)User Defined Parameters 1)Bind
2)Lexical
Bind parameters will used to pass the the value into the query in the WHERE
Clause
Lexical parameters will be used to replace the string in side of the query
in any clause.
From Deptno
To Deptno
Deptno Empno Ename Sal HireDate
Total Emp : should display if >=5
Total Salary : Should display if >=7000
Before Parameter form is open Deptno = 10
To Deptno = 20
Sort By : Empno
Sal
Deptno
SELECT * FROM EMP
&P_ORDER_BY
After Parameter Form Trigger
From Deptno
To Deptno
if user pass the deptno then select between that
otherwise select all the dept employees data
SELECT * FROM EMP
WHERE DEPTNO BETWEEN :P_FROM AND :P_TO
If P_from is entered then it should retrieve all dept from the specified dept
if p_to is entered then it whould retireve upto specified deptno.
WHERE DEPTNO>=:P_FROM
WHERE DEPTNO<=:P_TO
Bind Lexical
1)To pass the Values into the WHERE 1) To Replace the string in the
clause
query
2)We will use the symbol ":"
2)We will use the symbol "&"
3)Can be used only in the WHERE 3)In Any Clause
clause
System Parameters :
DESFORMAT : Html,pdf.....
DESNAME : Name of the Printer,E-mail,Fax,File
DESTYPE : File,Screen,Print,Fax,E-mail
MODE : Bitmap,Character
Master Detail Reports:
Confine Mode :
Lock mode if it is on then we can not take
the child object out of the Parent Object.If it is
off then we can take out of the parent.
FlexMode:
If Flex mode is on . If we Incr/Decr the child
object automatically parent object also incr/decr.
If it is off then Only child object will be decr/Incr.
Program Unit :
Is a PL/SQL objects(Procedures,Functions,Packages)
which will be stored in the Report we can use only in
the Current Report.
Program units PL/SQL Objects
1)Will be stored in the file 1)Stored in the Database.
2)Can be used only in the 2)We can use any report
Current Report
3)Improve the Performance 3)It takes more time
Libraries : Group of Program units and Libraries
We can attach the Attach the Library to another report
and use the Program units.
We can not attach the Program units to another reports
directley that's why we will PL/SQL Library to attach
.pll - Source Code - Program Link Library
.plx - Executable Code - Program Link Executable
Lexical Parameter:
To replace the string inside of the query dynamically
Order By Clause:
SELECT EMPNO ,
ENAME,
DEPTNO,
SAL
FROM EMP;
WHERE DEPTNO BETWEEN :FROM and :TO;
Sort By : EMPNO
ENAME
SAL
DEPTNO
From Deptno :
To Deptno :
Bind Lexical
1) To Pass the Value 1)To Replace the String in the query
2)Only in WHERE Clause 2)in any Clause
3)Any Datatype 3)Must be Character
4)VAlues will be passed in the Parameter 4)Values will be assigned dynamically
form in the After Parameter Form Trigger
5)Symbol ':' 5)Symbol '&'
