1) About reports
A report consists of objects that collectively define the report:
1)data model objects (queries, groups, columns, links, user parameters)
2)layout objects (repeating frames, frames, fields, boilerplate, anchors)
3)parameter form objects (parameters, fields, boilerplate)
2) Report Styles
2.1)About tabular reports
A tabular report is the most basic type of report. Each column corresponds to acolumn selected from the database.
2.2)About group above reports
A group above report contains multiple groups in its data model.
It is a "master/detail" report, where there may be a lot of information in the master group.
For every master group, the related values of the detail group(s) are fetched from thedatabase and are displayed below the master information.
2.3)About group left reports
A group left report also contains multiple groups in its data model, dividing the rowsof a table based on a common value in one of the columns.
Use this type of report to restrict a column from repeating the same value several times while values of relatedcolumns change.
The data model for group above and group left reports is the same,but the layouts differ; group above reports display the master information at the topwhile group left reports display break columns to the side.
2.4)About form-like reports
A form-like report displays one record per page, displaying field values to the right offield labels.
2.5)About form letter reports
A form letter report contains database values embedded in boilerplate text (any textthat you enter or import into a Report Editor.)
2.6)About mailing label reports
A mailing label report prints mailing labels in multiple columns on each page. Usingthe Report Wizard, you can specify the format for your mailing labels.
2.7)About matrix reports
A matrix (cross-product) report is a cross-tabulation of four groups of data:
One group of data is displayed across the page. One group of data is displayed down the page. One group of data is the cross-product, which determines all possible locations where the across and down data relate and places a cell in those locations. One group of data is displayed as the"filler" of the cells.
Thus, to create a matrix report, you need at least four groups in the data model: onegroup must be a cross-product group, two of the groups must be within thecross-product group to furnish the "labels", and at least one group must provide theinformation to fill the cells. The groups can belong to a single query or to multiplequeries.
3)About triggers
Triggers check for an event.
When the event occurs they run the PL/SQL code associated with the trigger.
Report triggers are activated in response to report events such as the report openingand closing rather that the data that is contained in the report. They are activated in apredefined order for all reports.
Format triggers are executed before an object is formatted. A format trigger can be usedto dynamically change the formatting attributes of the object.
Validation triggers are PL/SQL functions that are executed when parameter values arespecified on the command line and when you accept the Runtime Parameter Form.
Database triggers are procedures that are stored in the database and implicitly executedwhen a triggering statement such as INSERT, UPDATE, or DELETE is issued against theassociated table.
3.1)About report triggers
Report triggers execute PL/SQL functions at specific times during the execution andformatting of your report.
Using the conditional processing capabilities of PL/SQL forthese 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, you use the Report Triggers node in the Object Navigator.
Report triggers must explicitly return TRUE or FALSE.
Oracle Reports has five global report triggers.
The trigger names indicate at what point the trigger fires:
3.1.1)Before Report trigger: Fires before the report is executed but after queries areparsed.
3.1.2)After Report trigger: Fires after you exit the Paper Design view, or after reportoutput is sent to a specified destination, such as a file, a printer, or an e-mail ID.
This trigger can be used to clean up any initial processing that was done, such asdeleting tables.
Note, however, that this trigger always fires, whether or not yourreport completed successfully.
3.1.3)Between Pages trigger: Fires before each page of the report is formatted, except thevery first page.
This trigger can be used for customized page formatting.
In the Paper Design view, 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.
3.1.4)Before Parameter Form trigger: Fires before the Runtime Parameter Form isdisplayed. 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.
3.1.5)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 theRuntime 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.
4)Order of report trigger execution
The order of events when a report is executed is as follows:
1. Before Parameter Form trigger is fired.
2. Runtime Parameter Form appears (if not suppressed).
3. After Parameter Form trigger is fired (unless the user cancels from the Runtime
Parameter Form).
4. Report is "compiled".
5. Queries are parsed.
6. Before Report trigger is fired.
7. SET TRANSACTION READONLY is executed (if specified with the READONLY command line keyword or setting).
8. The report is executed and the Between Pages trigger fires for each page except the first one. COMMITs can occur during this time due to: SRW.DO_SQL with DDL, or if ONFAILURE=COMMIT, and the report fails.
9. COMMIT is executed (if READONLY is specified) to end the transaction.
10. After Report trigger is fired.
11. COMMIT/ROLLBACK/NOACTION is executed based on what was specifiedwith the ONSUCCESS command line keyword or setting.
Ramya Vivek
