Report Builder
Report is an output in specific layout
Report Builder is used
to generates the reports for clients in-order analyze their business
performance.
Ex: Super Market :
Sales Report at end of the Day or Monthly / Quarterly / Yearly report.
Company :
Employees report.
The Report Builder
provides the following and ever thing is called as an object.
1) Reports 2) Templates 3)
External Libraries
4) PL/SQL libraries 5) Debug Actions
6) Stack 7) Bulilt-in Packages 8) Database Objects
Reports consist of
following
1) Data Model
2) Layout Model
3) Parameter form
4) Report Triggers
5) Program Units
6) Attached Libraries
Working with
Data Model and Layout Model
Data Model :
The Data Model view is a work area in which you define
the data to be used in your report. You
create, define, and modify data model objects (queries, groups, columns,
parameters, and links). Report Builder uses the data model to determine what
data to retrieve for the report. The
data retrieved from the database may or may not appear in the report output.
Access
There are several ways
that you can access the Data Model view:
à Click the Data Model tool.
à Choose View Data Model.
à From the Windows menu, select a window with the Data
Model in it.
à From the Object Navigator, double-click the Data
Model node's icon.
Data Model consist
of following objects
1) System Parameters 2) User Parameters 3) Queries
4) Groups 5) Formula Column 6) Summary Column
7) Place holders column 8) Data Links
Layout Model
The Layout Model view is a work area, where you can
define the format of your report output.
To do this, you work with layout objects, such as frames, repeating
frames, fields, boilerplate, anchors, and Graphics objects. When you run a
report, Report Builder uses the Layout Model as a default template for the
report output. This template will grow
or shrink based on the data fetched for and the PL/SQL logic assigned to the
layout objects.
Layout Model
consists of following objects
1)
Header Section 2)
Main Section 3) Trailer Section.
A report can be
developed in two ways
1) Using Report Wizard
and 2) Manually
Steps to develop a
simple report by using Report Wizard
1) Start report
builder
2) Select the following
as you desired
Designing :
a) Use the
Report Wizard --> to develop report automatically by report developer
b) Build a
new report manually --> to develop report manually as you desired.
c) Open an
existing report --> to open the already existing report in your system
Select Use the
Report Wizard
3) Select OK Button à Next
4) Specify the title ex:
Employees Report --> Select the option as you desired. ( ex : select the table option )
--->
Click on Next Button
5) Select the Sql
Statement in the following
6) supply the sql query
as shown in Query Statement Text box ( select empno, ename, sal from emp )
Click
on Connect
7)Supply the User Name
and Password and click on connect
Click on next button
8) Select the
required fields in the following.
> to add fields one by one to displayed
section
> > to add all fields at time to displayed
section
<
to remove fields one by one from displayed section
< < to remove all fields at time from displayed
section
Click on Next
button
9) Select the
fields for which you would like to calculate sum, count, total..... as shown in
the following
Click
on Next.
10) Edit the Labels
and width in the following as you desired.
Click
on Next. and FINISH
For the above Data
Model will be as shown below
Layout Model will be
as shown below
Report Shows as below
12) Compile the above
module
Programs --> Compile --> All
click on OK.
13) Run the module
View -->Runtime Preview.
Steps to develop a simple report by Manually ( it is
recommended in the real time projects )
Ex: Report on
Employees as shown below.
EmpNumber EmpName Salary
1) Start report
builder
2) Select the
following as you desired
Designing :
a) Use the Report Wizard --> to
develop report automatically by report developer
b) Build a new report manually --> to
develop report manually as you desired.
c) Open an existing report --> to open
the already existing report in your system
Select
Build a new report manually
3) Select OK Button
4) Select Sql Query
shown below and drop on Data Model area.
5) supply the sql quarry
as shown in Quarry Statement Text box
(
select empno, ename, sal from emp )
Click on OK
supply the user name and password and then connect.
Click
on OK
6) for the above
data model will be as shown below.
Development of
Layout Model
It consists of the
three sections 1) Header Section 2) Main Section 3) Trailer Section
1) Header Section :
This is to show the company title and logo and etc....
2) Main Section : It is an area where we can design the layout of the report.
Step1 : Header
Section
1) Develop the Header
Section as shown below.
2) Select T ( Text ) on the left side control panel
and place on Layout area and then supply the
text as you desired as shown below.
Step2 : Main Section
1) Develop the main
section as explained below
2) Select the Frame from Tool Palette and place on layout area
--- Frame to show headers like
EmpNumber EmpName Salary
3) Select the T
(text) from the Tool Palette objects and
place on frame of layout area as shown
below.
4) Select Repeated
Frame from Tool Palette and place inside frame ( which is on the layout
area)
Frame will be
displayed only once where as repeated frame will be displayed for each and every record.
5) Select the Frame
and right click, go to property palette and change the following
properties
1)
Name ( optional ) -- as you desired.
ex.. EMP_REPORT
2) Vertical Elasticity -- Expand
3) Horizontal Elasticity – Variable
6) ) Select the
Repeated Frame and right click, go to property palette and change the
following properties
1)
Name ( optional ) -- as you desired.
ex.. EMP_REPORT
2) Source -- G_EMPNO
2) Vertical Elasticity -- Expand
3) Horizontal Elasticity -- Variable
7) Select the text
filed and place on the repeated frame under
the headers as shown below
such as
F1 , F2, F3 ....
8) Select Text field
and right click, go to property palette and set the following
1) Name ( optional ) -- as you desired
2)
Source -- empno ( Name of the data base table column )
9) Repeat the same
procedure for all text fields
Step3: Develop the
Trailer Section
2) Place text on
Trailer section of Layout Model as shown below
Step 4:
Compile the module
Program -->Compile --> All
Click on OK
Step 5: Run the module
: View --> Runtime Preview
Final Report as shown below
Page1:
Page2:
Page3:
To Set No.of Records
per Page go to repeating frame Properties
Set the Maximum Records per Page : 6 ( as desired )
Additional features
Select the desired one
such as text fields, lines, text and select the desired colors ( Fill Color,
Line Color, Text Color etc...)
Margin Setting :
Select the Edit Margin
as shown below and set the margins on Header Section, Main Section and Trailer
Sections as you desired.
when Edit Margins is
being used we can not work with layout area.
to work with the
layout area we must quit from the edit margins mode
click on edit margins
again to quit from the edit margins mode
Default Value
value is null then go to property palette and set value is
null to required value ( default value )
ex: hiredate is null
set value is null to some date ( 23-jul-2009 )
update emp set
hiredate=NULL where deptno=10
Working with Summary
Column :
It provides the
following built-in functions
1) sum 2) average 3)
minimum 4) maximum 5) count 6) first
7) last 8) % of total
9)Std. Deviation 10) variance
It can be created
in two levels 1) Group Level 2) Report Level
1)
Report Level à It will be displayed only once
2)
Group Level à It will be
repeated for every record in the table
Report Level :
Ex: 1) Count of
employees, 2) sum of the salaries, 3) Max Salary
Step1:
1)
Start Report
Builder
2)
Select manually
3)
Select the query
form tool palette of data model ( left side ) and write the required query
Ex: select empno, ename, sal,
hiredate, comm from emp ( these are in the group level )
4)
Select Summary Column form tool palette of data
model and place on data model area
5)
Right click and
set the properties
1) For Count of employees :
a) Name : Emp_Count
b) Function : Count
c) Source : Sal
Repeat the same
procedure for
sum of the salaries and Max
Salary as shown below
Step2:
1)
goto layout Model
and design the out put layout as shown below
2) Set Properties to text fields 1) Total No.of Employees 2) Sum of the salaries 3) Maximum Salary
Ex: Total No.of Employees
--- go property palette and set the Source
( Emp_Count )
Repeat the same
procedure for 2) Sum of the salaries 3)
Maximum Salary
Step3: Compile and
run
Group Above Report
Working with Formula Column :
It can be created in
two levels 1) Group Level 2) Report Level
1) Report Level à It will be displayed only once
2)
Group Level à It will be repeated for every record in the table
Using this user can
define formulas and can also write sql statements by using PL/SQL window.
Ex: 1) Average Salary and 2) Name of the employee who is getting
maximum salary
1) Average
Salary
Step1:
a) go to Data Model and Select Formula Column and place
on Data Model area
b) Set the properties
Name : Avg_Sal ( as desired
)
Data Type : Number
PL/SQL Formula : Double Click and write the following
function Avg_SalFormula return Number is
begin
return ( :Sal_Sum/:Emp_Count );
end;
compile above coding in PL/SQL Window
by using compile option
Step2:
Go to Layout model and design the Average
Salary field and Map with the Avg_Sal ( formula column )
Step3 :
Compile and run the module.
2) Name of the
employee who is getting maximum salary
Step1:
a) go to Data Model and Select Formula Column and place
on the Data Model area
b) Set the properties
Name : Max_Emp_Name ( as desired )
Data Type : Char.
PL/SQL Formula : Double Click and write the following
function
Max_Emp_NameFormula return Number is
l_name varchar2(100);
begin
select
ename into l_name from emp where sal=:Max_Sal;
return l_name;
exception
when
too_many_rows then
return
('duplicate emp');
when
no_data_found then
return ('no
emp');
when others
then
return
('Unknown error');
end;
compile above coding in PL/SQL Window
by using compile option
Step2:
Go
to layout model and design the Name of Employees field and set Property
Source : Max_Emp_Name
Step3 :
Compile and run the module.
Group Level Columns
Defining group level formula
column / summary column
Ex: User wish to generate the
following report
Empno
|
EmpName
|
EmpSalary
|
EmpComm
|
EmpGrade
|
7565
|
Allen
|
115000
|
500
|
A
|
1287
|
Scott
|
850
|
150
|
D
|
3464
|
Ramesh
|
4000
|
400
|
C
|
7245
|
Smith
|
9000
|
200
|
B
|
If sal < =1000 then grade ‘D’
If sal >1000 and <=5000
then grade ‘C’
If sal > 5000 and <=10000
then grade ‘B’
If sal >10000 then grade ‘A’
In the above EmpGrade is group level formula column
Step1: Data Model
1)
Query : select empno, ename, sal, comm
from emp
2)
formula column : drop the formula column into group and set properties
Name
: Emp_Grade
Data
Type : Char
Width :
1
PL/SQL formula
: write the following code
function Emp_GradeFormula return Char is
begin
if (
:sal>=10000) then
return
('A');
elsif ( :sal>=5000 ) then
return
'B';
elsif ( :sal>=3000 ) then
return
'C';
else
return
'D';
end
if;
end;
Step2: Layout Model
Design
Layout as explained in the previous examples and set the properties of text
fileds
For
grade set the source as Emp_Grade.
Parameter From
It is used to pass the input parameters to the
application in-order to generate the dynamic reports.
Parameters are of two
types 1) Bind Parameters and 2) Lexical Parameters
1) Bind Parameters : These can be data types of CHAR, NUMBER, DATE. These are used to pass
the input values to Query ex: in clause
Deptno= bind parameter ( which is an input ).
2) Lexical Parameters : These must be character data type. These are use to
replace the string inside of the
query in any clause.
Working with Bind
Parameters
Ex : Select * from emp
where deptno=(user input value )
Enter Dept Number : 20.
Step1:
a) Go to object navigator through Window à Object Navigator
b) Select User parameters and create.
c) Set the properties
Name : P_Deptno ( as desired )
Data Type : number.
Width : 2 ( as desired )
Initial value : 10 ( as desired and
optional )
List of Values can also be provided:
For list of values : Double click on
list of values
It provides list in two ways 1)
Static Values 2) Select Query
1) Static Values :
Specify the value in the value box and click on add
Repeat the same no of
values as desired.
To
remove the values form the list we can use remove option.
2)
Select Statement:
Select the Select
Statement option and supply the query in the Sql Query statement box. Ex: Select deptno from dept;
Step2: go to Data Model and Double Click on Q_1 and change the query as follows
select empno, ename, sal, hiredate, comm from emp
where deptno= : P_Deptno ( it is name of the input parameter)
Step3: Go
to Parameter form
1) by selecting parameter
form on report builder ( 4th icon
on tool bar)
( or )
2) view à select parameter form
3) Right click and select parameter form builder. It shows as below.
Set the required properties
as desired
Title : DeptNumber ( Report
Parameters )
Hint Line : Dept Number
Input ( Enter
values for the parameters)
Go to end and set the P_Deptno : Enter the Dept number ( P Deptno)
Step4:
Compile
and run the module.
It shows as below
|
Enter the Dept Number
Two parameters
Ex: to display records
of employees between range of values. ( between 10 to 40 )
|
Enter the From Dept
Number
|
Enter the To Dept
Number
Step1:
Go to object navigator
and create two parameters
Ex: P_From, P_To
Step2:
Go to Data Model and change the query as below.
select empno, ename,
sal, hiredate, comm from emp where deptno between : P_From and
: P_To
Step3:
Go to parameter form
and right click and set the labels as explained in the previous example.
Working with
Lexical Parameters.
These parameters must
be Character data type only
Ex : Select * from emp where ( user input value )
Select * from emp where deptno=20
order by sal desc , the under lined string is an user input.
|
Enter the query string :
Step1:
a) Go to object navigator Select User parameters and create.
b) Set the properties
Name : P_Query ( as desired )
Data Type : character ( mandatory ).
Width : 200 ( as desired )
Step2:
Go to Data Model and Double Click on
Q_1 and change the query as follows
select empno, ename, sal, hiredate, comm from emp &P_Query
here &P_Query is the name of the parameter for strings we should
use & and should not use : before the parameter
Step3:
a) Go to Parameter form Right click and select parameter
form builder.
Set the required labels as explained in the previous example.
Step4:
Compile
and run the module.
It shows as below
Ex1:
|
Ex2:
|
REPORT TRIGGERS
Report Builder
provides five types of Triggers. These will be executed in a sequential manner
1)
Before parameter
form
2)
After parameter
form
3)
Before Report
4)
Between Pages
5)
After Report
1)
Before Parameter
Form : It will be executed before invoking of the parameter form.
2)
After Parameter
Form : It will be executed after closing the parameter form ( which is on user
pressed enter key after supplying the input values )
3)
Before Report : It
will be executed before generating the report.
4)
Between Pages :
It will be executed when navigating between pages: if no.of pages are “n “ then
it will be executed for ( n-1 ) times ex: if no.of pages are 5, then it will be
executed for 4 times.
Reverse and second time it will not be applicable.
5)
After Report : It
will be executed after quitting form the report.
These triggers will be executed weather the Parameter form developed or
not.
Purpose of above five triggers in the real time scenario
Before parameter
form :
1)
Used to add some
built-in packages to the module.
2)
Used to
initialize the form parameters.
This can tested by writing trigger as shown below.
function BeforePForm return boolean is
begin
srw.Message(160, ‘before
parameter form'); // srw == Standard report writer.//
:P_Dptno=20; // initialization
//
return (TRUE);
end;
similarly others can also be
tested by writing above code.
After Parameter
Form :
1) Used to validate the input parameters .
Ex1:
function AfterPForm return boolean is
begin
if( :P_Deptno>80 ) then
srw.Message(190,'Enter the value <80');
return false;
end if ;
return (TRUE);
end;
Ex2:
function AfterPForm return boolean is
l_Deptno number(5);
begin
select deptno into l_Deptno from dept where deptno= : P_Deptno;
return true;
exception
when others then
srw.Message(200,'invalid dept number');
return false;
end;
Before Report :
This is similar to the After Parameter Form the difference is that, we
can access the Data Model Columns before the report generation.
Between Pages: to get the page numbers dynamically. To Handle some
events while moving between pages.
function BetweenPage return boolean is
begin
return (TRUE);
end;
After Report : Used to log some details into table or text files.
Lexical
parameters dynamically
Ex: Where clause
dynamically
1) Define P_From , P_To of numeric type as bind
parameter
2) Define P_Where of char type as Lexical parameter
Note : In parameter form
builder select the required parameters to be displayed on parameter form.
3) Open the After Parameter Form trigger write the
following code.
function AfterPForm return boolean is
begin
if ( :P_From is null and :P_To
is null ) then
:P_Where:='';
return (TRUE);
end if ;
if ( :P_From is not null and
:P_To is null ) then
:P_Where:='where
deptno>=:P_From';
return (TRUE);
end if ;
if ( :P_From is null and :P_To is not null ) then
:P_Where:='where deptno<=:P_To';
return (TRUE);
end if ;
if ( :P_From is not null and :P_To is not null ) then
:P_Where:='where deptno between :P_From and :P_To';
return (TRUE);
end if ;
return true;
end;
3) Change the Query( Data Model ) to Select empno, ename,
sal, deptno, hiredate from emp & P_Where
4) Compile and run the module
Order by clause
dynamically
Ex : Sort by
Empno,
Ename,
Sal,
Deptno.
if
Empno is supplied then order by clause will be : order by empno
if Ename is
supplied then order by clause will be : order by ename
1) Go to object navigator add one more bind parameter
of char type ( P_Sort ) and supply the list of values (Empno, Ename, Sal, Deptno )
Add
one more lexical parameter to hold the order by clause. ( P_Order )
2) Go to Parameter form and add set labels for
P_Sort input parameter and P_Order
Note : In parameter form builder select the required
parameters to be displayed on parameter form.
3) Change the code in the parameter form trigger as
shown below
function AfterPForm return boolean is
begin
:P_Order:='order by
‘||:P_Sort; --added to
the previous code.
if ( :P_From is null and :P_To
is null ) then
:P_Where:='';
return (TRUE);
end if ;
if ( :P_From is not null and
:P_To is null ) then
:P_Where:='where deptno>=:P_From';
return (TRUE);
end if ;
if ( :P_From is null and :P_To is not null ) then
:P_Where:='where deptno<=:P_To';
return (TRUE);
end if ;
if ( :P_From is not null and :P_To is not null ) then
:P_Where:='where deptno between :P_From and :P_To';
return (TRUE);
end if ;
return true;
end;
4) Change the Query in Data Model as shown below.
select empno, ename, sal, deptno, hiredate from emp
&P_Where
&P_Order
From table dynamically
1) Create table xx_Emp as select * from emp where
deptno in (10, 20);
2) go to object navigator create lexical parameter to hold the name of
the table ( P_Table) and
create input parameter as
bind parameter ( P_InTable )
3) When using from clause, the parameter it must be
initialized
4) go to parameter form builder and set the lable as
desired
5) Change the code in the parameter form trigger as
shown below
function
AfterPForm return boolean is
begin
:P_Table:=':P_InTable';
--added to the previous code for from clause to supply the table name.
:P_Order:='order
by :P_Sort'; -- 1st time added to the previous code for order by clause
if ( :P_From
is null and :P_To is null ) then
:P_Where:='';
return (TRUE);
end if ;
if ( :P_From is
not null and :P_To is null ) then
:P_Where:='where deptno>=:P_From';
return (TRUE);
end if ;
if ( :P_From
is null and :P_To is not null ) then
:P_Where:='where deptno<=:P_To';
return (TRUE);
end if ;
if ( :P_From
is not null and :P_To is not null ) then
:P_Where:='where deptno between :P_From and
:P_To';
return (TRUE);
end if ;
return true;
end;
6)Change Sql statement as shown below.
select empno, ename, sal, deptno, hiredate from
&P_Table
&P_Order
7) compile and run the module
Difference
between Bind Parameters and Lexical Parameters
System Parameters
1) BACKGROUND : Is whether the report should run in the foreground or
the background.
2) COPIES: Is the number of report copies that should be made
when the report is printed.
3) CURRENCY: Is
the symbol for the currency indicator (e.g., "$").
4) DECIMAL : Is the symbol for the decimal indicator (e.g.,
"."). ( DES – Destination )
5) DESFORMAT : Is
the definition of the output device's format (e.g., landscape mode for a
printer). This parameter is used when
running a report in a character-mode environment, and when sending a bitmap
report to a file (e.g. to create PDF or HTML output).
6) DESNAME : Is the name of the output device (e.g., the file name,
printer's name, mail userid).
7) DESTYPE : Is the type of device to which to send the report
output (screen, file, mail, printer, or screen using PostScript format).
8) MODE : Is whether the report should run in character mode or
bitmap.
9) ORIENTATION : Is the print direction for the report (landscape, portrait, default).
10) PRINTJOB : Is
whether the Print Job dialog box should appear before the report is run.
11) THOUSANDS :
Is the symbol for the thousand's indicator (e.g., ",").
The parameters 5 to 9
are related to output. These are very useful in real-time scenario.
Other Triggers
1)
Format Triggers
2)
Validation
Triggers
3)
Action Triggers
Format Triggers
:
Theses are used to hide/display the layout objects based on some
condition. It is a PL/SQL function. It returns Boolean value ( true / false ).
true à display, false à hide.
Eg :
EmpNo EmpName EmpSal EmpComm
Eg1: If salary is more
than 3000 then, it should be displayed.
go to layout model
right click on sal field and select PL/SQL Editor, write the following code
function F_3FormatTrigger return boolean is
begin
function F_3FormatTrigger
return boolean is
begin
if ( :sal>2000) then
return true;
end if;
return false;
end;
Eg2: If name of the
employee more than five then, it should be displayed
Go to layout model
right click on ename field and select PL/SQL Editor, write the following code
function
F_2FormatTrigger return boolean is
begin
if( length (:ename)>5) then
return TRUE;
end if;
return false;
end;
Hiding Labels :
Ex: at end of the repot following should be
displayed, if no data found
*********** NO Data Found
******************
1) go to Data Model
and create a summary column to get the count of employees. ( Emp_Count )
2) Define the
properties
Name : Emp_Count.
Data Type : Number
Source : Empno
3) go to Layout
Model and create the following label at end
*********** NO Data Found ******************
4) right click and
select the PL/SQL Editor and write the following in the code
function B_5FormatTrigger return boolean is
begin
if ( :Emp_Count > 0)then
return (FALSE);
end if;
return TRUE;
end;
for testing purpose
take one input parameter and test
ex: Enter Dept no : 90
*********** NO Data Found ******************
a)
go to object
navigator and create an user parameter ( P_Deptno )
b)
go to parameter
and design as explained in the previous examples.
c)
Go to Data Model
and Change query as shown velow
Select empno, ename, sal, comm from emp where deptno=:P_Deptno
5) Compile and run
the module.
To hide labels like
EmpNo, EmpName, EmpSal …. ( headers )
Right click on
labels individually and select PL/SQL editor write the following
function B_1FormatTrigger return boolean is
begin
if(:Emp_Count>0) then
return (TRUE);
end if;
return false;
end;
if user wish to
hide the all headers then right click on the frame and write the above code
Additional Layout :
This is to display additional report.
Ex: Abstract of the report.
Step1: Design two
layouts 1) main layout 2) Additional Layout
Main layout as explained in the previous examples.
Additional layout:
1)
go to layout
model and select Additional default layout, which is at the bottom of the tool
palette.
Create text fields for the
summary columns on the additional layout
Step2 : Create input parameter ( P_Layout ) which takes list of values (DETAILED, SUMMARY, BOTH )
If selected DETAILED, then o/p
should contain only detailed report
If selected SUMMARY, then o/p should
contain only abstract report
If selected BOTH, then o/p should contain both detailed and abstract
Step3: go to detailed
layout trigger and write the following
Begin
If( :P_layout =
‘DETAILED’ or ‘BOTH’ ) then
Return true;
End if;
return false;
End;
go to abstract layout trigger and write the following
Begin
If( :P_layout = ‘SUMMARY
or ‘BOTH’ ) then
Return true;
End if;
return false;
End;
Validation
Triggers :
To validate the
parameter values. Applicable only for parameters.
This is used with individual parameters.
Steps :
1) go to properties of parameter and
select the Validation trigger then
write the following code
Begin
If(
:P_Deptno > 50 ) then
srw.Message
(155, ‘invalid input enter lessthan 50’);
return
false;
end if;
return
true;
end;
same validation can be
done by using after parameter form trigger. The difference is, validation
triggers are used individually for each end every field where as after
parameter form trigger is used to validate all fields after submitting the
form.
Action triggers :
These are used with buttons . These triggers are used,
when user wish to execute some PL/SQL
code on click a button.
Steps :
1)
Select a button
from the tool palette and place on the layout area.
2)
Right click on
button and define properties.
Name : MY_Button
Lable Text : Test.
Type : PL/SQL
PL/SQL Trigger : double click and write following and the trigger
procedure MY_BUTTONButtonAction is
begin
srw.Message(180, 'This is is
text message');
end;
SRW.RUN_REPORT (command_line CHAR); à to run the
exe file
Ex: SRW.RUN_REPORT
(‘C:\EMPTEST.rep’);
.rep is an exe file à report executable program
.rdf is a source file à report definition
SRW.DO_SQL (sql_statement CHAR);
This procedure executes the specified SQL statement from within Report
Builder. The SQL statement can be DDL
(statements that define data), or DML (statements that manipulate data). DML statements can be executed directly in
pl/sql coding. The said function is not necessary to execute DML statement.
Since you cannot perform DDL statements in PL/SQL, the SRW.DO_SQL
packaged procedure is especially useful for performing them within Report
Builder.
Note : Action
Triggers are not used in the oracle apps. Only used with oracle developer ( D2k
)
applications.
SRW Package functions ( see the help for more details )
SRW.ADD_DEFINITION
SRW.APPLY_DEFINTION
SRW.BREAK
SRW.CONTEXT_FAILURE
SRW.DO_SQL
SRW.DO_SQL_FAILURE
SRW.GETTERR_RUN
SRW.GET_PAGE_NUM
SRW.INTEGER_ERROR
SRW.MAXROW_INERR
SRW.MAXROW_UNSET
SRW.MESSAGE
SRW.NULL_ARGUMENTS
SRW.PROGRAM_ABORT
SRW.REFERENCE
SRW.RUN_REPORT
SRW.RUN_REPORT_BATCHNO
SRW.RUN_REPORT_FAILURE
SRW.SETattributes
SRW.TRACE_ADD_OPTION
SRW.TRACE_END
SRW.TRACE_REM_OPTION
SRW.TRACE_START
SRW.TRACEOPTS.MASK
SRW.TRUNCATED_VALUE
SRW.UNKNOWN_QUERY
SRW.UNKNOWN_USER_EXIT
SRW.USER_EXIT
SRW.USER_EXIT20
SRW.USER_EXIT_FAILURE
Parent and Child
relation ship (or) Master and Detailed relation ship
It can be established
in two ways
1)
Manually and 2)
Using Data link object.
Manually:
Requirement :
DeptNo
|
DeptName
|
EmpNumber
|
EmpName
|
EmpSalary
|
EmpComm
|
10
|
Sales
|
7565
|
Allen
|
7000
|
500
|
7415
|
Smith
|
8000
|
400
|
||
1465
|
Rakesh
|
5600
|
300
|
||
20
|
Finance
|
6542
|
Sharma
|
7500
|
0
|
1267
|
Suresh
|
3500
|
|||
3865
|
Ravi
|
9500
|
300
|
Ex: if all the
fields are taken on same frame
Step1:
Query : Select
D.deptno, D.dname, E.empno, E.ename, E.sal, E.comm from emp E, Dept D where E.deptno=D.deptno
Step2: Design the layout model fields on the same frame as
shown below
Report will be
generated as shown below.
The above method is
not correct
To generated as per
the requirement, the application to be developed as explained in the following
steps
Manually ( 1st
method )
Step1: Data Model
1) Query ( same as
above )
select D.deptno, D.dname, E.empno, E.ename, E.sal,
E.comm from emp E, Dept D where E.deptno=D.deptno
2) Develop the data
model as shown below
Drag the (G_Deptno group to bottom)
separate the Deptno and Dname
from G_Deptno group by dragging to top as shown below.
Right click on the groups and define the names of the groups as G_Dept
and G_Emp
Step2: Layout Model
1) Design a frame
2) Inside the frame place the Master frame ( Repeated
Frame ) for dept details
3) Inside the Master frame place Detailed frame ( Repeated frame ) for
emp details.
4) Select the source for the master
frame as G_Dept
5) Select the source for the master
frame as G_Emp
Step3: Compile and
run the module
The report
generates as shown below
2)
Using Data
Link Object
Step1 : Design the
Data Model
1) develop two queries for two
groups
Query Q1: select * from dept
Query Q2: select * from
emp
2) Define the names of the groups
G_Dept, G_Emp
3) select the data link object from
tool palette and establish the link Deptno in EMP Group and Dept Group
After establishing link query will
be as the previous example
select D.deptno, D.dname, E.empno, E.ename, E.sal, E.comm from emp E,
Dept D where E.deptno=D.deptno
Step2 : Design the
Layout Model as explained in the previous example ( manually )
Step3 : compile and
run the module.
Note: Master fields can be displayed on the detailed
Repeating frame but not vice versa
For the above report
we can add the summary columns and formula columns
Ex: user wish to
obtain the out put as shown below.
DeptNo
|
DeptName
|
EmpNumber
|
EmpName
|
EmpSalary
|
EmpComm
|
10
|
Sales
|
7565
|
Allen
|
7000
|
500
|
7415
|
Smith
|
8000
|
400
|
||
1465
|
Rakesh
|
5600
|
300
|
||
7235
|
Ramesh
|
7500
|
200
|
||
Total No.of Employees
|
4
|
||||
Sum of the Salaries
|
28100
|
||||
Average Salary
|
7000.50
|
||||
20
|
Finance
|
6542
|
Sharma
|
7500
|
0
|
1267
|
Suresh
|
3500
|
|||
3865
|
Ravi
|
9500
|
300
|
||
Total No.of Employees
|
3
|
||||
Sum of the Salaries
|
20500
|
||||
Average Salary
|
6833.33
|
Step1 : Data Model
1) develop two queries for two groups
Query Q1: select * from dept
Query Q2: select * from
emp
2) Define the names of the groups
G_Dept, G_Emp
3) select the data link object from
tool palette and establish the link Deptno in EMP Group and Dept Group
After establishing link query will
be as the previous example
select D.deptno, D.dname, E.empno, E.ename, E.sal, E.comm from emp E,
Dept D where E.deptno=D.deptno
1) select summary columns for Total No.of Employees ( Emp_Count
)and Sum of the Salaries(Sum_Sal ) at the Dept group level
2) right click on the summary column set
properties
Name : Emp_Count
Source : Empno
Reset at : G_Dept
For salary
Name : Emp_Sal
Source : sal
Reset at : G_Dept
3)
select
formula column for average salary (
return (: Sum_Sal/: Emp_Count) at the
Dept group level
4)
right
click on the formula column set properties
Name : Avg_Sal
PL/SQL formula :
function Avg_SalFormula return Number
is
begin
return ( :Sal_Sum/:Emp_Count);
end;
Step2: Layout Model :
Add the following to the Master frame;
Total
No.of Employees :
Sum
of the salaries :
Average
Salary :
Step3: Compile and run the from
Output generates as shown below
Report level columns can also be displayed on the
frame
Select parent
frame ( at the end of the Tool bar of
Report builder ) à used to
find out the parent of the particular field ( object ) . This is a situation
where the application consists many no.of frames and repeating fames.
Flex Mode : ( on/of
)
 |
Off : the
icon shows as
If the flex mod is on,
then parent object will be increased / decreased automatically, if child object
increased / decreased.
If the flex mod is
off, then there is no effect.
Confined Mode ( lock Mode ):
If it is in lock mode, then we can not take out the child objects out
of the parent objects.
If it is in unlock mode, then we can take out the child object out of
the parent object and can be placed any where on the frame.
Irrespective of Lock or Unlock mode a Parent Object Can be moved Child.
Frame / Repeating Frame
/ Text Filed etc….. Properties.
Contract : Means the horizontal size of the object
decreases, if the formatted objects or data within it are wide enough, but it
cannot increase to a width greater than that shown in the Report Editor.
Truncation of data may occur; look at the examples. (You can think of this option as meaning
"only contract, do not expand.")
Expand :
Means the horizontal size of the
object increases, if the formatted objects or data within it are wide enough,
but it cannot decrease to a width less than that shown in the Report
Editor. (You can think of this option as
meaning "only expand, do not contract.")
Fixed : Means the width of the object is the same on
each logical page, regardless of the size of the objects or data within it.
Truncation of data may occur; look at the examples. The width of the object is defined to be its
width in the Report Editor.
Variable :
Means the object may expand or contract horizontally to accommodate the objects
or data within it (with no extra space), which means the width shown in the
Report Editor has no effect on the object's width at runtime.
Program Units :
Database
Program Units ( PL/SQL Objects ) :
These are server side objects
Stored program units (also known as stored subprograms)
can be compiled separately and stored permanently in an Oracle database, ready
to be executed. Once compiled and stored
in the data dictionary, they are schema objects, which can be referenced by any
number of applications connected to that database.
Stored program units offer higher productivity, better
performance, memory savings, application integrity, and tighter security. For example, by designing applications around
a library of stored procedures and functions, you can avoid redundant coding
and increase your productivity.
Stored program units are stored in parsed, compiled
form. So, when called, they are loaded
and passed to the PL/SQL engine immediately.
Also, they take advantage of shared memory. So, only one copy of a program unit need be
loaded into memory for execution by multiple users.
Report Builder Program Units
These
are client side objects. It provides the facility to create following program
units.
1) Procedures 2) Functions 3) Package Spec 4) Package
Body
Difference between PL/SQL Objects and Report Builder
Program Units
PL/SQL Objects Report
Builder Program Units
 |
Program Units :
Select
Program units and Create
Name
: DATA_PKG ( package name )
Select Package
Spec
Write the following code
PACKAGE Data_PKG IS
procedure
log_data( p_nRec number, p_msg varchar2, p_retCode out number);
function validate( p_deptno number ) return boolean;
END;
Again Select Program units and Create
Name : DATA_PKG (
package name )
Select Package
body
Write the following code
PACKAGE BODY DATA_PKG IS
procedure
log_data( p_nRec number, p_msg varchar2, p_retCode out number)
as
begin
insert
into emp_report_log values (p_nRec,p_msg);
commit;
p_retCode:=0;
exception
when
others then
p_retCode:=1;
end;
function
validate( p_deptno number ) return boolean
as
l_deptno number;
begin
select
deptno into l_deptno from dept where deptno= p_deptno;
return
true;
exception
when
others then
return
false;
end;
END;
Data
Model Query
select * from emp where deptno =
:P_Deptno
Take Summary Column for the Count of
Employees
Name : CS_COUNT
Write
the after parameter to validate deptno
function
AfterPForm return boolean is
begin
if(DATA_PKG.validate(:P_Deptno))then
return true;
end if;
srw.Message(120,'Invalid Deptno');
return (false);
end;
Write
the before report trigger for logging data
function
BeforeReport return boolean is
l_retCode
number;
begin
DATA_PKG.log_data(:CS_COUNT,
'HELLO',l_retCode);
if(l_retCode=0) then
srw.Message(100,
'successfully logged');
return
(TRUE);
end if;
srw.Message(100, 'fail to log');
return
false;
end;
Creating
a procedure
1) Select program units and create
2) Select Procedure from the new program unit and specify
the name procedure
3) Write the following code
PROCEDURE Test IS
BEGIN
insert into
log_table
( Sno,
Total_Rec, SDate)
values (
SnoSeq.nextval, :Emp_Count, sysdate );
commit;
END;
Calling a procedure
AfterReport Trigger
function
AfterReport return boolean is
begin
Test;
-- Calling a procedure
return (TRUE);
end;
Attached libraries
It is a group of program units. It
can be attached any Report / Form. Attached libraries are external PL/SQL
libraries that you have associated with a report or another external
library. When an external library is
attached, you can reference its packages, functions, and procedures from within
your report. For example, if you
attached an external library name MYLIB to your report and it contained a
function named ADDXY, then you could reference ADDXY from any PL/SQL in the
report.
File
extensions are ==== .pll à Program link library ( source code )
.plx à program link executable ( Executable code)
Place Holder Column
It
is to store some value. It can be accessed across the report builder. It works
like a global variable
Place
holder column is used in the situation where the two values are to be returned
from a function
1) Value returned by the function 2) Value taken into the
place holder.
Eg:
Empno Ename Salary Dname Grade
In
this example Dname is value, which will be obtained by using place holder and
Grade
will be obtained by return value of the function.
Steps to develop the above sample report
Step1 : Data Model
1)
Query
select empno, ename, sal, deptno from emp
2) Take the Place Holder column for Dname ( P_Dname ) and
define the properties
Name : P_Dname ( as desired )
Data Type : Char
Width : 100
3) Take the formula column for Grade
and define the properties
Name : P_Grade ( as desired )
Data Type : Char
Width : 1
PL/SQL Formula : double click and write the following
code
function F_GradeFormula return Char is
begin
select dname into :P_Dname from dept where
deptno=:deptno;
exception
when others
then
:P_Dname :=
'No DEPT';
End;
if ( :sal>=10000) then
return ('A');
elsif ( :sal>=5000 ) then
return 'B’;
elsif ( :sal>=3000 ) then
return 'C';
else
return 'D';
end if;
end;
in
the above PL/SQL program Dept Field is taken into the :P_Dname ( place holder
column ) and returned a char for Grade filed.
Step2 : Layout model
Design the layout model as explained
in the previous examples and set properties
Dname : Source = P_Dname
Grade : Source = F_Grade
Step3 : Compile and run the module
Link file
To link the text file to report
Ex:
in trailer section of the report, data to be obtained from text file
1) go to layout model and select the trailer section
2) select the link file from the tool palette
3) place on canvas
4) define the properties
Name : as desired
Source file format : text
Source file Name : C:\Documents and
Settings\Administrator\Desktop\MYFILE.txt
Vertical Elasticity : Expand
Horizontal Elasticity : Variable
Anchor :
Anchors are used to determine the vertical and
horizontal positioning of a child object relative to its parent. The end of the anchor with a symbol on it is
attached to the parent object.
Eg:
Name : xxxx
S/o : yyyyy
Street No;
Door No:
City :
State :
Country :
In the above some times street No may not be available
then report should not display the null value ( empty . instead of the Street
No should be replaced with Door No and continue
for remaining.
Here Street No is a parent object and Door No is a
child object
Properties
Conditional Formatting : to apply the colors based on some
condition
Right click on filed/frame or any
object and define the conditional Formatting.
Page Protect
The Page Protect
property indicates whether to try to keep the entire object and its contents on
the same logical page. Setting Page
Protect to Yes means that if the contents of the object cannot fit on the
current logical page, the object and all of its contents will be moved to the
next logical page.
Ex:
DeptNo Dname Empno EmpName Salary
10 Sales
7565 AAAAAA 5600
6215 BBBBBB 4500
5423 CCCCCC 65000
20 Marketing 3672 XXXXXX 5400
1543 ZZZZZZZ 2300
7524 NNNNNN 3876
In
this example the report is generated that, the 3672 EmpNO of 20th
dept is displayed in the first page and remaining employees information of 20th
dept is displayed in the next page. Instead of that, all records of 20th
dept to be displayed in the next page then, Set the Page Protect to Yes.
No comments:
Post a Comment