Report Builder is one of the Oracle GUI Tool.
By using this we can develop simple and Complex reports
There are two ways we can develop the report
1) Wizard base
2) Manual
While developing Report we have two stages.
1) Data Model
2) Layout model
Data Model :This section shows the logical structure of the report.
It contains below.
System Parameters.
User Parameters.
Queries.
Groups.
Formula Columns.
Summary Columns.
Place Holder Columns.
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 : used for calculating summary information like sum, average etc 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.
This column uses a set of predefined oracle aggregate functions. Summary columns are generally preceded by CS_ to distinguish them from other columns.
Ex:
Total Emp:
Total Sal:
Min Salary :
Max Salary :
In report layout summary columns should be used out of repeating frames. Summary columns will have only one parameter as input.
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
Formula columns are generally preceded by CF_ to distinguish from other columns. Column names or parameters with a prefix ‘:’ in formula column are considers as input to a formula column.
Examples:
- Comparison of data in two different columns/variables and perform some action.
- When you need to use any If-else block.
- To execute some sql query to find out email-address of a party using party_id
A datatype which stores some value and we can use this across the report.
It will work like Global variable in the reports.If we want to return more than one value from the Formula Columns then it will be used.
We can use this place holder columns inside the data group or outside the data group.
Place Holder column can be of Character, Date or Number datatype. Place Holder columns are generally preceded by CP_ to distinguish from other columns.
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 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 would retrieve up to 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’
No comments:
Post a Comment