Report Builder is one of the Oracle GUI Tool Graphical user Interface
By using this we can develop simple and Complex reports
There are two ways we can develop the report
ü Wizard base
ü Manuval
While developing Report we have two stages.
ü Data Model
ü Layout model
Data Model 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.
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
Place Holder 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.
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.
ü Before Parameter Form
ü after Parameter Form
ü Before report
ü Between pages
ü 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 :
ü System Parameters
ü User Defined Parameters
ü Bind
ü Lexical
User Parameters:
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.
SELECT * FROM EMP &P_ORDER_BY
Ex:
After Parameter Form Trigger
If user pass the deptno then select between that otherwise select all the dept employees data
SELECT *
FROM EMP
WHERE DEPTNO BETWEEN
_FROM AND _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
System Parameters:
DESFORMAT : Html, pdf…..
DESNAME : Name of the Printer, E-mail,Fax,File
DESTYPE : File, Screen, Print, Fax, E-mail
MODE : Bitmap, Character
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.
Flex Mode: 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.
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
Report Registration Steps:
1) Develop the report (.rdf) as per client requirement by using Reports Builder
Step – 1: select “Built a new Report manually” option and click OK button.
Step -2: Now you are in Data Model, select SQL query tool and write required query.
Step – 3: Click on OK and connect to apps/apps@VISR12
Step- 4 : As our query is having parameters its creating two bind parameters
Step – 5: Go to Paper layout and design Frame, then keep Labels and then design Repeating Frame with in Frame and keep Fields with in Repeating Frames.
Go to Frame Properties set Vertical Elasticity as Expand
Go to Repeating Frame Properties set like following
Go to Field Properties set like below.
Step – 6: Run the form and enter details like below.
2) Move the report from the local machine in to the server by using WINSCP
A. CUS_TOP\11.5.0\reports\US .rdf (or)
Reports Deployment
Note: Plz notice that as we don’t have CUST_TOP in our server, we are feeling PO_TOP as CUST_TOP
for our practice. But in real time we will use only CUST_TOP which is named with Client Name.
Step – 1: open Putty and type below commands to know path of Custom top
Step – 2: Open WinSCP to reach the Custom Path through GUI
Step – 3: Deploy the test.rdf into Custom top Location.
3) Select System Administrator
a. Create Executable
b. Executable name
c. Application Name
d. Execution Method
e. Report (.rdf) file name
Step – 4: Create Executable by linking to test.rdf
Executable File name should be Report name which is available in server only.
4) Create Concurrent Program and attach :
concurrent program is Instance of executable file along with input parameters and incompatible programs. Development of sample report and Move to oracle apps and execution.
a. Executable
b. Parameters
c. Incompatibilities
Step – 5: Create Concurrent Program, while creating provide Executable name to this.
Step – 6: Click on Parameters Tab and go to screen to provide details like below screen. Token values should be like parameter from Report Builder.
5) Create Request group and attach Concurrent Programe
Step – 7: Go to Request Group screen , create group, or open existing group to attach our Concurrent Program Name.
6) Create Responsibility
a. Request Group – Collection of Concurrent Programs
b. Data Group – Collection of User IDs
c. Menu – Collection of forms
Step – 7: Go to Responsibility Screen and create one responsibility and attach our Request Group to this Responsibility.
7) Create user attach Responsibility to the user
Step – 9: Go to User Screen and attach our responsibility to existing Business User.
Run the Report through SRS window.
Click on ok button to submit single request. Then call our report to run SRS window.
Provide the Values for Parameters before click on OK button.
Click on No button for not to submit another request.
Click on Refresh Button to make our report into complete Normal Stage.
Types of executables : These are programs
Oracle Application supports 12 types of languages
1) Oracle Reports --> .rdf
2) Sql * Plus --> .sql
3) PL/SQL Procedures --> .proc
4) Sql * Loader --> .ctl
5) Unix shell script --> .sh
6) C – Language --> .C
7) Pro – C --> .prc
8) Java Programs --> .java
9) Java Serve Pages (OAF) --> .jsp
10) PERL scripting --> .perl
11) Java Server Faces(ADF --> .JSF
Default Types
1) Constant
2) SQL Statement
3) Segment
4) Current Date
5) Current Time
6) Profile
These are of 8 types of Validation types
1) NONE
2) Dependent
3) Independent
4) Table
5) Translate dependent
6) Translate Independent
7) Special
8) Pair
Request Set:Request set is a collection of report / Programs that you group together and can be submitted to run is a single interaction.
Request Id: When submit a request oracle application assigns a request id to the request. We can use this request id to search for request.
Concurrent Manager: A concurrent manager is a component of concurrent processing that monitors and runs tasks without typing up your computer.
View LOG:Using this we can see the technical details of the concurrent programs.
View Output:Using this we can see the actual result data.
Concurrent Program: After creation of executable we will create concurrent Program by attachingExecutable, if parameters are there we will attach parameter if incompatibility Programs are there we will add those programs , for single executable we create Multiple concurrent programs with different parameters.
Request Group: After creation of concurrent program we must add the program to the request
Group. Request Group is nothing but collection of Concurrent Programs and Reports.
Responsibility: It is level of authority where we will combine Data Group, Request Group and
Menu. Request Group is optional, where as menu and data group is mandatory.
Data Group: Data Group is nothing but collection of applications names and Oracle User
Names based on this Username data will be retrieved from database.
Menu: Menu is nothing but collection of functions (forms) and submenus.
Value Set: values set is nothing but list values. It will be used to validate values while entering the parameters.
Token: Token is one the field will be used to map concurrent Program parameters with report builder Bind variable. We will enter the Bind variable name in the toke field so that parameters will be passed to the Bind variable.
Required check box: By using this check box we can make the parameters mandatory or optional
Enabled Check Box: By using this we can enable or disable the parameter.
Display Check Box: By using this we can hide or display the parameters in SRS Window.
Range Option:While defining the from and to parameters if we wanted to accept Values in accession order menus from values is low and to value is high we will Select the options called low and high.
Default Types: If we are hiding the parameters user can’t enter the values that Time we can pass default values by using default type and default value filed.
Scheduling the Concurrent program We can submit the Concurrent program future date or date by using the schedule button in SRS window.
1. As soon as possible: This is default option whenever we submit the request it will submit the as soon as possible
2. Once: It will submit the rest only once for future date.
3. Periodically: WE can specify the from_date and to_date to submit program periodically no of. Days months, hours, minutes and so on.
4. Specific Days: If we want submit concurrent program in the specific days we write select this option
5. Save this Schedule: This check box will be used to save the schedule and apply same schedule to other concurrent programs by selecting the button called ‘Apply save schedule’
By using this we can develop simple and Complex reports
There are two ways we can develop the report
ü Wizard base
ü Manuval
While developing Report we have two stages.
ü Data Model
ü Layout model
Data Model 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.
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
Place Holder 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.
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.
ü Before Parameter Form
ü after Parameter Form
ü Before report
ü Between pages
ü 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 :
ü System Parameters
ü User Defined Parameters
ü Bind
ü Lexical
User Parameters:
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.
SELECT * FROM EMP &P_ORDER_BY
Ex:
After Parameter Form Trigger
If user pass the deptno then select between that otherwise select all the dept employees data
SELECT *
FROM EMP
WHERE DEPTNO BETWEEN
_FROM AND _TOIf 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 |
|
ü To pass the Values into the WHERE Clause. ü We will use the symbol “:” ü Can be used only in the WHERE Clause. ü Any Data Type. ü Values will be passed in the Parameter Form. |
ü To Replace the String in the Query. ü We will use the symbol “&” ü In any Clause we can use this. ü Must be Character. ü Values will be assigned dynamically form in the After Parameter Form Trigger. |
System Parameters:
DESFORMAT : Html, pdf…..
DESNAME : Name of the Printer, E-mail,Fax,File
DESTYPE : File, Screen, Print, Fax, E-mail
MODE : Bitmap, Character
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.
Flex Mode: 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 |
|
ü Will be stored in the file. ü Can be used only in the Current Report. ü Improve the Performance. |
ü Stored in the Database ü We can use any report. ü 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
Report Registration Steps:
1) Develop the report (.rdf) as per client requirement by using Reports Builder
Step – 1: select “Built a new Report manually” option and click OK button.
Step -2: Now you are in Data Model, select SQL query tool and write required query.
Step- 4 : As our query is having parameters its creating two bind parameters
Step – 5: Go to Paper layout and design Frame, then keep Labels and then design Repeating Frame with in Frame and keep Fields with in Repeating Frames.
Go to Frame Properties set Vertical Elasticity as Expand
Go to Repeating Frame Properties set like following
Go to Field Properties set like below.
Step – 6: Run the form and enter details like below.
2) Move the report from the local machine in to the server by using WINSCP
A. CUS_TOP\11.5.0\reports\US .rdf (or)
Reports Deployment
Note: Plz notice that as we don’t have CUST_TOP in our server, we are feeling PO_TOP as CUST_TOP
for our practice. But in real time we will use only CUST_TOP which is named with Client Name.
Step – 1: open Putty and type below commands to know path of Custom top
Step – 2: Open WinSCP to reach the Custom Path through GUI
Step – 3: Deploy the test.rdf into Custom top Location.
3) Select System Administrator
a. Create Executable
b. Executable name
c. Application Name
d. Execution Method
e. Report (.rdf) file name
Step – 4: Create Executable by linking to test.rdf
Executable File name should be Report name which is available in server only.
4) Create Concurrent Program and attach :
concurrent program is Instance of executable file along with input parameters and incompatible programs. Development of sample report and Move to oracle apps and execution.
a. Executable
b. Parameters
c. Incompatibilities
Step – 5: Create Concurrent Program, while creating provide Executable name to this.
Step – 6: Click on Parameters Tab and go to screen to provide details like below screen. Token values should be like parameter from Report Builder.
5) Create Request group and attach Concurrent Programe
Step – 7: Go to Request Group screen , create group, or open existing group to attach our Concurrent Program Name.
6) Create Responsibility
a. Request Group – Collection of Concurrent Programs
b. Data Group – Collection of User IDs
c. Menu – Collection of forms
Step – 7: Go to Responsibility Screen and create one responsibility and attach our Request Group to this Responsibility.
7) Create user attach Responsibility to the user
Step – 9: Go to User Screen and attach our responsibility to existing Business User.
Run the Report through SRS window.
- Go to File —-à switch Responsibility —–à select our responsibility
- Go to View—à Request —à find then we can fine below screen.
Click on ok button to submit single request. Then call our report to run SRS window.
Provide the Values for Parameters before click on OK button.
Click on No button for not to submit another request.
Click on Refresh Button to make our report into complete Normal Stage.
Types of executables : These are programs
Oracle Application supports 12 types of languages
1) Oracle Reports --> .rdf
2) Sql * Plus --> .sql
3) PL/SQL Procedures --> .proc
4) Sql * Loader --> .ctl
5) Unix shell script --> .sh
6) C – Language --> .C
7) Pro – C --> .prc
8) Java Programs --> .java
9) Java Serve Pages (OAF) --> .jsp
10) PERL scripting --> .perl
11) Java Server Faces(ADF --> .JSF
Default Types
1) Constant
2) SQL Statement
3) Segment
4) Current Date
5) Current Time
6) Profile
These are of 8 types of Validation types
1) NONE
2) Dependent
3) Independent
4) Table
5) Translate dependent
6) Translate Independent
7) Special
8) Pair
Request Set:Request set is a collection of report / Programs that you group together and can be submitted to run is a single interaction.
Request Id: When submit a request oracle application assigns a request id to the request. We can use this request id to search for request.
Concurrent Manager: A concurrent manager is a component of concurrent processing that monitors and runs tasks without typing up your computer.
View LOG:Using this we can see the technical details of the concurrent programs.
View Output:Using this we can see the actual result data.
Concurrent Program: After creation of executable we will create concurrent Program by attachingExecutable, if parameters are there we will attach parameter if incompatibility Programs are there we will add those programs , for single executable we create Multiple concurrent programs with different parameters.
Request Group: After creation of concurrent program we must add the program to the request
Group. Request Group is nothing but collection of Concurrent Programs and Reports.
Responsibility: It is level of authority where we will combine Data Group, Request Group and
Menu. Request Group is optional, where as menu and data group is mandatory.
Data Group: Data Group is nothing but collection of applications names and Oracle User
Names based on this Username data will be retrieved from database.
Menu: Menu is nothing but collection of functions (forms) and submenus.
Value Set: values set is nothing but list values. It will be used to validate values while entering the parameters.
Token: Token is one the field will be used to map concurrent Program parameters with report builder Bind variable. We will enter the Bind variable name in the toke field so that parameters will be passed to the Bind variable.
Required check box: By using this check box we can make the parameters mandatory or optional
Enabled Check Box: By using this we can enable or disable the parameter.
Display Check Box: By using this we can hide or display the parameters in SRS Window.
Range Option:While defining the from and to parameters if we wanted to accept Values in accession order menus from values is low and to value is high we will Select the options called low and high.
Default Types: If we are hiding the parameters user can’t enter the values that Time we can pass default values by using default type and default value filed.
Scheduling the Concurrent program We can submit the Concurrent program future date or date by using the schedule button in SRS window.
1. As soon as possible: This is default option whenever we submit the request it will submit the as soon as possible
2. Once: It will submit the rest only once for future date.
3. Periodically: WE can specify the from_date and to_date to submit program periodically no of. Days months, hours, minutes and so on.
4. Specific Days: If we want submit concurrent program in the specific days we write select this option
5. Save this Schedule: This check box will be used to save the schedule and apply same schedule to other concurrent programs by selecting the button called ‘Apply save schedule’
