#Introduction:
Oracle Report Builder is an enterprise-reporting tool that retrieves data dynamically from database, formats the report, display the final report and can also help with printing quality reports.
All the reports created using Report Builder in Oracle are stored in Report Builder Tables in ‘*.rdf’ and ‘*.rep’ format. Reports stored in ‘*.rdf’ format have full report definition of binary file. It also includes some comments and source code. You can modify these reports by use of Report Builder. If you transfer these reports on a secondary storage media in binary format then act as executable portable files. However, ‘*.rep’ format files are binary run-only files that do not contain any comments or source code. These files cannot be modified using Report Builder also.
Below are the components in Oracle Report Builder.
Object Navigator:With the help of Object Navigator, you can view the hierarchical view of all the objects that are displayed in the report.
Data Model Editor:This section shows the logical structure of the report.It contains below.
System Parameters:
DESFORMAT : Html, pdf…..
DESNAME : Name of the Printer, E-mail,Fax,File
DESTYPE : File, Screen, Print, Fax, E-mail
MODE : Bitmap, Character
User Parameters.: Bind and Lexical
Queries: User Query using for the report.
Groups:
1)Group Level : Executed for every record in the group
2)Report Level : Executed only once for entire report.
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.
Summary Columns: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.
Place Holder Columns: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.
Parameter Form Editor: Parameter Form Editor is used to accept user inputs.
Property Palette:Property Palette can be used to view or change settings of Oracle report object.
Layout Model Editor: Layout Model Editor contains various types of layout objects that help you to choose the type of layout you want for your report like below.
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.
#Creating Report:
Step1: Start Connecting.
Open the Oracle Reports Builder, go to File Menu and press Connect or execute the command Ctrl+J.
On the second step, you need to enter the user name, password and the database name.
Step2: Create Report Wizard in Oracle Reports.
Click on Reports and the press the button Create. An alert "Create a new report" with two option will appear, and here you choose: Use the Report Wizard.
Step3: Choose The Type of Layout and Style Reports in Oracle 11g.
Select the type of layout you would like to generate:
We have three options here.
Create both Web and Paper Layout,
Create Web Layout only,
Create paper Layout only.
Next step is to choose the report style and give a title name for your report.
The report style option are: Tabular, Group Left, Group Above, Matrix, Matrix with Group.
Step 4: Selecting a Data Source and Select Sql Query Builder for Reports in Oracle.
In this step you have to choose a Data Source for your report. The Data Source can be from JDBC Query, SQL Query, Text Query, XML Query.
In the next window you can write the query manually or else you can use Query Builder or Import Builder.
Example Query with Explanation:
SELECT * FROM MTL_SYSTEM_ITEMS_B WHERE SEGMENT1 =:P_SEGMENT1
In the above query: p_segment1 is called as bind parameter/variable.
Note: There are two types of parameters that we use in reports. Lexical Parameter and Bind Parameter
#Bind Parameter: This is used to pass the values dynamically at run time.
Examples: 1) If you want to see only one item from your inventory and you want to select that item at the time of running report. 2) If you want select only particular department employees and you want to select that department at runtime.
#Lexical Parameter: This parameter is used to build the query dynamically.
Examples: If you have two users A, B will be running the report and if use A want to see only columns 1, 2, 3 where as User B want to see columns 2, 3, 4 in that case we build the query dynamically using lexical parameters
Select the fields that you would like to display in your report. You can move one item or all items to target from Available Fields in Displayed Fields.
Select the fields that you would like to calculate totals in your report. Also you can choose to calculate: sum, count, minimum, maximum, average.
In the Next screen here you can modify the labels of the fields and also the width for each field.
Next Step is to select the template for your report. You have multiple options: Predefined Template, Template File or No Template
Now you are done with describing report.
Step 5:Run Module Report Editor Paper Design.
Select the template for your report. You have multiple options: Predefined Template, Template File or No Template.
FAQS:
What are the different file extensions that are created by oracle reports?
Ans:Rep file and Rdf file.
From which designation is it preferred to send the output to the printed?
Ans:Previewer.
Is it possible to disable the parameter from while running the report?
Ans:Yes
What are the default parameter that appear at run time in the parameter screen?
Ans: Destype and Desname.
Which parameter can be used to set read level consistency across multiple queries?
Ans:Read only.
How many different layouts are available in Reports?
Ans: There are eight different layout formats:
1. Tabular
2. Form Like
3. Form Letter
4. Mailing Label
5. Group Left
6. Group Above
7. Matrix
8. Matrix with group
Is it possible to insert comments into sql statements return in the data model editor?
Ans: Yes.
What is the Difference Between Paper Layout and Web lay out:
Paper layout is the layout you need to create "printable" reports.. Once you create this layout (paper layout), you can get your report output in various formats, eg, PDF, RTF, HTML, HTMLCSS etc.
On the other hand, web layout means that in place of (or in addition to) the paper layout, you get the report designed in JSP code. So all the frames, repeating frames etc will be expressed in JSP code using the Oracle Reports custom tags. Since it is code-based, you can insert your HTML or Java code in here to customize the output. You can in fact start with the HTML template, open it in reports builder, and start inserting the data using Reports custom JSP tags. In web layout, there are no "page breaks". So your report will be a JSP file, and can be deployed along with your J2EE application.
Oracle Report Builder is an enterprise-reporting tool that retrieves data dynamically from database, formats the report, display the final report and can also help with printing quality reports.
All the reports created using Report Builder in Oracle are stored in Report Builder Tables in ‘*.rdf’ and ‘*.rep’ format. Reports stored in ‘*.rdf’ format have full report definition of binary file. It also includes some comments and source code. You can modify these reports by use of Report Builder. If you transfer these reports on a secondary storage media in binary format then act as executable portable files. However, ‘*.rep’ format files are binary run-only files that do not contain any comments or source code. These files cannot be modified using Report Builder also.
Below are the components in Oracle Report Builder.
Object Navigator:With the help of Object Navigator, you can view the hierarchical view of all the objects that are displayed in the report.
Data Model Editor:This section shows the logical structure of the report.It contains below.
System Parameters:
DESFORMAT : Html, pdf…..
DESNAME : Name of the Printer, E-mail,Fax,File
DESTYPE : File, Screen, Print, Fax, E-mail
MODE : Bitmap, Character
User Parameters.: Bind and Lexical
Queries: User Query using for the report.
Groups:
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 : Executed for every record in the group
2)Report Level : Executed only once for entire report.
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.
Summary Columns: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.
Place Holder Columns: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.
Parameter Form Editor: Parameter Form Editor is used to accept user inputs.
Property Palette:Property Palette can be used to view or change settings of Oracle report object.
Layout Model Editor: Layout Model Editor contains various types of layout objects that help you to choose the type of layout you want for your report like below.
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.
#Creating Report:
Step1: Start Connecting.
Open the Oracle Reports Builder, go to File Menu and press Connect or execute the command Ctrl+J.
On the second step, you need to enter the user name, password and the database name.
Step2: Create Report Wizard in Oracle Reports.
Click on Reports and the press the button Create. An alert "Create a new report" with two option will appear, and here you choose: Use the Report Wizard.
Step3: Choose The Type of Layout and Style Reports in Oracle 11g.
Select the type of layout you would like to generate:
We have three options here.
Create both Web and Paper Layout,
Create Web Layout only,
Create paper Layout only.
Next step is to choose the report style and give a title name for your report.
The report style option are: Tabular, Group Left, Group Above, Matrix, Matrix with Group.
Step 4: Selecting a Data Source and Select Sql Query Builder for Reports in Oracle.
In this step you have to choose a Data Source for your report. The Data Source can be from JDBC Query, SQL Query, Text Query, XML Query.
In the next window you can write the query manually or else you can use Query Builder or Import Builder.
Example Query with Explanation:
SELECT * FROM MTL_SYSTEM_ITEMS_B WHERE SEGMENT1 =:P_SEGMENT1
In the above query: p_segment1 is called as bind parameter/variable.
Note: There are two types of parameters that we use in reports. Lexical Parameter and Bind Parameter
#Bind Parameter: This is used to pass the values dynamically at run time.
Examples: 1) If you want to see only one item from your inventory and you want to select that item at the time of running report. 2) If you want select only particular department employees and you want to select that department at runtime.
#Lexical Parameter: This parameter is used to build the query dynamically.
Examples: If you have two users A, B will be running the report and if use A want to see only columns 1, 2, 3 where as User B want to see columns 2, 3, 4 in that case we build the query dynamically using lexical parameters
Select the fields that you would like to display in your report. You can move one item or all items to target from Available Fields in Displayed Fields.
Select the fields that you would like to calculate totals in your report. Also you can choose to calculate: sum, count, minimum, maximum, average.
In the Next screen here you can modify the labels of the fields and also the width for each field.
Next Step is to select the template for your report. You have multiple options: Predefined Template, Template File or No Template
Now you are done with describing report.
Step 5:Run Module Report Editor Paper Design.
Select the template for your report. You have multiple options: Predefined Template, Template File or No Template.
FAQS:
What are the different file extensions that are created by oracle reports?
Ans:Rep file and Rdf file.
From which designation is it preferred to send the output to the printed?
Ans:Previewer.
Is it possible to disable the parameter from while running the report?
Ans:Yes
What are the default parameter that appear at run time in the parameter screen?
Ans: Destype and Desname.
Which parameter can be used to set read level consistency across multiple queries?
Ans:Read only.
How many different layouts are available in Reports?
Ans: There are eight different layout formats:
1. Tabular
2. Form Like
3. Form Letter
4. Mailing Label
5. Group Left
6. Group Above
7. Matrix
8. Matrix with group
Is it possible to insert comments into sql statements return in the data model editor?
Ans: Yes.
What is the Difference Between Paper Layout and Web lay out:
Paper layout is the layout you need to create "printable" reports.. Once you create this layout (paper layout), you can get your report output in various formats, eg, PDF, RTF, HTML, HTMLCSS etc.
On the other hand, web layout means that in place of (or in addition to) the paper layout, you get the report designed in JSP code. So all the frames, repeating frames etc will be expressed in JSP code using the Oracle Reports custom tags. Since it is code-based, you can insert your HTML or Java code in here to customize the output. You can in fact start with the HTML template, open it in reports builder, and start inserting the data using Reports custom JSP tags. In web layout, there are no "page breaks". So your report will be a JSP file, and can be deployed along with your J2EE application.
No comments:
Post a Comment