3
How To...

This chapter provides procedures for using Oracle Reports to create objects and design your reports. Each topic in this chapter is also included in the How To... section of the Reports Builder online help (see Section 3.1.1, "Using the online help").

The procedures are grouped into the following sections:

3.1 Access Oracle Reports Documentation

This section provides procedures for the following tasks that you may perform to access the Oracle Reports documentation:

3.1.1 Using the online help

You can access the Reports Builder online help in any of the following ways:

Choose Help > Help Contents.
Click Help or press F1 in any dialog box.
In the Property Inspector, click a property, then press F1 to display the property's help topic.

The following guidelines will help you to make effective use of the online help system:

Familiarize yourself with the Contents tab to get an idea of the scope of the topics covered in the online help.
Click the Index tab to locate specific topics on a subject.
The Help Navigator window and Help Topic window can be separated or kept together. Move the windows as appropriate for your use.

3.1.2 Locating other documentation

To get started and become proficient with building reports, refer to the following resources:

Table 3-1 Oracle Reports documentation roadmap

For...	Refer to...
an overview of Oracle Reports	Getting Started with Oracle Reports, available on the Oracle Technology Network Oracle Reports Documentation page (http://otn.oracle.com/docs/products/reports/content.html). the online help topics under the Welcome node in the Help Navigator window (choose Help > Help Contents): About Reports Builder and About this release.
an overview of new features in this release	Oracle Reports New Features, available on the Oracle Technology Network Oracle Reports main page (http://otn.oracle.com/docs/products/reports/content.html). the online help topic under the Welcome node in the Help Navigator window (choose Help > Help Contents): About this release.
an overview of deprecated, obsolete, and changed functionality in this release	Oracle Reports Obsolescence Plan, available on the Oracle Technology Network Oracle Reports main page (http://otn.oracle.com/docs/products/reports/content.html). the online help topic under the Welcome node in the Help Navigator window (choose Help > Help Contents): About this release.
instructions and hands-on lessons about building paper and Web-based reports	This manual, and Oracle Reports Tutorial, available on the Oracle Technology Network Oracle Reports Documentation page (http://otn.oracle.com/docs/products/reports/content.html). the online help (choose Help > Help Contents) for specific tasks and reference information.
context-sensitive help on dialog boxes, messages, and properties	for dialog boxes and messages, the online help topics displayed when you click the Help button, or press F1. for properties, the online help topic displayed when you click a property in the Property Inspector, then press F1.
Information about OracleAS Reports Services and configuring and using the Reports Server	Oracle Application Server Reports Services Publishing Reports to the Web, available on the Oracle Technology Network Oracle Reports Documentation page (http://otn.oracle.com/docs/products/reports/content.html).
information about using the Oracle Reports application program interface (API)	Oracle Reports JavaAPI Reference, available on the documentation CD and the Oracle Technology Network Oracle Reports Documentation page (http://otn.oracle.com/docs/products/reports/content.html).
examples and demos	Getting Started with Oracle Reports, available on the Oracle Technology Network Oracle Reports Documentation page (http://otn.oracle.com/docs/products/reports/content.html). Use the Index to find the list of all examples and demos for Oracle Reports. A subset of the examples is also available on the Oracle Developer Suite product CD: Tutorial example files Express example files Building a Report with a Barcode Bean example Building a Report with an XML Pluggable Data Source example Building a Report with a Text Pluggable Data Source example Bursting and Distributing a Report example Oracle Reports Demos
other resources, such as white papers	the Oracle Technology Network (http://otn.oracle.com).

3.2 Set Properties and Preferences

This section provides procedures for the following tasks that you may perform as you work with property and preference settings:

3.2.1 Displaying the Property Inspector

To display the Property Inspector for an object:

In the Object Navigator, do either of the following:
- Double-click the icon immediately to the left of the object name.
- Click the object name, then right-click and choose Property Inspector.
- Click the object name, then choose Tools > Property Inspector.
In the Report Editor views, do either of the following:
- Double-click the object.
- Click the object, then right-click and choose Property Inspector.
- Click the object, then choose Tools > Property Inspector.

3.2.2 Setting report properties

To set the properties for a report:

In the Object Navigator, double-click the properties icon next to the report name to display the Property Inspector.
Set report properties as desired.

3.2.3 Setting report preferences

To specify the preferences for a report:

Choose Edit > Preferences.
To specify preferences for designing reports, set values on the General, Access, and Wizards tabs.
To specify preferences for running reports, set values on the Runtime Values and Runtime Settings tabs.
For a detailed description of settings in the Preferences dialog box, click Help.

3.2.4 Setting preferences for the Object Navigator

To specify options for the Object Navigator display:

In the Object Navigator, choose Tools > Options > Navigator.
In the Object Navigator Options dialog box, change the settings as desired.
Click OK.

3.2.5 Setting properties for an ASCII (character-mode) report

To set properties for an ASCII (character-mode) report:

Double-click the properties icon next to the report name to display the Property Inspector.
Under the Character Mode node, set Design In Character Units to Yes.
In the Object Navigator, under the Paper Layout node, double-click the Header Section, Main Section, or Trailer Section properties icon for the pertinent report section (Header, Main, or Trailer) to display the Property Inspector.

Note:
By default, a report is defined in the Main section.
In the Property Inspector, under the Section node:
- set the Report Width and Report Height to the appropriate character-mode dimensions for the report. For example, 132 (or 180) width x 66 height for landscape or 102 width x 85 (or 116) height for portrait.
- set the Orientation property to the desired value.
In the Object Navigator, expand the Data Model node, then the System Parameters node.
Double-click the properties icon next to MODE to display the Property Inspector, and set properties:
- Under the Parameter node, set the Initial Value property to Character.
In the Paper Layout view, choose Tools > Options > Rulers to display the Ruler Settings dialog box:
- Set Units to Character Cells and Number of Snap Points Per Grid Spacing to 1.
- Click OK.
Click View in the menu bar and make sure that Snap to Grid is checked.
Choose Edit > Preferences to display the Preferences dialog box:
- On the Wizards page, set Horizontal Interfield to 1 and Vertical Interfield to 0.
- Click OK.
Choose Format > Font, and select the font, style, and size that most closely approximates the character-mode font. For example, Courier, Regular, 12 points.

3.2.6 Setting color palette preferences

To set color palette preferences:

Choose Edit > Preferences.
In the Preferences dialog box, on the General tab page, set the Color Mode as desired:
- Editable. Reports Builder temporarily replaces your system's color palette with the palette of the active report. Each time a new report is made active, its color palette replaces the system palette. The active report will then be shown accurately, although the appearance of the inactive reports may suffer. You must choose Editable if you want to modify the color palette or import or export a color palette.
- Read Only - Shared. (Default) Reports Builder continues to append each active report's color palette to your system's original palette until there is no room left in the palette for any more colors. That palette becomes the single palette that all open reports share. If you create a report that uses entirely different colors, it may not be shown accurately.
- Read Only - Private. Each time you make a report active, Reports Builder appends that report's color palette to your system's original palette until there is no room left in the palette for any more colors. That palette becomes the palette that is used whenever that report is active. The active report is then shown accurately, although the appearance of the inactive reports may suffer.
If you change the Color Mode, shut down and restart Reports Builder to enable the new mode.

3.2.7 Setting properties of multiple objects

To set the properties of multiple objects:

In the Object Navigator or any Report Editor view, select the objects whose properties you want to set. The objects can be of different types and can even be in different documents.
Choose Tools > Property Inspector.
In the Property Inspector, click the Union button to see all properties of all objects or leave the tool as the Intersection button to see only the properties the objects have in common.
Set the properties as desired.

Note:
Any change you make to a property setting is applied to all of the objects in the current selection to which that property applies.

For example, a report includes several fields, each of which displays the date. The Datatype property of each field is DATE, which causes dates to be displayed in the default ORACLE format DD-MON-YY.

To use a different date format throughout the application, you need to set the Format Mask property for each field that displays dates. Rather than setting each field's Format Mask property individually, select all of the items, then set the Format Mask property once to change the format mask for each item selected.

3.2.8 Comparing the properties of one object to another

To compare the properties of one object to another:

In the Object Navigators or the editors, double-click the first object so that its properties are displayed in the Property Inspector.
In the Property Inspector, click the Pin button to attach this copy of the Property Inspector to the current object.
In the Object Navigator, click the second object, then choose Property Inspector from the pop-up menu.

A second Property Inspector is displayed. If the second window is on top of the first, drag it to move it alongside the first window.

3.3 Perform Common Tasks

As you work with Reports Builder, you will become very familiar with the following tasks:

Usage notes

Beginning with Oracle Reports 10g, reports are no longer stored in the database (including the obsolescence of Rename, Grant, and Get Info functionality for reports in the database). Instead, reports are saved to files or into source control using the Check In and Check Out capabilities of Oracle Software Configuration Manager (SCM).

3.3.1 Connecting to a database

To connect to a database when designing a report in Reports Builder:

Choose File > Connect.
In the Connect dialog box, type the required information in the User Name, Password, and Database fields (click Help for more information), then click Connect.

To connect to a database using the command line:

On the rwbuilder or rwrun command line, use the USERID keyword to specify connection information (USERID=username[/password][@database]). For information about USERID, see the Reference > Command Line section of the Reports Builder online help.

3.3.2 Opening a report

To open a report:

Choose File > Open.
In the Open dialog box, browse to the report, click its name, then click Open to open the report in Reports Builder.

3.3.3 Saving a report

To save a report:

Choose File > Save or File > Save As or click the Save button in the toolbar.
In the Save dialog box, browse to the desired location, and type a name for the report, if it has never been saved before. Click Save.

3.3.4 Copying a report

To copy a report:

In the Object Navigator, click the report you want to copy.
Choose File > Save As.
In the Save As dialog box, browse to the desired location, and type a name for the new copy of the report. Click Save.

3.3.5 Renaming a report

To rename a report in the Object Navigator:

In the Object Navigator, double-click the properties icon next to the report name to display the Property Inspector.
In the Property Inspector, under the General Information node, set the Name property by typing a new name.

To rename a report in the file system:

In your operating system's file system (e.g., Windows Explorer on Windows), browse to the location of the report.
Click the report name, then click again to make its name active.
Type a new name for the report.

3.3.6 Deleting a report

To delete a report in the Object Navigator:

In the Object Navigator, click the report name, then choose File > Delete, or click the Delete button in the toolbar.
In the alert dialog box, click Yes to delete the report. To cancel the delete operation, click Cancel.

To delete a report in the file system:

In your operating system's file system, for example, Windows Explorer on Windows, browse to the location of the report.
Click the report name, then choose File > Delete.
In the alert dialog box, click Yes to delete the report. The cancel the delete operation, click Cancel.

3.3.7 Archiving a report

To archive a report in Oracle Reports 10g, it can be saved into source control using the Check In and Check Out capabilities of Oracle Software Configuration Manager (SCM).

SCM is a single central location where all application components, including business logic, can be stored. This provides complete control over your application development environment. SCM includes features such as source/version control, impact analysis, and check in and check out of all the related application components. Oracle Reports is integrated with Oracle SCM, which allows you to easily control your reports in a seamless integrated environment.

3.3.8 Selecting and deselecting objects

To manipulate objects or define their properties, you must first select them.

3.3.8.1 Selecting single objects

To select a single object in the Object Navigator:

Click the object. If the object is displayed in one of the Report Editor views, it is also selected in the corresponding view.

To select a single object in the Data Model view, Paper Layout view, Paper Design view, or Parameter Form view:

If a tool in the tool palette is active, first click the Select tool in the tool palette.
Click the object.

3.3.8.2 Selecting multiple objects

To select multiple objects in the Object Navigator:

Click the first object, then either:
- Shift-click another object to select all objects between the first and current object.
- Or, control-click additional objects to add them to the selection group.
Objects that are displayed in one of the Report Editor views are also selected in the corresponding view.

To select multiple objects in the Data Model view, Paper Layout view, Paper Design view, or Parameter Form view:

If a tool in the tool palette is active, first click the Select tool in the tool palette.
Do either of the following:
- Shift-click each object.
- Or, click and drag a region that includes all of the objects you want to select. (In the Data Model view, note that if the first object you select is a column within a group, everything else you select must be a column within the same group; you cannot select columns within groups at the same time as you select queries, groups, report-level columns, or parameters.)

3.3.8.3 Selecting objects owned by a frame

To select a set of objects owned by a frame or repeating frame:

In the Paper Layout view, either:
- Click the Confine On button in the toolbar.
- Or, if you want to explicitly select all objects to set common properties, click the Frame Select button in the toolbar.
Click the frame or repeating fame.

All objects within the frame or repeating frame are selected, depending upon their explicit anchors.

3.3.8.4 Selecting grouped objects

To select grouped objects (grouped using Layout > Group Operations > Group), click the Select tool in the tool palette and click on one of the grouped objects.

3.3.8.5 Selecting all objects in a report region

To select all objects within the region (Body or Margin of the Header Section, Main Section, or Trailer Section) you are currently editing, choose Edit > Select All. (In the Data Model view, note that this does not select any columns within groups.)

3.3.8.6 Selecting overlapped objects

To select any object that is partially or completely overlapped by another object:

Click the object that obscures the object you want to select.
Choose Layout > Send to Back to move the object that you want on top of the object that obscured it.
Click the object.

3.3.8.7 Deselecting single objects

To deselect a single object in the Object Navigator:

Control-click the object. If the object is displayed in one of the Report Editor views, it is also deselected in the corresponding view.

To deselect a single object in the Data Model view, Paper Layout view, Paper Design view, or Parameter Form view:

Shift-click the object.

3.3.8.8 Deselecting multiple objects

To deselect all selected objects:

Click anywhere in a blank area.

3.3.9 Deleting an object

To delete an object:

Click the object(s).

Tip:
To select multiple objects, click one object, then Shift-click all other objects. To select all objects, choose Edit > Select All.
Choose Edit > Clear.

3.3.10 Hiding or showing components

To hide or show components in the Object Navigator or Report Editor views:

Click the View menu, then choose the component you want to hide or show. A checkmark indicates that the component is shown; no checkmark indicates that the component is hidden.

3.4 Work with the Object Navigator

This section provides procedures for the following tasks that you may perform as you work with the Object Navigator:

3.4.1 Displaying a Report Editor view from the Object Navigator

To display a Report Editor view from the Object Navigator, do any of the following:

Double-click the icon next to the node of the view you want to display. For example, to display the Paper Layout view, double-click the view icon next to the Paper Layout node.
Click the node, then click the right mouse button and choose Report Editor.
Click the node, then choose Tools > Report Editor.

Note:
In the Object Navigator, there is no Paper Design node; the Paper Design view is displayed only after running a report.

3.4.2 Expanding and collapsing nodes

To expand or collapse a node one level, do either of the following:

In the Object Navigator, click the plus or minus sign next to the node.
Click the node, then click the Expand or Collapse tool in the tool palette.

To fully expand or collapse a node, do either of the following:

In the Object Navigator, click the plus or minus sign next to the node repeatedly until fully expanded.
Click the node, then click the Expand All or Collapse All tool in the tool palette.
Click the node, then choose View > Expand All or View > Collapse All.

3.4.3 Searching for nodes

To search for a specific node in the Object Navigator:

Type the full of partial name of the node in the Find field.
To search for the next match, click the Find Next button in the toolbar.
To search for the previous match, click the Find Previous button in the toolbar.

3.4.4 Changing Object Navigator views

To view objects in the Object Navigator by object hierarchy to see the parent-child relationships:

Choose View > Change View > Ownership View.

To view objects in the Object Navigator by their type such as all queries under a single heading:

Choose View > Change View > Object Type View.

3.4.5 Setting preferences for the Object Navigator

See Section 3.2.4, "Setting preferences for the Object Navigator".

3.5 Work with Reports

This section provides procedures for the following tasks that you may perform as you work with reports (either paper-based or Web-based):

3.5.1 Creating a report

To create a single-query report:

In the Object Navigator, click the Reports node, then click the Create button in the toolbar.
In the New Report dialog box, click Use the Report Wizard, then click OK.
Follow the Report Wizard to create your report, clicking Help for assistance on any tab page.
Modify the resulting report output in the Web Source view or Paper Design view, or choose Tools > Report Wizard to re-enter the wizard.

To build a multiquery report:

Create each query using the Data Wizard, clicking Help for assistance on any tab page (see Section 3.8.1.2, "Creating a query: Data Wizard").
Create a layout for the report (see Section 3.5.4, "Creating a default layout for a report").

3.5.2 Creating a multiquery group above report

See the example report in Chapter 11, "Building a Two-Query Group Report".

3.5.3 Creating a nested matrix report

See the example report in Chapter 26, "Building a Nested Matrix Report".

3.5.4 Creating a default layout for a report

To create a default layout for a report:

To default the layout for the current report (by default, the Main section of the report), choose Tools > Report Wizard, then follow the wizard to create the layout for the report style you choose.
To add another layout section to the current report layout, create an additional report layout, as described below.

Make further modifications to the default layout manually in the Paper Layout view.

Caution:

If you re-enter the Report Wizard after making manual adjustments to your layout in the Paper Layout or Paper Design view, you will lose these layout changes when you click Finish in the Report Wizard, which redefaults the layout.

3.5.5 Creating an additional report layout

To add a new layout section to a report without overriding existing layouts:

In the Paper Layout view, click the Report Block tool in the tool palette.
Drag a rectangular area for the new layout to display the Report Block Wizard.
Follow the wizard to select the data to display in the new layout section.
To reorder the layout sections, click and drag them to new positions in the Paper Layout view.

Modify the report output in the Paper Design view.

Caution:

If you re-enter the Report Wizard to make modifications, then click Finish in the Report Wizard, your entire layout will be overwritten with a new default layout. You will lose the additional report layout you have created, and any manual changes made to the layout in the Paper Layout or Paper Design view.

3.5.6 Adding a title to a report

To add a title to a report:

In the Object Navigator, select or open the report.
Choose Tools > Report Wizard.
On the Style page, type a title for the report in the Title field.
If the report title is to be used in a template, see Section 3.12.5, "Formatting the report title in a template".

3.5.7 Adding a table of contents to a report

To create a table of contents (TOC) for a report, you can use the Format Order property or the SRW.SET_FORMAT_ORDER built-in procedure to format the Main section of the report first and use report triggers to build a table containing the TOC entries. When the first element for the TOC is formatted, a trigger fires and creates a row in the TOC table containing the TOC entry and the page number. After the Main section has completed formatting, the format order setting can define that the Header section is formatted next. The Header section can contain a report block based on the TOC table. After formatting, you can output your report with a TOC (the Header section), followed by the report body (the Main section), followed by the Trailer section.

For two step-by-step examples, see Chapter 34, "Building a Paper Report with a Simple Table of Contents and Index" and Chapter 35, "Building a Paper Report with a Multilevel Table of Contents".

3.5.8 Adding index to a report

To create an index for a report, you can use report triggers to build a table containing the index entries as you format the Main section of your report. When the first element for the index is formatted, a trigger fires and creates a row in the index table containing the index entry and the page number. After the Main section has completed formatting, the Trailer section is formatted next by default. The Trailer section can contain a report block based on the index table. After formatting, you can output your report with the report body (the Main section), followed by an index (the Trailer section).

For a step-by-step example, see Chapter 34, "Building a Paper Report with a Simple Table of Contents and Index".

3.5.9 Creating an ASCII (character-mode) report

To create an ASCII (character-mode) report:

First, create and save a report using the Report Wizard.
In the Object Navigator, under the Reports node, click the report you want to convert to character mode.
Choose Tools > File Conversion.
In the Convert dialog box, on the Conversion page:
- set Document Type to Report.
- set Source to the name of the existing bit-mapped report.
- set Destination Type to Report Binary File (RDF).
- set Destination to the name of the new character-mode report.
On the Options page, set Destination Unit to Character.
Click OK.
Set properties for your new character-mode report.

3.5.10 Preparing a multiplatform report

To prepare a report to run on multiple platforms, consider the following GUI differences:

Fonts: A font type, style, or size might not be available in the target GUI. You can handle this in one of two ways:

Use a font that you know exists on the target GUI or one that maps well to the default font of the target GUI.
Modify the font mapping file, uifont.ali, to ensure that the fonts map correctly.

Colors: A color might not be available in the target GUI. If possible, use a color that you know exists on the target GUI; otherwise, use one that maps well to the default color of the target GUI. The following colors are typically available on many platforms: blue, magenta, red, cyan, green, yellow.

DPI: The dots-per-inch (DPI) that your monitor uses may not be the same as the DPI used by the person who runs the report. The DPI only affects how alpha-numeric characters word-wrap on the screen. If you design a report that may be displayed in the Paper Design view, try to use the same DPI as the people who will run it. Also avoid giving layout objects fixed sizing.

3.5.11 Preparing a report for translation into other languages

For detailed information about National Language Support (NLS), review the topics in the National Language Support section of the Reports Builder online help.

3.6 Work with Web Reports

This section provides procedures for the following tasks that you may perform as you work with Web reports:

3.6.1 Creating a Web report

You can create a Web report in any of the following ways:

Create a report using the Report Wizard, (see Section 3.5.1, "Creating a report"), choosing to create a report for both Web and paper, or Web only.
Insert a report in a Web page by adding report data, then adding a report block to a Web page, as described in Section 3.6.3, "Adding report data to an existing Web page (HTML file)" and Section 3.6.4, "Adding a report block to a Web page".
Display the Web Source view and manually insert the Oracle Reports custom JSP tags (see the Reference section of the Reports Builder online help).
Insert an existing report into an existing Web page, by displaying the Web Source view for both the report and the Web page, then copying and pasting the report block into the desired position in the Web page.
Prepare a paper-based report for the Web by adding hyperlinks to an existing report (see Section 3.6.6, "Preparing a paper-based report for the Web").

3.6.2 Viewing the source code for a Web report

To view the source code for a Web report:

In the Object Navigator, double-click the view icon next to the Web Source node for the report to display the source code in the Web Source view.

3.6.3 Adding report data to an existing Web page (HTML file)

To retrieve the data to be used to build a report to add to an existing Web page (HTML file):

In the Object Navigator, choose File > Open.
Navigate to your Web page (HTML file), and click Open.

In the Object Navigator, find the report created when you opened the HTML file (Reports Builder may give the report a default name, such as REP1), then double-click the view icon next to the Data Model node to display the Data Model view.

Caution:

If you right-click the Data Model node, then choose Report Wizard to use the Report Wizard to select the data for a report, the report will overwrite all of the data in your existing Web page.

In the Data Model view, choose Insert > Query to select data for the report using the Data Wizard.

After you click Finish, the data you have selected is available to your Web report.

Next step

See Section 3.6.4, "Adding a report block to a Web page"

3.6.4 Adding a report block to a Web page

To add a report block to an existing Web page:

In the Object Navigator, choose File > Open.
Navigate to your Web page (HTML file), and click Open.
In the Object Navigator, find the report created when you opened the HTML file (Reports Builder may give the report a default name, such as REP1).
If the Web page has never had a report added to it, you must first add the data to be used to build the report, as described above.
After you have added data to be used to build the report, double-click the view icon next to the Web Source node to display the source code for the Web page in the Web Source view.
Locate the section in the source code where you want to add the report block.

Note:
Adding some comment text such as "Place the report block here" to your Web page allows you to easily locate the correct position for your report block.
Choose Insert > Report Block.
In the Report Block Wizard, specify the information for the report block.

3.6.5 Adding a graph to a Web report

See Section 3.9.8.4, "Adding a graph to a Web report"

3.6.6 Preparing a paper-based report for the Web

To prepare a paper-based report for the Web:

Optionally, add desired Web links to a new or existing report:
- Create an HTML document header (see Section 3.6.7.1.1
- Create an HTML document footer (see Section 3.6.7.1.2)
- Create an HTML page header (see Section 3.6.7.1.3
- Create an HTML page footer (see Section 3.6.7.1.4)
- Create an HTML Parameter Form header (see Section 3.6.7.1.5)
- Create an HTML Parameter Form footer (see Section 3.6.7.1.6)
- Create a hyperlink (and, if linking to an object within the report, create an associated hyperlink destination) (see Section 3.6.7.1.7 and Section 3.6.7.1.8)
- Create an application command line link (PDF output only) (see Section 3.6.7.1.9)
- Create a bookmark (see Section 3.6.7.1.10)
Select a printer or use the default printer setup (the drivers for the currently selected printer are used to produce the output; you must have a printer configured for the machine on which you are running the report).
Display your report output in your Web browser (see Section 3.7.15.4, "Displaying report output in your Web browser"

3.6.7 Adding Web links to paper-based reports

This section provides procedures for adding links to paper-based reports that will become active when you run your report to an HTML or PDF file. This is completely separate from HTML that might be generated when you run a JSP-based Web report (when you click the Run Web Layout button in the toolbar, or choose Program > Run Web Layout).

You can add Web links to paper-based reports using either:

the user interface.
PL/SQL.

3.6.7.1 Using the user interface

This section provides procedures for the following tasks that you may perform using the Reports Builder user interface to add Web links to paper-based reports:

3.6.7.1.1 Creating an HTML document header using the Property Inspector

Note:

This procedure is for HTML output only.

To insert an HTML file or text on the header page of your HTML document:

In the Object Navigator, double-click the properties icon for the report to display the Property Inspector.
Under the Report Escapes node, set the Before Report Type property to Text (if you will type the header) or File (if you will import the header from a file).
Set the Before Report Value property by clicking the... button to either type HTML code in the dialog box or select an HTML file to import.

3.6.7.1.2 Creating an HTML document footer using the Property Inspector

Note:

This procedure is for HTML output only.

To insert an HTML file or text on the footer page of your HTML document:

In the Object Navigator, double-click the properties icon for the report to display the Property Inspector.
Under the Report Escapes node, set the After Report Type property to Text (if you will type the footer) or File (if you will import the footer from a file).
Set the After Report Value property by clicking the... button to either type HTML code in the dialog box or select an HTML file to import.

3.6.7.1.3 Creating an HTML page header using the Property Inspector

Note:

This procedure is for HTML output only.

To add a page header to every page of your HTML document:

In the Object Navigator, double-click the properties icon for the report to display the Property Inspector.
Under the Report Escapes node, set the Before Page Type property to Text (if you will type the header) or File (if you will import the header from a file).
Set the Before Page Value property by clicking the... button to either type HTML code in the dialog box or select an HTML file to import.

To add a page header to a single page of your HTML document:

See Section 3.6.7.2.3, "Creating an HTML page header using PL/SQL".

3.6.7.1.4 Creating an HTML page footer using the Property Inspector

Note:

This procedure is for HTML output only.

To add a page footer to every page of your HTML document:

In the Object Navigator, double-click the properties icon for the report to display the Property Inspector.
Under the Report Escapes node, set the After Page Type property to Text (if you will type the footer) or File (if you will import the footer from a file).
Set the After Page Value property by clicking the... button to either type HTML code in the dialog box or select an HTML file to import.

To add a page footer to a single page of your HTML document:

See Section 3.6.7.2.4, "Creating an HTML page footer using PL/SQL".

3.6.7.1.5 Creating an HTML Parameter Form header using the Property Inspector

Note:

This procedure is for HTML output only.

To add items to the top of the HTML Parameter Form:

In the Object Navigator, double-click the properties icon next to the report name to display the Property Inspector.
Under the Report Escapes node, set the Before Form Type property to Text (if you will type the header) or File (if you will import the header from a file).
Set the Before Form Value property by clicking the... button to either type HTML code in the dialog box or select an HTML file to import.

3.6.7.1.6 Creating an HTML Parameter Form footer using the Property Inspector

Note:

This procedure is for HTML output only.

To add items to the bottom of the HTML Parameter Form:

In the Object Navigator, double-click the properties icon next to the report name to display the Property Inspector.
Under the Report Escapes node, set the After Form Type property to Text (if you will type the footer) or File (if you will import the footer from a file).
Set the After Form Value property by clicking the ... button to either type HTML code in the dialog box or select an HTML file to import.

3.6.7.1.7 Creating a hyperlink using the Property Inspector

To add a hyperlink to your report:

Create a hyperlink destination, which will be the target of the Web link.
In the Object Navigator, expand the Paper Layout node, then expand the node that contains the object that will be the source of the Web link.

Note:
If you are defining a template, you can select objects in the margin. Objects in the body are unknown until the template is applied to a report.
Double-click the object that will be the source of the Web link to display the Property Inspector.

Under the Web Settings node, set the Hyperlink property to the destination of the link.

For examples, see the description of the Hyperlink property in the Reference section of the Reports Builder online help.

Note:

A report output in PDF format can include both hyperlinks and application command line links. If the Application Command Line property is set for an object, it will be applied to the object. Otherwise, the Hyperlink property is applied.

To apply additional HTML attributes to the hyperlink, set the Additional Hyperlink Attributes property accordingly.

3.6.7.1.8 Creating a hyperlink destination using the Property Inspector

To add a hyperlink destination to your report:

In the Object Navigator, expand the Paper Layout node, then expand the node that contains the object that will be the destination of a Web link.

Note:
If you are defining a template, you can select objects in the margin. Objects in the body are unknown until the template is applied to a report.
Double-click the object that will be the destination of a Web link to display the Property Inspector.
Under the Web Settings node, set the Hyperlink Destination property to an identifier for the object.

3.6.7.1.9 Creating an application command line link using the Property Inspector

Note:

This procedure is for PDF output only.

To associate a command with an object in your report:

In the Object Navigator, expand the Paper Layout node, then expand the node that contains the object with which you want to associate a command.

Note:
If you are defining a template, you can select objects in the Margin. Objects in the body are unknown until the template is applied to a report.
Double-click the object with which you want to associate a command to display the Property Inspector.

Under the Web Settings node, set the Application Command Line property to the command you want to execute when the object is clicked.

Note:

Restrictions

An object that is associated with an application command line link cannot also be the source of a Web link (a hyperlink).

3.6.7.1.10 Creating a bookmark using the Property Inspector

To create a bookmark on objects other than break columns in the bookmark area of your HTML or PDF document:

In the Object Navigator, expand the Paper Layout node, then expand the node that contains the object with which you want to associate a bookmark (typically, the object is a repeating frame or frame that encloses the relevant section of the report).

Note:
If you are defining a template, you can select objects in the Margin. Objects in the body are unknown until the template is applied to a report.
Double-click the object with which you want to associate a bookmark to display the Property Inspector, and set properties:
- Under the Web Settings node, set the Bookmark property to the string you want to appear in the bookmark area of the formatted report.

3.6.7.1.11 Creating a bookmark on break columns using the Property Inspector

To create a bookmark on break columns in the bookmark area of your paginated HTMLCSS or paper PDF document:

In the Paper Layout view, choose Insert > Bookmark.
In the Insert Bookmarks dialog box, move the desired column(s) to the Bookmarks list.
Click OK.

3.6.7.1.12 Adding navigation controls for HTML page-streamed output using the Property Inspector

Note:

This procedure is for HTML output only.

To add navigation controls for HTML page-streamed (paginated) output:

In the Object Navigator, double-click the properties icon next to the report name to display the Property Inspector.
Set the Page Navigation Control Type and Page Navigation Control Value properties.

Note:
If you do not change the default Page Navigation Control Value, Reports Builder will use a default built-in JavaScript for implementing the navigation between the pages of output.

3.6.7.1.13 Linking an image object to a URL

Note:

This procedure is for HTML output only.

To link an image object to a URL:

In the Paper Design view or Paper Layout view, click the Link File tool in the tool palette
Click and drag a rectangle.
Double-click the link file object to display the Property Inspector.
Under the Link File Boilerplate node:
- set the Source File Format property to Image URL.
- set the Source Filename property to the URL where the image is located with the required protocol.

Example 1

HTTP://www.oracle.com/images/logo.gif

Example 2

HTTP://&<P_SERVER_NAME>/images/logo.gif 
where P_SERVER_NAME is a user parameter of type CHAR
At runtime, the end user can specify a value for the parameter (e.g., P_SERVER_
NAME = www.oracle.us.com for a dynamic URL link of 
HTTP://www.oracle.us.com/images/logo.gif).

Example 3

FILE://c:/images/logo.gif

Note:

If you click Browse to find a file, Reports Builder automatically prepends FILE:// to the returned path.

3.6.7.1.14 Creating a boilerplate text object for HTML tags

Note:

This procedure is for HTML output only.

To create a boilerplate text object for HTML tags:

In the Paper Design view, click the Text tool in the tool palette.
Click and drag a rectangle.
In the new boilerplate object, type the desired HTML.

Note:
The text you type will format for HTML output only; for all other output formats, the text object will not appear in the report.
Click outside the boilerplate text object.
Double-click the boilerplate text object (or right-click the object, and choose Property Inspector).
In the Property Inspector, under the Web Settings node, set the Contains HTML Tags property to Yes.

Example 1

This example shows a boilerplate text object tagged as a hyperlink.

Create the following as a boilerplate text object:

<a href=http://your_webserver/reports/my_report.html><img src=oracle.gif> </a>

Double-click the boilerplate object to display the Property Inspector. Under the Web Settings node, set the Contains HTML tags property to Yes.

Example 2

In a boilerplate text object, you can type the following Java Applet, called NervousText.class, which takes the object width and height as parameters:

<base href=http://cagney.uk.oracle.com/java/NervousText/>
<applet code="NervousText.class" width=&ObjectWidth height=&<ObjectHeight>>
<param name=text value="&deptno">
</applet>

This is output to HTML as:

<base href=http://cagney.uk.oracle.com/java/NervousText/>
<applet code="NervousText.class" width=84 height=72>
<param name=text value="10">
</applet>

3.6.7.1.15 Linking an HTML object to a file

Note:

This procedure is for HTML output only.

To link an HTML object to a file:

In the Paper Design view or Paper Layout view, click the Link File tool in the tool palette.
Click and drag a rectangle.
Double-click the link file object to display the Property Inspector.
Under the Link File Boilerplate node:
- set the Source File Format property to Text.
- set the Source Filename property to the name of the file containing the HTML tags.
Under the Web Settings node, set the Contains HTML Tags property to Yes.

3.6.7.1.16 Selecting HTML tags from the database

Note:

This procedure is for HTML output only.

To select HTML tags from the database:

Create a query, with a SELECT statement that selects a column containing HTML tags or the names of files that contain HTML tags.
In the Data Model view, double-click the HTML tags column to display the Property Inspector.
If the column contains the names of files that contain the HTML tags, rather than the HTML tags themselves:
- Under the Column node, set the Read from File property to Yes.
- Set the File Format property to Text.
In the Paper Layout view, create a field object that references the column.
Double-click the field object to display the Property Inspector.
In the Property Inspector, under the Web Settings node, set the Contains HTML Tags property to Yes.

3.6.7.2 Using PL/SQL

This section provides procedures for the following tasks that you may perform as you work with PL/SQL to add Web links to paper-based reports:

3.6.7.2.1 Creating an HTML document header using PL/SQL

Note:

This procedure is for HTML output only. A before report escape should be set in a trigger that fires before the report starts formatting, such as the Before Report trigger.

To add items to the header page of your HTML document using PL/SQL instead of the Property Inspector:

In the Object Navigator, expand the Report Triggers node.
Double-click the PL/SQL icon next to the Before Report trigger.
In the PL/SQL Editor, include SRW.SET_BEFORE_REPORT_HTML to define the PL/SQL for the format trigger.

3.6.7.2.2 Creating an HTML document footer using PL/SQL

Note:

This procedure is for HTML output only. An after report escape should be set in a trigger that fires before the report is done formatting, such as the Before Report trigger.

To add items to the footer page of your HTML document using PL/SQL instead of the Property Inspector:

In the Object Navigator, expand the Report Triggers node.
Double-click the PL/SQL icon next to the Before Report trigger.
In the PL/SQL Editor, include SRW.SET_AFTER_REPORT_HTML to define the PL/SQL for the format trigger.

3.6.7.2.3 Creating an HTML page header using PL/SQL

Note:

This procedure is for HTML output only. If you want the escape to apply to every page, set it in a trigger that fires before the report begins formatting, such as the Before Report trigger. If you want the escape to apply to a single page, set it in a Format trigger

To add a page header to every page of your HTML document using PL/SQL instead of the Property Inspector:

In the Object Navigator, expand the Report Triggers node.
Double-click the PL/SQL icon next to the Before Report trigger.
In the PL/SQL Editor, include SRW.SET_BEFORE_PAGE_HTML to define the PL/SQL for the format trigger.

To add a page header to a single page of your HTML document using PL/SQL:

In the Object Navigator, expand the Paper Layout node, then expand the node that contains the object that should fire the trigger when formatted.
Double-click the PL/SQL icon next to the object that should fire the trigger when formatted.
In the PL/SQL Editor, include SRW.SET_BEFORE_PAGE_HTML to define the PL/SQL for the format trigger.

3.6.7.2.4 Creating an HTML page footer using PL/SQL

Note:

To add a page footer to every page of your HTML document using PL/SQL instead of the Property Inspector:

In the Object Navigator, expand the Report Triggers node.
Double-click the PL/SQL icon next to the Before Report trigger.
In the PL/SQL Editor, include SRW.SET_AFTER_PAGE_HTML to define the PL/SQL for the format trigger.

To add a page footer to a single page of your HTML document using PL/SQL:

In the Object Navigator, expand the Paper Layout node, then expand the node that contains the object that should fire the trigger when formatted.
Double-click the PL/SQL icon next to the object that should fire the trigger when formatted.
In the PL/SQL Editor, include SRW.SET_AFTER_PAGE_HTML to define the PL/SQL for the format trigger.

3.6.7.2.5 Creating an HTML Parameter Form header using PL/SQL

Note:

This procedure is for HTML output only. A before form escape should be set in a trigger that fires before the Parameter Form starts formatting, such as the Before Parameter Form trigger.

To add items to the top of the HTML Parameter Form using PL/SQL instead of the Property Inspector:

In the Object Navigator, expand the Report Triggers node.
Double-click the PL/SQL icon next to the Before Parameter Form trigger.
In the PL/SQL Editor, include SRW.SET_BEFORE_FORM_HTML to define the PL/SQL for the format trigger.

3.6.7.2.6 Creating an HTML Parameter Form footer using PL/SQL

Note:

This procedure is for HTML output only. A before form escape should be set in a trigger that fires before the Parameter Form starts formatting, such as the Before Parameter Form trigger.

To add items to the bottom of the HTML Parameter Form using PL/SQL instead of the Property Inspector:

In the Object Navigator, expand the Report Triggers node.
Double-click the PL/SQL icon next to the Before Parameter Form trigger.
In the PL/SQL Editor, include SRW.SET_AFTER_FORM_HTML to define the PL/SQL for the format trigger.

3.6.7.2.7 Creating a hyperlink using PL/SQL

To add a hyperlink to your report using PL/SQL instead of the Property Inspector:

In the Object Navigator, expand the Paper Layout node, then expand the node that contains the object that will be the source of the Web link.

Note:
If you are defining a template, you can select objects in the Margin. Objects in the body are unknown until the template is applied to a report
Double-click the PL/SQL icon next to the object that will be the source of the Web link.
In the PL/SQL Editor, include SRW.SET_HYPERLINK to define the PL/SQL for the format trigger.
To apply additional HTML attributes to the hyperlink, use SRW.SET_HYPERLINK_ATTRS.

Next step

Section 3.6.7.2.8, "Creating a hyperlink destination using PL/SQL"

3.6.7.2.8 Creating a hyperlink destination using PL/SQL

To add a hyperlink destination to your report using PL/SQL instead of the Property Inspector:

In the Object Navigator, expand the Paper Layout node, then expand the node that contains the object that will be the destination of a Web link.

Note:
If you are defining a template, you can select objects in the Margin. Objects in the body are unknown until the template is applied to a report.
Double-click the PL/SQL icon next to the object that will be the destination of a Web link.
In the PL/SQL Editor, include SRW.SET_LINKTAG to define the PL/SQL for the format trigger.

Next step

Section 3.6.7.2.7, "Creating a hyperlink using PL/SQL"

3.6.7.2.9 Creating an application command line link using PL/SQL

Note:

This procedure is for PDF output only.

To associate a command with an object in your report using PL/SQL instead of the Property Inspector:

In the Object Navigator, expand the Paper Layout node, then expand the node that contains the object with which you want to associate an application command line link.

Note:
If you are defining a template, you can select objects in the Margin. Objects in the body are unknown until the template is applied to a report.
Double-click the PL/SQL icon next to the object with which you want to associate an application command line link.
In the PL/SQL Editor, include SRW.SET_PDF_ACTION to define the PL/SQL for the format trigger.

Restrictions

An object that is associated with an application command line link cannot also be the source of a Web link (a hyperlink).

3.6.7.2.10 Creating a bookmark using PL/SQL

To add a bookmark string in the bookmark area of your report using PL/SQL:

In the Object Navigator, expand the Paper Layout node, then expand the node that contains the object with which you want to associate a bookmark (typically, the object is a repeating frame or frame that encloses the relevant section of the report).

Note:
If you are defining a template, you can select objects in the Margin. Objects in the body are unknown until the template is applied to a report.
Double-click the PL/SQL icon next to the object with which you want to associate a bookmark.
In the PL/SQL Editor, include SRW.SET_BOOKMARK to define the PL/SQL for the format trigger.

3.6.7.2.11 Adding navigation controls for HTML page-streamed output using PL/SQL

Note:

This procedure is for HTML output only.

To add navigation controls for HTML page-streamed output using PL/SQL instead of the Property Inspector:

in a Before Report trigger, use the SRW.SET_PAGE_NAVIGATION_HTML PL/SQL procedure

3.7 Run and Dispatch a Report

This section provides procedures for the following tasks that you may perform as you run and dispatch a report:

3.7.1 Running and dispatching a report from the user interface

To run and dispatch a report:

In the Object Navigator, click the report name, or choose File > Open to open it.
If you are not already connected, connect to a database.
Click the Run Paper Layout button in the toolbar.

If the Runtime Parameter Form displays, specify settings for the desired output:

Note:

When you create the report, you must use the Parameter Form Builder (choose Tools > Parameter Form Builder) to select the system and user parameters you want to appear in the Runtime Parameter Form.

To preview output in the Paper Design view using screen fonts: set Destination Type to Screen.
To preview output in the Paper Design view using screen fonts and size the fonts using the printer font metrics: set Destination Type to Preview.

To send output to a file: set Destination Type to File, Destination Name to the path and filename, Destination Format to the output format for the report (e.g., PDF, HTML, HTMLCSS, RTF, XML, or DELIMITED for bitmapped reports, or the character-mode printer definition file (.prt file) for character-mode reports).

Note:

You can also automatically display report output in your Web browser (see Section 3.7.15.4, "Displaying report output in your Web browser"), then save the temporary file as an HTML or PDF file.

Note:

When you view the report output in Microsoft Word, you must choose View > Page Layout to see all the graphics and objects in your report.

To send output to a printer: set Destination Type to Printer, Destination Name to the printer driver name.
To send output via e-mail using your Internet standard protocol SMTP e-mail application: set Destination Type to Mail, leave Destination Name blank. In the Mail dialog box, specify the recipients and subject line for your e-mail report.

Note:
The configuration file rwbuilder.conf must include the pluginParam mailServer with the outgoing mail server name. This applies in both the Windows NT and Solaris environments.

Optionally, display output in your Web browser (see Section 3.7.15.4, "Displaying report output in your Web browser").
In the Runtime Parameter Form, click the Run Report button in the toolbar.
If a dialog box appears, enter required information.

Usage notes

If you want the orientation of the report to be landscape, you must change the orientation for the report (see Section 3.7.13, "Changing orientation"). However, if your printer does not support the specified page size, you may get truncated results. To enable character-mode printers to support landscape (or other page sizes), you can set printer escape sequences in the printer definition files. Refer to your printer documentation for the escape sequences to use.

3.7.2 Running a report from the command line

To run a runfile (.rep) or a report definition file (.rdf) from the command line:

Type %ORACLE_HOME%\BIN\rwrun, followed by the report name and desired arguments.
If you sent the output to a file (DESTYPE=FILE DESNAME=filename DESFORMAT=fileformat), open or print the file to verify the output.

3.7.3 Running a report using a command file

To run a report using a command file:

Create a text file using a word processor of your choice.
In the file, type %ORACLE_HOME%\BIN\rwrun, followed by the report name and desired arguments.
Name the file filename.bat.
To run the report, type filename.bat on the command line.
If you sent the output to a file (DESTYPE=FILE DESNAME=filename DESFORMAT=HTML), open or print the file to verify the output.

3.7.4 Running a report to a remote Reports Server

To run a report to a remote Reports Server:

Type %ORACLE_HOME%\bin\rwclient, followed by the report name, server name, and desired arguments.

For additional information, see the Oracle Application Server Reports Services Publishing Reports to the Web manual, available on the Oracle Technology Network Oracle Reports Documentation page (http://otn.oracle.com/docs/products/reports/content.html).

3.7.5 Generating HTML or HTMLCSS output

To generate HTML or HTMLCSS output:

In the Object Navigator, select or open the report, then click the Run Paper Layout button in the toolbar to run the report.

In the Paper Design view, specify the scope of output you want to display:

To display only the current page of the report, choose File > Preview Format > Current Page.

To display all pages, choose File > Preview Format > All Pages.

Note:

If you choose to show all pages of your report output, and you have HTML page streaming enabled, the first page will display in your Web browser, and you will be able to quickly navigate to any other page in the rest of the report.

To preview your HTML or HTMLCSS report output in a Web browser, choose File > Preview Format > Paginated HTML or Paginated HTMLCSS (to format with style sheet extensions).

To preview your report output with Web layout in a Web browser, choose Program > Run Web Layout.

Note:

If Netscape 7.0 is your default browser, the browser may not display. You can work around this bug by making a copy of the Netscape 7.0 executable, naming it netscape.exe; with this name, the browser will display as expected.

To save your report output as an HTML or HTMLCSS file, choose File > Generate to File.

3.7.6 Generating PDF output

To generate PDF output:

In the Object Navigator, select or open the report, then click the Run Paper Layout button in the toolbar to run the report.
In the Paper Design view, specify the scope of output you want to display:
- To display only the current page of the report, choose File > Preview Format > Current Page.
- To display all pages, choose File > Preview Format > All Pages.
To preview your PDF report output in your Web browser, choose File > Preview Format > PDF.

To preview your report output with Web layout in a Web browser, choose Program > Run Web Layout.

Note:

To save your report output as a PDF file, choose File > Generate to File > PDF.

3.7.7 Generating XML output

In any Reports Builder-generated XML file, your output mimics the data model; that is, it is structured by groups and columns. Follow the steps below to:

produce an XML output file from your current report
preview the contents of an XML file in a Web browser in conjunction with command line invocation:
change the XML properties that control XML output for the entire report
change the XML properties that control XML output for a column or group

To produce an XML output file from your current report:

Optionally, in the Object Navigator, select or open the report, then click the Run Paper Layout button in the toolbar to run the report. Currently, any report style can be generated to XML output, but with limited support for matrix-style reports.
Optionally, in the Paper Design view, choose File > Preview Format > XML to preview XML data in your default XML viewer (e.g., your Web browser).

Tip:
An .XML extension is required by XML-supporting browsers to recognize the format as XML and display it accordingly.

Click the report in the Object Navigator, and choose File > Generate to File > XML to save the XML output file to the directory of your choice.

Notes:

Reports Builder creates "well-formed" XML output. This means that there is an XML Declaration present in the prolog of the XML document, but not necessarily a DTD (document type definition). If you wish to create "valid" XML, you must add the DTD in the XML Prolog Value property field at the report level.
If your report includes a graph, the graph information will not be saved in the XML file (XML is text-based output, which means images are not included).

To preview the contents of an XML file in a Web browser in conjunction with command line invocation:

On the command line, execute your report with the arguments DESFORMAT=XML, DESTYPE=FILE, and DESNAME=filename.XML.
Open an XML-supporting Web browser.
Choose File > Open to navigate to the XML file that you just generated. The XML file will appear in the browser window.
If you make changes to the XML output in your report and regenerate the XML file with the same name, click the Reload button in your browser to view the updated XML output.

To change the XML properties of the entire report:

In the Object Navigator double-click the properties icon next to the report name to display the Property Inspector.
Under XML Settings, set the XML properties as required for this report.

To change the XML properties of a column or group:

In the Data Model view, double-click the column or the title bar of the group to display the Property Inspector.
Under XML Settings, set the XML properties as required.

3.7.8 Generating RTF output

To generate RTF output:

In the Object Navigator, select or open the report, then click the Run Paper Layout button in the toolbar to run the report.
To preview your report output in a Microsoft Word document, choose File > Preview Format > RTF.
To save your report output as an RTF file, choose File > Generate to File > RTF.
In the Save dialog box, specify a location and file name. Click Save.

3.7.9 Generating delimited output

To generate delimited output:

In the Object Navigator, select or open the report, then click the Run Paper Layout button in the toolbar to run the report.
To preview your delimited report output in a spreadsheet, choose File > Preview Format > Delimited or File > Preview Format > DelimitedData (for large volume reports).
To save your delimited report output to a file, choose File > Generate to File > Delimited or File > Generate to File > DelimitedData (for large volume reports).
In the Delimited Output dialog box or DelimitedData Output dialog box, choose from the drop-down list or type the delimiter that will separate the cells in your report output.
If you want to use formatting options, choose from the drop-down list or type a Date Format Mask or a Number Format Mask.
If you want to use a cell wrapper, choose from the drop-down list or type the cell wrapper that will be placed around the cell data in your report output.
Click OK.
In the Save dialog box, specify a location and file name. Click Save.

3.7.10 Generating text output

To generate text output:

To generate pure text output, which can be read by many different applications, set the Initial Value property of the MODE system parameter to Character (see Section 3.11.1, "Using a pre-defined system parameter").

Note:
If MODE is set to Bitmap (the default), the result is PostScript output, which can be read and rendered only by PostScript-compatible applications (such as a PostScript printer).
In the Object Navigator, select or open the report, then click the Run Paper Layout button in the toolbar to run the report.
To preview your report output in a text viewer, choose File > Preview Format > Text.
To save your report output as a text file, choose File > Generate to File > Text.
In the Save dialog box, specify a location and file name. Click Save.

3.7.11 Distributing a report to multiple destinations

To distribute a report to multiple destinations, you first define the distribution, then enable the distribution.

You can define the distribution for a report in any of the following ways:

using XML, as described in the chapter "Creating Advanced Distributions" in the Oracle Application Server Reports Services Publishing Reports to the Web manual, available on the Oracle Technology Network Oracle Reports Documentation page (http://otn.oracle.com/docs/products/reports/content.html).
in the Distribution dialog box to define a distribution list for the entire report and/or any report section.

Note:
To display the Distribution dialog box: In the report or section Property Inspector, under the Report node, double-click the Distribution property value field.
on the command line, including the DESTINATION keyword to specify a DST file or XML file.

Note:
This method is supported for backward compatibility; the preferred and recommended method of distributing reports is via XML or the Distribution dialog box.
To enable the distribution of a report, you can do either of the following:
- Specify DISTRIBUTE=YES on the command line.
- Choose File > Distribute.

You can also trace the report distribution to verify a successful distribution.

3.7.12 Deploying a report

To deploy a report so that end users can view it, refer to the section "Deploying Your Reports" in the chapter "Running Report Requests" in the Oracle Application Server Reports Services Publishing Reports to the Web manual, available on the Oracle Technology Network Oracle Reports Documentation page (http://otn.oracle.com/docs/products/reports/content.html). This section describes how to deploy a report with a paper layout (i.e., a REP, RDF, XML, or JSP report) and how to deploy a report with a Web layout (i.e., a JSP report).

3.7.13 Changing orientation

To change the orientation (portrait or landscape) of a report section:

In the Object Navigator, under the Paper Layout node, double-click the Header Section, Main Section, or Trailer Section properties icon for the pertinent report section (Header, Main, or Trailer) to display the Property Inspector (by default, a report is defined in the Main Section).
In the Property Inspector, under the Section node:
- set the width and height properties as desired (e.g., 11 (or 15) width x 8.5 height for landscape or 8.5 width x 11 (or 15) height for portrait). Make sure that you subtract the margin depths (e.g., for an 8.5 x 11 page with top, bottom, left, and right margins of .25, set Width to 8 and Height to 10.5).
- set the Orientation property to the desired value.
If you want to be able to modify the orientation at runtime, choose Tools > Parameter Form Builder to display the Parameter Form Builder:
- Click the ORIENTATION system parameter.
- Click OK to display the Paper Parameter Form view.
- Double-click the Orientation value field (PF_ORIENTATION) to display the Property Inspector.
- Under the Parameter node, set the Initial Value property to Portrait or Landscape.
- Click the Run Paper Layout button in the toolbar.
- From the Orientation drop-down list, change the orientation if desired.

3.7.14 Suppressing the Parameter Form

If the report is run from another product, you may not want the Parameter Form to appear.

To suppress the Parameter Form and have the report assign a default value to DESTYPE, do either of the following:

Create a list parameter to explicitly pass the values of DESTYPE and PARAMFORM.
In the Preferences dialog box (Edit > Preferences), on the Runtime Settings page, clear the Parameter Form check box to specify a value of No for the PARAMFORM keyword.

3.7.15 Viewing report output

This section provides procedures for various ways to view report output:

3.7.15.1 Viewing the printable area

The printable area is the physical region in which your printer can print on a physical page. Most printers usually cannot print up to the edge of a page, leaving a blank "unprintable" area.

To view the printable area of your report:

Choose File > Page Setup to specify your page settings.
In the Paper Layout view, click the Edit Margin button in the toolbar.

The printable area is displayed as the area within the dotted lines in the margin area.
Choose View > Zoom > Zoom Out to see more.

3.7.15.2 Displaying report output in the Paper Design view

The Paper Design view is displayed whenever you run a report.

To run a report from the Object Navigator or any editor:

Click the Run Paper Layout button or choose Program > Run Paper Layout.

To run a report from the Report Wizard:

Click Finish.

You can also display the Paper Design view in these ways:

Click the Paper Design View button in the toolbar.
Choose View > Change View > Paper Design.
In the Object Navigator, double-click the view icon next to the Paper Design node.

In the Paper Design view, manipulate the output as desired, and use any of the following features:

scrolling and paging (see Section 3.7.15.6, "Scrolling and paging")
splitting horizontally and vertically (see Section 3.7.15.7, "Splitting the viewing region")
magnifying or reducing the output (see Section 3.7.15.8, "Magnifying or reducing the output")

3.7.15.3 Displaying report output in the Previewer

To display your report output in the Previewer:

In the Paper Design view, choose File > Print Preview.
In the Previewer, use any of the following features:
- scrolling and paging (see Section 3.7.15.6, "Scrolling and paging")
- splitting horizontally and vertically (see Section 3.7.15.7, "Splitting the viewing region")
- magnifying or reducing the output (see Section 3.7.15.8, "Magnifying or reducing the output")

3.7.15.4 Displaying report output in your Web browser

To display report output in your Web browser:

In the Object Navigator, select or open the report, then click the Run Paper Layout button in the toolbar to run the report.

In the Paper Design view, specify the scope of output you want to display:

To display only the current page of the report, choose File > Preview Format > Current Page.

To display all pages, choose File > Preview Format > All Pages.

Note:

To preview your report output in a Web browser, choose File > Preview Format > Paginated HTML, File > Preview Format > Paginated HTMLCSS (to format with style sheet extensions), File > Preview Format > PDF, or File > Preview Format > XML (if you have an XML-supporting browser set as your default XML viewer).

To preview your report output with Web layout in a Web browser, choose Program > Run Web Layout.

Note:

To save your report output as an HTML, HTMLCSS, PDF, or XML file, choose File > Generate to File.

3.7.15.5 Displaying individual pages of HTML report output

To paginate the HTML output for a report:

Specify navigation controls for HTML page-streamed output using either of the following methods:
- in the Report Property Inspector (see Section 3.6.7.1.12, "Adding navigation controls for HTML page-streamed output using the Property Inspector")
- using PL/SQL (see Section 3.6.7.2.11, "Adding navigation controls for HTML page-streamed output using PL/SQL")
Enable HTML page streaming by running your report from the command line, specifying PAGESTREAM=YES.

3.7.15.6 Scrolling and paging

To move around pages of the report using the scroll bars and paging buttons:

Use the mouse cursor to drag the horizontal and vertical scroll bars to move around the current page.
Click the First Page, Previous Page, Next Page, and Last Page buttons in the toolbar to display the indicated pages of the report.
To display a specific page, type the page number in the Page field.

3.7.15.7 Splitting the viewing region

To create two views of the report output by splitting the display horizontally and/or vertically:

In the Data Model view, Paper Layout view, Paper Design view, or Paper Parameter Form view, drag the black bar below the vertical scroll bar (to split horizontally), or to the right of the horizontal scroll bar (to split vertically).
In the Previewer, drag the grey bar above the vertical scroll bar (to split horizontally), or to the left of the horizontal scroll bar (to split vertically)

You can scroll and page within each view to move the contents of that view while the other view remains unchanged.

3.7.15.8 Magnifying or reducing the output

To magnify a hard-to-see portion of your report:

In the Data Model view, Paper Layout view, Paper Design view, or Paper Parameter Form view, click the Magnify tool in the tool palette (or, choose View > Zoom > Zoom In).
In the Previewer, click the Zoom In button in the toolbar.

To reduce the image to get a sense of your report's overall layout:

In the Data Model view, Paper Layout view, Paper Design view, or Paper Parameter Form view, choose View > Zoom > Zoom Out or Normal Size.
To magnify the output displayed in the viewing region,
In the Previewer, click the Zoom Out button in the toolbar.

3.7.16 Printing a report

This section provides procedures for the following tasks that you may perform to print a report:

3.7.16.1 Printing a report from the Paper Design or Paper Layout view

To print a report from the Paper Design or Paper Layout view:

In the Paper Design view or Paper Layout view choose File > Print.
In the Print dialog box, type the number of pages and copies you want to print.
Click OK.

3.7.16.2 Printing a report from the Previewer

To print a report from the Previewer:

In the Previewer, click the Page Setup button in the toolbar to verify your printer setup.
Click the Print button in the toolbar.
In the Print dialog box, specify the pages and copies you want to print.

If the report has a Destination Type of Screen, a warning appears that you should run the report with a Destination Type of Preview before printing. Preview creates PostScript output, which is typically more desirable for printing bit-mapped reports.
Click OK.

3.7.16.3 Printing a report from your Web browser

Note:

Formatting with style sheet extensions paginates the HTML document in the same way the report is paginated; formatting without style sheet extensions generates HTML output that does not break between the pages of the report.

To print a report from your Web browser:

If your report is formatted with HTML style sheet extensions (HTMLCSS), set up your browser to print the HTML document in the same way the report is paginated:
- In your browser, choose File > Page Setup and modify the margin settings as desired.
- In the Object Navigator, under the Paper Layout node, double-click the Header Section, Main Section, or Trailer Section properties icon for the pertinent report section (Header, Main, or Trailer) to display the Property Inspector (by default, a report is defined in the Main Section).
- In the Property Inspector, under the Section node:
Display your report output in your Web browser (see Section 3.7.15.4, "Displaying report output in your Web browser").
Choose File > Print.

3.7.16.4 Printing a report on a pre-printed form

See the example report in Chapter 31, "Building a Report Using a Pre-Printed Form".

3.7.16.5 Switching the printer tray

To switch the printer tray:

Choose File > Page Setup and in the Source drop-down list, note the names of the printer trays defined for your printer.
Use SRW.SET_PRINTER_TRAY to define the desired trigger:
- To change the printer tray before the report begins formatting, create a Before Report trigger (see Section 3.13.3.5, "Creating a report trigger").
  
  Note:
  The printer tray specified in the Page Setup dialog box is used as the default printer tray; create a Before Report trigger to change this default.
- To change the printer tray between pages of the report, create a Between Pages trigger (see Section 3.13.3.5, "Creating a report trigger").
- To change the printer tray for a specific page on which a particular object prints, create a format trigger for that object (see Section 3.13.4.1, "Creating or editing a format trigger").

3.7.16.6 Printing a report on UNIX

Printing on UNIX requires some setup and configuration to create the proper printing environment. For detailed information about printing your reports on UNIX, refer to the appendix titled "Printing on UNIX with Oracle Reports" in the Oracle Application Server Reports Services Publishing Reports to the Web manual, available on the Oracle Technology Network Oracle Reports Documentation page (http://otn.oracle.com/docs/products/reports/content.html).

Oracle Reports 10g (9.0.4) provides the following enhancements for printing reports on UNIX:

The REPORTS_DEFAULT_DISPLAY environment variable, which provides for the removal of dependency on the DISPLAY environment variable. Prior releases of Oracle Reports required the DISPLAY environment variable to be set to run and print reports on UNIX.
The PostScript printer driver screenprinter.ppd, which provides for the removal of dependency on having a valid printer available for Reports Runtime on UNIX. This driver provides surface resolution for images and specifies font information. Prior releases of Oracle Reports required a printer to resolve fonts to run and print reports on UNIX.

Limitations

If your report includes an Oracle6i Graphics graph, a printer is required to generate the graph. However, TK_PRINT_STATUS=echo is set in g90runm.sh. Thus, to run a report with an Oracle6i Graphics graph without a valid printer, set the PRINTER environment variable to any value.

Note:

Oracle6i Graphics, or Graphics Builder, is obsolete in Oracle Reports 10g; instead, use the Graph Wizard, which produces graphs that are automatically translated into JSP tags to enable you to add graphics to Web reports (for additional information, see the Reports Builder online help topic "Displaying Oracle6i Graphics charts in Oracle Reports 10g").

3.7.17 E-mailing a report

To e-mail a report from Reports Builder:

Log onto your Internet Standard Protocol SMTP mail application.
Select or open the report.
If you are not already connected, connect to a database.
Choose File > Mail.
In the Mail dialog box, specify the recipients and subject line for your e-mail report.
Click OK.

To e-mail a report in batch mode:

Note:

When using BATCH=yes, Reports Builder will use a blank subject line and cc list. To specify the subject line and cc lists, you must run with BATCH=no (interactively).

Log onto your Internet Standard Protocol SMTP mail application.

Run the report from the command line, specifying BATCH=YES, DESTYPE=MAIL, and DESNAME=recipient1, recipient2, recipient3, etc.

Usage notes

The configuration file rwbuilder.conf must include the pluginParam mailServer with the outgoing mail server name. This applies in both the Windows NT and Solaris environments.

3.8 Work with the Data Model

This section provides procedures for the following tasks that you may perform as you work with the data model of your report:

3.8.1 Creating a query

To create a query, you can use any of the following tools:

Report Wizard (single-query reports only)
Data Wizard
SQL Query tool (to create a query that selects data from an Oracle relational database)
XML Query tool (to access an XML data file, if you have the DTD file)
JDBC Query tool (to access any JDBC-enabled data source)
Text Query tool (to create a query that selects data from a text pluggable data source)
Express Server Query tool (to create a query that selects data from an Express Server)
OLAP Query tool (to create a query that selects from multidimensional Oracle OLAP (on-line analytical processing) data stored in an Oracle database)
Ref Cursor Query tool (to use ref cursors)

3.8.1.1 Creating a query: Report Wizard

To create a query using the Report Wizard:

On the Data page of the Report Wizard, enter the query statements for the type of data source previously selected. For example, if you selected the SQL Query data source, you can enter your SQL query in any of the following ways:
- Type the SELECT statement in the Data Source definition field.
- Click Query Builder for a graphical method of creating a query without a knowledge of SQL.
- Click Import Query to use a query that has been written by someone else or to use the text editor of your choice.

3.8.1.2 Creating a query: Data Wizard

To create a query using the Data Wizard:

In the Data Model view, choose Insert > Query to display the Data Wizard.
Follow the wizard to create the first query for the data model.
Repeat Steps 1 and 2 for each query you want to create.
Modify the resulting data model in the Data Model view.
To re-enter the Data Wizard, do either of the following:
- Right-click, and choose Data Wizard.
- Click the query, then choose Edit > Settings.
  
  Note:
  The Data Wizard does not support creating links between queries. To define parent/child query relationships, you can create a data link manually.

3.8.1.3 Creating a query: SQL Query tool

To create a SQL query:

In the Data Model view, single-click the SQL Query tool in the tool palette.
Click in the main area (canvas region) of the window.
In the SQL Query Statement dialog box, define a SELECT statement for the query:
- To use Query Builder for an easy graphical method of creating a query without a knowledge of SQL, click Query Builder.
- To import a query from a file, click Import SQL Query.
- To enter the SELECT statement yourself, type it in the SQL Query Statement area.
  
  Tip:
  Select the columns in the order you want them to appear in the report output.
Click OK.
Refine the query using either of the following methods:
- Right-click the query object, then choose Property Inspector to set desired properties
- Right-click the query object, then choose Data Wizard to specify which fields to display, group fields, and any totals.
Repeat Steps 1 through 5 for each query you want to create.

Note:
If you define multiple queries in the Data Model view of your report, the Data page does not appear when you invoke the Report Wizard to default the layout.

3.8.1.4 Creating a query: XML Query tool

To create an XML query:

In the Data Model view, single-click the XML Query tool in the tool palette.
Click in the main area (canvas region) of the window.
In the Define XML Query dialog box, define the XML query based on the fields defined in the data definition file (DTD) selected. The query is run against the data in the XML data file. If an XSL file is specified, it translates the XML data file before running the query.
Click OK.

3.8.1.5 Creating a query: JDBC Query tool

To create a JDBC query:

In the Data Model view, single-click the JDBC Query tool in the tool palette.
Click in the main area (canvas region) of the window.
In the JDBC Query dialog box, define the JDBC query or procedure and the connection parameters for the data source. Click Help for assistance.
Click OK.

3.8.1.6 Creating a query: Text Query tool

To create a Text query:

In the Data Model view, single-click the Text Query tool in the tool palette.
Click in the main area (canvas region) of the window.
In the Define Text Query dialog box, specify the data definition and data source for the text query. Click Help for assistance.
Click OK.

3.8.1.7 Creating a query: Express Server Query tool

To create an Express Server query:

In the Data Model view, single-click the Express Server Query tool in the tool palette.
Click in the main area (canvas region) of the window.
In the dialog box, specify the definition for the Express Server query. Click Help for assistance.
Click OK.

3.8.1.8 Creating a query: OLAP Query tool

To create an OLAP query:

In the Data Model view, single-click in the tool palette.
Click in the main area (canvas region) of the window.
In the dialog box, specify the definition for the OLAP query. Click Help for assistance.
Click OK.

3.8.1.9 Creating a query: Ref Cursor Query tool

To create a ref cursor query:

Create a package that defines a ref cursor type in one of the following ways:
- Create a local program unit (see Section 3.13.3.1, "Creating a local program unit")
- Create an external PL/SQL library (see Section 3.13.5.1, "Creating an external PL/SQL library")
- Create a stored program unit (see Section 3.13.3.2, "Creating a stored program unit")
If the package created in Step 1 is an external PL/SQL library, you must attach it to the report before referencing it.
In the Data Model view, single-click the Ref Cursor Query tool in the tool palette.
Click in the main area (canvas region) of the window.

In the PL/SQL Editor, type the PL/SQL for a function that opens a cursor and returns a cursor variable of the ref cursor type you defined in the package. For example:

empCur rcPackage.empCurType;

BEGIN
  OPEN empCur FOR SELECT * FROM emp;
  RETURN empCur;
END; 

/* Note, rcPackage is a local program unit defined as: */
PACKAGE rcPackage IS
  TYPE empCurType IS REF CURSOR RETURN emp%ROWTYPE;
END;

Refine the query as desired:
- Click the ref cursor query object, then choose Tools > Property Inspector to modify properties.
- Click the query object, then choose Tools > Data Wizard to specify which fields to display, group fields, and any totals.
Repeat Steps 1 through 6 for each ref cursor query you want to create.

3.8.2 Modifying a query

To modify a SQL query statement or the columns displayed in your report:

In the Data Model view, click the query object, then right-click and choose Data Wizard.
To change the SELECT statement, click the Data tab.
To change the break groups, click the Groups tab.
To add or change summary columns for totals, click the Totals tab.
To change which selected database columns are displayed in your report, choose Tools > Report Wizard, then click the Fields tab.

To modify the properties of a query:

In the Data Model view, click the query object, then right-click and choose Property Inspector.
In the Property Inspector, modify the properties as desired.

3.8.3 Using Query Builder

You can use Query Builder to define almost any query that you would build using a SQL SELECT statement. Query Builder automatically generates the appropriate SELECT FROM [table.column] clause based on columns displayed in the Query Builder workspace.

See the How To section of the Reports Builder online help for topics about using Query Builder.

3.8.4 Creating a break group

You can define break groups in the Report Wizard or create them manually.

To create a break group manually:

In the Data Model view, drag the group that contains the column at which you want to break your report down from the query object about 2 inches.
Select the column that you want to use to divide your report (e.g. if you want to group a list of employee data by department number, select the department number column) and drag it out of and above the group to create a new group. This is the break column.
Double-click the title bar of the new group object to display its Property Inspector, where you can set properties for the break group.

3.8.5 Creating a matrix (cross-product) group

You can define matrix groups in the Data Wizard or create them manually.To create a matrix (cross-product) group using the Data Wizard:

In the Data Model view, right-click the query object, then choose Data Wizard.
On the Query page, select the Matrix query check box.
Follow the wizard to select the columns for the matrix group(s), rows, columns, and cells.

To create a matrix (cross-product) group manually:
In the Data Model view, drag the title bar of the group object down.
Drag the "row" column and "column" column out of the group object into the space to create two new groups.
Single-click the Cross Product tool in the tool palette.
Drag a box around the groups you want to include in the matrix (cross-product) group.
If the cell group is a separate query from the matrix (cross-product) groups, create the appropriate links between the columns in the matrix groups and the cell group.

3.8.6 Creating a data link

To create a data link:

In the Data Model view, single-click the Data Link tool in the tool palette. A link is always drawn from the parent group to the child query.
Create the link:

Create a Group to Group Link: To create a link between one query's group and another query's group, which is useful when you want the child query to know about the parent's data, click the parent group (avoiding the columns in the group) and drag a link to the child group.

Create a Column to Column Link: To create a link between columns, click a column of the parent query and drag a link to a column of the child query.
Double-click the new link object to display the Property Inspector, and set the desired properties.

3.8.7 Creating or editing a formula column

To create or edit a formula column:

In the Data Model view, single-click the Formula Column in the tool palette, then:
- To create a column within a group, click in the group at the position you want the column placed in the hierarchy.
- To create a report-level column, click in an open area of the canvas region.
Double-click the formula column object to display the Property Inspector.
Under the Placeholder/Formula node, double click the PL/SQL Formula property field.
In the PL/SQL Editor, define the PL/SQL for the formula for example SAL * 0.07.

Example: Referencing a PL/SQL function in formulas

Suppose that you have a report with the following groups and columns:

Groups  Columns         Summary
---------------------------------------
RGN     REGION
        RGNSUMSAL       SUM(DEPTSUMSAL)
        COSTOFLIVING
 		
DEPT    DNAME
        DEPTNO
        DEPTSUMSAL      SUM(EMP.SAL)
 
JOB     JOB
        HEADCOUNT       COUNT(EMP.EMPNO)
 
EMP     ENAME
        EMPNO
        SAL
        COMM

Given these groups and columns, you might create multiple formulas that apply the cost of living factor (COSTOFLIVING) to salaries. To avoid duplication of effort, you could create the following PL/SQL function and reference it from the formulas:

function CompSal(salary number) return number is
begin
  return (salary*CostofLiving);
end;

Following are some examples of how you might reference the PL/SQL function in formulas:

CompSal(:RGNSUMSAL)

CompSal(:SAL) + COMM

3.8.8 Creating a summary column

To create a summary column (for totals or subtotals) using the Data Wizard:

In the Data Model view, click the query that contains the column you want to total.
Choose Tools > Data Wizard.

On the Totals page, follow the wizard to add the desired summary to your report.

Note:

For group reports, the Report Wizard and Data Wizard create n summary fields in the data model for each summary column you define: one at each group level above the column being summarized, and one at the report level. For example, if a report is grouped by division, and further grouped by department, then a summary column defined for a salary total would create fields for the sum of salaries for each division and each department group (group-level summaries), and the sum of all salaries (report-level summary).

To create a summary column (for totals or subtotals) using the tool palette:

In the Data Model view, single-click the Summary Column tool in the tool palette, then:
- To create a column within a group, click in the group at the position you want the column placed in the hierarchy.
- To create a report-level column, click in an open area of the canvas region.
Double-click the new summary column object to display the Property Inspector.
Under the Summary node:
- set the Function property by choosing the type of summary you want from the drop-down list.
- set the Source property to the column you want to summarize.

3.8.9 Creating or editing a placeholder column

To create or edit a placeholder column:

In the Data Model view, single-click the Placeholder Column tool in the tool palette.
- To create a column within a group, click in the group at the position you want the column placed in the hierarchy.
- To create a report-level column, click in an open area of the canvas region.
Double-click the placeholder column object.
In the Property Inspector, set the desired properties for the placeholder column.
Set the value of a placeholder column in:
- the Before Report trigger, if the placeholder is a report-level column
- a report-level formula column, if the placeholder is a report-level column
- a formula in the placeholder's group or a group below it (the value is set once for each record of the group)

3.9 Work with the Report Layout

This section provides procedures for creating and modifying objects in your report layout. The procedures are grouped into the following sections:

3.9.1 General Layout Objects

This section provides procedures for the following tasks that you may perform as you work with container objects:

3.9.1.1 Creating a field object

To create a field object manually in the report layout:

In the Paper Design view, Paper Layout view or Paper Parameter Form view, click the Field tool in the tool palette.
Click and drag a rectangle.
Double-click the new field object.
In the Property Inspector, under the Field node, set the Source property to the column or parameter that will provide the value for the field.
Set other properties as desired.

3.9.1.2 Creating a frame or repeating frame

To create a frame or repeating frame manually:

In the Paper Layout view, click the Frame tool or the Repeating Frame tool in the tool palette.
Click and drag a rectangle.

Note:
The size of the rectangle must be large enough to include the objects that it will contain.
Double-click the new frame object.
In the Property Inspector, set the desired properties.

3.9.1.3 Creating a matrix object

Note:

This procedure provides steps to create a matrix object manually. The recommended method for creating a matrix object is to use the Report Block Wizard (choose Insert > Report Block in the Paper Layout view).

To create a matrix object:

In the Paper Layout view, click the Repeating Frame tool in the tool palette, then drag a rectangle to create a repeating frame.
In the Property Inspector, under Repeating Frame, set the Source property to the "column" for the matrix, and set the Print Direction property to Down.
Click the Repeating Frame tool in the tool palette again, then drag a rectangle to create a second repeating frame, intersecting the first repeating frame.
In the Property Inspector, under Repeating Frame, set the Source property to the "row" for the matrix, and set the Print Direction property to Across.
Drag a rectangle around both repeating frames to select them, then choose Insert > Layout Matrix.

This creates a matrix object that is the intersection of the two repeating frames. For example:

Figure 3-1 Example matrix object

Text description of plmtxobj.gif follows.

Text description of the illustration plmtxobj.gif

Double-click the matrix object to set its properties.

3.9.1.4 Creating a barcode using a barcode font

Note:

Beginning with Oracle9i Reports Release 1 (9.0.2), you can use a barcode JavaBean that automatically generates the barcode for you. See the example report in Chapter 40, "Building a Report with a Barcode".

To create a barcode using a barcode font:

Install a barcode font on your machine (e.g., on Windows, install the barcode font in the Windows Control Panel Font). Barcode fonts are available from most computer stores.

Note:
For portability between platforms, you can use a PostScript barcode font to allow you to print your report to any PostScript-enabled output device.
Create a query that selects a column from the database containing the numbers for the barcodes you want included in your report.
In the Paper Layout view, click the barcode field, and choose Format > Font to apply the barcode font to the field.

3.9.1.5 Applying conditional formatting to a layout object

To apply conditional formatting to a layout object:

Click the object, then choose Format > Conditional Formatting.

Tip:
To select multiple objects, click one object, then Shift-click all other objects. To select all objects, choose Edit > Select All.
In the Conditional Formatting dialog box, click New to display the Format Exception dialog box.
Specify the conditions and formatting attributes to be applied when the condition(s) evaluate to TRUE.
Click OK.

Usage notes

For more complex conditional formatting, you can define conditions against an object using a PL/SQL format trigger in the PL/SQL Editor.

3.9.2 Text Objects

This section provides procedures for the following tasks that you may perform as you work with text objects:

3.9.2.1 Creating a boilerplate object for text

To create a boilerplate object for text:

In the Paper Design view, Paper Layout view or Paper Parameter Form view, click the Text tool in the tool palette.
Click and drag a rectangle.
In the new boilerplate object, type the desired text.

The text you type is composed of paragraphs which are separated by new lines. Text within a paragraph is word-wrapped inside the boilerplate object's horizontal size.
Click outside the boilerplate text object.

3.9.2.2 Creating a boilerplate object for text that displays every other page

To create a boilerplate object for text that displays every other page:

Create a boilerplate text object where you want it to appear on the page, either in the margin, or in a repeating frame (expand the repeating frame to make room for the boilerplate text, below the fields in the repeating frame).

Double-click the boilerplate text object to display the Property Inspector. Under the Advanced Layout node, set the Format Trigger property by typing the following code in the PL/SQL Editor:

function XXX_HDRFormatTrigger return boolean is page_num number; 
begin 
  srw.get_page_num(page_num); 
  if mod(page_num, 2) = 0 then 
    return(false); 
  else 
    return (true); 
  end if; 
end;

Run the report to see the boilerplate text appear every other page.

3.9.2.3 Editing text

To insert and replace text:

In the Paper Design view, Paper Layout view, or Paper Parameter Form view, single-click the text object.
Click the text to change to edit mode.
Modify the text as desired, then click outside the text object.

To delete text:

In the Paper Design view, Paper Layout view, or Paper Parameter Form view, single-click the text object.
To cut all the text, choose Edit > Clear.
To cut portions of text, click and drag to mark the text you want to cut, then press the Delete key.

To cut, copy, or paste text:

In the Paper Layout view or Parameter Form view, single-click the text object.
Click and drag to mark the text you want to cut or copy.
Choose Edit > Cut or Edit > Copy.
To paste text, choose Edit > Paste.

3.9.2.4 Referencing a field in boilerplate text

To reference a field in boilerplate text:

In the Paper Design view or Paper Layout view, click in the boilerplate text where you want the reference to a field to appear.
Type an ampersand (&) followed by the name of the field.

If you want to place the field reference in front of other text with no spaces in between, enclose the field name in angle brackets to separate it from the text (e.g., &<fieldname>Oracle). If the field reference follows other text, no angle brackets are needed (e.g., Oracle&fieldname). You can include field references right next to each other with no spaces in between and without angle brackets (e.g., &field1&field2&field3).

Usage notes

A reference to a field includes the field's properties. Therefore, if the Horizontal Elasticity property is set to Fixed, any extra spaces in the field will appear in the report output. For example, if field f_sal is fixed horizontally, $&<f_sal>/week may produce $800/week in the report output.
In addition to referencing fields in a boilerplate text object, you can directly reference a database column (e.g., &SAL). For example, $&<SAL>/week may produce $800/week in the report output.

3.9.2.5 Linking a boilerplate text object to a file

To link a boilerplate text object to a file:

In the Paper Design view, click the Link File tool in the tool palette.
Click and drag a rectangle.
Double-click the link file object to display the Property Inspector.
Under the Link File Boilerplate node:
- Set the Source File Format property to Text.
- Set the Source Filename property to the name of the file containing the text.

3.9.2.6 Wrapping text in a field

To wrap text in a field:

Choose Tools > Report Wizard.
In the Report Wizard, click the Labels tab.
Change the value in the Width column for the field in which you want to wrap text, as desired.

Click Apply.

Caution:

If you have made manual adjustments to your layout in the Paper Layout view or Paper Design view, you will lose these layout changes when you click Apply or Finish in the Report Wizard, which redefaults the layout. If you do not want to overwrite your current layout, you can manually drag the column to the desired width in the Paper Layout view or Paper Design view.

In the Paper Layout view or Parameter Form view, double-click the field to display the Property Inspector, and set properties:
- Under General Layout, verify that the Vertical Elasticity property is set to Expand.

3.9.2.7 Changing text attributes

To change the font, justification, spacing, or reading direction of a text object:

In the Paper Design view, click the text object(s).

Tip:
To select multiple objects, click one object, then Shift-click all other objects. To select all objects, choose Edit > Select All.
From the Format menu, choose Font, Justify, Text Spacing, or Direction.

3.9.2.8 Changing text attributes using PL/SQL

To change the font or justification of a text object using PL/SQL instead of the user interface:

In the Object Navigator, expand the Paper Layout node.
Double-click the PL/SQL icon next to the text object(s).

Tip:
To select multiple objects, click one object, then Shift-click all other objects. To select all objects, choose Edit > Select All.
In the PL/SQL Editor, use the following built-in procedures to change the font or justification of the object as desired:

SRW.SET_CHARMODE_TEXT

SRW.SET_FONT_FACE

SRW.SET_FONT_STYLE

SRW.SET_FONT_WEIGHT

SRW.SET_FONT_SIZE

SRW.SET_JUSTIFICATION

3.9.3 Page Numbers or Date/Time Stamps

This section provides procedures for the following tasks that you may perform as you work with page numbers or date/time stamps:

3.9.3.1 Creating page numbers

To create default page numbers:

In the Paper Layout view or Paper Design view, choose Insert > Page Number.
In the Insert Page Number dialog box, choose from the drop-down list the location for the page number.
Click the desired page number format: Page Number Only or Page Number and Total Pages.
Optionally, change the default attributes of the page number text as desired.

To create customized page numbers:

In the Paper Layout view or Paper Design view, click the Edit Margin button in the toolbar.

Note:

The margin area is defined by a thick black line that separates it from the body. If you create objects in the body portion of a report while displaying the margin area, you can only edit those objects when the margin is displayed.

Create a field object (see Section 3.9.1.1, "Creating a field object") in the margin area for each page number value you require, for example Physical Page Number, Total Physical Pages.
In the field's Property Inspector, under the Field node, set the Source property to the source for the page number, and set the Visible property to No. Under the General Layout node, set the Horizontal Elasticity property to Variable.
Create a boilerplate text object (see Section 3.9.2.1, "Creating a boilerplate object for text") in the margin area, and reference the page number field(s) (see Section 3.9.2.4, "Referencing a field in boilerplate text") using &fieldname.

Usage notes

To generate odd page numbers on the right and even pages numbers on the left, you have to create two fields: one on the right side, one on the left side. In the format trigger of each field, you test if it is an odd or an even page by using SRW.GET_PAGE_NUM. Then, you specify whether or not to display the field.

3.9.3.2 Resetting page numbers

To reset default page numbers (created with Insert > Page Number):

In the Paper Layout view or Paper Design view, click the default page number field, then choose Edit > Clear to delete it.
Create customized page numbers (see Section 3.9.3.1, "Creating page numbers").
In the page number field's Property Inspector, under the Field node, double-click the Page Numbering property field (labeled...).
In the Page Numbering dialog box, click the desired Reset At setting. Page numbers will reset to the Start at value each time the selected frame is formatted. Click OK.

3.9.3.3 Creating a time and/or date stamp

To add a date and/or time stamp to your report:

In the Paper Layout view Paper Design view, choose Insert > Date and Time.
In the Insert Date and Time dialog box, choose from the drop-down list the location for the date or time stamp.
Click the desired date and/or time format, or click Custom to define your own format.

3.9.4 Borders

This section provides procedures for the following tasks that you may perform as you work with borders:

3.9.4.1 Showing or hiding object borders

To show or hide the borders around an object:

Click the object(s).

Tip:
To select multiple objects, click one object, then Shift-click all other objects. To select all objects, choose Edit > Select All.
Click the Line color tool in the tool palette.
To add a border, click the solid black square.

To make a border transparent in the report output, click No Line at the bottom of the color palette.

Note:

If you make the borders transparent for all the objects, this includes the underlines beneath the column headings. To add the underlines, click the underline objects, and use the Line Color in the tool palette to select a line color.

To show or hide part of the border, choose Format > Line > Border, and select from the menu to toggle between showing and hiding the sides of the border.

3.9.4.2 Changing object border attributes

To change the borders around an object:

Click the object(s).

Tip:
To select multiple objects, click one object, then Shift-click all other objects. To select all objects, choose Edit > Select All.
To change the line width, choose Format > Line > Line Width.
To select a pattern, choose Format > Line > Dash. Even though it appears in the output, the dash pattern is sometimes obscured by the object's outline. You can choose Tools > Options > Paper Layout and clear the Options:Frame Outlines check box.
To "frame" the border, choose Format > Bevel.

3.9.4.3 Changing the current mode (Confine or Flex)

To set or override Confine mode:

In the Paper Layout view, click the Confine On button or the Confine Off button in the toolbar to toggle the mode:

On: child objects cannot be moved outside their enclosing parent objects.

Off: child objects can be moved outside their enclosing parent objects.

To set or override Flex mode:

In the Paper Layout view, click the Flex On button or the Flex Off button in the toolbar to toggle the mode:

On: parent borders "stretch" when child objects are moved against them. The child object maintains the same distance from the side it moves against.

Off: parent borders remain fixed when child objects are moved against them.

3.9.5 Anchors

This section provides procedures for the following tasks that you may perform as you work with anchors:

3.9.5.1 Anchoring objects together

Anchoring objects assures that the anchored object will move with the parent object. An object can be anchored to only one other object.

To anchor objects together:

In the Paper Layout view, click the Anchor tool in the tool palette.
Click an edge of the child object and double-click an edge of the parent object.

A line is drawn from the child to the parent. A small box appears at the end of the line that is attached to the parent object.

3.9.5.2 Viewing implicit anchors

By default, you see the explicit anchors created in the Paper Layout view of the Report Editor.

To view information on both implicit and explicit anchors:

In the Object Navigator, choose Tools > Options > Navigator to display the Object Navigator Options dialog box.

Click the Layout tab, and select the Anchoring Information check box.

With this option selected, you can see all information on both implicit and explicit anchors in the Object Navigator.

Note:

By default, objects are anchored to the upper left corner of their enclosing object. If this view of the Object Navigator does not show anchoring information for an object, you can assume that the object is anchored to its enclosing object, which might be the frame or the body.

3.9.5.3 Moving an anchor

Moving an anchor changes how the objects will be displayed in relationship to each other.

To move an anchor:

In the Paper Layout view, click the anchor.
Click the Reshape tool in the tool palette, then drag one of the anchor endpoints to its new location on the object edge.

To move an anchor along an object edge:

Press the constrain (e.g., shift) key when moving the anchor.

To change the position of the anchor on the object edge, as a percentage down or across from top to bottom or left to right:

Double-click the anchor object to display the Property Inspector.
Set the Child Edge Percent property and/or Parent Edge Percent property to a new value.
To change the edge on which the anchor is positioned, set the Child Edge Type property and/or Parent Edge Type property as desired.

3.9.6 Colors, Patterns, and Highlighting

This section provides procedures for the following tasks that you may perform as you work with colors, patterns, and highlighting:

3.9.6.1 Setting color palette preferences

See Section 3.2.6, "Setting color palette preferences".

3.9.6.2 Changing colors

To change the color of an object or text:

In the Paper Layout view, click the object(s).

Tip:
To select multiple objects, click one object, then Shift-click all other objects. To select all objects, choose Edit > Select All.

Click the Fill Color tool, the Line Color tool, or the Text Color tool in the tool palette, depending on which part of the object you want to apply a color to.

Note:

The Fill/Line/Text Display, the box directly above the three Color tools, shows the currently selected fill, border, and text. The default fill and border for objects created by Reports Builder is transparent, while the default for objects you create is a black, one-point line around a white.

On the color palette, click a color.

3.9.6.3 Changing patterns

To change the pattern of an object:

In the Paper Layout view, click the object(s).

Tip:
To select multiple objects, click one object, then Shift-click all other objects. To select all objects, choose Edit > Select All.
Click the Fill Color tool or the Line Color tool in the tool palette, depending on which part of the object you want to apply a pattern to.

Note:
The Windows platform does not support a border pattern (that is, patterns for the Line Color tool).
On the color palette, click Patterns.
On the pattern palette, click a pattern. To change the foreground and background colors, choose from the drop-down color palettes at the bottom of the pattern palette.

3.9.6.4 Changing colors and patterns using PL/SQL

To change the color of an object using PL/SQL instead of the user interface:

In the Object Navigator, expand the Paper Layout node.
Double-click the PL/SQL icon next to the object for which you want to change the color.
In the PL/SQL Editor, use the following built-in procedures to change the color of the object as desired:

SRW.SET_BACKGROUND_BORDER_COLOR

SRW.SET_BACKGROUND_FILL_COLOR

SRW.SET_FOREGROUND_BORDER_COLOR

SRW.SET_FOREGROUND_FILL_COLOR

SRW.SET_TEXT_COLOR

SRW.SET_BORDER_PATTERN

3.9.6.5 Modifying the color palette

To modify the color palette:

First, make the color palette editable:
- Choose Edit > Preferences, and set Color Mode to Editable, as described in Section 3.2.6, "Setting color palette preferences".
  
  Note:
  By default, color palettes are read-only.
- Shut down and restart Reports Builder to enable the Editable mode.
In the Paper Layout view, choose Format > Color Palette > Edit.
In the Color Palette dialog box, modify the color palette as desired:
- Click Edit to alter the settings of the current color.
- Type a new name in Current Color, then click Rename to rename the current color.
- Select Color to Edit contains the current color palette being used in the Report Editor for the current report. Select a color from the palette to alter it.
- Click OK to apply your changes to the current report.

3.9.6.6 Importing or exporting a color palette

To import or export a color palette:

First, make the color palette editable:
- Choose Edit > Preferences, and set Color Mode to Editable, as described in Section 3.2.6, "Setting color palette preferences".
  
  Note:
  By default, color palettes are read-only.
- Shut down and restart Reports Builder to enable the Editable mode.
In the Paper Layout view, choose Format > Color Palette > Import or Format > Color Palette > Export.
In the dialog box, specify the name and format of the file.
Click OK.

3.9.6.7 Highlighting a value

To highlight a value in a report:

In the Paper Layout view or Paper Design view, click the field that contains the value to be highlighted.
Choose Format > Conditional Formatting.
In the Conditional Formatting dialog box, click New to display the Format Exception dialog box.
Select the field and define the condition(s) when the value should be highlighted.

In the Format group box, select the Fill Color to be used to highlight the value.

Note:

If you can't select the condition you want to use in the dialog box, select the formatting you want and a placeholder condition. The condition can be edited in the PL/SQL Editor for the format trigger that is created.

Click OK to close the Format Exception dialog box, then click OK again to close the Conditional Formatting dialog box. If the code compiles without errors, the new formatting is reflected in the Paper Layout view. The code is stored as a format trigger for the field.

Example

The following code determines the monthly compensation and changes the background color to red if the compensation has exceeded four thousand dollars a month and the employee is not a manager.

function R_G_EMPNOFormatTrigger return boolean is varcomm number;
begin
 if :comm is null then
   varcomm := 0;
 else
   varcomm := :comm;
 end if;
  if (:sal * 2 + varcomm > 4000) and :job != 'MANAGER' then
   srw.set_background_fill_color('red');
 end if;
 return (TRUE);
end;

3.9.6.8 Highlighting a row

To highlight an entire row:

In the Paper Layout view, click the repeating frame that contains the fields that make up the rows to be highlighted.
Choose Format > Conditional Formatting.
In the Conditional Formatting dialog box, click New to display the Format Exception dialog box.
Select the field(s) and define the condition(s) that describe the rows to be highlighted.

In the Format group box, select the Fill Color to be used to highlight the row.

Note:

If you can't select the condition you want to use in the dialog box, select the formatting you want and a placeholder condition.The condition can be edited in the PL/SQL Editor for the format trigger that is created.

Click OK to close the Format Exception dialog box, then click OK again to close the Conditional Formatting dialog box. If the code compiles without errors, the new formatting is reflected in the Paper Layout view. The code is stored as a format trigger for the repeating frame.

Example

The following code determines the monthly compensation and changes the background color to red if the compensation has exceeded four thousand dollars a month and the employee is not a manager.

function R_G_EMPNOFormatTrigger return boolean is varcomm number;
begin
if :comm is null then
  varcomm := 0;
else
  varcomm := :comm;
end if;

  if (:sal * 2 + varcomm > 4000) and :job != 'MANAGER' then
  srw.set_background_fill_color('red');
end if;
  return (TRUE);
end;

3.9.6.9 Alternating row colors

To alternate the colors of either the text or the fill of a row:

See Chapter 41, "Building a Report with an XML Pluggable Data Source". This chapter provides detailed steps for using PL/SQL procedures and format triggers to apply alternating row colors.

3.9.7 Format Masks

This section provides procedures for the following tasks that you may perform as you work with format masks:

3.9.7.1 Specifying date and time format masks

The following tables describe the date and time format masks, and the suffixes you can add to date format masks:

Table 3-2 Data and time format masks

Format Mask	Description
SCC or CC	Century, abbreviated;'S' prefixes "BC" with (-)
SYYYY or YYYY	Year;'S' prefixes "BC" "BC" date with a (-)
I or IY or IYY	Last 1, 2, or 3 digit(s) of year
Y or YY or YYY	Last 1, 2, or 3 digit(s) of year
Y,YYY	Year with comma
SYEAR or YEAR	Year, spelled out; 'S' prefixes "BC" date with (-)
RRRR or SRRRR	Year; 'S' prefixes "BC" date with a (-)
RR	Last 2 digit(s) of year
BC, AD, or B.C., A.D.	Century indicator
Q	Quarter of year (Jan-Mar= Quarter 1)
MM	Month in digits (Jan = 01)
MONTH or MON	Name of month, or 3-letter abbreviation
WW, IW	Week in year
W	Week in Julian days
J	Julian day; the number of days since January 1,4712 BC
DDD, DD, or D	Day in year, month, or week
DAY	Day of week fully spelled out (e.g., MONDAY)
DY	Name of day, 3-letter abbreviation (e.g., MON)
AM, PM, or A.M., P.M.	Meridian indicator
HH or HH12	Hour of day (1-12)
HH24	Hour of day (0-23)
MI	Minute
SS; SSSSS	Second in minute; seconds in day
FM	Toggles fill mode which replaces multiple spaces before or between dates, numbers, or words with a single space

The following suffixes may be added to the format masks:

Table 3-3 Date format mask suffixes

Suffix	Description
TH	Suffixed number ("DDth" for "4th")
SP	Spelled out number ("DDSP" for "FOUR")
SPTH or THSP	Spelled and suffixed number ("DDSPTH" for "FOURTH")

Examples

Table 3-4 Date format mask examples

Sample Date Format	Date Displayed
MM/DD/RR	03/04/02
DD MON RRRR	04 MAR 2002
Mon. DD, RRRR	Mar. 4, 2002
Day Month DD fmHH:MI AM	Monday March 4 11:35 AM
Dy Mon ddth fmHH24:MI:SS	Mon Mar 4th 23:35:22
Day "the" ddthsp "of" Month	Monday the fourth of March

3.9.7.2 Specifying number format masks

The following table describes the tokens you can use in creating a number format mask:

Table 3-5 Tokens for number format masks

Format Token	Description
0	Prints one digit.
N	Prints one digit, unless it is a leading zero to the left of the decimal point or a trailing zero to the right of the decimal point.
*	Prints one digit, unless it is a leading zero to the left of the decimal point, in which case an asterisk (*) is printed. Trailing zeros to the right of the decimal point are printed.
9	Prints one digit, unless it is a leading zero to the left of the decimal point, in which case a space is printed. Trailing zeros to the right of the decimal point are printed.
+	Prints a leading plus (+) for positive values and a leading minus (-) for negative values. This token must lead the mask.
-	Prints a leading minus (-) for negative values and nothing for positive values. This token must lead the other tokens.
MI	Prints a minus (-) after negative values and a space after positive values. This token must trail the other tokens.
S	Prints a minus (-) for negative values and a plus (+) for positive values (wherever the S appears in the mask). This token must lead or trail the other tokens.
PR	Prints angle brackets (<>) around negative values and spaces around positive values. This token must trail the other tokens.
( )	Prints parentheses around negative values and spaces around positive values. The parentheses must surround the other tokens.
DB	Prints a "DB" after positive values. This token must trail the other tokens.
CR	Prints a "CR" after negative values. This token must trail the other tokens.
Y	Causes no sign symbol to be printed for negative or positive values.
V	Causes the number to be multiplied by 10N, where N is the number of 0, 9, *, and S tokens that appear to the right of the V.
EEEE	Causes the number to be printed in scientific notation. All digit tokens refer to digits of the mantissa. There must be exactly one digit to the left of the decimal point (displayed or implicit). The token EEEE prints as E followed by a plus (+), if the ordinate is positive or zero, and a minus (-), if the ordinate is negative, and two digits representing the ordinate (e.g., E-99).
"string"	Prints the string between the double quotes. To have double-quotes inside the string, type double-quotes back to back ("").
. (period)	Prints a period (.) to separate the integral and fractional parts of a number.
D	Prints the local decimal character to separate the integral and fractional parts of a number.
, (comma)	Prints a comma (,) as the group/thousands separator.
G	Prints the local group/thousands separator.
$	Prints $
L	Prints the local currency symbol.
C	Prints the ISO currency symbol.
%	Prints %.
" "	Prints a blank space. (Do not include quotes in mask.)
v	Prints a blank space for all zero values, regard-less of other tokens.
K	Prints a blank space.
<>	Delineates the beginning and ending of the decimal-aligned region (i.e., that part of the number that you want aligned with the decimal point in the format mask). Angle brackets indicate that the number should always occupy the same amount of space. If necessary, values are padded with blanks to the left or right of the decimal point.
RN, rn	Prints values in uppercase or lowercase Roman numerals, respectively. You cannot enter any other tokens with this token.

Restrictions

For number format masks, if the actual value is longer than the specified format mask, the value will appear as a string of asterisks in the report output, regardless of the field's width. For example, if a fixed field's width is 8, the value is 1234567, and the format mask is <NNNNNN>, your output will be *******.
Similarly, if the number format mask causes the value to be larger than the field width, asterisks will appear in place of the value. For example, if a fixed field's width is 6, the value is 1234, and the format mask is -99999999, your output will be ******. This occurs because the format token 9 prints a blank for leading zeros to the left of the decimal. As a result, the value is too long to be displayed in a field of width 6.
If you do not specify a sign token in the format mask, positive values are preceded by a space and negative values are preceded by a minus (-).
After you create a format mask it will display in the list of values only if it is an appropriate format mask for the Datatype of the Source --i.e., format masks for numbers are displayed when the Source is a number, and format masks for dates are displayed when the Source is a date.
Format masks that print spaces for zeros (e.g., 9) increase the number of bytes per page taken up by your output.

Examples

Table 3-6 Number format mask examples

Sample Number Format	Number	Number Displayed
-0000	7934	"7934"
	-7934	"-7934"
-00000	7934	"07934"
-NNNN	7639	"7639"
	535	"535"
-NNN	7639	"****"
-****	7902	"7902"
-*****	7902	"*7902"
+NNNN	100	"+100"
	-99	"-99"
(NNNN)	1600	" 1600 "
	-800	"(800)"
NNNNPR	1600	" 1600 "
	-800	"<800>"
NNNNMI	1600	"1600 "
	-800	"800-"
NNNVNN	343	"34300"
N.NNEEEE	7369	"7.37E+03"
"SRW"-0000	7782	"SRW7782"
-$NNNN.NN	800	"$800"
	1600	"$1600"
-%NNN	10	"%10"
-NN NNN.NN	3000	"3 000"
+KKNNNNN.00	1950	"+ 1950.00"
	900	"+ 900.00"
$<NNNNN.00>	1200	"$1200.00"
	500	"$500.00"
$<NNNNN.00> DB	1200	"$1200.00 DB"
	-500	"$500.00"
$<NNNNN.00> CR	1200	"$1200.00"
	-500	"$500.00 CR"

* The quotation marks will not appear in your output. They are used here to make it clear where there are leading or trailing spaces.

3.9.7.3 Applying a format mask to a numeric object

To apply a format mask to a numeric object:
In the Paper Design view, click the object(s).

Tip:
To select multiple objects, click one object, then Shift-click all other objects. To select all objects, choose Edit > Select All.
To apply the currency format to the object, click the Currency button in the toolbar.
To apply the percentage format to the object, click the Percentage button in the toolbar.
To add commas to the value of the object, click the Commas button in the toolbar. To move the comma right or left, click the Remove Decimal Place button or the Add Decimal Place button.
Choose Tools > Property Inspector.
Under the Field node, verify the Format Mask property is set to the desired format.
Set other properties as desired.

3.9.7.4 Applying a format mask to a date object

To apply a format mask to a date object:

In the Paper Design view, click the object(s).

Tip:
To select multiple objects, click one object, then Shift-click all other objects. To select all objects, choose Edit > Select All.
Choose Tools > Property Inspector.
Under the Field node, set the Format Mask property to the desired date format.
Set other properties as desired.

3.9.7.5 Adding a custom format mask

To add a custom format mask to the default format masks list:

Choose Edit > Preferences.
In the Preferences dialog box, click Edit Masks.
In the Format Masks dialog box, set Display to the type of format mask you want to add.
In the Mask field, type the format mask to add, then click Add.
Click OK.

Now, you will see your new custom format masks in the list of values for the Format Mask property for fields and the Input Mask property for parameters.

3.9.7.6 Changing the format mask for multiple fields

To change the format mask for multiple fields at one time:

In the Paper Layout view, click the fields you want to change.
Choose Tools > Property Inspector.
In the Property Inspector, set the Format Mask property to a new format mask.

3.9.8 Graphic or Image Objects

This section provides procedures for the following tasks that you may perform as you work with graphic or image objects:

3.9.8.1 Creating a drawing object

To create a drawing object:

In the Paper Layout view, click a drawing tool in the tool palette (e.g., Rectangle, Ellipse, Polyline, etc.).
To create a line, rounded rectangle, rectangle, ellipse, arc, or freehand object, click in the main area (canvas region) of the window and drag to create the object.
To create a polygon or polyline, click in the main area (canvas region) of the window where you want each point of the object, then double-click to create the object.
To draw constrained objects (i.e., perfect circles and squares, etc.), hold down the constrain key (e.g., the shift key) when drawing the graphic.
Double-click the drawing object.
In the Property Inspector, set the desired properties.

3.9.8.2 Adding a graph to a paper report

To add a graph to your paper report:

In the Paper Layout view, click the Graph tool in the tool palette.
Drag a square in the area where the graph should appear to display the Graph Wizard.
Follow the wizard to create the desired graph and position it in your report.
Double-click the graph object you have created to display the Property Inspector, and set properties as desired.
To re-enter the Graph Wizard, do either of the following:
- Right-click, and choose Graph Wizard.
- Click the graph, then choose Edit > Settings.

3.9.8.3 Editing a graph in a paper report

To edit a graph in a paper report:

In the Paper Layout view, click the graph.

Note:
The graph is represented as a bar graph even if the graph is of another type.
To edit the XML definition of the graph:
- Right-click and choose Property Inspector.
- In the Property Inspector, under the Graph node, click the Graph Settings property value field to display the Graph Settings dialog box where you can edit the XML.
To re-enter the Graph Wizard to redefine the graph, do either of the following:
- Right-click and choose Graph Wizard.
- Choose Edit > Settings.

3.9.8.4 Adding a graph to a Web report

To add a graph to a JSP-based Web report:

In the Object Navigator, double-click the view icon next to the Web Source node to display the source code in the Web Source view.
Locate the section in the source code where you want to add the graph.

Note:
Adding some text such as "Place the graph here" to your Web page allows you to easily locate the correct position for your graph.
Choose Insert > Graph.
In the Graph Wizard, specify the information for the graph. Click Help on any tab page for assistance.

Click the Run Web Layout button in the toolbar to display your report and graph in your Web browser.

Note:

To re-enter the Graph Wizard, place your cursor anywhere between the <rw:graph> and </rw:graph> tags in the Web Source view, then choose Edit > Settings.

3.9.8.5 Editing a graph in a Web report

To edit a graph in a JSP-based Web report, do any of the following in the Web Source view:

Between the <rw:graph> and </rw:graph> tags that define the graph, make changes directly to the JSP and XML tags.

Note:
This is not recommended as errors can result in messages or an unreadable graph.
Place your cursor anywhere between the <rw:graph> and </rw:graph> tags, then choose Edit > Settings to re-enter the Graph Wizard.

Note:
Any manual changes and additions you've made to the graph XML will be lost if you click Finish, which redefaults the graph to the definition in the Graph Wizard.
Delete and re-create the graph as follows:
1. Delete all lines from the <rw:graph> tag to the </rw:graph> tag.
2. Leaving the cursor in the position where the graph was cut, choose Insert > Graph and follow the Graph Wizard to re-create the graph.

3.9.8.6 Importing a drawing or image

To import a drawing or image:
In the Paper Layout view, choose Insert > Image and click the type of object you want to import.
In the dialog box, specify the name and format of the file. Click OK.
Move the object to the desired position.

3.9.8.7 Selecting an image from the database

To select an image from the database:

Create a query that selects a column containing images or the filenames of image objects.
In the Data Model view, double-click the image column to display the Property Inspector.
If the column contains the filenames of image objects, rather than the images themselves:
- Under the Column node, set the Read from File property to Yes.
Under the Column node, set the File Format property to Image.

3.9.8.8 Selecting an image URL from the database

To select a URL from the database for including an image in an HTML report:

Create a query, with a SELECT statement that selects a column containing URLs.
In the Data Model view, double-click the column containing the URLs to display the Property Inspector.
Under Column:
- set the Read from File property to Yes.
- set the File Format property to Image URL.

3.9.8.9 Linking an image object to a file

To link an image object to a file:

In the Paper Design view or Paper Layout view, click the Link File tool in the tool palette.
Click and drag a rectangle.
Double-click the link file object to display the Property Inspector.
Under Link File Boilerplate:
- set the Source File Format property to Image.
- set the Source Filename property to the name of the file containing the image.

3.9.8.10 Linking an image object to a URL

Note:

This procedure is for HTML output only.

To link an image object to a URL:

In the Paper Design view or Paper Layout view, click the Link File tool in the tool palette
Click and drag a rectangle.
Double-click the link file object to display the Property Inspector.
Under the Link File Boilerplate node:
- set the Source File Format property to Image URL.
- set the Source Filename property to the URL where the image is located with the required protocol.

Example 1

HTTP://www.oracle.com/images/logo.gif

Example 2

HTTP://&<P_SERVER_NAME>/images/logo.gif

where P_SERVER_NAME is a user parameter of type CHAR

At runtime, the end user can specify a value for the parameter (e.g., P_SERVER_NAME = www.oracle.us.com for a dynamic URL link of HTTP://www.oracle.us.com/images/logo.gif).

Example 3

FILE://c:/images/logo.gif

Note:

If you click Browse to find a file, Reports Builder automatically prepends FILE:// to the returned path.

3.9.9 Page or Group Headers or Footers

This section provides procedures for the following tasks that you may perform as you work with page or group headers or footers:

3.9.9.1 Creating a text heading

To create an object or heading in the margin of a report:

In the Paper Layout view, click the Edit Margin button in the toolbar.

Note:

To adjust the margin, click the margin border, then drag a handle to the desired position. You can adjust the margin on all four sides of a report.
Create required objects in the margin area. They will appear on all pages of the report.
Click the Header Section, Main Section, or the Trailer Section buttons in the toolbar to reactivate the appropriate section of the body area of the report.

3.9.9.2 Creating a heading that includes database values

To create a heading that includes database values:

Choose Tools > Report Wizard to re-enter the Report Wizard for the current report.

On the Totals page, create any totals that you want to include in the heading.

Note:

Any totals that you intend to place in the margin area of your report must be report-level totals. Group totals (e.g., the total for a department) placed in the margin will cause a frequency error when you run your report.

On the Fields page, verify that the fields and totals you want to include in the heading are either available or displayed in the report.

In the Paper Layout view, click the Margin button in the toolbar.

Note:

For any values that require that you specify a format mask, create a hidden field object in the margin area (see Section 3.9.1.1, "Creating a field object"):
- In the Property Inspector, under the Field node, set the Source property to the source column for the value, set the Visible property to No, and set the Format Mask property as desired.
- Under the General Layout node, set the Horizontal Elasticity property to Variable.
  
  Note:
  If you do not need to specify a format mask, you can simply reference the value directly in Step 6, and you do not need to create this hidden field.
Create a boilerplate text object in the margin area, and reference the field and/or report-level totals you want to include in the heading. See Section 3.9.2.1, "Creating a boilerplate object for text" and Section 3.9.2.4, "Referencing a field in boilerplate text".

3.9.9.3 Creating a group header or footer

To create a header or footer above or below each group of records:

In the Paper Layout view, click the Confine Off button and the Flex On button in the toolbar.
Click frame that encloses the repeating frame for the group to which you want to add a header or footer, then drag and resize the frame to allow enough room to type the header or footer text.

Tip:
In the Object Navigator, under the Paper Layout node, expand the Body node to select the frame by name.
Create a boilerplate text object for the header or footer text (see Section 3.9.2.1, "Creating a boilerplate object for text").

3.9.10 Margin, Header Page, or Trailer Page Objects

This section provides procedures for the following tasks that you may perform as you work with margin, header page, or trailer page objects:

3.9.10.1 Creating a margin object

To create an object or heading in the margin of a report:

In the Paper Layout view, click the Edit Margin button in the toolbar.

Note:

To adjust the margin, click the margin border, then drag a handle to the desired position. You can adjust the margin on all four sides of a report.
Create required objects in the margin area. They will appear on all pages of the report.
Click the Header Section, Main Section, or the Trailer Section buttons in the toolbar to reactivate the appropriate section of the body area of the report.

3.9.10.2 Creating a header page or trailer page object

With report sectioning, Header and Trailer pages are identical to Body pages. In effect, this means that the Header, Trailer, and Body are three sections of a report. The names of the sections are exposed under the Paper Design node in the Object Navigator as Header Section, Main Section, and Trailer Section. You can use the margin and body of the Header and Trailer sections to create a Header and Trailer "page" as in earlier releases.

In the Paper Layout view, click the Confine Off button and the Flex On button in the toolbar.
Click the repeating frame for the page, then drag and resize the frame to allow enough room to type the header or footer text.

Tip:
In the Object Navigator, under the Paper Layout node, expand the Body node to select the repeating frame by name.
Create a boilerplate text object for the header or footer text.

3.9.11 Move Objects

This section provides procedures for the following tasks that you may perform as you move your report objects:

3.9.11.1 Moving multiple objects

To move multiple objects:

Click or drag a box around the objects you want to move.

Tip:
To select multiple objects, click one object, then Shift-click all other objects. To select all objects, choose Edit > Select All.
Use the arrow keys on your keyboard to move the objects in the desired direction.

3.9.11.2 Moving an object outside its parent

To move a child object outside its enclosing parent object:

In the Paper Layout view or Paper Design view, click the Confine Off button in the toolbar.
Click and drag the child object(s) as desired.

Tip:
To select multiple objects, click one object, then Shift-click all other objects. To select all objects, choose Edit > Select All.

3.9.11.3 Adjusting parent borders automatically

To adjust parent borders as you move child objects:

In the Paper Layout view, click the Flex On button in the toolbar.
Click and drag the child object(s) as desired.

Tip:
To select multiple objects, click one object, then Shift-click all other objects. To select all objects, choose Edit > Select All.
To move an object that aligns with another object in the horizontal or vertical direction (for example, a field and its label), hold down the Ctrl key as you drag the first object. To move both objects simultaneously, do not use the Ctrl key.

3.9.11.4 Moving a column in report output

To move a column in report output:

In the Paper Layout view or Paper Design view, click the column.
Click the Flex On button in the toolbar
Drag the column to the desired position.

3.9.11.5 Offsetting detail objects in a group report

To offset the detail fields in a group above or group left report:

In the Paper Design view, click the Flex On button in the toolbar.
Select the detail objects and move them to the right.
Click the Flex Off button in the toolbar.

3.9.11.6 Aligning objects

To align objects:

Click the objects you want to align.

Tip:
To select multiple objects, click one object, then Shift-click all other objects. To select all objects, choose Edit > Select All.
Choose Layout > Alignment.
In the Align Objects dialog box, specify the desired alignment.
Click OK.

3.9.11.7 Changing object layering

To change the order in which objects are layered on top of each other:

In the Paper Layout view, click the Confine Off button in the toolbar.
Click the object you want to move.
Choose one of the following items from the Arrange menu:
- Bring to Front to move the object in front of all other objects.
- Send to Back to move the object behind all other objects.
- Move Forward to move the object in front of the object directly on top of it.
- Move Backward to move the object behind the object directly underneath it.

3.9.11.8 Rotating a boilerplate object

You can only rotate boilerplate text and graphics. You cannot rotate other layout objects (repeating frames or fields).

To rotate a boilerplate object:

Click the object(s) that you want to rotate.

Tip:
To select multiple objects, click one object, then Shift-click all other objects. To select all objects, choose Edit > Select All.
Click the Rotate tool in the tool palette.
Drag a handle to rotate the object or group.

3.9.12 Resize Objects

This section provides procedures for the following tasks that you may perform as you resize your report objects:

3.9.12.1 Resizing objects

To resize one or more objects:

Click the object(s) that you want to resize.

Tip:
To select multiple objects, click one object, then Shift-click all other objects. To select all objects, choose Edit > Select All.
Set the size:
- For a fixed size, drag the handle of one of the selected objects. All selected objects will change size accordingly.
- Or, choose Tools > Property Inspector. In the Property Inspector, under the General Layout node, set the Vertical Elasticity and Horizontal Elasticity properties to Contract, Expand, Fixed, or Variable.

3.9.12.2 Making multiple objects the same size

To make multiple objects the same size:

Click the objects you want to size.

Tip:
To select multiple objects, click one object, then Shift-click all other objects. To select all objects, choose Edit > Select All.
Choose Layout > Size Objects.
In the Size Objects dialog box, specify the desired settings. Click OK.

3.9.12.3 Resizing object borders

See Section 3.9.11.3, "Adjusting parent borders automatically".

3.9.13 Change Spacing

This section provides procedures for the following tasks that you may perform as you change spacing between your report objects:

3.9.13.1 Changing columns labels or widths

To change column labels or widths:

Choose Tools > Report Wizard.
In the Report Wizard, click the Labels tab.
Change the values in the Labels and Width columns, as desired.
Click Apply.

3.9.13.2 Changing spacing within a text object

To change spacing within a text object:

In the Paper Design view, click the object(s).

Tip:
To select multiple objects, click one object, then Shift-click all other objects. To select all objects, choose Edit > Select All.
Choose Format > Text Spacing and select the desired spacing for the text object.

3.9.13.3 Changing the default layout spacing

To change the default layout spacing used by the Report Wizard when defaulting the report layout:

Choose Tools > Preferences.
In the Preferences dialog box, click the Wizards tab.
To increase the space between objects and the objects they enclose, type larger values for Horizontal Gap and Vertical Gap.
To increase the space between fields, type larger values in Horizontal Interfield and Vertical Interfield.
Click OK to close the Preferences dialog box.
For smaller spacings, choose View > Snap to Grid to toggle this setting off (no checkmark). When Snap to Grid is set on, defaulting the layout honors the layout spacing values, but then snaps to the closest gridpoint; the result may be that smaller changes to these values are not evident in the report output.

3.9.13.4 Changing the spacing between all rows

To change the spacing between all rows using the Paper Design view:

In the Paper Design view, click the second record.
Drag the second record down to create the desired spacing between all rows in the report output.

To change the spacing between all rows using the Property Inspector:

In the Paper Design view, click any field in the body of the report.
Click the Select Parent Frame button in the toolbar.
Choose Tools > Property Inspector.
Under the Repeating Frame node, set the Vert. Space Between Frames (inches) property to the amount of blank space to leave between row in the report output (e.g., 0.25).

3.9.13.5 Adding blank lines between groups of rows

To add blank lines between groups of rows:

Create a summary column (see Section 3.8.8, "Creating a summary column"). On the Totals page of the Data Wizard (or Report Wizard), select any field that appears in each row of output, and Count as the calculation. On the Fields page, remove the summary from the displayed fields column.
Create a user parameter named SPACE (see Section 3.11.2, "Creating a user parameter"). In the Property Inspector, under the Parameter node, set the Datatype property to Number and the Initial Value property to the number of records you want in each group (e.g., 5).
In the Paper Layout view, click the Flex On and Confine Off buttons in the toolbar.
Click the repeating frame for the group, then drag the handle on the bottom of the frame to create a space slightly larger than the space you want to add between groups of rows in the report output.
Choose Tools > Property Inspector to display the Property Inspector for the repeating frame.
Under the General Layout node, set the Vertical Elasticity property to Variable.
Click the Rectangle tool in the tool palette.
Draw a rectangle in the space under the record.
Double-click the rectangle object.
In the Property Inspector, under the Advanced Layout node, double-click the Format Trigger property field (labeled...) to display the PL/SQL Editor.
In the PL/SQL Editor, define the PL/SQL for the format trigger. For example, the following PL/SQL code inserts blank space between groups of rows (displays a boilerplate rectangle when the row count divided by the value of SPACE leaves no remainder):
```
function spacing return BOOLEAN is
begin
  if :CountENAMEPerReport MOD :SPACE = 0 then
    return (true);
  else 
    return (false);
  end if;
end;
```
Hide the rectangle object:
- on the Fill Color palette, click No Fill
- on the Line Color palette, click No Line

3.9.14 Modify the Page Layout

This section provides procedures for the following tasks that you may perform as you modify the page layout of your report:

3.9.14.1 Adjusting margins

To adjust margins:

In the Paper Layout view, click the Edit Margin button in the toolbar.

Note:

Click the margin border, then drag a handle to the desired position. You can adjust the margin on all four sides of a report.

3.9.14.2 Adding a page break

To add a page break:

In the Paper Layout view, click any field that is part of the frame at which you want to insert a page break.
Click the Parent Frame in the toolbar.
Choose Tools > Property Inspector.
To set a page break...
- before or after the selected frame: under the General Layout node, set the Page Break Before property to Yes for the object you want placed on a separate page.
- after a certain number of rows of output on each page: under the Repeating Frame node, set the Maximum Records per Page property to the number of rows you want to display on each page.
To display icons that identify where page breaks are set, choose View > Page Breaks in the Paper Layout view.

Usage notes

If you want to set a page break at a specific row of report output, you can create a hidden object (for example, a rectangle with No Fill and No Line) that formats only at the specific row. If you set the Page Break Before property to Yes for the rectangle object, you will get a page break after the row.

3.10 Work with Report Sections

This section provides procedures for the following tasks that you may perform as you work with report sections:

3.10.1 Displaying a section layout view

To display the layout view for a report section:

In the Paper Layout view, do either of the following:
- Choose View > Layout Section, then select the section you want to view and check whether or not you want to view or edit the margin area.
- In the toolbar, click the Header Section, the Main Section, or Trailer Section buttons to view the Header Section, Main Section, or Trailer Section, respectively; click the Edit Margin button to view or edit the margin area.

3.10.2 Creating a default layout for a section

To create a default layout for a report section:

In the Paper Layout view, display the layout view for the section.
Choose Tools > Report Wizard.
Follow the wizard to create a default layout for your report.
To create a layout or objects in the margin area of a section, click the Edit Margin button in the toolbar to view/edit the margin area.
To add another layout section to the current layout, create an additional report layout (see Section 3.5.5, "Creating an additional report layout").

Make further modifications to the default layout manually in the Paper Layout view.

Caution:

Set properties for the section, as desired.

3.11 Work with Parameters and the Parameter Form

This section provides procedures for the following tasks that you may perform as you work with parameters and the Parameter Form:

3.11.1 Using a pre-defined system parameter

To use a system parameter:

In the Object Navigator, expand the Data Model node, then expand the System Parameters node.
Double-click the properties icon for the desired parameter to display the Property Inspector.
Under the Parameter node, set the Initial Value property, if required.
To validate the parameter's value at runtime, set the Validation Trigger property by clicking the... button to display the PL/SQL Editor and define the PL/SQL to be triggered at runtime.

3.11.2 Creating a user parameter

Note:

Reports Builder automatically creates a user parameter when you use a bind parameter reference in a query.

To create a user parameter:

In the Object Navigator, expand the Data Model node, then click the User Parameters node.
Click the Create button in the toolbar.
Double-click the properties icon for the new parameter to display the Property Inspector.
Under the General Information node, replace the Name property with the desired parameter name.
Under the Parameter node, set the Initial Value and List of Values properties, if required.
To validate the parameter's value at runtime, set the Validation Trigger property by clicking property field to display the PL/SQL Editor and define the PL/SQL to be triggered at runtime.

3.11.3 Creating a list of values (LOV) for a parameter

Tip:

If you define a parameter in a template, you must apply the template to a report in order to select that parameter for the Runtime Parameter Form.

To create a LOV for a parameter:

In the Object Navigator, expand the Data Model node, then the User Parameters node.
Double-click the properties icon for the parameter for which you want to create a LOV to display the Property Inspector.
Under the Parameter node, double-click the List of Values property field to display the Parameter List of Values dialog box.
Select the type of list that you want to create:
- For Static Values, type a value in the Value text box and click Add. Repeat for each value you want to add. (Click Remove to delete items from the list)
- For SELECT Statement, type a query to populate the list of values. You can select more than one column to display in the LOV, where the first column contains the value to be assigned to the parameter. The LOV displays columns in the order specified in the query.
If you want the parameter value to be restricted to only those in the LOV, select the Restrict List to Predetermined Values check box. To display a combo box that allows users to edit values or type a different value in the Runtime Parameter Form, clear the check box.

If you do not want the first column (which contains the parameter value) of your query displayed in the LOV, select the Hide First Column check box. If there is no need to preserve the confidentiality of the first column, clear the check box.

Caution:

If you send the report output to an HTML file, either from Reports Builder or running it in your Web browser, the value of the first column will be visible in the HTML source, even if Hide First Column is selected.

If you run the report from a Web browser and the list of values is unrestricted, the HTML Parameter Form will display a text field instead of a combo box, and a list of static values that you can copy and paste into the text field. In this case, the first column will always be shown in the Parameter Form, even if Hide First Column is selected.

Click OK.

3.11.4 Validating a parameter value at runtime

To validate a parameter value at runtime:

In the Object Navigator, expand the Data Model node, then the System Parameters or User Parameters node.
Double-click the PL/SQL icon for the parameter for which you want to add a PL/SQL validation trigger.
In the PL/SQL Editor, define the PL/SQL to be triggered at runtime.

3.11.5 Creating a default Parameter Form

To create a default Parameter Form:

Choose Tools > Parameter Form Builder.
Click OK to display the Paper Parameter Form view with the default Parameter Form.

3.11.6 Selecting parameters to include in the Runtime Parameter Form

To select parameters to include in the Runtime Parameter Form:

Choose Tools > Parameter Form Builder.
In the Parameter Form Builder, click the parameters you want to include in the Runtime Parameter Form.
Modify the parameter labels as desired.
Click OK to display the Paper Parameter Form view.

3.11.7 Displaying the Parameter Form at runtime

To display the Runtime Parameter Form when you run your report:

Choose Edit > Preferences to display the Preferences dialog box.

On the Runtime Settings page, make sure that the Parameter Form check box is selected.

Note:

The Parameter Form can be used only for paper reports. If you display your paper-based report on the Web, you can create an HTML Parameter Form by adding HTML header and footer tags (using either the Before Form Value property and After Form Value property or the SRW.SET_BEFORE_FORM_HTML and SRW.SET_AFTER_FORM_HTML procedures). However, if you design a JSP-based Web report with a Parameter Form in Reports Builder, be aware that Web reports that use JSPs do not support the display of the Runtime Parameter Form at runtime. For more information, see Section 1.9.4, "About Parameter Forms for Web reports".

3.11.8 Adding more pages to the Runtime Parameter Form

To add more pages to the Runtime Parameter Form:

In the Object Navigator, double-click the properties icon next to the report name.
In the Property Inspector, under the Parameter Form Window node, set the Number of Pages property as desired.

3.11.9 Passing parameters to reports running in batch mode

To pass parameters (e.g., data ranges) to reports running in batch mode:

Use bind variables in your query to restrict the query, and use the command line parameters to pass the values to the query. For example:

Query:

 SELECT * FROM EMP WHERE HIREDATE BETWEEN
 :FROM_DATE AND :END_DATE

Runtime:

RWRUN REP1 SCOTT/TIGER FROM_DATE='12-JUN-92' 
END_DATE='24-JUN-92'

3.11.10 Creating an HTML Parameter Form header using PL/SQL

See Section 3.6.7.2.5, "Creating an HTML Parameter Form header using PL/SQL"

3.11.11 Creating an HTML Parameter Form footer using PL/SQL

See Section 3.6.7.2.6, "Creating an HTML Parameter Form footer using PL/SQL"

3.11.12 Creating HTML Parameter Form input or select events

To create an HTML Parameter Form field with input or select events:

In the Object Navigator, double click the view icon next to the Paper Parameter Form node to display the Paper Parameter Form view.
Create or edit a Parameter Form field (see Section 3.9.1.1, "Creating a field object").
Double-click the field object to display the Property Inspector.
Under Web Settings, set the Additional Attributes (HTML) property to a valid JavaScript event handler.

Note:
In some cases, for example, when raising messages it may be necessary to type JavaScript code in the Before Form trigger.

To insert the JavaScript code in the Before Form trigger:

In the Object Navigator, double-click the properties icon next to the report name to display the Property Inspector.
Under Report Escapes, set the Before Form Type property to Text (if you will type the Javascript) or File (if you will import the JavaScript from a file).
Set the Before Form Value property by clicking the ... button to either type JavaScript in the dialog box or select an HTML file with the JavaScript to import.

Example 1: Data input validation

This example shows how to set Parameter Form fields for input validation when the report is run via the Web. Doing so will raise a message whenever an end user enters invalid data in the Parameter Form field.

In the Paper Parameter Form view, create a Parameter Form field called PF_DEPTNO.
Double-click the field object to display the Property Inspector, and set properties:
- Under Parameter Form Field, set the Source property to DEPTNO.
- Under Web Settings, set the Additional Attributes (HTML) property to the following JavaScript event handler:
```
onChange="checkIt(this.form)"
```

In the Object Navigator, click (the properties icon) next to your report name to display the Property Inspector, and set properties:

Under Report Escapes, set the Before Form Type property to Text.

Set the Before Form Value property the following JavaScript code:

<SCRIPT LANGUAGE = "JavaScript">
function isNumber(inputStr){
   for (var i = 0; i < inputStr.length; i++) {
      var oneChar = inputStr.charAt(i)      
      if (oneChar < "0" || oneChar > "9") {
         alert("Please enter a numeric value.")
         return false
         }
     }
    return true
   }
function checkIt(form) {
   inputStr = form.DEPTNO.value
   if (isNumber(inputStr)) {
      // statements if true
      } 
      else {
         form.numeric.focus()
         form.numeric.select()
      }
   }
</SCRIPT>

At runtime, if the end user enters the department name in the Runtime Parameter Form rather than the department number when running the report via the Web, the following message is raised:

Please enter a numeric value.

Example 2: Select validation

This example shows you how to set Parameter Form fields for select validation when the report is run via the Web. Doing so will raise a message whenever an end user selects Printer from the DESTYPE list of values in the Runtime Parameter Form.

In the Paper Parameter Form view, create a Parameter Form field called PF_DESTYPE.
Double-click the field object to display the Property Inspector, and set properties:
- Under Parameter Form Field, set the Source property to DESTYPE.
- Under Web Settings, set the Additional Attributes (HTML) property to the following JavaScript event handler:
```
onChange="isPrinter(this.form)"
```

In the Object Navigator, click (the properties icon) next to your report name to display the Property Inspector, and set properties:

Under Report Escapes, set the Before Form Type property to Text.

Set the Before Form Value property the following JavaScript code:

<SCRIPT LANGUAGE = "JavaScript">
function isPrinter(form) {
  if( form.DESTYPE.options[form.DESTYPE.selectedIndex].value 
       == 'Printer')
    alert("Please be sure that your print is installed and running.")
    return true}
   }
</SCRIPT>

At runtime, if the end user selects PRINTER from a list of values in the DESTYPE field, the following message is raised:

Please be sure that your print is installed and running.

3.11.13 Changing HTML Parameter Form input to uppercase

To change data input values default to uppercase upon entry in a Parameter Form field:

Choose Tools > Parameter Form Builder.
In the Parameter Form Builder, create a Parameter Form field called PF_DESFORMAT, with a source of DESFORMAT.
Choose Tools > Property Inspector.
In the Property Inspector, under Web Settings, set the Additional Attributes (HTML) property to:
```
onChange="this.value=this.value.toUpperCase()"
```

Example: Default input to uppercase

This example specifies that data input values default to uppercase upon entry in a Parameter Form field.

In the Paper Parameter Form view, create a Parameter Form field called PF_DESTYPE.
Double-click the field object to display the Property Inspector, and set properties:
- Under Parameter Form Field, set the Source property to DESFORMAT.
- Under Web Settings, set the Additional Attributes (HTML) property to the following JavaScript event handler:
```
onChange="this.value=this.value.toUpperCase()"
```

At runtime, if the end user enters pdf as the destination format, the value will change to uppercase (i.e., PDF) in the Runtime Parameter Form when running the report via the Web.

3.12 Define a Template

This section provides procedures for the following tasks that you may perform as you define a report template:

3.12.1 Creating a template

To create a template:

In the Object Navigator, click the Templates node, then click the Create button in the toolbar.
Define default template attributes for all report styles.
Optionally, define override template attributes for a selected report style.
To define system parameters, user parameters, report triggers, program units, and attached libraries for the template, double-click the associated nodes in the Object Navigator.

3.12.2 Defining default template attributes

To define default attributes for report objects in a template that applies to all report styles:

In the Object Navigator, expand the Templates node, then the Paper Layout node, then the Section node, then the Body node, then the Default node.
To change default properties for the overall layout and spacing for report styles, double-click the properties icon next to the Default node to display the Property Inspector.

To change default properties for frames, field labels/headings, fields, summary labels, and summaries (totals), fully expand the Default node, then double-click the properties icons to display the Property Inspector.

Note:

The Paper Layout view is synchronized with the Object Navigator; when you select a layout object, the associated node is highlighted in the Object Navigator. To select the parent frame for a layout object, click the object, then click the Select Parent Frame button in the toolbar to select the parent frame.

In the Paper Layout view, select from the Report Style drop-down list to view the layout for individual report styles.
To make further changes for the currently displayed report style, either return to the Object Navigator, or double-click an object to display the Property Inspector.

Note:
Any changes you make for an individual report style will override the attributes defined under the Default node.

3.12.3 Defining override template attributes

To define override attributes for a selected report style:

In the Object Navigator, expand the Templates node, then the Paper Layout node, then the Section node, then the Body node, then the Override node, then the node for the report style for which you want to define overrides (e.g., Tabular, Group Left).
To change default properties for the overall layout and spacing for a section of the selected report style, double-click the properties icon next to the Section (Level n) node to display the Property Inspector.
To create more than the default number of sections for a particular report style, click a Section (Level n) node, then click the Create button in the toolbar.

Note:
Sections are mapped to groups in a report. For more information, see About template attributes.
To delete a section for a particular report, click a Section (Level n) node, then click the Delete button in the toolbar.

Note:
You can only delete nodes down to the minimum requirement for the current report style.

For each section for which you want to define overrides, fully expand the Section (Level n) node, then double-click the properties icons to display the Property Inspectors for the frames, field labels/headings, fields, summary labels, and summaries (totals).

Note:

In the Paper Layout view, select from the Report Style drop-down list to view the layout for individual report styles.
To make further changes for the currently displayed report style, either return to the Object Navigator, or double-click an object to display the Property Inspector.

Note:
Any changes you make for an individual report style will override the attributes defined under the Default node.

3.12.4 Applying a template to a report

To apply a template to a report:

In the Object Navigator, click the report or report section to which you want to apply a template.

Note:

If you select the node for the entire report in the Object Navigator, the template will be applied to the Main section of the report by default. To apply the template to a specific section of your report, select the node for that section in the Object Navigator.

Choose Tools > Report Wizard.
On the Template page, select a pre-defined template, or click Template file, and click Browse to open the desired template.
Click Finish to apply the template to the current report section.

3.12.5 Formatting the report title in a template

To set default attributes (such as font and color) for the title in a selected template:

In the Object Navigator, expand the Templates node, then the Paper Layout node, then the Section node, then the Body node, then the Default node.
Double-click the properties icon next to the Default node to display the Property Inspector.
Under Title, modify the properties as desired.

To set the placement and override attributes (such as font and color) for the title in a selected template:

In the Paper Layout view of the template, create a boilerplate text object for the title in the margin.
Size and position the title, and modify the color and text attributes as desired.
Double-click the title object to display its Property Inspector.
Under General Information, set the Name property to B_OR$REPORT_TITLE.

3.12.6 Adding items and objects to a template

To add items and objects to a template:

In the Paper Layout view of the Template Editor, click the margin button in the toolbar to display the margin area.
Create objects in the margin of your template. You cannot create objects in the body of a template.

3.12.7 Modifying objects in the template margin

To modify objects in the margin of a template:

In the Paper Layout view of the Template Editor, click the Edit Margin button in the toolbar.
Modify objects in the margin of your template.

3.12.8 Modifying the color, pattern, or border of body objects in a template

Template objects can be modified using the same techniques as report objects. To change the color or pattern of objects in a template, however, you use the template Property Inspector, rather than the Color tools in the tool palette:

The Fill Pattern property defines the pattern to use for the space enclosed by the objects. You can define the background and foreground colors of the fill pattern using the Foreground Color and Background Color properties.
The Edge Pattern property defines the pattern to use for the borders of the objects. You can define the background and foreground colors of the edge pattern using the Edge Foreground Color and Edge Background Color properties.
The Text Color property specifies the text color to use for the object(s).

3.12.9 Adding a template to the pre-defined templates list

To add your own template to the list of predefined templates in the Report Wizard:

In a text editor (e.g., Wordpad), open the preferences file. (On Windows, open ORACLE_HOME\CAUPREFS.ORA (user preferences) if it exists; otherwise, open ORACLE_HOME\CAGPREFS.ORA (global preferences). On UNIX, open your_home_directory/prefs.ora (user preferences) if it exists; otherwise, open ORACLE_HOME/tools/admin/prefs.ora (global preferences).)
Scroll down to the template descriptions identified by Reports.xxx_Template_Desc (where xxx specifies a report style: Tabular, BreakAbove, BreakLeft, FormLetter, Formlike, MailingLabel, Matrix, MatrixBreak).
For each report style for which the template is defined:
- To the Reports.xxx_Template_Desc list, add the description that you want to appear on the Template page of the Report Wizard.
- To the corresponding Reports.xxx_Template_File list, add the file name of your template in the same position as the addition you made to the description list.
Copy the template file (filename.tdf) to ORACLE_HOME/REPORTS/TEMPLATES.

To include a sample image of your template in the Report Wizard:

Copy or create the .bmp file you want to use as the image for the template and name it yyy.bmp, where yyy is the file name of your template.
Copy each .bmp file to ORACLE_HOME/REPORTS/TEMPLATES.

3.13 Use PL/SQL in a Report or Template

This section provides procedures for the following tasks that you may perform as you use PL/SQL in a report or template:

3.13.1 Using a built-in package

To use a built-in package:

Choose Tools > PL/SQL Editor to display the PL/SQL Editor.
In the Object Navigator, expand the Built-in Packages node, then the package you want to use.
Right-click a procedure, function, or exception and choose Paste Name or Paste Arguments to copy a call to the package into your PL/SQL code.

3.13.2 Working with the PL/SQL Editor

This section provides procedures for the following tasks that you may perform as you work with the PL/SQL Editor:

3.13.2.1 Defining PL/SQL

To define PL/SQL:

In the PL/SQL Editor, type or edit the PL/SQL code for the program unit.
Click Compile.
If necessary, click an error to navigate to its location in the source code.

Tip:
Check for missing semicolons at the end of statements, or misspelled syntax.
When the code compiles successfully, click Close.

3.13.2.2 Searching and replacing text in a program unit

To search and replace text in a program unit:

In the Object Navigator, expand the Program Units node.
Double-click the PL/SQL Editor view icon next to the program unit you want to edit.
In the PL/SQL Editor, place your cursor where you wish to begin the search.
Choose Edit > Find and Replace.
In the dialog box, type your search criteria, and, optionally, the replace string. You can supply either a text string or a regular expression for your search text.
Click Search.
Upon locating an instance of the search criteria, click Replace to replace a single instance, Replace All to replace all instances, or edit the text directly in the PL/SQL Editor.
Click Search to proceed to the next instance, or click Cancel to close the dialog box.
When you have finished replacing all text in the program unit, click Compile to recompile the program unit.

Tip:
If you are going to be replacing text in multiple program units, you can recompile them all at once when you are finished.
When the code compiles successfully, click Close.

Tip:
If your edits are extensive, you may want to click Apply to save your changes incrementally, without having to compile.

3.13.2.3 Editing a program unit

To edit a program unit:

In the Object Navigator, expand the Program Units node.
Double-click the PL/SQL Editor view icon for the program unit you want to edit.
In the PL/SQL Editor, edit the PL/SQL for the program unit.

3.13.2.3.1 Editing features in the PL/SQL Editor

The editing features in the PL/SQL Editor (and Stored PL/SQL Editor) include:

Table 3-7 Editing features of PL/SQL Editor

Editing Feature	Description
Automatic indent	When you press the Enter key at the end of a line, the next line is automatically indented.
Color syntax highlighting	Keywords, comments, strings, and symbols such as := and \|\| are colored differently.
Column and line selection	You can select columns of text as well as lines of source code. To select a column, press the ALTkey, then click and drag horizontally.To select a line, click on the extreme left margin of the line in the Source pane.
Drag and drop text manipulation	Selected text may be copied or moved by dragging and dropping.To copy text, press the CTRL key and drag the selected text.To move text, simply drag the selected text.
Indent/Outdent lines	The Indent/Outdent commands in the Edit menu enable you to indent or outdent selected source lines.
Multiple split views	You can create up to four separate views of the current program unit.To create horizontal views, place the cursor on the split bar at the top of the vertical scroll bar. Then click and drag the split bar down.To create vertical views, place the cursor on the split bar at the far left of the horizontal scroll bar. Then click and drag the split bar to the right.To remove a horizontal or vertical view, double-click the split bar that separates the views.
Printing	Choose File Print to print the current program unit.
Unlimited undo/redo	The Undo/Redo commands on theEdit menu enable you to undo or redo changes as far back as the last save operation.

Note:

These features are available in Microsoft Windows only. In UNIX, you can print the current program unit, and you can use TAB/Shift-TAB to indent/outdent selected lines.

3.13.2.3.2 Using the keyboard in the PL/SQL Editor

The following keyboard actions are supported when using the PL/SQL Editor (and Stored PL/SQL Editor) in Microsoft Windows and UNIX:

Table 3-8 Using the keyboard in the PL/SQL Editor

To do the following	Use Keystroke	On Platform
Move cursor left one character	Left arrow key	Windows and UNIX
Select character as cursor moves left	Shift + Left arrow key	Windows and UNIX
Move cursor right one character	Right arrow key	Windows and UNIX
Select character as cursor moves right	Shift + Right arrow key	Windows and UNIX
Copy selected characters or words	Ctrl + C	Windows and UNIX
Cut selected characters or words	Ctrl + X	Windows and UNIX
Paste from the clipboard	Ctrl + V	Windows and UNIX
Delete character on the right of current cursor position, or delete selected characters or words	Delete key	Windows and UNIX
Delete character on the left of current cursor position	Backspace	Windows and UNIX
Move cursor to the end of the program unit	Ctrl + End	Windows
Move cursor to the start of the program unit	Ctrl + Home	Windows
Select source lines from the current cursor position to the end of the program unit	Ctrl + Shift + End	Windows
Select source lines from the current cursor position to the start of the program unit	Ctrl + Shift + Home	Windows
Move cursor to the end of current line	End	Windows
Move cursor to the start of current line	Home	Windows
Select characters from the current cursor position to the end of the current line	Shift + End	Windows
Select characters from the current cursor position to the start of the current line	Shift + Home	Windows
Indent selected line, or indent characters on the right of current cursor position	Tab Key	Windows and UNIX
Outdent selected line	Shift + Tab key	Windows and UNIX
Move cursor down one line	Down arrow key	Windows and UNIX
Select line as cursor moves down	Shift + Down arrow key	Windows and UNIX
Move cursor up one line	Up arrow key	Windows and UNIX
Select line as cursor moves up	Shift + Up arrow key	Windows and UNIX
Scroll down the program unit by the number of lines that are shown in the Source pane	PageDown key	Windows and UNIX
Scroll down the program unit by the number of lines that are shown in the Source pane and selects the lines at the same time	Shift + PageDown key	Windows and UNIX
Scroll up the program unit by the number of lines that are shown in the Source pane	PageUp key	Windows and UNIX
Scroll up the program unit by the number of lines that are shown in the Source pane and selects the lines at the same time	Shift + PageUp key	Windows and UNIX
Undo most recent action	Ctrl + Z	Windows
Revert most recent undo action	Ctrl + Y	Windows
Move cursor left one character	Left arrow key	Windows and UNIX
Select character as cursor moves left	Shift + Left arrow key	Windows and UNIX
Move cursor right one character	Right arrow key	Windows and UNIX

3.13.2.3.3 Using the mouse in the PL/SQL Editor

The following table describes the mouse actions that are supported when using the PL/SQL Editor (and Stored PL/SQL Editor) in Microsoft Windows:

Table 3-9 Using the mouse in the PL/SQL Editor

To do the following	Use mouse action
Select characters in a range	Click and drag the cursor from the first character to the last character in the range you wish to select. (also supported in UNIX) or... Click the first character, then press the Shift key, and click the last character in the range you wish to select.
Select word under cursor	Double-click the word. (also supported in UNIX) or... Press the Ctrl key, then click the word you wish to select.
Select words in a range	Press the Ctrl key, then click and drag the cursor from the first word to the last word in the range you wish to select.
Select a line	Place the cursor on the left margin of the line you wish to select. Click when the cursor changes to an arrow.
Select multiple lines	Place the cursor on the left margin of the first line you wish to select. When the cursor changes to an arrow, click and drag the cursor to the last line you wish to select.
Select columns of text	Press the Alt key, then click and drag the cursor from the first column to the last column in the range you wish to select.
Move selected text	Select the text first. Then click and drag the selected text to its new position.
Copy selected text	Select the text first. Then press the Ctrl key, and drag the selected text to the location where you want to place a copy and release the mouse.
Split window into two horizontal views	Double-click the split bar at the top of the vertical scroll bar. or... Click and drag the split bar at the top of the vertical scroll bar.
Split window into two vertical views	Double-click the split bar at the extreme left of the horizontal scroll bar. or... Click and drag the split bar at the extreme left of the horizontal scroll bar.
Adjust relative size of split views	Click and drag the split bar that separates the views.
Remove split views	Double-click the split bar that separates the views. or... Click and drag the split bar to the edge of the window. To remove all four views at once, double-click the intersection where the split bars meet or drag it to any corner of the window.
Select characters in a range	Click and drag the cursor from the first character to the last character in the range you wish to select. (also supported in UNIX) or... Click the first character, then press the Shift key, and click the last character in the range you wish to select.
Select word under cursor	Double-click the word. (also supported in UNIX) or... Press the Ctrl key, then click the word you wish to select.
Select words in a range	Press the Ctrl key, then click and drag the cursor from the first word to the last word in the range you wish to select.
Select a line	Place the cursor on the left margin of the line you wish to select. Click when the cursor changes to an arrow.
Select multiple lines	Place the cursor on the left margin of the first line you wish to select. When the cursor changes to an arrow, click and drag the cursor to the last line you wish to select.
Select columns of text	Press the Alt key, then click and drag the cursor from the first column to the last column in the range you wish to select.
Move selected text	Select the text first. Then click and drag the selected text to its new position.

3.13.2.4 Inserting syntax into the PL/SQL Editor

To copy syntax into the PL/SQL Editor:

Make sure the PL/SQL Editor or Stored PL/SQL Editor is the current (most recently selected) window.
Place the cursor in the editor where you want to insert the syntax, then choose Tools > Syntax Palette.
In the Syntax Palette, click the PL/SQL tab or the Built-ins tab.
Choose a PL/SQL category or a built-in package from the drop-down list.

When you click a category or a package, the PL/SQL language elements or PL/SQL constructs that are available for selection appear in the list area below.
Choose a PL/SQL language element or construct in the list area.

When you click a PL/SQL language element or construct, the syntax appears in the display area that is below the list area.
Click Insert to copy the selected syntax.

The selected PL/SQL language element or construct is inserted into the active editor at the current cursor position.
Replace all lowercase items that are not comments with the appropriate values. Items within comments are optional. Reserved words are in uppercase.

Note:
You can also double-click a PL/SQL language element or construct in the list area to insert the syntax into an editor.

3.13.3 Creating or editing report-level or template-level PL/SQL

This section provides procedures for the following tasks that you may perform as you create or edit report-level or template-level PL/SQL:

3.13.3.1 Creating a local program unit

To create a local (client-side) program unit:

In the Object Navigator, click the Program Units node.
Click the Create button in the toolbar.
In the dialog box, type a name for the program unit in the Name text box.
If your program unit is not a procedure (a PL/SQL subprogram that performs a specified sequence of actions), click one of the following:
- Function (a PL/SQL subprogram that performs a specified sequence of actions, and then returns a value)
- Package Spec (datatypes and subprograms that can be referenced by other program units)
- Package Body (implementation of the package, which may include private subprograms and datatypes; optional if the package consists only of declarations)
Click OK.
In the PL/SQL Editor, define the PL/SQL for the program unit.

3.13.3.2 Creating a stored program unit

To create a stored (server-side) program unit:

In the Object Navigator, double-click the Database Objects node. If this node is disabled, the Connect dialog box displays so you can establish a database connection.
Expand the subnode that corresponds to the database user name you used to log in to the database to show the PL/SQL Stored Program Units node.
Click the PL/SQL Stored Program Units node, then click the Create button in the toolbar.
In the dialog box, type a name for the program unit in the Name text box.
If your program unit is not a procedure (a PL/SQL subprogram that performs a specified sequence of actions), click one of the following:
- Function (a PL/SQL subprogram that performs a specified sequence of actions, and then returns a value)
- Package Spec (datatypes and subprograms that can be referenced by other program units)
- Package Body (implementation of the package, which may include private subprograms and datatypes; optional if the package consists only of declarations)
- Type Spec (declares the name, variables (attributes) and member subprograms (methods) for an object type or collection type)
- Type Body (implementation of the member methods (functions and procedures) defined in the type specification for an object type. For each method specified in an object type, there must be a corresponding method body)
Click OK.
In the Stored PL/SQL Editor, select a database owner name from the Owner drop-down list to indicate where the program unit will be stored in the database, then define the PL/SQL for the stored program unit.

3.13.3.3 Deleting a program unit

Note:

If you delete a PL/SQL package, function, or procedure, you must also delete all references to it in your report. Otherwise, you will get an error when you compile, generate, or run the report.

To delete a program unit:

In the Object Navigator, expand the Program Units node.
Click the program unit you want to delete.
Click the Delete button in the toolbar.
In the message dialog box, click Yes to confirm the deletion.

3.13.3.4 Moving a program unit between client and database server

To move a program unit from the client to the database server:

In the Object Navigator, double-click the Database Objects node. If this node is disabled, the Connect dialog displays so you can establish a database connection.
Expand the subnode that corresponds to the database user name you used to log in to the database to show the PL/SQL Stored Program Units node.
In the Reports section of the Object Navigator, expand the Program Units node.
Click the program unit you want to store in the database, and drag it from the Program Units node to the Stored Program Units subnode.
Release the mouse button to insert a copy of the program unit on the server.

To move a program unit from the database server to the client:

In the Object Navigator, double-click the Database Objects node. If this node is disabled, the Connect dialog box displays so you can establish a database connection.
Expand the subnode that corresponds to the database user name you used to log in to the database.
Expand the Stored Program Units node.
Click the stored program unit you wish to move to the client and drag it from the Stored Program Units node to the Program Units node in the Reports section of the Object Navigator.
Release the mouse button to insert a copy of the program unit on the client.

3.13.3.5 Creating a report trigger

To create a report trigger:

In the Object Navigator, expand the Report Triggers node.
Double-click the PL/SQL icon for the trigger you want to create.
In the PL/SQL Editor, define the PL/SQL for the report trigger.

3.13.3.6 Deleting a report trigger

To delete a report trigger:

In the Object Navigator, expand the Report Triggers node.
Double-click the PL/SQL icon for the trigger you want to delete.
In the PL/SQL Editor, drag to select the PL/SQL code.
Choose Edit > Delete.

3.13.3.7 Creating a database trigger

To create a database trigger:

Choose Tools > Database Trigger Editor.
In the Database Trigger Editor, choose a user name (schema) from the Table Owner drop-down list.
Choose a table name from the Table: drop-down list.

The Table drop-down list shows a list of table names owned by the user shown in the Table Owner field. If you select a user name other than your own in the Table Owner field, the Table drop-down list shows only the tables to which you have been granted access.
(For views only) To define an INSTEAD OF trigger for an object view, click the arrow next to Table: to display a drop-down list and choose View: Then choose a view name from the View drop-down list.
Click New to create a new database trigger.

A unique trigger name (with respect to other triggers in the same schema) is automatically assigned to the new trigger in the Name drop-down list. You can modify the trigger name.

The Name drop-down list displays a list of trigger names associated with the table (or view) shown in the Table (or View) field. The Name drop-down list displays only the names of the database triggers associated with the tables to which you have access.
After specifying the trigger options and action, click Save to compile the trigger.

3.13.4 Creating or editing object-level PL/SQL

This section provides procedures for the following tasks that you may perform as you create or edit object-level PL/SQL:

3.13.4.1 Creating or editing a format trigger

To create or edit a format trigger using the Property Inspector:

In the Paper Design view, double-click the object for which you want to create or edit a format trigger to display the Property Inspector.
Under Advanced Layout, set the Format Trigger property by clicking the ... button to display the PL/SQL Editor.
Define the PL/SQL for the format trigger.

To create or edit a format trigger using the Object Navigator:

In the Object Navigator, expand the Paper Layout node, then expand the node that contains the object for which you want to create or edit a format trigger.
Double-click the PL/SQL icon next to the object for which you want to create or edit a format trigger to display the PL/SQL Editor.
Define the PL/SQL for the format trigger.

3.13.4.2 Creating or editing a group filter

To create or edit a group filter:

In the Data Model view, double-click the title bar of the group to display the Property Inspector.
Scroll to the Group node.
To display the first n records for the group, set the Action Type property to First and set the Number of Records property to n.
To display the last n records for the group, set the Action Type property to Last and set the Number of Records property to n.
To create your own filter using PL/SQL, set the Action Type property to PL/SQL and set the PL/SQL Filter property by clicking ... to display the PL/SQL Editor to define the PL/SQL for the filter.
Set other properties as desired.

3.13.5 Creating or editing an external PL/SQL library

This section provides procedures for the following tasks that you may perform as you create or edit an external PL/SQL library:

3.13.5.1 Creating an external PL/SQL library

To create an external PL/SQL library:

In the Object Navigator, click the PL/SQL Libraries node.
Click the create button in the toolbar.

The newly created library (initially named LIB_xxx) is automatically opened. Once a library has been created, its contents can be modified by inserting or removing program units, or attaching other libraries.

3.13.5.2 Adding a program unit to an open library

To add a program unit to an open library:

Perform one of the following steps in the Object Navigator, depending on your current state:
- if the library is open, expand the library node
- if the library is not currently open, click the PL/SQL Libraries node and choose File > Open to open the library
Under the Reports node, expand the Program Units node.
Drag the program unit you want to add to the library below the library's Program Units node.
Release the mouse button to insert a copy of the program unit in the library.

3.13.5.3 Editing a program unit in a PL/SQL library

To edit a program unit in a PL/SQL library:

In the Object Navigator, expand the PL/SQL Libraries node, then the library node for the program unit.
Under the library's Program Units node, double-click the PL/SQL Editor view icon for the program unit you want to edit.
In the PL/SQL Editor, edit the PL/SQL for the program unit.

3.13.5.4 Removing a program unit from a PL/SQL library

To remove a program unit from a PL/SQL library:

In the Object Navigator, expand the PL/SQL Libraries node, then the library node for the program unit.
Under the open library's Program Units node, click the library program unit you want to delete.
Click the Delete button in the toolbar.
In the message box, click Yes.

3.13.5.5 Attaching a PL/SQL library

To attach a PL/SQL library:

In the Object Navigator, click the Attached Libraries node.
Click the Create button in the toolbar.
In the Attach Library dialog box, type the name of the external PL/SQL library in the Library text box, or click Browse to search for the external PL/SQL library you want to reference.
Click Attach.

Restrictions

If Reports Builder cannot find the specified library, a warning will be raised when you accept the dialog box, save the report, or open the report. If you try to run the report or compile the PL/SQL in it, an error will be raised.
The Attached Libraries list is saved. The next time you open the report or library the list will have the same contents it did when you last saved the report.
If an external library references another library, you must attach both libraries to the report even if the first library already has the second one attached.

3.13.5.6 Converting external PL/SQL libraries

To convert one or more report definitions or PL/SQL libraries from one storage format to another:

On the command line, type ORACLE_HOME\BIN\rwconverter, followed by the report name and desired arguments. See the Reference section of the Reports Builder online help for more information about rwconverter.

3.13.6 Compiling and running program units

This section provides procedures for the following tasks that you may perform as you compile and run program units:

3.13.6.1 Compiling a single program unit

To compile a single program unit:

In the Object Navigator, under the Program Units node, click the program unit you want to compile (PL/SQL subprogram, report trigger, formula, group filter, format trigger, or validation trigger).

Choose Program > Compile > Selection to compile the selected program unit, regardless of its current compilation status.

Note: An uncompiled program unit is indicated by an asterisk (*) after its name under the Program Units node in the Object Navigator. When you make changes to a program unit, dependent program units lose their compiled status.:

In the Compile dialog box, click any error, then click GoTo to navigate to the source of the error in the program unit.

Tip:
Check for missing semicolons at the end of statements, or misspelled syntax.

3.13.6.2 Compiling all program units

To compile all program units:

In the Object Navigator, click the report or library for which you want to compile all program units (including PL/SQL subprograms, report triggers, formulas, group filters, format triggers, and validation triggers).

Choose Program > Compile > All to compile all program units, regardless of their current compilation status.

Note:

An uncompiled program unit is indicated by an asterisk (*) after its name under the Program Units node in the Object Navigator. When you make changes to a program unit, dependent program units lose their compiled status.

In the Compile dialog box, click any error, then click Go To to navigate to the source of the error in the program unit.

Tip:
Check for missing semicolons at the end of statements, or misspelled syntax.
Compiling all uncompiled program units

To compile all uncompiled program units:

In the Object Navigator, click the report or library for which you want to compile all uncompiled program units (including PL/SQL subprograms, report triggers, formulas, group filters, format triggers, and validation triggers).

Choose Program > Compile > Incremental to compile all uncompiled program units.

Note:

In the Compile dialog box, click any error, then click Go To to navigate to the source of the error in the program unit.

Tip:
Check for missing semicolons at the end of statements, or misspelled syntax.

3.14 Debug a Report

This section provides procedures for the following tasks that you may perform as you debug a report:

For conceptual information that supports these tasks, see Section 2.10, "Debugging Tools".

3.14.1 Debugging a report

To debug a report:

Run the report in debug mode (described below) to check for logical errors in the report, and displays these as warnings at runtime, before displaying the report output. Running a report in debug mode is not the same as debugging a report using the PL/SQL Interpreter.
After identifying a problem area, choose Tools > PL/SQL Interpreter to display the PL/SQL Interpreter, and create the desired debug actions (see Section 3.14.3, "Setting a breakpoint" and Section 3.14.4, "Setting a debug trigger") to isolate the failing code to a specific region of number of statement.
Close the PL/SQL Interpreter to run your report.
Browse your debug actions and evaluate application information.
After narrowing the failing code to a specific region, use the PL/SQL Interpreter to implement and test possible code fixes.

3.14.2 Running a report in debug mode

To compile and run a report in debug mode:

Choose Edit > Preferences.
In the Preferences dialog box, on the Runtime Settings page, select Run Debug. Click OK.
Click the Run Paper Layout button in the toolbar to run the report.

To compile and run a report in debug mode from the command line:

On the rwbuilder or rwrun command line, specify RUNDEBUG=YES. For information about RUNDEBUG, see the Reference > Command Line section of the Reports Builder online help.

Usage notes

Running a report in debug mode specifies that you want extra runtime checking for logical errors in the report. It checks for things that are not errors but might result in undesirable output, and displays these as warnings at runtime, before displaying the report output. Running a report in debug mode is not the same as debugging a report using the PL/SQL Interpreter.

3.14.3 Setting a breakpoint

To set a breakpoint in the execution of your report:

If the PL/SQL Interpreter is not already displayed, choose Tools > PL/SQL Interpreter.
In the Object Navigator, single-click a compiled program unit node to display the program unit in the Interpreter Source pane.

Note:
Uncompiled program units are indicated by an asterisk (*) after their name.
Double-click an executable statement (a PL/SQL construct used for conditional, iterating, and sequential control, and for error handling. A semi-colon (;) must terminate every PL/SQL statement) where you wish to create the break point.

Tip:
You cannot place a breakpoint on a BEGIN, END, or NULL, statement, or on a comment.

The breakpoint is inserted and is indicated by B00n, where n is the number of the breakpoint. When you run the program unit, execution is suspended at the line just prior to the breakpoint.

3.14.4 Setting a debug trigger

To set a debug trigger:

If the PL/SQL Interpreter is not already displayed, choose Tools > PL/SQL Interpreter.
In the Object Navigator, single-click a compiled program unit node to display the program unit in the Interpreter source pane.

Note:
Uncompiled program units are indicated by an asterisk (*) after their name.
In the Source pane, select the line where you want to create the debug trigger, then choose Program > Debugging Triggers (or right-click in the Source pane and choose Debug Trigger).

Tip:
You cannot place a breakpoint on a BEGIN, END, or NULL, statement, or on a comment.
In the Trigger dialog box, define the trigger:
- If you want the trigger to fire at different location than the current program unit, select a location from the Location drop-down list.
- Type the debug trigger in the Trigger Body text box.
  
  For example, to create a debug trigger that interrupts program execution if the local NUMBER variable my_sal exceeds 5000, enter the following as the trigger body:
```
 IF Debug.Getn('my_sal') > 5000 THEN
  raise Debug.Suspend;
 END IF;
```
  Note:
  To create a debug trigger that contains multiple lines of text, include a BEGIN and an END statement around the code.
  
  You must raise the DEBUG.SUSPEND exception from the DEBUG package if you want the PL/SQL Interpreter to appear when this line is executed. Otherwise, Reports Builder executes the code silently and the PL/SQL Interpreter does not appear.
Click OK to create a debug trigger for the selected line.

Tip:
You can also create a debug trigger by entering commands in the PL/SQL Interpreter pane.

3.14.5 Browsing debug actions

To browse debug actions:

In the Object Navigator, expand the Debug Actions node. The Object Navigator displays a list of the debug actions associated with the current report.

or
In the PL/SQL Interpreter, use the DESCRIBE command to display information about a specific debug action.

For example, entering .DESCRIBE BREAK 1 in the Interpreter pane reveals the following about Breakpoint 1:
```
Breakpoint: 1
Program Unit: Procedure Body <programunit_name>
Line: 3
Enabled: YES
```

3.14.6 Editing a debug action

To edit a debug action:

In the Object Navigator, expand the Debug Actions node, then double-click the debug action icon to display the appropriate dialog box.
Edit the content or properties of the debug action in the dialog box.
Click OK.

3.14.7 Disabling and enabling debug actions

To disable/enable a debug action:

In the Object Navigator, under the Debug Actions node, right-click the desired debug action and choose Enable or Disable.

3.14.8 Deleting a debug action

To delete a debug action:

In the Object Navigator, expand the Debug Actions node, then click the action you want to delete.
Click the Delete button in the toolbar.

3.14.9 Running a program unit in the PL/SQL Interpreter

To run a program unit in the PL/SQL Interpreter:

If the PL/SQL Interpreter is not already displayed, choose Tools > PL/SQL Interpreter to display it.
At the Interpreter's PL/SQL> prompt, type the name of the program unit followed by a terminating semi-colon (;). If the program unit requires any arguments, be sure to supply them in parentheses. For example: getdata(SCOTT);
Press the Enter or Return key to produce one of the following reactions:
- Any output generated by the program unit is displayed at the command line, and the PL/SQL> prompt returns to indicate successful execution.
- The secondary prompt appears (+>) indicating you have not finished entering an executable statement. If you forgot the terminating semicolon, enter it now and press Enter or Return. Otherwise, right-click and choose New Prompt.
- Runtime errors are displayed at the command line, then the PL/SQL> prompt appears. You need to edit or debug your program unit.
- If you have set a breakpoint or debug trigger in the program unit, execution is suspended and a new prompt is displayed as: (debug n)PL/SQL>.

Usage notes

Running a program unit as described above only works for procedures (or packaged procedures), not for functions (since there's no variable for a return value to be returned to).

3.14.10 Inserting a Navigator pane in the PL/SQL Interpreter

To insert an Object Navigator pane in the PL/SQL Interpreter:

If the Interpreter is not already displayed, choose Program > PL/SQL Interpreter.
Choose View > Navigator Pane to insert the Object Navigator pane in the middle of the PL/SQL Interpreter.

Notice that the button bar is updated with new Object Navigator buttons.
Optionally, use the split bars to resize the proportions of the three panes.

3.14.11 Controlling program unit execution

Once you have inspected and modified the program state, you can resume or terminate execution using the following features:

Table 3-10 Program unit execution

Execution Feature

Description

STEP

You can use the STEP command to temporarily resume execution of an interrupted program. Control returns to the PL/SQL Interpreter after the specified set of statements have been executed. STEP Into or Over allows you to:

execute the next statement (optionally descending into subprogram calls)

resume execution until the current subprogram has returned

continue execution until the specified source location is reached

Use the GO command to resume program execution indefinitely--that is, until either the currently executing thread of execution terminates or it is interrupted again due to a debug action.

RESET

Use the RESET command to return control to an outer debug level without continuing execution in the current debug level. Thus, RESET effectively aborts execution at the current (and possibly higher) debug levels.

You can explicitly reset execution to any previous debug level, or you can simply reset to top level, which is the default.

Execute these commands from either the PL/SQL Interpreter toolbar or by typing the command in the PL/SQL Interpreter pane.

3.14.12 Stepping through the code

To step through the code:

Before proceeding, you must already have set a debug action such as a breakpoint, and run your program unit at the PL/SQL Interpreter PL/SQL> prompt to suspend execution.

To step to the next line of the suspended program unit:

Click the Step Into button in the PL/SQL Interpreter toolbar to execute the next line of executable code in the current program unit.

If the next executable line is a call to a nested subprogram (a program unit that is called from within another program unit), Step Into halts execution at the first line of the nested subprogram.

To step over a nested subprogram call in the suspended program unit:

Click the Step Over button in the PL/SQL Interpreter toolbar.

Step Over executes any calls to nested subprograms and then halts execution at the next executable line of the current program unit.

To step out of a nested subprogram and return to the outer program unit:

Click the Step Out button in the PL/SQL Interpreter toolbar.

If you previously used Step In to descend into a nested subprogram, Step Out completes execution of the nested subprogram and returns to the next line of the original program unit.

To resume program unit execution:

Click the Go button in the PL/SQL Interpreter toolbar.

Execution of the program unit continues until the program unit execution has finished, or until interrupted again by another debug action.

Note:
If your debug action is located in a PL/SQL LOOP, using Go will cycle through the loop.

To exit suspended execution at the current debug level:

Click the Reset button in the PL/SQL Interpreter toolbar.

Control is returned to the Interpreter, or to an outer debug level (if any exist).

3.14.13 Modifying code at runtime

To modify your code at runtime:

In the Object Navigator, double-click the desired program unit, menu item command, or trigger to display the PL/SQL Editor.
In the PL/SQL Editor, make the desired modifications.
Click Compile then Close to dismiss the PL/SQL Editor.
In the PL/SQL Interpreter toolbar, choose Go or Step Into, Over, or Out to resume program execution.

3.14.14 Displaying the current scope location

To display the current scope location:

In the Object Navigator, expand the Stack node.
Expand the desired frame in the stack to reveal information about local variables and parameters.

Or

In the PL/SQL Interpreter, display the Source pane to view the current scope location.

3.14.15 Examining or changing local variables

You must currently have a suspended program unit in the PL/SQL Interpreter to examine or change local variables values.

To examine local variable values:

In the Object Navigator, expand the Stack node to show the call stack frames.
Under the Stack node, double-click the node for the program unit whose variables you wish to examine or modify.

Each local variable is displayed with its current value.

To edit local variable values:

In the Object Navigator, expand the Stack node to show the call stack frames.
Under the Stack node, double-click the node for the program unit whose variables you wish to examine or modify.

Each local variable is displayed with its current value.
Click the variable value in the Object Navigator.
Click the variable value again to enter edit mode.
Change the value of the variable.
Click a blank area in the Object Navigator to exit edit mode and accept the changed value.
Resume program unit execution in the PL/SQL Interpreter to test the effect of the new value.

3.14.16 Modifying application variables

To modify a variable:

In the Object Navigator, expand the Global Variables node or the Stack node for local variables.
Select and expand the desired entry. The Object Navigator displays any variables associated with the entry.
Click the existing value after the "=" then edit the value by entering the desired value.

3.14.17 Viewing subprogram references

To view subprogram references:

In the Object Navigator, select and expand the desired subprogram.
Select and expand either the References or Referenced By node.

The Object Navigator displays any subprogram references.

Note:
Referenced By shows the program units that call the current program unit. References shows the program units that are called by the current program unit.

3.14.18 Tracing report execution

To set tracing options, do one of the following:

Choose Program > Tracing and fill out the Runtime Trace Settings dialog box as desired.
On the command line, specify tracing options using rwbuilder or rwrun with the TRACEFILE, TRACEMODE, and TRACEOPTS keywords.
Specify tracing options via the Reports Builder built-in package using SRW.TRACE_START, SRW.TRACE_END, SRW.TRACE_ADD_OPTIONS, SRW.TRACE_REM_OPTIONS.

3.14.19 Tracing report distribution

To trace report distribution:

Choose Program > Tracing.
In the Runtime Trace Settings dialog box, specify a name for the trace file in the Trace File field.
Select the Distribution check box.
Click OK.
Run the report.
Use a text editor to open and view the trace file. If the trace file is empty, the distribution was successful. Otherwise, the trace file identifies the distribution error.

3.14.20 Tracing using the SQL TRACE function

The TRACE function provides you with the exact statements that are being parsed. Once you have them, you can time them in SQL*Plus, and multiply these times with the expected number of rows to retrieve from the database. (Always compare apples to apples, i.e., send output to a file--not to the screen. Do not change anything in the SQL statements when moving it to SQL*Plus; even the slightest change in the WHERE clause can make a big difference in the performance.)

There are two ways to trace your reports:

user level
system level

We recommend the user level because you can more easily find the information you need. For more information on SQL TRACE, see the Oracle9i Server SQL Language Reference Manual.

3.14.20.1 Performing a user-level trace

Open the report for which you want the performance data.
Create a report-level formula column named SQL-TRACE that has the following formula:
```
SRW.DO_SQL ('ALTER SESSION SET SQL_TRACE=TRUE');
return(1); --Formulas must return a value.
```
Note:
You could also call SQL TRACE from the Before Form trigger.
Run the report. A new file, <some_number>.trc will be created in either ORACLE_HOME/rdbms/log, or the destination indicated by the init.ora parameter USER_DUMP_DEST. (The date stamp on the file can help you determine which .trc file is yours.)
Use the TKPROF command to format the trace output file.

If you issue more than one trace during the rwbuilder session, the trace outputs are concatenated into one file.

3.14.20.2 Performing a system-level trace

Insert these statements into your init.ora file: SQL_TRACE=TRUE and TIMED_STATISTICS=TRUE.
Shut down, then restart your database.

Note:
Every interaction with the database will be traced, and the ORACLE_HOME/rdbms/log is likely to grow very large.

3.15 Integrate with Other Products

This section provides procedures for the following tasks that you may perform as you integrate your reports with other products:

3.15.1 Accessing non-Oracle data sources

Oracle Reports allows you to access any data source. The new pluggable data source (PDS) architecture replaces Oracle Open Client Adapter (OCA), and the Open Database Connectivity (ODBC) drivers are no longer supported in Oracle Reports. However, Java Database Connectivity (JDBC) is one of the pluggable data sources available that can utilize the JDBC-ODBC bridge, allowing access to other data sources.

Use the following steps to determine the easiest method for accessing your data source:

Does your data source have Java Database Connectivity (JDBC)?

A pluggable data source (PDS) has already been set up. All you have to do is select JDBC as your data type.
Does your data source have ODBC?

If so, make sure that you have downloaded the JDBC-ODBC bridge from Sun Microsystems. This lets you use the JDBC connection mentioned in Step 1. If you do not know if there is an ODBC driver for your data source, check the company Web site. For example, Microsoft has a downloadable ODBC driver for Excel.
Is the file in XML?

Oracle Reports includes an XML PDS. If the DTD file is available, all you have to do is select XML as your data type. If the DTD file is not available, you will have to create one.
Is the file comma-delimited?

You can use the Text PDS included with Oracle Reports to query the data source.
Has someone in your company built a PDS for your data type?

If the PDS exists, it will probably be on the Reports Server. Ask your administrator if such a file exists. If the PDS exists, add it to the classpath for your copy of Reports Builder. If the PDS does not exist, use the API Reference to help you build one.

Examples

Oracle Reports ships with XML data source sample files.

3.15.2 Publishing a report as a portlet in OracleAS Portal

OracleAS provides a portal building application called OracleAS Portal, which is integrated with Oracle Reports. This integration enables you to quickly publish a report as a portlet (i.e., a small, dynamic region) or an item on a page, so that your users can easily access dynamic reports

For more information, refer to the section titled "Integration with OracleAS Portal" in Getting Started with Oracle Reports, available on the Oracle Technology Network Oracle Reports Documentation page (http://otn.oracle.com/docs/products/reports/content.html).

3.16 Administer Reports Builder

This section provides procedures for the following tasks that you may perform as you administer Reports Builder:

3.16.1 Setting a database role

Before beginning this procedure, verify that the database administrator has created the role, granted privileges to the role, and granted the role to approved end users. Refer to your Oracle9i Application Developer's Guide for more information.

To set a database role for a report:

In the Object Navigator, double-click the properties icon for the report to display the Property Inspector.
Under the Report node, set the Role Name property as defined by the database administrator in the database.
Optionally, to set a Role Password, double-click the button in the Role Name value field to display the Set Role dialog box.

Usage notes

The role settings in the report are overridden if you specify a role using the command line with Reports Runtime (rwrun).
You can set only one role for a report. To set multiple roles, you need to set a role to which the other roles have been granted.

Caution:
Do not attempt to set the role in a PL/SQL trigger. The PL/SQL will not compile.

3.16.2 Converting from one format to another

To convert one or more report definitions or PL/SQL libraries from one storage format to another:

In Reports Builder, choose Tools > File Conversion to display the Convert dialog box.
On the command line, type %ORACLE_HOME%\bin\rwconverter, followed by the report name and desired arguments.

3.16.3 Improving performance using SQL statements

Performing operations in SQL is faster than performing them in Reports Builder or PL/SQL. The following are the most common cases where using SQL would improve performance:

use a WHERE clause instead of a group filter or format trigger to exclude records
use the SUBSTR function to truncate character strings instead of truncating in Reports Builder
perform calculations directly in your query rather than in a formula or summary

Rationale: SQL can perform calculations more quickly than a summary or formula. WHERE and SUBSTR can reduce unnecessary fetching because they operate on the data during rather than after data retrieval. Improvements in performance become more noticeable when retrieving thousands of records versus a few records.

3.16.4 Improving performance using WHERE clauses

Consider adding a WHERE clause for the matrix cell query in a multiquery data model. If you are using a multiquery data model and your dimension queries are restricted by a WHERE clause, adding a WHERE clause to the matrix cell query ensures that you do not retrieve more records than are necessary. For example, suppose that you had the following queries for your dimensions:

Q_Dept

SELECT DEPTNO FROM DEPT
WHERE DEPTNO < 100

Q_Job

SELECT DISTINCT JOB FROM EMP
WHERE DEPTNO < 100

Q_Filler

To ensure that your cell query only retrieves the records that are necessary, you would write the following SELECT statement:

SELECT DEPTNO, JOB, SUM(SAL) FROM EMP
WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT
WHERE DEPTNO < 100) AND JOB IN (SELECT
DISTINCT JOB FROM EMP WHERE DEPTNO < 100)
GROUP BY DEPTNO, JOB

If you did not add the WHERE clause to this query, all rows would be retrieved from the database, regardless of what you selected in Q_Dept and Q_Job.

Note:

If you added a WHERE clause that did not use the subqueries (e.g., WHERE EMP.DEPTNO = DEPT.DEPTNO), the query would be executed once for each combination of values in the cross-product. This can lead to excessive execution of the filler query, if the cross-product has a lot of combinations.

3How To...

3.1 Access Oracle Reports Documentation

3.1.1 Using the online help

3.1.2 Locating other documentation

Table 3-1 Oracle Reports documentation roadmap

3.2 Set Properties and Preferences

3.2.1 Displaying the Property Inspector

See also

3.2.2 Setting report properties

3.2.3 Setting report preferences

3.2.4 Setting preferences for the Object Navigator

See also

3.2.5 Setting properties for an ASCII (character-mode) report

See also

3.2.6 Setting color palette preferences

See also

3.2.7 Setting properties of multiple objects

See also

3.2.8 Comparing the properties of one object to another

3.3 Perform Common Tasks

Usage notes

3.3.1 Connecting to a database

3.3.2 Opening a report

3.3.3 Saving a report

3.3.4 Copying a report

3.3.5 Renaming a report

3.3.6 Deleting a report

3.3.7 Archiving a report

3.3.8 Selecting and deselecting objects

3.3.8.1 Selecting single objects

3.3.8.2 Selecting multiple objects

3.3.8.3 Selecting objects owned by a frame

3.3.8.4 Selecting grouped objects

3.3.8.5 Selecting all objects in a report region

3.3.8.6 Selecting overlapped objects

3.3.8.7 Deselecting single objects

3.3.8.8 Deselecting multiple objects

3.3.9 Deleting an object

3.3.10 Hiding or showing components

3.4 Work with the Object Navigator

See also

3.4.1 Displaying a Report Editor view from the Object Navigator

See also

3.4.2 Expanding and collapsing nodes

3.4.3 Searching for nodes

3.4.4 Changing Object Navigator views

See also

3.4.5 Setting preferences for the Object Navigator

3.5 Work with Reports

3.5.1 Creating a report

See also

3.5.2 Creating a multiquery group above report

3.5.3 Creating a nested matrix report

3.5.4 Creating a default layout for a report

See also

3.5.5 Creating an additional report layout

3.5.6 Adding a title to a report

See also

3.5.7 Adding a table of contents to a report

See also

3.5.8 Adding index to a report

3.5.9 Creating an ASCII (character-mode) report

See also

3.5.10 Preparing a multiplatform report

3.5.11 Preparing a report for translation into other languages

3.6 Work with Web Reports

See also

3.6.1 Creating a Web report

3.6.2 Viewing the source code for a Web report

See also

3.6.3 Adding report data to an existing Web page (HTML file)

3.6.4 Adding a report block to a Web page

3.6.5 Adding a graph to a Web report

See also

3.6.6 Preparing a paper-based report for the Web

See also

3.6.7 Adding Web links to paper-based reports

3.6.7.1 Using the user interface

See also

3.6.7.1.1 Creating an HTML document header using the Property Inspector

3
How To...