Oracle 6i Reports/Report Builder - Part I
Report Builder enables you to develop production-quality reports in a client/server or Web environment. You can deploy reports on the Web using the Reports Server and Reports Web CGI or Reports Cartridge. Reports can be run standalone, or you can run them from forms or Graphics Builder displays.
Major concepts
A report is a collection of objects defining its data, layout, and runtime interface. To quickly create new reports, you use the Report Wizard. The wizard guides you through the process of choosing a report type, defining a data model, and laying out the data. You can reenter the Report Wizard after the initial creation of a report, so you may re-invoke it on an existing report, make changes, and re default the report, even if the report was not originally created with the Report Wizard.
Major Features
1. Data model and layout editors in which you can create the structure and format of your report
2. Object navigator to help you navigate among the data and layout objects in your report
3. Packaged functions for creating computations
4. Fully-integrated Previewer for viewing your report output
5. Support for fonts, colors, and graphics
Additional Features :
1. Context-sensitive online help system
2. Conditional printing capabilities
3. Non-procedural Approach: The unique non-procedural approach of Oracle Reports lets you concentrate on design improvements instead of programming. Its' easy- to-use, fill-in-the-form interface and powerful defaults make developing and maintaining even the most complex reports fast and simple.
4. Full Integration with Other Oracle Products: One can integrate Oracle Reports with other Oracle products such as Oracle Forms, Oracle Graphics, and Oracle Mail.
5. Open Architecture: Oracle Reports' open architecture enables incorporation of user-defined routines written in COBOL, C, and most other programming languages, as well as the powerful PL/SQL language.
Object Navigator
1. The Data Model editor, in which you define the data for the report.
Data model is composed of some or all of the following data definition objects:
a. Queries: Queries are ANSI-standard SQL SELECT statements that fetch data from a standard database.
b. Groups: Groups determine the hierarchy of the data appearing in a report, and are used primarily to create breaks. In addition to break reports, you also create groups to define the cross products of matrix reports.
c. Columns: Columns contain the data values for a report. Default report columns corresponding to the table columns included in each query's SELECT list are automatically created by Oracle Reports, then each column is placed in the group associated with the query that selected the column. If you want to perform summaries and computations on database column values, you can create new columns.
d. Parameters: Parameters are variables for your report that enable you to change selection criteria at runtime.
e. Links: Data links are used to establish parent-child relationships between queries and groups via column-matching. A data link (or parent-child relationship) causes the child query to be executed once for each instance of its parent group. You can create links with various SQL clauses (i.e., WHERE, HAVING, or START WITH) and conditions. When a report with a data link is executed, the data link is converted into a SQL clause and appended to the child query. The child query is executed with the values of the primary key used by the parent.
2. The Layout editor, in which you create the report layout. Layout objects define a report's format; i.e., the positioning and appearance of data, text, and graphics in the report output.
3. The Parameter Form editor, in which you customize the appearance of the Runtime Parameter Form, a window that optionally appears at runtime and enables you to enter parameter values that affect report execution.
4. The Report Editor's Live Previewer view enables you to preview and inspect your report output page by page.
5. Report triggers execute PL/SQL functions at specific times during the execution and formatting of your report.
6. Program units are packages, functions, or procedures that you can reference from any PL/SQL within the current report.
7. Attached libraries are external PL/SQL libraries that you have associated with a report or another external library.
Data Model – Groups
There are two types of groups in Oracle Reports:
1. Default (created by Oracle Reports)
2. User-created (created by you)
Break groups are created to produce subtotals, print columns in a different direction, create breaks, and so on. A break group suppresses duplicate values in sequential records Cross product groups perform mathematical cross products, which are generally used to create matrix reports.
Data Model – Parameter
Parameter values can be specified in these ways:
1. Accepting the default parameter values (default values are set in the Parameter property sheet, and you can control whether the values are displayed at runtime on the Runtime Parameter Form)
2. Typing the parameter value(s) as arguments on the command line (where applicable)
3. Choosing from a list or entering the parameter value(s) in the Runtime Parameter Form
User Parameters
1. Bind References: Use a bind reference when you want the parameter to substitute only one value at runtime. Precede a bind reference with a colon (:).
2. Lexical References: Use a lexical reference when you want the parameter to substitute multiple values at runtime. Precede a lexical reference with an ampersand (&).
Tips in Oracle 6i report customizations
2. Ensure that report builder/designer is in character mode.
3. Ensure constraints are off for stretching, grid is on. , snap to grid is on/off
4. use srw.message frequently
5. Port the rdf in Binary Mode.
6. Set the trace on for the concurrent program to actually see the sql code executed.
7. Bring the query out and changes should be tested in simple SQLs
8. Set the trace on if there are &lexical parameters if you need to check the query that is changing dynamically.
9. Use srw.message to print the value of lexical parameters after their evaluation
10. Use of Concsub to submit your report concurrent program from UNIX faster.
====
Custom Reports Development & Customization Process
Report Builder Components are
1. Data Model
2. Layout Model
3. Object Navigator
4. Report Triggers
5. Parameter Form
6. Program Units
7. Attached Libraries
Data Model
The Report Editor's Data Model view enables you to define and modify the data model objects for a report.
Data Model Comprises tool Palette which Comes with several Options They are
1. Select
2. Magnify
3. SQL Query
4. Ref Cursor Query
5. Express Query
6. Summary Column
7. Formula Column
8. Placeholder Column
9. Cross Product
10. Data Link
The tool palette is a set of tools you can use to create and manipulate objects. Click a tool to activate it for a single operation, or double-click a tool to "lock" it for multiple operations. The tools in the tool palette vary depending on the Report Editor view.
Select
The Selector toolbar provides tools to help you select or arrange dimension values that meet your criteria. The Selector toolbar is displayed at the top of the Selector dialog box.
Magnify
To magnify a hard-to-see portion of your report or reduce the image to get a sense of your report's overall layout.
SQL Query
The SQL Query Statement property is a SQL SELECT statement that retrieves data from the database for your report. Enter a valid SELECT statement not to exceed 64K. The upper limit may vary between operating systems All features of the SELECT statement are supported, except the INTO and FOR UPDATE clauses.
In building your SELECT statement, you can do the following:
1. Use the Tables and Columns dialog box
2. Insert comments
3. Insert bind and lexical references
4. Review SQL errors
Required/Optional : Required
Ref Cursor Query
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:
1. more easily administer SQL
2. avoid the use of lexical parameters in your reports
3. share data sources with other applications, such as Form Builder
4. encapsulate logic within a subprogram
Express Query
--------
Summary Column
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.
Formula Column
A formula column performs a user-defined computation on another column(s) data, including placeholder columns.
Placeholder Column
A placeholder is a column for which you set the data type and value in PL/SQL that you define.
You can set the value of a placeholder column in the following places:
1. The Before Report Trigger, if the placeholder is a report-level column
2. A report-level formula column, if the placeholder is a report-level column
3. A formula in the placeholder's group or a group below it
Cross Product
The Cross Product Group property is the group that contains the source groups of the Horizontal and Vertical Repeating Frames. The cross product group correlates values between one or more groups to produce the data in the matrix.
Values Enter a valid cross product group name.
Applies to matrices
Required/Optional required
Default blank
Data Link
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.
Layout Model
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.
This is the view where you can design your Report Layout.
Object Navigator
The Object Navigator provides a hierarchical display of all major objects in a report or template, including attached libraries and external queries. Using this view, you can take such actions as select an object, bring up the Property Palette for an object, edit an object's PL/SQL, drag and drop PL/SQL program units, and search for an object by name.
Report Triggers
Report triggers execute PL/SQL functions at specific times during the execution and formatting of your report. Using the conditional processing capabilities of PL/SQL for these triggers, you can do things such as customize the formatting of your report, perform initialization tasks, and access the database.
To create or modify a report trigger, use Report Triggers in the Object Navigator. Report triggers must explicitly return TRUE or FALSE. Report Builder has five global report triggers which are fired in the following sequence.
1. Before Parameter Form trigger
2. After Parameter Form trigger
3. Before Report trigger
4. Between Pages trigger
5. After Report trigger
1. Before Parameter Form trigger
The Before Parameter Form trigger fires before the Runtime Parameter Form is displayed. From this trigger, you can access and change the values of parameters, PL/SQL global variables, and report-level columns. If the Runtime Parameter Form is suppressed, this trigger still fires. Consequently, you can use this trigger for validation of command line parameters.
Definition Level Report
On Failure
Displays an error message and then returns to the place from which you ran the report.
2. After Parameter Form trigger
The After Parameter Form trigger fires after the Runtime Parameter Form is displayed. From this trigger, you can access parameters and check their values. This trigger can also be used to change parameter values or, if an error occurs, return to the Runtime Parameter Form. Columns from the data model are not accessible from this trigger. If the Runtime Parameter Form is suppressed, the After Parameter Form trigger still fires. Consequently, you can use this trigger for validation of command line parameters or other data.
Definition Level Report
On Failure
Returns to the Runtime Parameter Form. If the Form is suppressed, then returns to place from which you ran the report.
3. Before Report trigger
The Before Report trigger fires before the report is executed but after queries is parsed and data is fetched.
Definition Level Report
On Failure
Displays an error message and then returns to the place from which you ran the report.
4. Between Pages trigger
Between Pages trigger fires before each page of the report is formatted, except the very first page. This trigger can be used for customized page formatting. In the Runtime Previewer or Live Previewer, this trigger only fires the first time that you go to a page. If you subsequently return to the page, the trigger does not fire again.
Definition Level report
On Failure
Displays an error message when you try to go to the page for which the trigger returned FALSE. The Between Pages trigger does not fire before the first page.
5. After Report trigger
The After Report trigger fires after you exit the Runtime Previewer, or after report output is sent to a specified destination, such as a file, a printer, or a mailid. This trigger can be used to clean up any initial processing that was done, such as deleting tables.
Note, however, that this trigger always fires, whether or not your report completed successfully.
Definition Level Report
On Failure
Does not affect formatting because the report is done. You can raise a message, though, to indicate that the report did not run correctly
Note: The After-Report trigger does not fire when you are in the Live Previewer.
Layout Model Properties
In this Layout model, we have several Options
Frames:
Frames are used to surround other objects and protect them from being overwritten or pushed by other objects.
For example, a frame might be used to surround all objects owned by a group, to surround column headings, or to surround summaries.
When you default the layout for a report, Report Builder creates frames around report objects as needed; you can also create a frame manually in the Layout Model view.
Repeating Frames
Repeating frames surround all of the fields that are created for a group’s columns. The repeating frame prints (is fired) once for each record of the group.
When you default the layout for a report, Report Builder creates repeating frames around fields as needed; you can also create a repeating frame manually in the Layout Model view.
Text
This allows to embed the text in the layout view.
Field
A field is the layout container for each column in the layout. A field is owned by the object surrounding it, typically a repeating frame, unless the field is a summary (in which case it is owned by a frame).
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...
Parameter Form
The Report Editor's Parameter Form view enables you to create a Runtime Parameter Form for your report. You can select pre-defined system parameters for your form using the Parameter Form Builder, or you can create your own.
Program Units
Program units are packages, functions, or procedures that you can reference from any PL/SQL within the current report.
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.
Attached Libraries
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.
Report Customizations
You can customize business intelligence reports by both content and layout. You also have the option to add columns, filter the data and sort the resulting information. For example, you can add another column for a running total, or insert grand totals and subtotals as you need them. In layout, you can change column headings, report titles and the order of columns. You can also display the data in graphical form, such as bar graphs or pie charts.
Customization is enhancement of new features to the Existing Report Content and Layout depending on the new requirement.
No comments:
Post a Comment