Oracle Reports
Objects of Reports builder6i
Object Navigator
Data Model
Layout Model
Parameter Form
Report Triggers
Property palette
Overview of Reports Styles
Tabular
Form like
Mailing label
Form letter
Group left
Group above
Matrix
Matrix with group
Creating Reports
Wizard
Manual
Data model objects
Group
Summary column
Formula column
Placeholder column
Data Link
Cross Product
Layout Model objects
Fields
Repeating Frame
Frame
Text
Anchor
Button
Layout Sections
1. Header Section
2. Main section
3. Trailer section
Parameters Reports
System Parameters
User Parameters
1. Bind parameter Report
2. Lexical parameter Report\
Different Triggers in Report6i
Validation Trigger
Format Trigger
Group Filter Trigger
Action Trigger
Report Trigger
Oracle Reports 6i
In this tutorial you will learn about Introduction to Oracle Reports Builder, Report file storage formats, Oracle Reports Builder Tools, Report Wizard, Triggers in Reports, Types of Triggers and Case Study - Creating a Tabular report.
Introduction to Oracle Reports Builder
Oracle Reports Builder is a powerful enterprise reporting tool used to build reports that dynamically retrieve data from the database, format, display and print quality reports.Reports can be stored in File or Database (Report Builder Tables).
Report file storage formats
.rdf Report
Binary File Full report definition (includes source code and comments)
Modifiable through Builder. Binary, executable Portable if transferred as binary.
PL/SQL recompiles on Open/Run
.rep Report
Binary Run-Only File
No source code or comments. Not modifiable binary, executable.
Report Executables
RWBLD60 Report Builder
RWRUN60 Report Runtime
RWCON60 Report Converter/Compiler [File => Administration => Compile (rdf to rep)/Convert]
Oracle Reports Builder Tools
Oracle Reports Builder comes with the following components
Object Navigator
Property Palette
Data Model Editor
Layout Model Editor
Parameter Form Editor
Object Navigator
The Object Navigator shows a hierarchical view of objects in the report. Each item listed is called a node and represents an object or type of object the report can contain or reference.
Property Palette
A Property Palette is a window that displays the settings for defining an Oracle reportsobject.
Data Model Editor
To specify data for a report, a data model should be defined. A data model is composed of some or all of the following data definition objects.
Queries
Queries are SQL Select statements that fetch data from the oracle database. These statements are fired each time the report is run.
Groups
Groups determine the hierarchy of data appearing in the report and are primarily used to group columns selected in the query. Oracle report automatically creates a group for each query.
Data Columns
Data columns contain the data values for a report. Default data columns, corresponding to the table columns included in each query’s SELECT list are automatically created byoracle reports. Each column is placed in the group associated with the query that selected the column.
Formula Columns
Formulas can be entered in formula columns to create computed columns. Formulas can be written using PL/SQL syntax. Formula columns are generally preceded by CF_ to distinguish from other columns.
Summary Columns
Summary columns are used for calculating summary information like sum, average etc. This column uses a set of predefined oracle aggregate functions. Summary columns are generally preceded by CS_ to distinguish them from other columns.
Data Links
Data links are used to establish parent-child relationships between queries and groups via column matching.
Layout Model Editor
A report layout editor contains the following layout objects
Frames
Frames surround other layout objects, enabling control of multiple objects simultaneously
Repeating Frames
Repeating frames acts as placeholders for groups (I.e repeating values) and present rows of data retrieved from the database. Repeating frames repeat as often as the number of rows retrieved.
Fields
Fields acts as placeholders for columns values. They define the formatting attributes for all columns displayed in the report.
Boilerplate
Boilerplate consists of text (label of the column) and graphics that appear in a report each time it is run.
Parameter Form Editor
Parameter form is a runtime form used to accept inputs from the user.
Parameters
Parameters are variables for a report that accept input from the user at runtime. These parameter values can then be used in the SQL select statements to retrieve data conditionally. Oracle reports creates a set of system parameters at runtime namely report destination type, number of copies etc.
Report Wizard
When we create a default Tabular Report using report wizard, the wizard will take you through the below mentioned pages
Report Style Tabular, Form-Like, Mailing Label, Form Letter, Group Left, Group Above, Matrix, Matrix with Group
Query Type Choose whether to build a SQL query or an Express query.
Data Enter a SELECT statement to retrieve the report data
Displayed Fields Select the fields that you want to display in the output.
Fields to Total Select the fields that you want to summarize.
Labels for Fields Alter the labels that appear for each field and the width of each field.
Template Select the template that you want to use for this report. A template contains standard information such as company logo, date, and so on.
Note: The above steps are different for each report style.
Group Left & Have an additional page: ‘Groups’
Group Above styles
Matrix Reports styles Have 3 additional pages: ‘Matrix Rows’ ‘Columns’ ‘Cells’
Mailing Label & Have 4 pages: ‘Report Style’ ‘Data’
Form Letter styles ‘Text’ ‘Template’
The difference between Mailing Labels and Form Letters is, Mailing Label shows multiple records on one page while Form Letter shows one record on each page.
Triggers in Reports
Types of Triggers
Formula Triggers: Formula triggers are PL/SQL functions that populate columns of type Formula.
Format Triggers:
Format triggers are PL/SQL functions executed before the object is formatted. These triggers are used to dynamically change the formatting attributes and used to conditionally print and not to print a report column value. These triggers return Boolean values TRUE or FALSE. If the return value of the format trigger is FALSE, the value is not displayed.
Action Triggers: Action triggers are used to perform user-defined action. These triggers do not return any value.
Validation Triggers: Validation triggers are PL/SQL functions that are executed when a parameter value is entered and the cursor moves to the next parameter. These triggers return Boolean value TRUE / FALSE.
Report Triggers: Report triggers enable execution of PL/SQL functions at specific time during execution and formatting of report.
Before Parameter Form
Fires before the Runtime Parameter Form are displayed. Can access the PL/SQL global variables, report level columns and manipulate accordingly.
After Parameter Form
Fires after the Runtime Parameter form are displayed. Used to validate the parameter values.
Before Report
Fires before the report is executed but after the queries is parsed and date is fetched.
Between Pages
Fires before each page of the report are formatted, except the very first page. This page is used to customize page formatting.
After Report
Fires after the report previewer are exited, or after report output is sent to a specified destination.
Case Study - Create a Tabular report
After invoking the report builder and connecting to the database invoke Report wizard.
Click on Tools… Report Wizard… to start the report wizard for a new report. Report wizard shows the following tab pages to enter information required for report.
Style Totals Data
Labels Fields Template
In the Style tab select ‘Tabular’ as the report style and Click Next
The Data tab allows creation of an SQL statement using Query Builder or to enter the SQL statement in the multi line edit box provided in the Data tab.
Click Next
This will take you to the next tab if your SQL statement syntax is correct.
Fields tab is used to specify the fields that must be displayed in tabular format. Select all fields by clicking on >> icon.
Click Next
The Totals tab is displayed that allows creation of Summary columns using aggregate functions.
This report does not include totals for the selected fields and thus Click Next.
The Labels tab is displayed that enable us to change the labels of the columns.
Click Next
The Templates tab is displayed that enable us to create report from templates. There are number of pre-determined templates available in Oracle Reports Builder.
Select Corporate 1 as the template. Click Finish
Click on File. Save to save the report, specify the report name and click OK.
Run the report!!
1.What is SRW Package?
Ans: The Report builder Built in package know as SRW Package (Sql Report Writer) This package extends reports, Control report execution, output message at runtime, Initialize layout fields, Perform DDL statements used to create or Drop temporary table, Call User Exit, to format width of the columns, to page break the column, to set the colors
Ex: SRW.DO_SQL, It’s like DDL command, we can create table, views , etc.,
SRW.SET_FIELD_NUM
SRW. SET_FIELD_CHAR
SRW. SET FIELD _DATE
2.What are Lexical Parameters and bind parameters?
Lexical Parameter is a Simple text string that to replace any part of a SELECT statement. Column names, the from clause, where clause or the order by clause. To create a lexical reference in a query we prefix the parameter name with an ampersand (ex. &.dname,)
3. What is User Parameters?
A parameter, which is created by user. For to restrict values with where clause in select statement.
Data type, width, input mask, initial value, validation trigger, list of values
We can use Lovs in use in user parameter with static and Dynamic Select Statement.
4. What is System Parameters: These are built-in parameters by corporation.
BACKGROUND: Is whether the report should run in the foreground or the background.
COPIES Is the number of report copies that should be made when the report is printed.
CURRENCY Is the symbol for the currency indicator (e.g., "$").
DECIMAL Is the symbol for the decimal indicator (e.g., ".").
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).
DESNAME Is the name of the output device (e.g., the file name, printer's name, mail userid).
DESTYPE Is the type of device to which to send the report output (screen, file, mail, printer, or
screen using PostScript format).
MODE Is whether the report should run in character mode or bitmap.
ORIENTATION Is the print direction for the report (landscape, portrait, default).
PRINTJOB Is whether the Print Job dialog box should appear before the report is run.
THOUSANDS Is the symbol for the thousand's indicator (e.g., ",").
5. How many Types of Reports available in Reports
Tabular form-like form – letter Group left
Group above matrix Matrix with group Mailing label
Matrix Report: Simple, Group above, Nested
Simple Matrix Report required 4 groups
1.Cross Product Group
2. Row and Column Group
3. Cell Group
4. Cell column is the source of a cross product summary that becomes the cell content.
Frames: 1.Repeating frame for rows (down direction)
2.Repeating frame for columns (Across)
3.Matrix object the intersection of the two repeating frames
6.What Types of Triggers are Available in Reports.
Report level Triggers
Data Model Triggers
Layout Model Triggers
Report Level Triggers
Before parameter form: If u want take parameters passed to the report and manipulate them so that they appear differently in the parameter form., this is where modification can be done for ex: when u want pass a deptno but show the dname selected , use a before parameter form trigger.
After parameter form & Before Report: These two triggers are fired one after the other. No event occurs in between them. However the way the way that the reports product behaves when the triggers fail is quite different. If the After Parameter trigger fails the report will be put back into the parameter form. It’s useful to place code here to check whether values in your parameter form are valid. Even though the Before Report trigger is executed before the query runs, if this trigger fails it won’t fail until reports tries to display the first page of the report. This means that even if something goes wrong in the before report trigger (meaning that you may not want to run the query at all) It will run anyway
Between pages: This Trigger fires before all pages except first page one. It will not fire after the last page of a report. If a report only has one page it will not fire at all. You can use this trigger to send specific control to the change the paper orientation or to do double sided printing
After report: This trigger fires the report has printed or in the case of a screen report, after the report is closed following viewing. This trigger can be used to update a global variable if u r returning the number of pages in a report. It is also used to delete temporary table used to print the report
Data Model Triggers
Formula Column, Group Filter, Parameter values
Layout Model Triggers
7. What is Format triggers.
Format triggers enable you to modify the display of objects dynamically at run time or to suppress display altogether
For Headings, for repeating frames, for field, for boilerplate object
To format a column based on certain criteria for example
i) To format the max(Sal) for particular department.
ii) To format the Sal column with a Dollar($) prefix.
iii) To format Date formats….etc
8. What is Data Model?
Data Model is logically group of the Report Objects through query and Data model tools . Once query is compiled report automatically generates group. The queries build the groups ant then Groups are used to populate the report. The only function of queries in report is to create the groups. The Report Editor's Data Model view enables you to define and modify the data model objects for a report. In this view, objects and their property settings are represented symbolically to highlight their types and relationships. To create the query objects for your data model, you can use the Report Wizard, Data Wizard, or the Query tools in the tool palette.
9. What is Layout model?
Layout Model is to Physically arrange Data model group objects on the Report. The Report Editor's Layout Model view enables you to define and modify the layout model objects for a report. In this view, objects and their property settings are represented symbolically to highlight their types and relationships.
10 What is Livepreviewer?
Ans: The Live Previewer is a work area in which you can preview your report and manipulate the actual, or live data at the same time. In the Live Previewer you can customize reports interactively, meaning that you can see the results immediately as you make each change.
To activate buttons in the Live Previewer, you must display the report output in the Runtime Previewer. In order to edit your report, such as changing column size ,move columns, align columns insert page numbers, edit text, change colors, change fonts set format masks, insert field the Live Previewer must be in Flex Mode.
Access
Title
Viewing region
Rulers
Grid
Toolbar
Style bar
Tool palette
Status bar
11. What is Parameter Form
Ans: Parameters are variables for report that users can change at runtime immediately prior to the execution of the report. You can use system parameters to specify aspects of report execution, such as the output format, printer name , mailed or number of copies. We can also create own parameters through sql or Pl/sql at runtime.
The Parameter Form view is the work area in which you define the format of the report's Runtime Parameter Form. To do this, you define and modify parameter form objects (fields and boilerplate).
When you run a report, Report Builder uses the Parameter Form view as a template for the Runtime Parameter Form. Fields and boilerplate appear in the Runtime Parameter Form exactly as they appear in the Parameter Form view. If you do not define a Runtime Parameter Form in the Parameter Form view, Report Builder displays a default Parameter Form for you at runtime.
12. What is Query?
The first thing in data model is the query. Through query we access database objects with sql query. Compiled query creates groups. We can create query through query builder, sql query and import query from o/s file or database.
13. What is Group?
Ans: Groups are created to organize the columns in your report. When you create a query, Report Builder automatically creates a group that contains the columns selected by the query. You create additional groups to produce break levels in the report, either manually or by using the Report Wizard to create a group above or group left report.
14 What is Repeating Frame?
Ans: Repeating frames surround all of the fields that are created for a group’s columns.
Repeating frames correspond to groups in the data model. Each repeating frame must to be associated with a group of data model The repeating frame prints (is fired) once for each record of the group.
15. What is Reference Cursor?
A ref cursor query uses PL/SQL to fetch data. Each ref cursor query is associated with a PL/SQL function that returns a strongly typed ref cursor. The function must ensure that the ref cursor is opened and associated with a SELECT statement that has a SELECT list that matches the type of the ref cursor.
You base a query on a ref cursor when you want to:
n more easily administer SQL
n avoid the use of lexical parameters in your reports
n share datasources with other applications, such as Form Builder
n increase control and security
n encapsulate logic within a subprogram
Furthermore, if you use a stored program unit to implement ref cursors, you receive the added benefits that go along with storing your program units in the Oracle database.
16. What is Template?
Ans: Templates define common characteristics and objects that you want to apply to multiple reports. For example, you can define a template that includes the company logo and sets fonts and colors for selected areas of a report. And properties of the objects also
Creation of Template: In Report editor , open a existing Template or Create a new Template and save it concerned directory. Then Edit CAGPREFS.ORA File , and Specify which type of Template are u going to develop.
Ex. Tabular, form, matrix Then give your developed template *.tdf file name.
Develop Report with Newly developed Template.
17 what is Flex mode and Confine mode?
Confine mode
On: child objects cannot be moved outside their enclosing parent objects.
Off: child objects can be moved outside their enclosing parent objects.
Flex mode:
On: parent borders "stretch" when child objects are moved against them.
Off: parent borders remain fixed when child objects are moved against them.
18. What is Page Break?
Ans: To limit the records per page.
19 What is Page Protector?
Ans: 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: if you set yes, the object information print another page.
Print Condition
The print condition type First, All, All but first, Last, All but last refer to the frequency with which you want to appear based upon the setting of the print condition object. A print condition object of Enclosing Object is whichever object encloses the current object (could be the parent or a frame within the parent), while Anchoring Object is the parent object (unless you have explicitly anchored the object in which case it is the object to which it is anchored). The key here is that this is about the pages on which the Print Condition Object appears, not the current object. Oracle views First as the first page on which any part of the Print Condition Object is printed, likewise Last is the last page on which any part of the Print Condition Object is printed. For objects inside a repeating frame, this condition is re-evaluated for each instance of the frame.
20 What is Print Direction?
Ans: The Print Direction property is the direction in which successive instances of the repeating frame appear.
21 What is Vertical and Horizental Elacity
Ans: The Horizontal Elasticity property is how the horizontal size of the object will change at runtime to accommodate the objects or data within it:
22.What is Place holder Columns?
Ans: A placeholder is a column is an empty container at design time. The placeholder can hold a value at run time has been calculated and placed in to It by pl/sql code from anther object. You can set the value of a placeholder column is in a Before Report trigger , A report level formula column(if the place holder column is at report level) A formula column in the place holder group or a group below it
Uses of place holder columns enables u to populate multiple columns from one piece of code. U can calculate several values in one block of pl/sql code in a formula column and assign each value into a different placeholder column. U therefore create and maintain only program unit instead of many.
Store a Temporary value for future reference. EX. Store the current max salary as records are retrieved.
23 What is Formula Column?
Ans: A formula column performs a user-defined computation on another column(s) data, including placeholder columns.
24 What is Summary columns?
Ans: A summary column performs a computation on another column's data. Using the Report Wizard or Data Wizard, you can create the following summaries: sum, average, count, minimum, maximum, % total. You can also create a summary column manually in the Data Model view, and use the Property Palette to create the following additional summaries: first, last, standard deviation, variance.
25 What is Boilerplate?
Ans: Boilerplate is any text or graphics that appear in a report every time it is run. Report Builder will create one boilerplate object for each label selected in the Report Wizard (it is named B_
Column name). Also, one boilerplate object is sometimes created for each report summary. A boilerplate object is owned by the object surrounding it, unless otherwise noted.
26 What is Data Link
When we join multiple quires in a report the join condition is stored in the data link section
Data links relate the results of multiple queries. A data link (or parent-child relationship) causes the child query to be executed once for each instance of its parent group. When you create a data link in the Data Model view of your report, Report Builder constructs a clause (as specified in the link's Property Palette) that will be added to the child query's SELECT statement at runtime. You can view the SELECT statements for the individual parent and child queries in the Builder, but can not view the SELECT statement that includes the clause created by the data link you define.
27 What is filter and Group Filter
28.What is Query Builder
Ans: it’s a gui tool to build a query in Report Wizard, Data Wizard or Data model.
29 What is Break Column?
Ans: We can break a column through data model , it Display once for a group
30.How do u call Report From form?
Ans: RUN_PRODUCT and RUN_REPORT_OBJECT
40. HOW CAN U CREATE TWO FORMATS
USING DISTRIBUTION WE CAN CREATE DIFFERENT FORMATS
45 HOW TO DISPLY ONE RECORD PER PAGE ( WHICH PROPERTY WE SHOULD SET)
Set Repeating Frame Properties : Maximum records per page=1 And it will override group filter property.
In Data model Layout , Group Property Through Filter Type & No of records to display
Property, Values are First, last, pl/sql
47. What is Header ,Body, Trailer, and Footer in Reports
Header: The header consist of on e or more pages that are printed before report proper. The type of
Information you might want to print title of the page ,company logo and address or chart the
Summarizes the report.
Trailer: The trailer consists of one or more pages that print after the report itself, usually used for nothing more than an end of report blank page, but also used for a report summary or chart.
Body: The body is where all the main report objects are placed
Margin: the report layout only governs the part of the pages designated for the main data portion of the report. The margins are can be used to specify page headers and page footers.
49. what are Executable file definitions in Reports
Report Builder (RWBLD60.EXE)
n Reports Runtime (RWRUN60.EXE)
n Reports Convert (RWCON60.EXE)
n Reports Background Engine (RWRBE60.EXE)
n Reports Server (RWMTS60.EXE)
n Reports Web Cartridge (RWOWS60.DLL)
n Reports CGI (RWCGI60.EXE)
n Reports Queue Manager (RWRQM60.EXE)
n Reports Launcher (RWSXC60.EXE)
n Reports ActiveX Control (RWSXA60.OCX)
What are the Non_query fields?
Aggregated Information, Calculated information, A string Function
Can I highlight and change all the format masks and print conditions of a bunch of fields all at once?
You can. If you highlight a bunch of objects and then right click and select "properties..", Oracle gives you a stacked set of the individual properties forms for each of the selected objects. While this may be useful for some things, it requires changing values individually for each object. However, instead you can select the group of fields and then select "Common properties" from the "Tools" menu which will allow you to set the format mask , print conditions etc. for the whole set of objects at once.
How do I change the printed value of a field at runtime?
Triggers are intended to simply provide a true or false return value to determine whether an object should be printed. It is generally not allowed to change any values held in the cursor, make changes to the database, or change the value of it's objects value.
That being said, there is a highly unpublicized method of doing just that using the SRW.Set_Field_Char procedure.
The syntax is SRW.Set_Field_char (0,) and the output of the object that the current trigger is attached to will be replaced by .
There are also SRW.set_fileld_num and SRW.set_field_date for numeric or date fields.
While these options do work, they should only be used if a suitable NVL or DECODE statement in the original query is not possible as they are much, much slower to run. Also, note that this change of value only applies to the formatted output. It does not change the value held in the cursor and so can not be used for evaluating summary totals
Report Bursting
The capability of producing multiple copies of a given report or portion of it in different output formats is referred to as report bursting.
Additional Layout:
Additional layout created for to different format using same query and groups without modifying default layout created by report wizard., we can use both layouts according to user requirement.
System Variables as Source Field In Layout Editor
Ans: Current date, Page Number, Panel number, Physical Page Number, Total Pages,
Total Panels, Total Physical Pages.
Link File : Is a special type of boilerplate, that doesn’t have to remain constant for each report run
The type of file contents, can be Text, Image, CGM, Oracle drawing format, or image URL
Source filename :the name of the file the u want link to the report Through import Image from
