Skip Headers

Oracle® Reports Building Reports 10g Release 2 (10.1.2) B13895-01 |
| Library | Product | Contents | Index

Previous | Next |

# 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 Oracle Reports online Help (see Section 3.1.1, "Using the Oracle Reports online Help").

The procedures are grouped into the following sections:

- Section 3.1, "Access Oracle Reports Documentation"

- Section 3.2, "Set Properties and Preferences"

- Section 3.3, "Perform Common Tasks"

- Section 3.4, "Work with the Object Navigator"

- Section 3.5, "Work with Reports"

- Section 3.6, "Work with Web Reports"

- Section 3.7, "Run and Dispatch a Report"

- Section 3.8, "Work with the Data Model"

- Section 3.9, "Work with the Report Layout"

- Section 3.10, "Work with Report Sections"

- Section 3.11, "Work with Parameters and the Parameter Form"

- Section 3.12, "Define a Template"

- Section 3.13, "Use PL/SQL in a Report or Template"

- Section 3.14, "Debug a Report"

- Section 3.15, "Integrate with Other Products"

- Section 3.16, "Administer Reports Builder"

## 3.1 Access Oracle Reports Documentation

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

- Using the Oracle Reports online Help

- Locating other documentation

### 3.1.1 Using the Oracle Reports online Help

For more information on using Reports Builder, as well as Oracle Reports reference information, refer to the Oracle Reports online Help, which you can access in any of the following ways:

- From Reports Builder:

  - 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.

- On the Oracle Technology Network (OTN) Oracle Reports 10g page (`http://www.oracle.com/technology/products/reports/index.html`):

  - Under Resources, click Hosted Online Help to display the Web-based version of the most recent Oracle Reports online Help.

  - Under News, click Oracle Reports Online Help Update to replace your Oracle Reports online Help in Reports Builder with the most recent update. Instructions for replacing your help file are included in the `readme.txt` in the download file.

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

- 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 10g page (`http://www.oracle.com/technology/products/reports/index.html`). 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 10g page (`http://www.oracle.com/technology/products/reports/index.html`). 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 | A Guide to Functional Changes Between Oracle Reports 6i and 10g, available on the Oracle Technology Network Oracle Reports 10g page (`http://www.oracle.com/technology/products/reports/index.html`). 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://www.oracle.com/technology/documentation/reports.html`). 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 Help, or press F1. For properties, the online Help topics 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 manual, available on the Oracle Technology Network Oracle Reports Documentation page (`http://www.oracle.com/technology/documentation/reports.html`).
information about using the Oracle Reports application program interface (API) | Oracle Reports JavaAPI Reference, available in the following locations: On the documentation CD. In Getting Started with Oracle Reports, available on the Oracle Technology Network (OTN): on the Oracle Reports 10g page (`http://www.oracle.com/technology/products/reports/index.html`), click Getting Started to display the Getting Started with Oracle Reports home page. In the list of topic sections on the left, click Index. In the Collateral Type list, choose Documentations and click Search. In the list that displays, find the Oracle Reports Java API Documentation. The Reports Software Development Kit (RSDK), available on the Oracle Technology Network (OTN): on the Oracle Reports 10g page (`http://www.oracle.com/technology/products/reports/index.html`), click SDK. The Oracle Reports Plugin Exchange, available on the Oracle Technology Network (OTN): on the Oracle Reports 10g page (`http://www.oracle.com/technology/products/reports/index.html`), click Plugin Exchange.
examples and demos | Getting Started with Oracle Reports, available on the Oracle Technology Network (OTN): on the Oracle Reports 10g page (`http://www.oracle.com/technology/products/reports/index.html`), click Getting Started to display the Getting Started with Oracle Reports home page. In the list of topic sections on the left, click Index. In the Collateral Type list, choose Examples or Demonstrations and click Search. In the list that displays, click an example or demo that interests you. A subset of the examples is also available on the Oracle Developer Suite product CD.
other resources, such as white papers | The Oracle Technology Network (`http://www.oracle.com/technology/index.html`).

## 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:

- Displaying the Property Inspector

- Setting report properties

- Setting report preferences

- Setting preferences for the Object Navigator

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

- Setting color palette preferences

- Setting properties of multiple objects

- Comparing the properties of one object to another

### 3.2.1 Displaying the Property Inspector

To display the Property Inspector for an object:

- In the Object Navigator, do any 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.

  - Click the object name, then press F4.

- In the Report Editor views, do any 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.

  - Click the object, then press F4.

See also

Section 1.10.1, "About the Property Inspector"

### 3.2.2 Setting report properties

To set the properties for a report:

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

2. Set report properties as desired.

### 3.2.3 Setting report preferences

To specify the preferences for a report:

1. Choose Edit > Preferences.

2. To specify preferences for designing reports, set values on the General, Access, and Wizards tabs.

3. To specify preferences for running reports, set values on the Runtime Values and Runtime Settings tabs.

4. 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:

1. In the Object Navigator, choose Tools > Options > Navigator.

2. In the Object Navigator Options dialog box, change the settings as desired.

3. Click OK.

See also

Section 1.5.1, "About the Object Navigator"

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

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

1. Double-click the properties icon next to the report name to display the Property Inspector.

2. Under the Character Mode node, set Design In Character Units to Yes.

3. 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.

4. 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.

5. In the Object Navigator, expand the Data Model node, then the System Parameters node.

6. Double-click the properties icon next to MODE to display the Property Inspector, and set the following properties:

   - Under the Parameter node, set the Initial Value property to Character.

7. 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.

8. Click View in the menu bar and make sure that Snap to Grid is checked.

9. 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.

10. Choose Format > Font, and select the font, style, and size that most closely approximates the character-mode font. For example, Courier, Regular, 12 points.

See also

Section 3.5.9, "Creating an ASCII (character-mode) report"

### 3.2.6 Setting color palette preferences

To set color palette preferences:

1. Choose Edit > Preferences.

2. 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. A report can have only one color 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. This palette is then used whenever that report is active. The active report is then shown accurately, although the appearance of the inactive reports may suffer.

   Note: A report can have only one color palette.

3. If you change the Color Mode, shut down and restart Reports Builder to enable the new mode.

See also

Section 2.4.5, "About changing colors and patterns"

Section 3.9.6.6, "Importing or exporting a color palette"

Section 3.9.6.5, "Modifying the color palette"

Section 3.9.6.2, "Changing colors"

Topics "Oracle CDE1 color palette", "Default color palette", and "Grayscale color palette" in the Reference > Color and Pattern Palettes section of the Oracle Reports online Help.

### 3.2.7 Setting properties of multiple objects

To set the properties of multiple objects:

1. 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.

2. Choose Tools > Property Inspector.

3. 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.

4. 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.

See also

Section 1.10.1.1, "About making multiple selections in the Property Inspector"

### 3.2.8 Comparing the properties of one object to another

To compare the properties of one object to another:

1. In the Object Navigators or the editors, double-click the first object so that its properties are displayed in the Property Inspector.

2. In the Property Inspector, click the Pin button to attach this copy of the Property Inspector to the current object.

3. In the Object Navigator, double-click the second object, then choose Tools > Property Inspector.

   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:

- Connecting to a database

- Opening a report

- Saving a report

- Renaming a report

- Deleting a report

- Archiving a report

- Selecting and deselecting objects

- Deleting an object

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:

1. Choose File > Connect.

2. 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 Oracle Reports online Help.

### 3.3.2 Opening a report

To open a report:

1. Choose File > Open.

2. 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:

1. Choose File > Save or File > Save As or click the Save button in the toolbar.

2. 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:

1. In the Object Navigator, click the report you want to copy.

2. Choose File > Save As.

3. 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:

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

2. 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:

1. In your operating system's file system (for example, Windows Explorer on Windows), browse to the location of the report.

2. Click the report name, then click again to make its name active.

3. Type a new name for the report.

### 3.3.6 Deleting a report

To delete a report in the Object Navigator:

1. In the Object Navigator, click the report name, then choose Edit > Delete, or click the Delete button in the toolbar.

2. In the alert dialog box, click Yes to delete the report. To cancel the delete operation, click No.

To delete a report in the file system:

1. In your operating system's file system, for example, Windows Explorer on Windows, browse to the location of the report.

2. Click the report name, then choose File > Delete.

3. In the alert dialog box, click Yes to delete the report. To cancel the delete operation, click NoS.

### 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 enables you to easily control your reports in a seamless integrated environment.

When you perform a full Developer Suite (DS) installation, SCM is installed and registry variables are set by the installation. However, if you performed a partial DS installation (for example, Business Intelligence only) you may not have SCM installed. If Reports Builder's File > Administration menu is not enabled, this indicates that SCM is not installed. You can download and install SCM from the Oracle Technology Network (). Then, to enable the use of SCM with Oracle Reports, perform this step to set registry variables:

- From your Windows Start menu, choose Programs > Oracle Developer Suite > Oracle Software Configuration Manager > Use as Source Control for Forms and Reports.

  This menu item runs a file named `drsc61.reg`, which updates the registry variables required by Oracle Reports as follows:

  `FORMS_SCCONVERT` set to `FALSE`

  `FORMS_SCDRIVER` set to `d2scrw32.dll`

  `FORMS_SCVIEWER` set to `NOTEPAD.EXE`

  `FORMS_SOURCECONTROL` set to `CUSTOM`

For more information about using the SCM functionality in Reports Builder (File > Administration menu), refer to the SCM documentation:

- From your Windows Start menu, choose Programs > Oracle Developer Suite > Oracle Software Configuration Manager for a menu of available documentation.

Usage notes

When using SCM in Reports Builder, do not select File > Administration > Check Out after File > Administration > Check In. Doing so will result in the report being placed in read-only mode. To avoid this problem, you should always perform checkins and checkouts as separate operations.

### 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:

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

2. 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:

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

2. 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:

1. 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.

2. 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 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:

1. Click the object that obscures the object you want to select.

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

3. 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:

1. 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.

2. Choose Edit > Delete.

## 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:

- Displaying a Report Editor view from the Object Navigator

- Expanding and collapsing nodes

- Searching for nodes

- Changing Object Navigator views

- Setting preferences for the Object Navigator

See also

Section 1.5.1, "About 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.

See also

Section 1.6.2, "About the Data Model view"

Section 1.6.3, "About the Paper Layout view"

Section 1.6.5, "About the Paper Parameter Form view"

Section 1.6.6, "About the Web Source view"

### 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:

1. Type the full of partial name of the node in the Find field.

2. To search for the next match, click the Find Next button in the toolbar.

3. 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.

See also

Section 1.5.2, "About Object Navigator views"

### 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):

- Creating a report

- Creating a multiquery group above report

- Creating a nested matrix report

- Creating a default layout for a report

- Creating an additional report layout

- Adding a title to a report

- Adding a table of contents to a report

- Adding index to a report

- Creating an ASCII (character-mode) report

- Preparing a multiplatform report

- Preparing a report for translation into other languages

### 3.5.1 Creating a report

To create a single-query report:

1. In the Object Navigator, click the Reports node, then click the Create button in the toolbar.

2. In the New Report dialog box, click Use the Report Wizard, then click OK.

3. Follow the Report Wizard to create your report, clicking Help for assistance on any tab page.

4. 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:

1. 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").

2. Create a layout for the report (see Section 3.5.4, "Creating a default layout for a report").

See also

Section 1.2.1, "About reports"

Section 1.2.2, "About Web reports"

Section 3.5.1, "Creating a report"

Section 1.3.1, "About tabular reports"

Section 1.3.2, "About group above reports"

Section 1.3.3, "About group left reports"

Section 1.3.4, "About form-like reports"

Section 1.3.5, "About form letter reports"

Section 1.3.6, "About mailing label reports"

Section 1.3.7, "About matrix reports"

Section 2.1.7, "About nested matrix reports"

Section 2.1.8, "About matrix with group reports"

### 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:

1. 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.

2. To add another layout section to the current report layout, create an additional report layout, as described below.

3. 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.

See also

Section 2.4.2, "About layout defaulting"

Section 3.10.2, "Creating a default layout for a section"

Chapter 3, "Changing the default layout spacing"

### 3.5.5 Creating an additional report layout

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

1. In the Paper Layout view, click the Report Block tool in the tool palette.

2. Drag a rectangular area for the new layout to display the Report Block Wizard.

3. Follow the wizard to select the data to display in the new layout section.

4. To reorder the layout sections, click and drag them to new positions in the Paper Layout view.

5. 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:

1. In the Object Navigator, select or open the report.

2. Choose Tools > Report Wizard.

3. On the Style page, type a title for the report in the Title field.

4. If the report title is to be used in a template, see Section 3.12.5, "Formatting the report title in a template".

See also

Section 2.1.1, "About report titles"

Section 2.7.1, "About templates"

### 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 35, "Building a Paper Report with a Simple Table of Contents and Index" and Chapter 36, "Building a Paper Report with a Multilevel Table of Contents".

See also

Section 2.8.1, "About format order"

### 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 35, "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:

1. First, create and save a report using the Report Wizard.

2. In the Object Navigator, under the Reports node, click the report you want to convert to character mode.

3. Choose Tools > File Conversion.

4. In the Convert dialog box, on the Conversion tab 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.

5. On the Options tab page, set Destination Unit to Character.

6. Click OK.

7. Set properties for your new character-mode report.

See also

Section 2.8.15, "About creating an ASCII (character-mode) report"

Section 3.2.5, "Setting properties for an ASCII (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 Oracle Reports 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:

- Creating a Web report

- Viewing the source code for a Web report

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

- Adding a report block to a Web page

- Adding an image

- Adding a graph

- Editing a graph

- Adding a graph hyperlink

- Preparing a paper-based report for the Web

- Adding Web links to paper-based reports

See also

Section 1.2.2, "About 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 Oracle Reports 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.9, "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, do any of the following:

- In the Report Editor, click the Web Source button in the toolbar.

- 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.

- Choose View > Change View > Web Source.

See also

Topic "Oracle Reports JSP tags" in the Reference section of the Oracle Reports online Help.

### 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):

1. In the Object Navigator, choose File > Open.

2. Navigate to your Web page (HTML file), and click Open.

3. 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.

4. 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:

1. In the Object Navigator, choose File > Open.

2. Navigate to your Web page (HTML file), and click Open.

3. 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).

4. After you have added the data to be used to build the report (see Section 3.6.4, "Adding a report block to a Web page"), 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.

5. 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 enables you to easily locate the correct position for your report block.

6. Choose Insert > Report Block.

7. In the Report Block Wizard, specify the information for the report block.

### 3.6.5 Adding an image

See Section 3.9.8.1, "Adding an image"

### 3.6.6 Adding a graph

See Section 3.9.8.2, "Adding a graph"

### 3.6.7 Editing a graph

See Section 3.9.8.3, "Editing a graph"

### 3.6.8 Adding a graph hyperlink

See Section 3.9.8.4, "Adding a graph hyperlink"

### 3.6.9 Preparing a paper-based report for the Web

To prepare a paper-based report for the Web:

1. (Optional) Add desired Web links to a new or existing report:

   - Create an HTML document header (see Section 3.6.10.1.1)

   - Create an HTML document footer (see Section 3.6.10.1.2)

   - Create an HTML page header (see Section 3.6.10.1.3)

   - Create an HTML page footer (see Section 3.6.10.1.4)

   - Create an HTML Parameter Form header (see Section 3.6.10.1.5)

   - Create an HTML Parameter Form footer (see Section 3.6.10.1.6)

   - Create a hyperlink (and, if linking to an object within the report, create an associated hyperlink destination) (see Section 3.6.10.1.8 and Section 3.6.10.1.7)

   - Create an application command line link (PDF output only) (see Section 3.6.10.1.9)

   - Create a bookmark (see Section 3.6.10.1.10)

2. 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).

3. Display your report output in your Web browser (see Section 3.7.16.4, "Displaying report output in your Web browser")

See also

Section 1.2.2, "About Web reports"

Section 2.2.3, "About Web links for HTML output"

Section 2.2.4, "About Web links for PDF output"

### 3.6.10 Adding Web links to paper-based reports

This section provides procedures that apply to paper-based reports generated to an HTML or PDF file. This is unrelated to 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 by either:

- Using the user interface.

- Using PL/SQL.

#### 3.6.10.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:

- Creating an HTML document header using the Property Inspector

- Creating an HTML document footer using the Property Inspector

- Creating an HTML page header using the Property Inspector

- Creating an HTML page footer using the Property Inspector

- Creating an HTML Parameter Form header using the Property Inspector

- Creating an HTML Parameter Form footer using the Property Inspector

- Creating a hyperlink destination using the Property Inspector

- Creating a hyperlink using the Property Inspector

- Creating an application command line link using the Property Inspector

- Creating a bookmark using the Property Inspector

- Creating a bookmark on break columns using the Property Inspector

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

- Selecting HTML tags from the database

See also

Section 1.2.2, "About Web reports"

Section 2.2.3, "About Web links for HTML output"

Section 2.2.4, "About Web links for PDF output"

Section 2.2.10, "About before and after escapes"

##### 3.6.10.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:

1. In the Object Navigator, double-click the properties icon for the report to display the Property Inspector.

2. 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).

3. 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.

See also

Section 3.6.10.2.1, "Creating an HTML document header using PL/SQL"

##### 3.6.10.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:

1. In the Object Navigator, double-click the properties icon for the report to display the Property Inspector.

2. 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).

3. 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.

See also

Section 3.6.10.2.2, "Creating an HTML document footer using PL/SQL"

##### 3.6.10.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:

1. In the Object Navigator, double-click the properties icon for the report to display the Property Inspector.

2. 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).

3. 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.10.2.3, "Creating an HTML page header using PL/SQL".

##### 3.6.10.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:

1. In the Object Navigator, double-click the properties icon for the report to display the Property Inspector.

2. 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).

3. 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.10.2.4, "Creating an HTML page footer using PL/SQL".

##### 3.6.10.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:

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

2. 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).

3. 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.

See also

Section 2.5.1, "About Parameter Form HTML extensions"

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

##### 3.6.10.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:

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

2. 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).

3. 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.

See also

Section 2.5.1, "About Parameter Form HTML extensions"

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

##### 3.6.10.1.7 Creating a hyperlink destination using the Property Inspector

To add a hyperlink destination to your report:

1. 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.

2. Double-click the object that will be the destination of a Web link to display the Property Inspector.

3. Under the Web Settings node, set the Hyperlink Destination property to an identifier for the object.

See also

Section 2.2.7, "About hyperlink destinations"

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

##### 3.6.10.1.8 Creating a hyperlink using the Property Inspector

To add a hyperlink to your report:

1. Create a hyperlink destination, as described in the steps above, which will be the target of the Web link.

2. 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.

3. Double-click the object that will be the source of the Web link to display the Property Inspector.

4. 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 Oracle Reports 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.

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

See also

Section 2.2.5, "About hyperlinks"

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

##### 3.6.10.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:

1. 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.

2. Double-click the object with which you want to associate a command to display the Property Inspector.

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

   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.

Restrictions

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

See also

Section 2.2.9, "About application command line links"

Section 3.6.10.2.9, "Creating an application command line link using PL/SQL"

##### 3.6.10.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:

1. 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.

2. Double-click the object with which you want to associate a bookmark to display the Property Inspector, and set the following 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.

See also

Section 2.2.8, "About bookmarks"

Section 3.6.10.2.10, "Creating a bookmark using PL/SQL"

##### 3.6.10.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:

1. In the Paper Layout view, choose Insert > Bookmark.

2. In the Insert Bookmarks dialog box, move the desired column(s) to the Bookmarks list.

3. Click OK.

See also

Section 2.2.8, "About bookmarks"

Section 3.6.10.2.10, "Creating a bookmark using PL/SQL"

##### 3.6.10.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:

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

2. 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.

See also

Section 2.8.8.1, "About HTML page streaming"

Section 3.7.16.5, "Displaying individual pages of HTML report output"

Section 3.6.10.2.11, "Adding navigation controls for HTML page-streamed output using PL/SQL"

#### 3.6.10.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:

- Creating an HTML document header using PL/SQL

- Creating an HTML document footer using PL/SQL

- Creating an HTML page header using PL/SQL

- Creating an HTML page footer using PL/SQL

- Creating an HTML Parameter Form header using PL/SQL

- Creating an HTML Parameter Form footer using PL/SQL

- Creating a hyperlink using PL/SQL

- Creating a hyperlink destination using PL/SQL

- Creating an application command line link using PL/SQL

- Creating a bookmark using PL/SQL

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

See also

Section 1.2.2, "About Web reports"

Section 2.2.3, "About Web links for HTML output"

Section 2.2.4, "About Web links for PDF output"

Section 2.2.10, "About before and after escapes"

##### 3.6.10.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:

1. In the Object Navigator, expand the Report Triggers node.

2. Double-click the PL/SQL icon next to the Before Report trigger.

3. In the PL/SQL Editor, include SRW.SET_BEFORE_REPORT_HTML to define the PL/SQL for the format trigger. (See the Reference section of the Oracle Reports online Help for more information).

See also

Section 3.6.10.1.1, "Creating an HTML document header using the Property Inspector"

##### 3.6.10.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:

1. In the Object Navigator, expand the Report Triggers node.

2. Double-click the PL/SQL icon next to the Before Report trigger.

3. In the PL/SQL Editor, include SRW.SET_AFTER_REPORT_HTML to define the PL/SQL for the format trigger.

See also

Section 3.6.10.1.2, "Creating an HTML document footer using the Property Inspector"

##### 3.6.10.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:

1. In the Object Navigator, expand the Report Triggers node.

2. Double-click the PL/SQL icon next to the Before Report trigger.

3. 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:

1. In the Object Navigator, expand the Paper Layout node, then expand the node that contains the object that should fire the trigger when formatted.

2. Double-click the PL/SQL icon next to the object that should fire the trigger when formatted.

3. In the PL/SQL Editor, include `SRW.SET_BEFORE_PAGE_HTML` to define the PL/SQL for the format trigger.

See also

Section 3.6.10.1.3, "Creating an HTML page header using the Property Inspector"

##### 3.6.10.2.4 Creating an HTML page footer 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 footer to every page of your HTML document using PL/SQL instead of the Property Inspector:

1. In the Object Navigator, expand the Report Triggers node.

2. Double-click the PL/SQL icon next to the Before Report trigger.

3. 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:

1. In the Object Navigator, expand the Paper Layout node, then expand the node that contains the object that should fire the trigger when formatted.

2. Double-click the PL/SQL icon next to the object that should fire the trigger when formatted.

3. In the PL/SQL Editor, include `SRW.SET_AFTER_PAGE_HTML` to define the PL/SQL for the format trigger.

See also

Section 3.6.10.1.4, "Creating an HTML page footer using the Property Inspector"

##### 3.6.10.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:

1. In the Object Navigator, expand the Report Triggers node.

2. Double-click the PL/SQL icon next to the Before Parameter Form trigger.

3. In the PL/SQL Editor, include `SRW.SET_BEFORE_FORM_HTML` to define the PL/SQL for the format trigger.

See also

Section 2.5.1, "About Parameter Form HTML extensions"

Section 3.6.10.1.5, "Creating an HTML Parameter Form header using the Property Inspector"

##### 3.6.10.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:

1. In the Object Navigator, expand the Report Triggers node.

2. Double-click the PL/SQL icon next to the Before Parameter Form trigger.

3. In the PL/SQL Editor, include `SRW.SET_AFTER_FORM_HTML` to define the PL/SQL for the format trigger.

See also

Section 2.5.1, "About Parameter Form HTML extensions"

Section 3.6.10.1.6, "Creating an HTML Parameter Form footer using the Property Inspector"

##### 3.6.10.2.7 Creating a hyperlink using PL/SQL

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

1. 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

2. Double-click the PL/SQL icon next to the object that will be the source of the Web link.

3. In the PL/SQL Editor, include `SRW.SET_HYPERLINK` to define the PL/SQL for the format trigger.

4. To apply additional HTML attributes to the hyperlink, use `SRW.SET_HYPERLINK_ATTRS`.

Next step

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

See also

Section 2.2.5, "About hyperlinks"

Section 3.6.10.1.8, "Creating a hyperlink using the Property Inspector"

##### 3.6.10.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:

1. 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.

2. Double-click the PL/SQL icon next to the object that will be the destination of a Web link.

3. In the PL/SQL Editor, include `SRW.SET_LINKTAG` to define the PL/SQL for the format trigger.

Next step

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

See also

Section 2.2.7, "About hyperlink destinations"

Section 3.6.10.1.7, "Creating a hyperlink destination using the Property Inspector"

##### 3.6.10.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:

1. 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.

2. Double-click the PL/SQL icon next to the object with which you want to associate an application command line link.

3. 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).

See also

Section 2.2.9, "About application command line links"

Section 3.6.10.1.9, "Creating an application command line link using the Property Inspector"

##### 3.6.10.2.10 Creating a bookmark using PL/SQL

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

1. 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.

2. Double-click the PL/SQL icon next to the object with which you want to associate a bookmark.

3. In the PL/SQL Editor, include `SRW.SET_BOOKMARK` to define the PL/SQL for the format trigger.

See also

Section 2.2.8, "About bookmarks"

Section 3.6.10.1.10, "Creating a bookmark using the Property Inspector"

Section 3.6.10.1.11, "Creating a bookmark on break columns using the Property Inspector"

##### 3.6.10.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

See also

Section 2.8.8.1, "About HTML page streaming"

Section 3.7.16.5, "Displaying individual pages of HTML report output"

Section 3.6.10.1.12, "Adding navigation controls for HTML page-streamed output using the Property Inspector"

## 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:

- Running and dispatching a report from the user interface

- Running a report from the command line

- Running a report using a command file

- Running a report to a remote Reports Server

- Generating HTML or HTMLCSS output

- Generating PDF output

- Generating XML output

- Generating RTF output

- Generating text output

- Generating delimited output

- Generating spreadsheet output

- Distributing a report to multiple destinations

- Deploying a report

- Changing orientation

- Suppressing the Parameter Form

- Viewing report output

- Printing a report

- E-mailing a report

### 3.7.1 Running and dispatching a report from the user interface

To run and dispatch a report from the Reports Builder user interface:

1. In the Object Navigator, click the report name, or choose File > Open to open it.

2. If you are not already connected, connect to a database.

3. Click the Run Paper Layout button in the toolbar.

4. 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 (for example, PDF, HTML, HTMLCSS, RTF, XML, or DELIMITED for bitmapped reports, or the character-mode printer definition file (`.prt` file) for character-mode reports).

     Note: It is not possible to generate spreadsheet output directly from Reports Builder. Instead, on the command line, you can run the report using `rwrun` and Reports Server clients (`rwcgi`, `rwclient`, `rwservlet`), with `DESFORMAT=SPREADSHEET`.

   - To send output to a printer: set Destination Type to Printer and Destination Name to the printer driver name.

   - To send output through e-mail using your Internet standard protocol SMTP e-mail application: set Destination Type to Mail and 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 and Solaris environments.

5. In the Runtime Parameter Form, click the Run Report button in the toolbar.

6. 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.14, "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.

- 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.

See also

Section 3.11.6, "Selecting parameters to include in the Runtime Parameter Form"

### 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 on Windows:

1. Type `%ORACLE_HOME%\BIN\rwrun`, followed by the report name and desired arguments.

2. If you sent the output to a file (`DESTYPE=FILE DESNAME=``filename` `DESFORMAT=``fileformat`), open or print the file to verify the output.

See also

Section 1.12, "Executables"

Topic "rwrun" in the Reference section of the Oracle Reports online Help

Section 2.8.2, "About batch reporting"

### 3.7.3 Running a report using a command file

To run a report using a command file on Windows:

1. Create a text file using a word processor of your choice.

2. In the file, type `%ORACLE_HOME%\BIN\rwrun`, followed by the report name and desired arguments.

3. Name the file `filename``.bat`.

4. To run the report, type `filename`.`bat` on the command line.

5. If you sent the output to a file (`DESTYPE=FILE DESNAME=``filename` `DESFORMAT=HTML`), open or print the file to verify the output.

See also

Section 2.8.2, "About batch reporting"

Section 1.12, "Executables"

Topic "rwrun" in the Reference section of the Oracle Reports online Help.

### 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 ().

### 3.7.5 Generating HTML or HTMLCSS output

In 10g Release 2 (10.1.2), to apply external style sheets and user-defined styles to your report or report objects for HTMLCSS output, you can set the following new properties (for information about these properties, see the Reference > Properties section of the Oracle Reports online Help) :

- Style Sheets property CSS Class Name property CSS ID property

To generate HTML or HTMLCSS output:

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

2. 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.

3. 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).

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

See also

Section 2.8.8, "About HTML and HTMLCSS output"

Section 2.8.8.1, "About HTML page streaming"

Section 2.2.11, "About style sheets"

### 3.7.6 Generating PDF output

To generate PDF output:

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

2. 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.

3. To preview your PDF report output in your Web browser, choose File > Preview Format > PDF.

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

See also

Section 2.8.10, "About PDF output"

### 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:

1. 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.

2. Optionally, in the Paper Design view, choose File > Preview Format > XML to preview XML data in your default XML viewer (for example, your Web browser).

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

3. 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:

1. On the command line, execute your report with the arguments `DESFORMAT=XML`, `DESTYPE=FILE`, and `DESNAME=``filename``.XML`.

2. Open an XML-supporting Web browser.

3. Choose File > Open to navigate to the XML file that you just generated. The XML file will appear in the browser window.

4. 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:

1. In the Object Navigator double-click the properties icon next to the report name to display the Property Inspector.

2. Under XML Settings, set the XML properties as required for this report.

To change the XML properties of a column or group:

1. In the Data Model view, double-click the column or the title bar of the group to display the Property Inspector.

2. Under XML Settings, set the XML properties as required.

See also

Section 2.8.7, "About XML in reports"

Topic "Oracle Reports XML tags" in the Reference section of the Oracle Reports online Help.

The chapters "Creating Advanced Distributions" and "Customizing Reports with XML" in the Oracle Application Server Reports Services Publishing Reports to the Web manual, available on the Oracle Technology Network Oracle Reports Documentation page ().

### 3.7.8 Generating RTF output

To generate RTF output:

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

2. To preview your report output in a Microsoft Word document, choose File > Preview Format > RTF.

3. To save your report output as an RTF file, choose File > Generate to File > RTF.

4. In the Save dialog box, specify a location and file name. Click Save.

See also

Section 2.8.11, "About RTF output"

### 3.7.9 Generating text output

To generate text output:

1. 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).

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

3. To preview your report output in a text viewer, choose File > Preview Format > Text.

4. To save your report output as a text file, choose File > Generate to File > Text.

5. In the Save dialog box, specify a location and file name. Click Save.

See also

Section 2.8.14, "About text output"

### 3.7.10 Generating delimited output

You can generate a report to delimited output using either of the following methods:

- From Reports Builder

- From the command line

To generate delimited output from Reports Builder:

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

2. To preview your delimited report output in a spreadsheet, choose File > Preview Format > Delimited or File > Preview Format > DelimitedData (for large volume reports).

3. 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).

4. In the Delimited Output dialog box or DelimitedData Output dialog box, choose from the list or type the delimiter that will separate the cells in your report output.

5. If you want to use formatting options, choose from the list or type a Date Format Mask or a Number Format Mask.

6. If you want to use a cell wrapper, choose from the list or type the cell wrapper that will be placed around the cell data in your report output.

7. Click OK.

8. In the Save dialog box, specify a location and file name. Click Save.

To generate delimited output from the command line:

- Run a report using `rwrun` or Reports Server clients (`rwcgi`, `rwclient`, `rwservlet`), with `DESFORMAT=DELIMITED` or `DELIMITEDDATA`.

- Note: To run a report through a URL, specify the Excel mimetype so that your browser opens the report output directly in Excel. For example:
      http://hostname:port/reports/rwservlet?report=report_name
        +server=server_name+userid=db_connect_string+destype=cache
        +desformat=delimited+mimetype=application/vnd.ms-excel

You can open your delimited output file (`.csv`) to display it automatically in Microsoft Excel. Or, you can import the output file into an existing Excel worksheet.

Limitations

- When generating a report to delimited output, only data (as defined by the report Data Model), no layout information, displays in Microsoft Excel. To generate report output that preserves the layout information in Excel, see Section 2.8.13, "About spreadsheet output".

- Bursting and distributing a report is not supported for Delimited or DelimitedData output.

See also

Section 2.8.12, "About delimited output"

Section 3.9.7.1, "Specifying date and time format masks"

Section 3.9.7.2, "Specifying number format masks"

### 3.7.11 Generating spreadsheet output

To generate spreadsheet output:

- On the command line, run the report using `rwrun` or Reports Server clients (`rwcgi`, `rwclient`, `rwservlet`), with `DESFORMAT=SPREADSHEET`.

  For example, to use `rwservlet` to generate Microsoft Excel output from the paper layout of your report, enter a command that includes the following:

      http://hostname:port/reports/rwservlet?report=report_name
        +server=server_name+userid=db_connect_string+destype=file
        +desformat=spreadsheet+desname=C:\temp\myexcel_output.htm

Notes: It is not possible to generate spreadsheet output directly from Reports Builder, Microsoft Office 2000/2002/2003 supports Hypertext Markup Language (HTML) as a native file format. Spreadsheet output relies on producing HTML output, which can be understood by Excel as a file with valid Excel format. For reliable formatting, enclose the whole layout area in a frame. This prevents the possibility of parallel objects displaying in different vertical positions, one below the other.

See also

Section 2.8.13, "About spreadsheet output"

### 3.7.12 Distributing a report to multiple destinations

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

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

- Use 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 ().

- In the Distribution dialog box, define a distribution list for the entire report or any report section.

  Note: To display the Distribution dialog box: In the report or section Property Inspector, under the Report node, click the Distribution property value field.

  For an example of using section-level distribution, see Chapter 37, "Bursting and Distributing a Report". This chapter covers defining distribution of a single section to multiple destinations, using the Repeat On property and distribution XML file.

- On the command line, include the `DESTINATION` keyword to specify a DST file or XML file.

  Note: The DST file method is supported for backward compatibility; the preferred and recommended method of distributing reports is with XML or the Distribution dialog box.

- To enable the distribution of a report, you can do either of the following:

  - Choose File > Distribute in Reports Builder.

  - Specify `DISTRIBUTE=YES` on the command line.

You can also trace the report distribution to verify a successful distribution, as described in Section 3.14.19, "Tracing report distribution".

Usage notes

In some cases, Microsoft Internet Explorer ignores the mimetype of a URL's return stream and instead sets the type by looking at the URL. This can be a problem when you are defining the distribution for a report because your URL might end with the `DESTINATION` keyword. For example:

    ...DISTRIBUTE=yes
    DESTINATION=c:\oracle\reports\distribution\mydist.xml

In this scenario, the URL ends with the extension xml and Internet Explorer treats the return stream as XML, when in fact it is HTML. As a result, you will receive a browser error. To work around this issue, you should never use recognized file extensions at the end of a URL. In the preceding example, you could switch the positions of the `DISTRIBUTE` and `DESTINATION` keywords in your URL.

See also

Section 2.8.3, "About report distribution"

Section 2.1.2, "About report sectioning and sections"

"Creating Advanced Distributions" in the Oracle Application Server Reports Services Publishing Reports to the Web manual.

### 3.7.13 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 (). This section describes how to deploy a report with a paper layout (that is, a REP, RDF, XML, spreadsheet, or JSP report) and how to deploy a report with a Web layout (that is, a JSP report).

### 3.7.14 Changing orientation

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

1. In the Object Navigator, under the Paper Layout node, double-click the 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).

2. In the Property Inspector, under the Section node:

   - Set the Width and Height properties as desired (for example, 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 (for example, 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.

3. 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 list, change the orientation if desired.

Usage notes

You can also specify orientation on the command line using the `ORIENTATION` command line keyword. This overrides the `ORIENTATION` system paremeter setting, and sets the orientation only when `DESTYPE=PRINTER`. For PDF and RTF report output, change the orientation as described in the preceding steps.

See also

Section 2.1.2, "About report sectioning and sections"

### 3.7.15 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.

See also

Section 1.6.5, "About the Paper Parameter Form view"

Section 1.11.1, "About the Runtime Parameter Form"

Section 1.9.4, "About Parameter Forms for Web reports"

### 3.7.16 Viewing report output

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

- Viewing the printable area

- Displaying report output in the Paper Design view

- Displaying report output in the Previewer

- Displaying report output in your Web browser

- Displaying individual pages of HTML report output

- Scrolling and paging

- Splitting the viewing region

- Magnifying or reducing the output

#### 3.7.16.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:

1. Choose File > Page Setup to specify your page settings.

2. 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.

3. Choose View > Zoom > Zoom Out to see more.

#### 3.7.16.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.16.6, "Scrolling and paging")

- splitting horizontally and vertically (see Section 3.7.16.7, "Splitting the viewing region")

- magnifying or reducing the output (see Section 3.7.16.8, "Magnifying or reducing the output")

See also

Section 3.7.17.1, "Printing a report from the Paper Design or Paper Layout view"

#### 3.7.16.3 Displaying report output in the Previewer

To display your report output in the Previewer:

1. In the Paper Design view, choose File > Print Preview.

2. In the Previewer, use any of the following features:

   - scrolling and paging (see Section 3.7.16.6, "Scrolling and paging")

   - splitting horizontally and vertically (see Section 3.7.16.7, "Splitting the viewing region")

   - magnifying or reducing the output (see Section 3.7.16.8, "Magnifying or reducing the output")

See also

Section 1.11.2, "About the Previewer"

Section 3.7.17.2, "Printing a report from the Previewer"

#### 3.7.16.4 Displaying report output in your Web browser

To display report output in your Web browser:

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

2. 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.

3. 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).

4. To preview your report output with Web layout in a Web browser, choose Program > Run Web Layout to run the Web Source. If you have made changes to the Web Source view (for JSP-based Web reports), this enables you to immediately see the effect of your changes on the output.

   Notes: If Netscape 7.0 is your default browser, and the browser does not display, set the registry key `HKEY_CURRENT_USERS\Software\Oracle\Toolkit\Tkbrowser` to the default browser location. Ensure that the `BrowserName` and the `BrowserPath` keys reflect the correct values. For example: `BrowserName=Netscape 7`; `BrowserPath=C:\Program Files\Netscape\Netscape\Netscp.exe`. If Run Web Layout fails with an error related to the `major.minor` version, you will need to update the environment variables `CLASSPATH` or `REPORTS_CLASSPATH`, as follows: remove the 9.0.x `ORACLE_HOME` specification in the `oc4j.jar` file path (the `oc4j.jar` file path should specify only the 10g Release 2 (10.1.2) `ORACLE_HOME`).

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

See also

Section 1.2.2, "About Web reports"

Section 2.2.2, "About previewing JSP-based Web reports"

Section 2.2.11, "About style sheets"

Section 3.7.17.3, "Printing a report from your Web browser"

#### 3.7.16.5 Displaying individual pages of HTML report output

To paginate the HTML output for a report:

1. Specify navigation controls for HTML page-streamed output using either of the following methods:

   - in the Report Property Inspector (see Section 3.6.10.1.12, "Adding navigation controls for HTML page-streamed output using the Property Inspector")

   - using PL/SQL (see Section 3.6.10.2.11, "Adding navigation controls for HTML page-streamed output using PL/SQL")

2. Enable HTML page streaming by running your report from the command line, specifying PAGESTREAM=YES.

See also

Section 1.2.2, "About Web reports"

Section 2.8.8.1, "About HTML page streaming"

#### 3.7.16.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.16.7 Splitting the viewing region

To create two views of the report output by splitting the display horizontally 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.16.8 M agnifying 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.

- In the Previewer, click the Zoom Out button in the toolbar.

### 3.7.17 Printing a report

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

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

- Printing a report from the Previewer

- Printing a report from your Web browser

- Printing a report on a preprinted form

- Switching the printer tray

- Printing a report on UNIX

#### 3.7.17.1 Printing a report from the Paper Design or Paper Layout view

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

1. In the Paper Design view or Paper Layout view choose File > Print.

2. In the Print dialog box, type the number of pages and copies you want to print.

3. Click OK.

See also

Section 3.7.16.2, "Displaying report output in the Paper Design view"

#### 3.7.17.2 Printing a report from the Previewer

To print a report from the Previewer:

1. In the Previewer, click the Page Setup button in the toolbar to verify your printer setup.

2. Click the Print button in the toolbar.

3. In the Print dialog box, specify the pages and copies you want to print.

   If the report has a Destination Type of Screen (set by system parameter `DESTYPE`), a warning displays to 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.

4. Click OK.

See also

Section 3.7.16.3, "Displaying report output in the Previewer"

#### 3.7.17.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:

1. 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:

     - set the Width and Height properties as desired (for example, 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 (for example, 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.

2. Display your report output in your Web browser (see Section 3.7.16.4, "Displaying report output in your Web browser").

3. Choose File > Print.

See also

Section 1.2.2, "About Web reports"

Section 2.2.2, "About previewing JSP-based Web reports"

Section 2.2.11, "About style sheets"

#### 3.7.17.4 Printing a report on a preprinted form

See the example report in Chapter 32, "Building a Report Using a Preprinted Form".

#### 3.7.17.5 Switching the printer tray

To switch the printer tray:

1. Choose File > Page Setup and in the Source list, note the names of the printer trays defined for your printer.

2. 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").

See also

Section 2.8.6, "About switching the printer tray"

#### 3.7.17.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 ().

Oracle Reports 10g (9.0.4) introduced 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 eliminates the dependency on having a valid printer defined (`PRINTER` and `TK_PRINTER` environment variables set to a valid printer, or a valid entry in `uiprint.txt`) 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.

  This driver is the first entry in `uiscreenprint.txt`. The file locations (UNIX only) are:

      uiscreenprint.txt: ORACLE_HOME/guicommon/tk/admin
      screenprinter.ppd: ORACLE_HOME/guicommon/tk/admin/PPD

Limitations

- If your report includes an Oracle Graphics 6i 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 Oracle Graphics 6i graph without a valid printer, set the `PRINTER` environment variable to any value.

  Note: Oracle Graphics 6i, 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 Oracle Reports online Help topic "Displaying Oracle Graphics 6i charts in Oracle Reports 10g").

### 3.7.18 E-mailing a report

To e-mail a report from Reports Builder:

1. Log on to your Internet Standard Protocol SMTP mail application.

2. Select or open the report.

3. If you are not already connected, connect to a database.

4. Choose File > Mail.

5. In the Mail dialog box, specify the recipients and subject line for your e-mail report.

6. 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).

1. log on to your Internet Standard Protocol SMTP mail application.

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

Usage notes

The configuration file `rwbuilder.conf` must include the `pluginParam mailServer` with the outgoing mail server name. This applies in both the Windows 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:

- Creating a query

- Modifying a query

- Using Query Builder

- Selecting HTML tags from the database

- Selecting an image from the database

- Selecting an image URL from the database

- Creating a break group

- Creating a matrix (cross-product) group

- Creating a data link

- Creating or editing a formula column

- Creating a summary column

- Creating or editing a placeholder column

### 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 CURSOR`s)

See also

Section 1.7.1, "About queries"

Section 1.7.3, "About database columns"

#### 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:

1. In the Data Model view, choose Insert > Query to display the Data Wizard.

2. Follow the wizard to create the first query for the data model.

3. Repeat Steps 1 and 2 for each query you want to create.

4. Modify the resulting data model in the Data Model view.

5. 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:

1. In the Data Model view, single-click the SQL Query tool in the tool palette.

2. Click in the main area (canvas region) of the window.

3. 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 Query.

   - To enter the `SELECT` statement yourself, type it in the SQL Query Statement field.

     Tip: Select the columns in the order you want them to appear in the report output.

4. Click OK.

5. 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.

6. 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:

1. In the Data Model view, single-click the XML Query tool in the tool palette.

2. Click in the main area (canvas region) of the window.

3. 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.

4. Click OK.

#### 3.8.1.5 Creating a query: JDBC Query tool

To create a JDBC query:

1. In the Data Model view, single-click the JDBC Query tool in the tool palette.

2. Click in the main area (canvas region) of the window.

3. In the JDBC Query dialog box, define the JDBC query or procedure and the connection parameters for the data source. Click Help for assistance.

4. Click OK.

#### 3.8.1.6 Creating a query: Text Query tool

To create a Text query:

1. In the Data Model view, single-click the Text Query tool in the tool palette.

2. Click in the main area (canvas region) of the window.

3. In the Define Text Query dialog box, specify the data definition and data source for the text query. Click Help for assistance.

4. Click OK.

#### 3.8.1.7 Creating a query: Express Server Query tool

To create an Express Server query:

1. In the Data Model view, single-click the Express Server Query tool in the tool palette.

2. Click in the main area (canvas region) of the window.

3. In the dialog box, specify the definition for the Express Server query. Click Help for assistance.

4. Click OK.

#### 3.8.1.8 Creating a query: OLAP Query tool

To create an OLAP query:

1. In the Data Model view, single-click in the tool palette.

2. Click in the main area (canvas region) of the window.

3. In the dialog box, specify the definition for the OLAP query. Click Help for assistance.

4. Click OK.

#### 3.8.1.9 Creating a query: REF CURSOR Query tool

To create a `REF CURSOR` query:

1. 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")

2. If the package created in Step 1 is an external PL/SQL library, you must attach it to the report before referencing it.

3. In the Data Model view, single-click the REF CURSOR Query tool in the tool palette.

4. Click in the main area (canvas region) of the window.

5. 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;

6. 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.

7. Repeat Steps 1 through 6 for each `REF CURSOR` query you want to create.

See also

Section 2.6.10, "About REF CURSOR queries"

### 3.8.2 Modifying a query

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

1. In the Data Model view, click the query object, then right-click and choose Data Wizard.

2. To change the `SELECT` statement, click the Data tab.

3. To change the break groups, click the Groups tab.

4. To add or change summary columns for totals, click the Totals tab.

5. 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:

1. In the Data Model view, click the query object, then right-click and choose Property Inspector.

2. 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 Oracle Reports online Help for topics about using Query Builder.

See also

Section 1.7.5, "About Query Builder"

### 3.8.4 Selecting HTML tags from the database

Note: This procedure applies to paper-based reports.

To select HTML tags from the database:

1. Create a query, with a `SELECT` statement that selects a column containing HTML tags or the names of files that contain HTML tags.

2. In the Data Model view, double-click the HTML tags column to display the Property Inspector.

3. 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.

4. In the Paper Layout view, create a field object that references the column.

5. Double-click the field object to display the Property Inspector.

6. In the Property Inspector, under the Web Settings node, set the Contains HTML Tags property to Yes.

Usage notes

When the text object's Contains HTML Tags property is set to Yes, HTML tags and attributes are formatted in your report output as follows:

- If the report is generated to HTML or HTMLCSS output, all HTML tags and attributes are interpreted and formatted in the output.

- If the report is generated to other bitmap output formats (for example, PDF, RTF, PostScript), a fixed set of HTML tags and attributes are interpreted and formatted in the output, as described in Section 2.8.9, "About HTML formatting". Any HTML not in the supported set of tags and attributes are not interpreted, and appear as is in the report output.

### 3.8.5 Selecting an image from the database

Note: These steps, and the steps in Section 3.9.8.1.2, "Linking an image object to a file" (for paper-based reports only), allow you to include images in a wide variety of formats supported by Oracle Reports, including JPEG (all types, such as Progressive JPEG and Exif JPEG), PNG, BMP, TIFF, GIF, and CGM. For paper-based reports, you can also use Insert > Image to import an image from a file into the report layout for images in the following formats: TIFF, JFIF, BMP, TGA, PCX, PICT, GIF, CALS, RAS, OIF, PCD (see Section 3.9.8.1.1, "Importing an image").

To select an image from the database:

1. Create a query that selects a column containing images or the filenames of image objects.

2. In the Data Model view, double-click the image column to display the Property Inspector.

3. 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.

4. In both cases (a column containing images or a column containing the filenames of image objects): under the Column node, set the File Format property to Image.

5. If you are developing a JSP-based Web report, you will also need to add the `containsHtml` attribute to the `<rw:field>` JSP tag for the column, as follows:

   - Display the Web Source view for the report.

   - Search for the `rw:field` tag for the column (`<rw:field ... src="column_name" ...>`)

   - Add the `containsHtml=yes` attribute to specify that the field's value contains an image.

     For example:

         <rw:field id="F_MYIMAGE" src="MYIMAGE" nullValue="&nbsp;" containsHtml="yes">

6. To preview your report output with Web layout in a Web browser, choose Program > Run Web Layout to run the Web Source. If you have made changes to the Web Source view (for JSP-based Web reports), this enables you to immediately see the effect of your changes on the output.

See also

Section 2.4.3, "About images"

Section 1.7.3, "About database columns"

### 3.8.6 Selecting an image URL from the database

Notes: This procedure is for HTML output only. These steps, and the steps in Section 3.9.8.1.3, "Linking an image object to a URL" (for paper-based reports only), allow you to include images in a wide variety of formats supported by Oracle Reports, including JPEG (all types, such as Progressive JPEG and Exif JPEG), PNG, BMP, TIFF, GIF, and CGM. For paper-based reports, you can also use Insert > Image to import an image from a file into the report layout for images in the following formats: TIFF, JFIF, BMP, TGA, PCX, PICT, GIF, CALS, RAS, OIF, PCD (see Section 3.9.8.1.1, "Importing an image").

To select a URL from the database that point to an image to include in an HTML report:

1. Create a query, with a `SELECT` statement that selects a column containing URLs.

2. In the Data Model view, double-click the column containing the URLs to display the Property Inspector.

3. Under the Column node:

   - Set the Read from File property to Yes.

   - Set the File Format property to Image URL.

4. If you are developing a JSP-based Web report, you will also need to add the `containsHtml` attribute to the `<rw:field>` JSP tag for the column, as follows:

   - Display the Web Source view for the report.

   - Search for the `rw:field` tag for the column (`<rw:field ... src="column_name" ...>`)

   - Add the `containsHtml=yes` attribute to specify that the field's value contains an image.

     For example:

         <rw:field id="F_MYIMAGE" src="MYIMAGE" nullValue="&nbsp;" containsHtml="yes">

5. To preview your report output with Web layout in a Web browser, choose Program > Run Web Layout to run the Web Source. If you have made changes to the Web Source view (for JSP-based Web reports), this enables you to immediately see the effect of your changes on the output.

See also

Section 2.4.3, "About images"

Section 2.8.8, "About HTML and HTMLCSS output"

### 3.8.7 Creating a break group

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

To create a break group manually:

1. 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 (5 centimeters).

2. Select the column that you want to use to divide your report (for example, 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.

3. Double-click the new group object to display its Property Inspector, where you can set properties for the break group.

See also

Section 1.7.2, "About groups"

Section 1.3.2, "About group above reports"

Section 1.3.3, "About group left reports"

### 3.8.8 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:

1. In the Data Model view, right-click and choose Data Wizard.

2. On the Query page, select the Matrix query check box.

3. Follow the wizard to select the columns for the matrix group(s), rows, columns, and cells.

   To create a matrix (cross-product) group manually:

4. In the Data Model view, drag the title bar of the group object down.

5. Drag the "row" column and "column" column out of the group object into the space to create two new groups.

6. Single-click the Cross Product tool in the tool palette.

7. Drag a box around the groups you want to include in the matrix (cross-product) group.

8. 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.

See also

Section 2.3.7, "About matrix objects"

Section 1.3.7, "About matrix reports"

Section 2.1.7, "About nested matrix reports"

Section 2.1.8, "About matrix with group reports"

Section 1.7.2, "About groups"

Section 3.9.1.3, "Creating a matrix object"

Section 3.5.3, "Creating a nested matrix report"

Section 1.3.2, "About group above reports"

Section 1.3.3, "About group left reports"

### 3.8.9 Creating a data link

To create a data link:

1. 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.

2. 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.

3. Double-click the new link object to display the Property Inspector, and set the desired properties.

See also

Section 1.7.4, "About data links"

Section 2.3.5, "About non-linkable queries"

### 3.8.10 Creating or editing a formula column

To create or edit a formula column:

1. 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.

2. Double-click the formula column object to display the Property Inspector.

3. Under the Placeholder/Formula node, double click the PL/SQL Formula property field.

4. 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)

or

    CompSal(:SAL) + COMM

See also

Section 2.3.2, "About formula columns"

### 3.8.11 Creating a summary column

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

1. In the Data Model view, click the query that contains the column you want to total.

2. Choose Tools > Data Wizard.

3. 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:

1. 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.

2. Double-click the new summary column object to display the Property Inspector.

3. Under the Summary node:

   - Set the Function property by choosing the type of summary you want from the list.

   - Set the Source property to the column you want to summarize.

   - Set the Reset At property to the group at which the summary column value resets.

Usage notes

For JSP-based Web reports with a Group Above or Matrix style, if any summary column values are not correctly left-aligned when you click the Run Web Layout button in the toolbar (or choose Program > Run Web Layout), you can implement the following workaround to align the values:

- In the Web Source view, locate the section where the summary column is defined, and delete the following line to remove the extra space:

      <th class="summary_column_name"> </th>

See also

Section 2.3.1, "About summary columns"

Chapter 9, "Building a Summary Report"

Chapter 13, "Building a Group Left Summary Report"

### 3.8.12 Creating or editing a placeholder column

To create or edit a placeholder column:

1. 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.

2. Double-click the placeholder column object.

3. In the Property Inspector, set the desired properties for the placeholder column.

4. 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)

See also

Section 2.3.2, "About formula columns"

## 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:

- General Layout Objects

- Text Objects

- Page Numbers or Date/Time Stamps

- Borders

- Anchors

- Colors, Patterns, and Highlighting

- Format Masks

- Graphic or Image Objects

- Page or Group Headers or Footers

- Margin, Header Page, or Trailer Page Objects

- Move Objects

- Resize Objects

- Change Spacing

- Modify the Page Layout

### 3.9.1 General Layout Objects

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

- Creating a field object

- Creating a frame or repeating frame

- Creating a matrix object

- Creating a barcode using a barcode font

- Applying conditional formatting to a layout object

#### 3.9.1.1 Creating a field object

To create a field object manually in the report layout:

1. In the Paper Design view, Paper Layout view, or Paper Parameter Form view, click the Field tool in the tool palette.

2. Click and drag a rectangle.

3. Double-click the new field object.

4. 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.

5. Set other properties as desired.

See also

Section 1.8.4, "About fields"

Section 1.9.2, "About Parameter Form fields"

#### 3.9.1.2 Creating a frame or repeating frame

To create a frame or repeating frame manually:

1. In the Paper Layout view, click the Frame tool or the Repeating Frame tool in the tool palette.

2. Click and drag a rectangle.

   Note: The size of the rectangle must be large enough to include the objects that it will contain.

3. Double-click the new frame object.

4. In the Property Inspector, set the desired properties.

See also

Section 1.8.1, "About frames"

Section 1.8.2, "About repeating frames"

Section 1.8.3, "About frame and repeating frame sizing"

#### 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:

1. In the Paper Layout view, click the Repeating Frame tool in the tool palette, then drag a rectangle to create a repeating frame.

2. 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.

3. 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.

4. 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.

5. 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

   Description of "Figure 3-1 Example matrix object"

6. Double-click the matrix object to set its properties.

See also

Section 2.3.7, "About matrix objects"

Section 1.3.7, "About matrix reports"

Section 2.1.7, "About nested matrix reports"

Section 2.1.8, "About matrix with group reports"

Section 3.8.8, "Creating a matrix (cross-product) group"

Section 3.5.3, "Creating a nested matrix report"

#### 3.9.1.4 Creating a barcode using a barcode font

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

To create a barcode using a barcode font:

1. Install a barcode font on your machine (for example, 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.

2. Create a query that selects a column from the database containing the numbers for the barcodes you want included in your report.

3. 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:

1. 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.

2. In the Conditional Formatting dialog box, click New to display the Format Exception dialog box.

3. Specify the conditions and formatting attributes to be applied when the condition(s) evaluate to TRUE.

4. 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.

See also

Section 2.1.6, "About conditional formatting"

Chapter 3, "Highlighting a value"

Chapter 3, "Highlighting a row"

### 3.9.2 Text Objects

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

- Creating a boilerplate object for text

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

- Creating a boilerplate text object for HTML tags

- Editing text

- Referencing a field in boilerplate text

- Linking a boilerplate text object to a file

- Linking an HTML text object to a file

- Wrapping text in a field

- Changing text attributes

#### 3.9.2.1 Creating a boilerplate object for text

To create a boilerplate object for text:

1. In the Paper Design view, Paper Layout view or Paper Parameter Form view, click the Text tool in the tool palette.

2. Click and drag a rectangle.

3. 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.

4. Click outside the boilerplate text object.

See also

Section 1.8.5, "About boilerplate objects"

#### 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:

1. 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).

2. 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;

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

See also

Section 1.8.5, "About boilerplate objects"

#### 3.9.2.3 Creating a boilerplate text object for HTML tags

Note: This procedure applies to paper-based reports.

To create a boilerplate text object for HTML tags:

1. In the Paper Design view, click the Text tool in the tool palette.

2. Click and drag a rectangle.

3. In the new boilerplate object, type the desired HTML.

4. Click outside the boilerplate text object.

5. Double-click the boilerplate text object (or right-click the object, and choose Property Inspector).

6. In the Property Inspector, under the Web Settings node, set the Contains HTML Tags property to Yes.

Usage notes

When the text object's Contains HTML Tags property is set to Yes, HTML tags and attributes are formatted in your report output as follows:

- If the report is generated to HTML or HTMLCSS output, all HTML tags and attributes are interpreted and formatted in the output.

- If the report is generated to other bitmap output formats (for example, PDF, RTF, PostScript), a fixed set of HTML tags and attributes are interpreted and formatted in the output, as described in Section 2.8.9, "About HTML formatting". Any HTML not in the supported set of tags and attributes are not interpreted, and appear as is in the report output.

Example 1: Turning formatting on and off

Consider a text boilerplate object that is defined as follows:

    <i><b>Design time format</b> is merged with <span style="font-family:Arial;
    color:#00F">run time formatting instructions.</span></i>

With its Contains HTML Tags property set to Yes, this will format as:

Design time format is merged with run time formatting instructions.

With its Contains HTML Tags property set to No, this will format in the report output as:

<i><b>Design time format</b> is merged with <span style="font-family:Arial; color:#00F">run time formatting instructions.</span></i>

Example 2: Boilerplate text object tagged as a hyperlink

In a boilerplate text object, you can type the following text:

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

With the Contains HTML tags property set to Yes, this object will appear in HTML/HTMLCSS report output as a graphic (oracle.gif) , which the end user can click to link to my_report.html.

Example 3: Parameter values output to HTML

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>

With the Contains HTML tags property set to Yes, this object will be interpreted with the parameters replaced with their actual values, as follows:

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

See also

Section 1.8.5, "About boilerplate objects"

Section 2.5.1, "About Parameter Form HTML extensions"

#### 3.9.2.4 Editing text

To insert and replace text:

1. In the Paper Design view, Paper Layout view, or Paper Parameter Form view, single-click the text object.

2. Click the text to change to edit mode.

3. Modify the text as desired, then click outside the text object.

To delete text:

1. In the Paper Design view, Paper Layout view, or Paper Parameter Form view, single-click the text object.

2. To cut all the text, choose Edit > Delete.

To cut, copy, or paste text:

1. In the Paper Layout view or Parameter Form view, single-click the text object.

2. Click and drag to mark the text you want to cut or copy, then choose Edit > Cut or Edit > Copy.

3. To paste text, choose Edit > Paste.

#### 3.9.2.5 Referencing a field in boilerplate text

To reference a field in boilerplate text:

1. In the Paper Design view or Paper Layout view, click in the boilerplate text where you want the reference to a field to appear.

2. 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 (for example, `&<``fieldname``>Oracle`). If the field reference follows other text, no angle brackets are needed (for example, `Oracle&``fieldname`). You can include field references right next to each other with no spaces in between and without angle brackets (for example, `&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 (for example, `&SAL`). For example, `$&<SAL>/week` may produce `$800/week` in the report output.

See also

Section 1.8.4, "About fields"

Section 1.8.5, "About boilerplate objects"

#### 3.9.2.6 Linking a boilerplate text object to a file

Note: This procedure applies paper-based reports. File Link objects are not implemented in JSP-based Web reports.

To link a boilerplate text object to a file:

1. In the Paper Design view or Paper Layout view, click the File Link tool in the tool palette.

2. Click and drag a rectangle.

3. Double-click the file link object to display the Property Inspector.

4. Under the File Link Boilerplate node:

   - Set the Source File Format property to Text.

   - Set the Source Filename property to the name of the file containing the text.

See also

Section 1.8.5, "About boilerplate objects"

#### 3.9.2.7 Linking an HTML text object to a file

Note: This procedure applies to paper-based reports. File Link objects are not implemented in JSP-based Web reports.

To link an HTML object to a file:

1. In the Paper Design view or Paper Layout view, click the File Link tool in the tool palette.

2. Click and drag a rectangle.

3. Double-click the file link object to display the Property Inspector.

4. Under the File Link 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.

5. Under the Web Settings node, set the Contains HTML Tags property to Yes.

Usage notes

When the text object's Contains HTML Tags property is set to Yes, HTML tags and attributes are formatted in your report output as follows:

- If the report is generated to HTML or HTMLCSS output, all HTML tags and attributes are interpreted and formatted in the output.

- If the report is generated to other bitmap output formats (for example, PDF, RTF, PostScript), a fixed set of HTML tags and attributes are interpreted and formatted in the output, as described in Section 2.8.9, "About HTML formatting". Any HTML not in the supported set of tags and attributes are not interpreted, and appear as is in the report output.

See also

Section 1.8.5, "About boilerplate objects"

#### 3.9.2.8 Wrapping text in a field

To wrap text in a field:

1. Choose Tools > Report Wizard.

2. In the Report Wizard, click the Labels tab.

3. Change the value in the Width column for the field in which you want to wrap text, as desired.

4. 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.

5. In the Paper Layout view or Parameter Form view, double-click the field to display the Property Inspector, and set the following properties:

   - Under General Layout, verify that the Vertical Elasticity property is set to Expand.

#### 3.9.2.9 Changing text attributes

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

1. 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.

2. From the Format menu, choose Font, Justify, Text Spacing, or Direction.

#### 3.9.2.10 Changing text attributes using PL/SQL

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

1. In the Object Navigator, expand the Paper Layout node.

2. 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.

3. 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

See also

Topic "SRW built-in package" in the Reference > PL/SQL Reference > Built-in Packages section of the Oracle Reports online Help.

### 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:

- Creating page numbers

- Resetting page numbers

- Creating a time or date stamp

#### 3.9.3.1 Creating page numbers

To create default page numbers:

1. In the Paper Layout view or Paper Design view, choose Insert > Page Number.

2. In the Insert Page Number dialog box, choose from the list the location for the page number.

3. Click the desired page number format: Page Number Only or Page Number and Total Pages.

4. Optionally, change the default attributes of the page number text as desired.

To create customized page numbers:

1. 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.

2. 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.

3. 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.

4. 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.5, "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):

1. In the Paper Layout view or Paper Design view, click the default page number field, then choose Edit > Delete to delete it.

2. Create customized page numbers (see Section 3.9.3.1, "Creating page numbers").

3. In the page number field's Property Inspector, under Field, double-click the Page Numbering property value.

4. 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 or date stamp

To add a date or time stamp to your report:

1. In the Paper Layout view or Paper Design view, choose Insert > Date and Time.

2. In the Insert Date and Time dialog box, choose from the list the location for the date or time stamp.

3. Click the desired date 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:

- Showing or hiding object borders

- Changing object border attributes

- Changing the current mode (Confine or Flex)

#### 3.9.4.1 Showing or hiding object borders

To show or hide the borders around an object:

1. 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.

2. Click the Line color tool in the tool palette.

3. To add a border, click any color solid square.

4. 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.

5. 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:

1. 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.

2. To change the line width, choose Format > Line > Line Width.

3. 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.

4. To "frame" the border, choose Format > Bevel.

See also

Section 2.4.5, "About changing colors and patterns"

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

#### 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.

See also

Section 2.4.7, "About moving and layering objects in the Paper Layout view"

Chapter 3, "Adjusting parent borders automatically"

Chapter 3, "Moving an object outside its parent"

### 3.9.5 Anchors

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

- Anchoring objects together

- Viewing implicit anchors

- Moving an anchor

See also

Section 2.4.4, "About 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:

1. In the Paper Layout view, click the Anchor tool in the tool palette.

2. 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:

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

2. 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.

See also

Section 2.4.4.1, "Implicit anchoring algorithm"

#### 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:

1. In the Paper Layout view, click the anchor.

2. 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 (for example, 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:

1. Double-click the anchor object to display the Property Inspector.

2. Set the Child Edge Percent property 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 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:

- Setting color palette preferences

- Changing colors

- Changing patterns

- Changing colors and patterns using PL/SQL

- Modifying the color palette

- Importing or exporting a color palette

- Highlighting a value

- Highlighting a row

#### 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:

1. 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.

2. 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.

3. On the color palette, click a color.

See also

Section 2.4.5, "About changing colors and patterns"

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

#### 3.9.6.3 Changing patterns

To change the pattern of an object:

1. 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.

2. 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).

3. On the color palette, click Patterns.

4. On the pattern palette, click a pattern. To change the foreground and background colors, choose from the color palettes at the bottom of the pattern palette.

See also

Section 2.4.5, "About changing colors and patterns"

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

Topic "Pattern color palette" in the Reference > Color and Pattern Palettes section of the Oracle Reports online Help.

#### 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:

1. In the Object Navigator, expand the Paper Layout node.

2. Double-click the PL/SQL icon next to the object for which you want to change the color.

3. 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

See also

Section 2.4.5, "About changing colors and patterns"

Topic "SRW built-in package" in the Reference > PL/SQL Reference > Built-in Packages section of the Oracle Reports online Help.

#### 3.9.6.5 Modifying the color palette

To modify the color palette:

1. 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.

2. In the Paper Layout view, choose Format > Color Palette > Edit.

3. 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.

Note: A report can have only one color palette.

See also

Section 2.4.5, "About changing colors and patterns"

Topics "Oracle CDE1 color palette", "Default color palette", and "Grayscale color palette" in the Reference > Color and Pattern Palettes section of the Oracle Reports online Help.

#### 3.9.6.6 Importing or exporting a color palette

To import or export a color palette:

1. 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.

2. In the Paper Layout view, choose Format > Color Palette > Import or Format > Color Palette > Export.

3. In the dialog box, specify the name and format of the file.

4. Click OK.

Note: A report can have only one color palette.

See also

Section 2.4.5, "About changing colors and patterns"

Topics "Oracle CDE1 color palette", "Default color palette", and "Grayscale color palette" in the Reference > Color and Pattern Palettes section of the Oracle Reports online Help.

#### 3.9.6.7 Highlighting a value

To highlight a value in a report:

1. In the Paper Layout view or Paper Design view, click the field that contains the value to be highlighted.

2. Choose Format > Conditional Formatting.

3. In the Conditional Formatting dialog box, click New to display the Format Exception dialog box.

4. Select the field and define the condition(s) when the value should be highlighted.

5. 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.

6. 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;

See also

Section 2.1.6, "About conditional formatting"

Section 3.9.1.5, "Applying conditional formatting to a layout object"

#### 3.9.6.8 Highlighting a row

To highlight an entire row:

1. In the Paper Layout view, click the repeating frame that contains the fields that make up the rows to be highlighted.

2. Choose Format > Conditional Formatting.

3. In the Conditional Formatting dialog box, click New to display the Format Exception dialog box.

4. Select the field(s) and define the condition(s) that describe the rows to be highlighted.

5. 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.

6. 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;

See also

Section 2.1.6, "About conditional formatting"

Section 3.9.1.5, "Applying conditional formatting to a layout object"

#### 3.9.6.9 Alternating row colors

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

- See Chapter 44, "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:

- Specifying date and time format masks

- Specifying number format masks

- Applying a format mask to a numeric object

- Applying a format mask to a date object

- Adding a custom format mask

- Changing the format mask for multiple fields

#### 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 Date and time format masks

Format Mask | Description
--- | ---
SCC or CC | Century, abbreviated; 'S' prefixes "BC" with (-)
SYYYY or YYYY | Year; 'S' prefixes "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 (for example, MONDAY)
DY | Name of day, 3-letter abbreviation (for example, 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 (for example, 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, regardless of other tokens.
K | Prints a blank space.
<> | Delineates the beginning and ending of the decimal-aligned region (that is, 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. That is, 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 (for example, 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

1. To apply a format mask to a numeric object:

2. 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.

3. To apply the currency format to the object, click the Currency button in the toolbar.

4. To apply the percentage format to the object, click the Percentage button in the toolbar.

5. 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.

6. Choose Tools > Property Inspector.

7. Under the Field node, verify the Format Mask property is set to the desired format.

8. 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:

1. 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.

2. Choose Tools > Property Inspector.

3. Under the Field node, set the Format Mask property to the desired date format.

4. 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:

1. Choose Edit > Preferences.

2. In the Preferences dialog box, click Edit Masks.

3. In the Format Masks dialog box, set Display to the type of format mask you want to add.

4. In the Mask field, type the format mask to add, then click Add.

5. 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:

1. In the Paper Layout view, click the fields you want to change.

2. Choose Tools > Property Inspector.

3. 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:

- Adding an image

- Adding a graph

- Editing a graph

- Adding a graph hyperlink

- Creating a drawing object

#### 3.9.8.1 Adding an image

To add an image to your report, perform the steps in any of the topics listed below.

For paper-based reports and JSP-based Web reports:

- Section 3.8.5, "Selecting an image from the database"

- Section 3.8.6, "Selecting an image URL from the database"

For paper-based reports only:

- Importing an image

- Linking an image object to a file

- Linking an image object to a URL

See also

Section 2.4.3, "About images"

##### 3.9.8.1.1 Importing an image

Note: This procedure applies to paper-based reports. For JSP-based Web reports, see the Usage notes, below. These steps allow you to include images in the following formats: TIFF, JFIF, BMP, TGA, PCX, PICT, GIF, CALS, RAS, OIF, PCD. For images in other formats supported by Oracle Reports, including JPEG (all types, such as Progressive JPEG and Exif JPEG), PNG, BMP, TIFF, GIF, and CGM, see Section 3.9.8.1.2, "Linking an image object to a file" or Section 3.8.5, "Selecting an image from the database".

To import an image:

1. In the Paper Layout view, choose Insert > Image and click the type of object you want to import.

2. In the dialog box, specify the name and format of the file. Click OK.

3. Move the object to the desired position.

Usage notes

This procedure is for paper-based reports only. If you want to include static images in your JSP-based Web reports, you can add HTML `<img>` tags in the Web Source view.

##### 3.9.8.1.2 Linking an image object to a file

Notes: This procedure applies to paper-based reports. File link objects are not implemented in JSP-based Web reports. These steps, and the steps in Section 3.8.5, "Selecting an image from the database", allow you to include images in a wide variety of formats supported by Oracle Reports, including JPEG (all types, such as Progressive JPEG and Exif JPEG), PNG, BMP, TIFF, GIF, and CGM. For paper-based reports, you can also use Insert > Image to import an image from a file into the report layout for images in the following formats: TIFF, JFIF, BMP, TGA, PCX, PICT, GIF, CALS, RAS, OIF, PCD (see Section 3.9.8.1.1, "Importing an image", above).

To link an image object to a file:

1. In the Paper Design view or Paper Layout view, click the File Link tool in the tool palette.

2. Click and drag a rectangle.

3. Double-click the file link object to display the Property Inspector.

4. Under the File Link Boilerplate node:

   - 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.1.3 Linking an image object to a URL

Notes: This procedure applies to paper-based reports output to an HTML or HTMLCSS file. This is unrelated to 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). Additionally, file link objects are not implemented in JSP-based Web reports. These steps, and the steps in Section 3.8.6, "Selecting an image URL from the database", allow you to include images in a wide variety of formats supported by Oracle Reports, including JPEG (all types, such as Progressive JPEG and Exif JPEG), PNG, BMP, TIFF, GIF, and CGM. For paper-based reports, you can also use Insert>Image to import an image from a file into the report layout for images in the following formats: TIFF, JFIF, BMP, TGA, PCX, PICT, GIF, CALS, RAS, OIF, PCD (see Section 3.9.8.1.1, "Importing an image", above).

To link an image object to a URL that points to an image to include in an HTML report:

1. In the Paper Design view or Paper Layout view, click the File Link tool in the tool palette.

2. Click and drag a rectangle.

3. Double-click the file link object to display the Property Inspector.

4. Under the File Link 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: Image URL

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

Example 2: Image URL containing user parameter

    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 (for example, `P_SERVER_NAME=www.oracle.us.com` for a dynamic URL link of `http://www.oracle.us.com/images/logo.gif)`.

Example 3: URL to a file

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

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

#### 3.9.8.2 Adding a graph

To add a graph to your report, follow the steps in either of the following topics:

- Adding a graph to a paper-based report

- Adding a graph to a JSP-based Web report

Usage notes

Inserting a graph with a once-per-group position in the Paper Design view of a matrix with group report causes Reports Builder to fail. As a workaround, insert the graph in the Paper Layout view, rather than the Paper Design view, when adding a graph to a matrix with group report.

See also

Section 1.4.4, "About the Graph Wizard"

Section 1.6.6, "About the Web Source view"

##### 3.9.8.2.1 Adding a graph to a paper-based report

To add a graph to your paper-based report:

1. In the Paper Layout view, click the Graph tool in the tool palette.

2. Drag a square in the area where the graph should appear to display the Graph Wizard.

3. In the Graph Wizard, specify information for the graph. Click Help on any wizard page for assistance.

   Note: When you specify a graph title, subtitle, footnote, or axis title in the Graph Wizard, you can insert lexical references (to user parameters, system parameters, and columns) in the text. For example, in the Show Title field on the Title page of the Graph Wizard, type: `titletext` `&<P_1>` to insert the value of the user parameter `P_1` into the title text at runtime. The angle brackets (`<>`) are required around the parameter or column name..

4. Double-click the graph object you have created to display the Property Inspector, and set properties as desired.

   To customize your graph XML definition beyond the scope of the Graph Wizard, you can directly edit the XML in the graph's Graph Settings property. The full list of attributes available is defined in the DTD file, `graph.dtd`.

   Note: The `graph.dtd` is available in the following locations: `ORACLE_HOME``\jlib\bigraphbean.jar` In Getting Started with Oracle Reports on the Oracle Technology Network (OTN): on the Oracle Reports 10g page (`http://www.oracle.com/technology/products/reports/index.html`), click Getting Started to display the Getting Started with Oracle Reports home page. In the list of topic sections on the left, click Index. In the Collateral Type list, choose Documentation and click Search. In the list that displays, find the Documented Graph DTD.

5. To re-enter the Graph Wizard, do either of the following:

   - Right-click the graph, and choose Graph Wizard.

   - Click the graph, then choose Edit > Settings.

##### 3.9.8.2.2 Adding a graph to a JSP-based Web report

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

1. 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.

2. Locate the section in the source code where you want to add the graph.

   Note: Previously adding some text such as "Place the graph here" to your Web page enables you to easily locate the correct position for your graph.

3. Choose Insert > Graph.

4. In the Graph Wizard, specify the information for the graph. Click Help on any tab page for assistance.

   Note: When you specify a graph title, subtitle, footnote, or axis title in the Graph Wizard, you can insert lexical references (to user parameters, system parameters, and columns) in the text. For example, in the Show Title field on the Title page of the Graph Wizard, type: `titletext` `&<P_1>` to insert the value of the user parameter `P_1` into the title text at runtime. The angle brackets (`<>`) are required around the parameter or column name.

5. Click the Run Web Layout button in the toolbar (or choose Program > Run Web Layout) to display your report and graph in your Web browser.

   Notes: If Netscape 7.0 is your default browser, and the browser does not display, set the registry key `HKEY_CURRENT_USERS\Software\Oracle\Toolkit\Tkbrowser` to the default browser location. Ensure that the `BrowserName` and the `BrowserPath` keys reflect the correct values. For example: `BrowserName=Netscape 7`; `BrowserPath=C:\Program Files\Netscape\Netscape\Netscp.exe`. If Run Web Layout fails with an error related to the `major.minor` version, you will need to update the environment variables `CLASSPATH` or `REPORTS_CLASSPATH`, as follows: remove the 9.0.x `ORACLE_HOME` specification in the `oc4j.jar` file path (the `oc4j.jar` file path should specify only the 10g Release 2 (10.1.2) `ORACLE_HOME`).

6. 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.

   To customize your graph XML definition beyond the scope of the Graph Wizard, you can directly edit the XML in the Web Source view. The full list of attributes available is defined in the DTD file, `graph.dtd`.

Note: The `graph.dtd` is available in the following locations: `ORACLE_HOME``\jlib\bigraphbean.jar` In Getting Started with Oracle Reports on the Oracle Technology Network (OTN): on the Oracle Reports 10g page (`http://www.oracle.com/technology/products/reports/index.html`), click Getting Started to display the Getting Started with Oracle Reports home page. In the list of topic sections on the left, click Index. In the Collateral Type list, choose Documentation and click Search. In the list that displays, find the Documented Graph DTD.

#### 3.9.8.3 Editing a graph

To add a graph to your report, follow the steps in either of the following topics:

- Editing a graph in a paper-based report

- Editing a graph in a JSP-based Web report

See also

Section 1.4.4, "About the Graph Wizard"

Section 1.6.6, "About the Web Source view"

##### 3.9.8.3.1 Editing a graph in a paper-based report

To edit a graph in a paper-based report:

1. 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.

2. To edit the XML definition of the graph:

   - Right-click the graph, 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. To customize your graph XML definition beyond the scope of the Graph Wizard, specify additional `rw:graph` tag attributes. The full list of attributes available is defined in the DTD file, `graph.dtd`.

     Note: The `graph.dtd` is available in the following locations: `ORACLE_HOME``\jlib\bigraphbean.jar` In Getting Started with Oracle Reports on the Oracle Technology Network (OTN): on the Oracle Reports 10g page (`http://www.oracle.com/technology/products/reports/index.html`), click Getting Started to display the Getting Started with Oracle Reports home page. In the list of topic sections on the left, click Index. In the Collateral Type list, choose Documentation and click Search. In the list that displays, find the Documented Graph DTD.

3. To re-enter the Graph Wizard to redefine the graph, do either of the following:

   - Right-click the graph, and choose Graph Wizard.

   - Click the graph, then choose Edit > Settings.

##### 3.9.8.3.2 Editing a graph in a JSP-based Web report

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

- 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.

- Between the <`rw:graph`> and <`/rw:graph`> tags that define the graph, make changes directly to the JSP and XML tags. This enables you to customize your graph XML definition beyond the scope of the Graph Wizard. The full list of attributes available is defined in the DTD file, `graph.dtd`.

Note: The `graph.dtd` is available in the following locations: `ORACLE_HOME``\jlib\bigraphbean.jar` In Getting Started with Oracle Reports on the Oracle Technology Network (OTN): on the Oracle Reports 10g page (`http://www.oracle.com/technology/products/reports/index.html`), click Getting Started to display the Getting Started with Oracle Reports home page. In the list of topic sections on the left, click Index. In the Collateral Type list, choose Documentation and click Search. In the list that displays, find the Documented Graph DTD.

#### 3.9.8.4 Adding a graph hyperlink

You can add a graph hyperlink to a graph in your paper-based Web report (output to PDF or HTML), or JSP-based Web report. When end users display the report on the Web, they can click the areas of the graph to drill down to additional linked information.

To add a graph hyperlink to a graph, follow the steps in either of the following topics:

- Adding a graph hyperlink to a paper-based report

- Adding a graph hyperlink to a JSP-based Web report

See also

Section 2.2.6, "About graph hyperlinks"

Section 1.4.4, "About the Graph Wizard"

Section 1.6.6, "About the Web Source view"

##### 3.9.8.4.1 Adding a graph hyperlink to a paper-based report

For a paper-based Web report (for HTML or PDF output), you can add a graph hyperlink to a graph in either of the following ways:

- Property Inspector

- Graph Wizard

To specify a graph hyperlink in a paper-based report using the Property Inspector:

1. In the Paper Layout view, display the Property Inspector for the graph.

2. Set the Graph Hyperlink property to a hyperlink from any graph section to any object in your report output, another report, or any valid hyperlink destination.

   Examples:

   - A hyperlink from any graph section to any object in your report:

     `#&<dept_id>`

   - A hyperlink to another report:

     `http://my_machine:8888/reports/myWebApp/Dept_Detail.jsp? p_deptid=&<deptid>+...`

   - A hyperlink to any valid hyperlink destination:

     `www.oracle.com`

To specify a graph hyperlink in a paper-based report using the Graph Wizard:

1. On the Define Hyperlink panel, use the examples shown on the panel to enter a hyperlink value to link areas of the graph to specified destinations.

2. Click Finish.

After you have defined the graph hyperlink, generate your report to HTML, HTMLCSS, or PDF to test the graph hyperlinks. Refer to Section 3.7.5, "Generating HTML or HTMLCSS output" and Section 3.7.6, "Generating PDF output".

##### 3.9.8.4.2 Adding a graph hyperlink to a JSP-based Web report

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

- In the Web Source view of your report, modify the <rw:graph> JSP tag to inlcude the graphHyperlink attribute.

  For example:

  `graphHyperlink="http://my_machine:8888/reports/myWebApp/Dept_Detail.jsp? userid=hr/hr@mydb+server=myserver+p_deptno=&<deptno>`"

After you have defined the graph hyperlink, click the Run Web Layout button in the toolbar (or choose Program > Run Web Layout) to display your report and graph in your Web browser, and click the graph hyperlinks to test them.

#### 3.9.8.5 Creating a drawing object

To create a drawing object:

1. In the Paper Layout view, click a drawing tool in the tool palette (for example, Rectangle, Ellipse, Polyline, and so on).

2. 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.

3. 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.

4. To draw constrained objects (that is, perfect circles and squares, and so on), hold down the constrain key (for example, the shift key) when drawing the graphic.

5. Double-click the drawing object.

6. In the Property Inspector, set the desired properties.

### 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:

- Creating a text heading

- Creating a heading that includes database values

- Creating a group header or footer

#### 3.9.9.1 Creating a text heading

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

1. In the Paper Layout 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.

2. 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.

3. Create required objects in the margin area. They will appear on all pages of the report.

4. 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:

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

2. 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 (for example, the total for a department) placed in the margin will cause a frequency error when you run your report.

3. 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.

4. In the Paper Layout view, click the 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.

5. For any values that require 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.

6. Create a boilerplate text object in the margin area, and reference the field 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.5, "Referencing a field in boilerplate text".

See also

The example report in Chapter 17, "Building a Header with Database Values Report".

#### 3.9.9.3 Creating a group header or footer

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

1. In the Paper Layout view, click the Confine Off button and the Flex On button in the toolbar.

2. 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.

3. 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:

- Creating a margin object

- Creating a header page or trailer page object

#### 3.9.10.1 Creating a margin object

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

1. In the Paper Layout 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.

2. 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.

3. Create required objects in the margin area. They will appear on all pages of the report.

4. 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 of Oracle Reports.

1. In the Paper Layout view, click the Confine Off button and the Flex On button in the toolbar.

2. 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.

3. 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:

- Moving multiple objects

- Moving an object outside its parent

- Adjusting parent borders automatically

- Moving a column in report output

- Offsetting detail objects in a group report

- Aligning objects

- Changing object layering

- Rotating a boilerplate object

#### 3.9.11.1 Moving multiple objects

To move multiple objects:

1. 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.

2. Use the arrow keys on your keyboard to move the objects in the desired direction.

See also

Section 2.4.7, "About moving and layering objects in the Paper Layout view"

#### 3.9.11.2 Moving an object outside its parent

To move a child object outside its enclosing parent object:

1. In the Paper Layout view or Paper Design view, click the Confine Off button in the toolbar.

2. 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.

See also

Section 2.4.7, "About moving and layering objects in the Paper Layout view"

#### 3.9.11.3 Adjusting parent borders automatically

To adjust parent borders as you move child objects:

1. In the Paper Layout view, click the Flex On button in the toolbar.

2. 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. 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.

See also

Section 2.4.6, "About resizing objects"

#### 3.9.11.4 Moving a column in report output

To move a column in report output:

1. In the Paper Layout view or Paper Design view, click the column.

2. Click the Flex On button in the toolbar

3. Drag the column to the desired position.

See also

Section 2.4.7, "About moving and layering objects in the Paper Layout view"

Chapter 3, "Changing columns labels or widths"

Chapter 3, "Changing the default layout spacing"

#### 3.9.11.5 Offsetting detail objects in a group report

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

1. In the Paper Design view, click the Flex On button in the toolbar.

2. Select the detail objects and move them to the right.

3. Click the Flex Off button in the toolbar.

See also

Section 1.3.2, "About group above reports"

Section 1.3.3, "About group left reports"

#### 3.9.11.6 Aligning objects

To align objects:

1. 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.

2. Choose Layout > Alignment.

3. In the Align Objects dialog box, specify the desired alignment.

4. Click OK.

See also

Section 2.4.7, "About moving and layering objects in the Paper Layout view"

#### 3.9.11.7 Changing object layering

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

1. In the Paper Layout view, click the Confine Off button in the toolbar.

2. Click the object you want to move.

3. 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.

See also

Section 2.4.7, "About moving and layering objects in the Paper Layout view"

#### 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:

1. 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.

2. Click the Rotate tool in the tool palette.

3. Drag a handle to rotate the object or group.

See also

Section 1.8.5, "About boilerplate objects"

### 3.9.12 Resize Objects

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

- Resizing objects

- Making multiple objects the same size

- Resizing object borders

#### 3.9.12.1 Resizing objects

To resize one or more objects:

1. 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.

2. 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.

See also

Section 2.4.6, "About resizing objects"

Section 1.8.3, "About frame and repeating frame sizing"

#### 3.9.12.2 Making multiple objects the same size

To make multiple objects the same size:

1. 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.

2. Choose Layout > Size Objects.

3. 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:

- Changing columns labels or widths

- Changing spacing within a text object

- Changing the default layout spacing

- Changing the spacing between all rows

- Adding blank lines between groups of rows

#### 3.9.13.1 Changing columns labels or widths

To change column labels or widths:

1. Choose Tools > Report Wizard.

2. In the Report Wizard, click the Labels tab.

3. Change the values in the Labels and Width columns, as desired.

4. Click Apply.

See also

Chapter 3, "Moving a column in report output"

#### 3.9.13.2 Changing spacing within a text object

To change spacing within a text object:

1. 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.

2. 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:

1. Choose Tools > Preferences.

2. In the Preferences dialog box, click the Wizards tab.

3. To increase the space between objects and the objects they enclose, type larger values for Horizontal Gap and Vertical Gap.

4. To increase the space between fields, type larger values in Horizontal Interfield and Vertical Interfield.

5. Click OK to close the Preferences dialog box.

6. 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 grid point; the result may be that smaller changes to these values are not evident in the report output.

See also

Chapter 3, "Moving a column in report output"

#### 3.9.13.4 Changing the spacing between all rows

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

1. In the Paper Design view, click the second record.

2. 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:

1. In the Paper Design view, click any field in the body of the report.

2. Click the Select Parent Frame button in the toolbar.

3. Choose Tools > Property Inspector.

4. Under the Repeating Frame node, set the Vert. Space Between Frames (inches) property to the amount of blank space in inches to leave between row in the report output (for example, 0.25).

#### 3.9.13.5 Adding blank lines between groups of rows

To add blank lines between groups of rows:

1. Create a summary column (see Section 3.8.11, "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.

2. 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 (for example, 5).

3. In the Paper Layout view, click the Flex On and Confine Off buttons in the toolbar.

4. 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.

5. Choose Tools > Property Inspector to display the Property Inspector for the repeating frame.

6. Under the General Layout node, set the Vertical Elasticity property to Variable.

7. Click the Rectangle tool in the tool palette.

8. Draw a rectangle in the space under the record.

9. Double-click the rectangle object.

10. In the Property Inspector, under the Advanced Layout node, double-click the Format Trigger property value to display the PL/SQL Editor.

11. 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;

12. 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:

- Adjusting margins

- Adding a page break

#### 3.9.14.1 Adjusting margins

To adjust margins:

1. In the Paper Layout 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.

2. 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:

1. In the Paper Layout view, click any field that is part of the frame at which you want to insert a page break.

2. Click the Parent Frame in the toolbar.

3. Choose Tools > Property Inspector.

4. 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.

5. 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:

- Displaying a section layout view

- Creating a default layout for a section

See also

Section 2.1.2, "About report sectioning and 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:

1. In the Paper Layout view, display the layout view for the section.

2. Choose Tools > Report Wizard.

3. Follow the wizard to create a default layout for your report.

4. 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.

5. To add another layout section to the current layout, create an additional report layout (see Section 3.5.5, "Creating an additional report layout").

6. 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.

7. Set properties for the section, as desired.

See also

Section 2.4.2, "About layout defaulting"

Section 3.5.4, "Creating a default layout for a report"

Chapter 3, "Changing the default layout spacing"

## 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:

- Using a pre-defined system parameter

- Creating a user parameter

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

- Validating a parameter value at runtime

- Creating a default Parameter Form

- Selecting parameters to include in the Runtime Parameter Form

- Displaying the Parameter Form at runtime

- Adding more pages to the Runtime Parameter Form

- Passing parameters to reports running in batch mode

- Creating an HTML Parameter Form header using PL/SQL

- Creating an HTML Parameter Form footer using PL/SQL

- Creating HTML Parameter Form input or select events

- Changing HTML Parameter Form input to uppercase

See also

Section 1.9.1, "About parameters"

Section 2.3.4, "About referencing columns and parameters"

Section 1.6.5, "About the Paper Parameter Form view"

Section 1.11.1, "About the Runtime Parameter Form"

Section 1.9.4, "About Parameter Forms for Web reports"

### 3.11.1 Using a pre-defined system parameter

To use a system parameter:

1. In the Object Navigator, expand the Data Model node, then expand the System Parameters node.

2. Double-click the properties icon for the desired parameter to display the Property Inspector.

3. Under the Parameter node, set the Initial Value property, if required.

4. 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:

1. In the Object Navigator, expand the Data Model node, then click the User Parameters node.

2. Click the Create button in the toolbar.

3. Double-click the properties icon for the new parameter to display the Property Inspector.

4. Under the General Information node, replace the Name property with the desired parameter name.

5. Under the Parameter node, set the Initial Value and List of Values properties, if required.

6. 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:

1. In the Object Navigator, expand the Data Model node, then the User Parameters node.

2. Double-click the properties icon for the parameter for which you want to create a LOV to display the Property Inspector.

3. Under the Parameter node, double-click the List of Values property field to display the Parameter List of Values dialog box.

4. 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.

5. 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.

6. 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.

7. Click OK.

### 3.11.4 Validating a parameter value at runtime

To validate a parameter value at runtime:

1. In the Object Navigator, expand the Data Model node, then the System Parameters or User Parameters node.

2. Double-click the PL/SQL icon for the parameter for which you want to add a PL/SQL validation trigger.

3. In the PL/SQL Editor, define the PL/SQL to be triggered at runtime.

See also

Section 2.6.13.3, "About validation triggers"

### 3.11.5 Creating a default Parameter Form

To create a default Parameter Form:

1. Choose Tools > Parameter Form Builder.

2. 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:

1. Choose Tools > Parameter Form Builder.

2. In the Parameter Form Builder, click the parameters you want to include in the Runtime Parameter Form.

3. Modify the parameter labels as desired.

4. 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:

1. Choose Edit > Preferences to display the Preferences dialog box.

2. 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:

1. In the Object Navigator, double-click the properties icon next to the report name.

2. 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 (for example, 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.10.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.10.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:

1. In the Object Navigator, double click the view icon next to the Paper Parameter Form node to display the Paper Parameter Form view.

2. Create or edit a Parameter Form field (see Section 3.9.1.1, "Creating a field object").

3. Double-click the field object to display the Property Inspector.

4. 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:

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

2. 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).

3. 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 throughwith the Web. Doing so will raise a message whenever an end user enters invalid data in the Parameter Form field.

1. In the Paper Parameter Form view, create a Parameter Form field called PF_DEPTNO.

2. Double-click the field object to display the Property Inspector, and set the following 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)"

3. In the Object Navigator, click (the properties icon) next to your report name to display the Property Inspector, and set the following 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 through 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 through 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.

1. In the Paper Parameter Form view, create a Parameter Form field called PF_DESTYPE.

2. Double-click the field object to display the Property Inspector, and set the following 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)"

3. In the Object Navigator, click (the properties icon) next to your report name to display the Property Inspector, and set the following 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.

See also

Section 2.5.1, "About Parameter Form HTML extensions"

### 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:

1. Choose Tools > Parameter Form Builder.

2. In the Parameter Form Builder, create a Parameter Form field called PF_DESFORMAT, with a source of DESFORMAT.

3. Choose Tools > Property Inspector.

4. 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.

1. In the Paper Parameter Form view, create a Parameter Form field called PF_DESTYPE.

2. Double-click the field object to display the Property Inspector, and set the following 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 (that is, PDF) in the Runtime Parameter Form when running the report through 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:

- Creating a template

- Defining default template attributes

- Defining override template attributes

- Applying a template to a report

- Formatting the report title in a template

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

- Adding a template to the pre-defined templates list

### 3.12.1 Creating a template

To create a template:

1. In the Object Navigator, click the Templates node, then click the Create button in the toolbar.

2. Define default template attributes for all report styles.

3. Optionally, define override template attributes for a selected report style.

4. 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.

See also

Section 2.7.5, "About the Template Editor"

Section 2.7.1, "About templates"

Section 2.7.2, "About template attributes"

Section 2.7.3, "About applying templates"

Section 2.7.4, "About inheritance in templates"

### 3.12.2 Defining default template attributes

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

1. 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.

2. 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.

3. 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.

4. In the Paper Layout view, select from the Report Style list to view the layout for individual report styles.

5. 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.

See also

Section 2.7.1, "About templates"

Section 2.7.2, "About template attributes"

### 3.12.3 Defining override template attributes

To define override attributes for a selected report style:

1. 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 (for example, Tabular, Group Left).

2. 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.

3. 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.

4. 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.

5. 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: 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.

6. In the Paper Layout view, select from the Report Style list to view the layout for individual report styles.

7. 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.

See also

Section 2.7.1, "About templates"

Section 2.7.2, "About template attributes"

### 3.12.4 Applying a template to a report

To apply a template to a report:

1. 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.

2. Choose Tools > Report Wizard.

3. On the Template page, select a pre-defined template, or click Template file, and click Browse to open the desired template.

4. Click Finish to apply the template to the current report section.

See also

Section 2.7.1, "About templates"

Section 2.7.3, "About applying templates"

### 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:

1. 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.

2. Double-click the properties icon next to the Default node to display the Property Inspector.

3. 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:

1. In the Paper Layout view of the template, create a boilerplate text object for the title in the margin.

2. Size and position the title, and modify the color and text attributes as desired.

3. Double-click the title object to display its Property Inspector.

4. Under General Information, set the Name property to B_OR$REPORT_TITLE.

See also

Section 2.1.1, "About report titles"

Section 2.7.1, "About templates"

### 3.12.6 Adding items and objects to a template

To add items and objects to a template:

1. In the Paper Layout view of the Template Editor, click the Margin button in the toolbar to display the margin area.

2. Create objects in the margin of your template. You cannot create objects in the body of a template.

See also

Section 2.7.5, "About the Template Editor"

Section 2.7.1, "About templates"

Section 2.7.2, "About template attributes"

Section 2.7.4, "About inheritance in templates"

### 3.12.7 Modifying objects in the template margin

To modify objects in the margin of a template:

1. In the Paper Layout view of the Template Editor, click the Edit Margin button in the toolbar.

2. Modify objects in the margin of your template.

See also

Section 2.7.5, "About the Template Editor"

Section 2.7.1, "About templates"

Section 2.7.2, "About template attributes"

Section 2.7.4, "About inheritance in templates"

### 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).

See also

Section 2.4.5, "About changing colors and patterns"

Section 3.9.6.2, "Changing colors"

Section 3.9.6.3, "Changing patterns"

Section 2.7.1, "About templates"

### 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:

1. In a text editor (for example, 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).)

2. 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).

3. 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.

4. Copy the template file (`filename``.tdf`) to `ORACLE_HOME/REPORTS/TEMPLATES`.

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

1. 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.

2. Copy each `.bmp` file to `ORACLE_HOME``/REPORTS/TEMPLATES`.

See also

Section 2.7.1, "About templates"

Section 2.7.3, "About applying templates"

### 3.12.10 Creating an HTML template for a report portlet

To create and use an HTML template for a report that can be published on an OracleAS Portal page:

- See the How To Create a Report Portlet Template in Getting Started with Oracle Reports white paper, available on the Oracle Technology Network Oracle Reports 10g page (`http://www.oracle.com/technology/products/reports/index.html`). Click Getting Started. In the list of topic sections on the left, click Index. On the Index page, in the Collateral Type list, choose White Papers & How To Documents, and click Search. In the list that displays, click Creating a Report Portlet Template.

See also

Section 2.7.1, "About templates"

Section 2.7.3, "About applying 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:

- Using a built-in package

- Working with the PL/SQL Editor

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

- Creating or editing an external PL/SQL library

- Compiling and running program units

### 3.13.1 Using a built-in package

To use a built-in package:

1. Choose Tools > PL/SQL Editor to display the PL/SQL Editor.

2. In the Object Navigator, expand the Built-in Packages node, then the package you want to use.

3. 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.

See also

Section 2.6.12, "About built-in packages"

### 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:

- Defining PL/SQL

- Searching and replacing text in a program unit

- Editing a program unit

- Inserting syntax into the PL/SQL Editor

#### 3.13.2.1 Defining PL/SQL

To define PL/SQL:

1. In the PL/SQL Editor, type or edit the PL/SQL code for the program unit.

2. Click Compile.

3. 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.

4. When the code compiles successfully, click Close.

See also

Section 2.6.4, "About program units"

#### 3.13.2.2 Searching and replacing text in a program unit

To search and replace text in a program unit:

1. In the Object Navigator, expand the Program Units node.

2. Double-click the PL/SQL Editor view icon next to the program unit you want to edit.

3. In the PL/SQL Editor, place your cursor where you wish to begin the search.

4. Choose Edit > Find and Replace.

5. 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.

6. Click Search.

7. 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.

8. Click Search to proceed to the next instance, or click Cancel to close the dialog box.

9. When you have finished replacing all text in the program unit, click Compile to recompile the program unit.

   Tip: If you replace text in multiple program units, you can recompile them all at once when you are finished.

10. 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.

See also

Section 2.6.4, "About program units"

#### 3.13.2.3 Editing a program unit

To edit a program unit:

1. In the Object Navigator, expand the Program Units node.

2. Double-click the PL/SQL Editor view icon for the program unit you want to edit.

3. In the PL/SQL Editor, edit the PL/SQL for the program unit.

See also

Section 2.6.4, "About program units"

##### 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 ALT key, then click and drag horizontally. To select a line, click 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 on 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 the Edit 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. On 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 select 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 select 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

##### 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.

#### 3.13.2.4 Inserting syntax into the PL/SQL Editor

To copy syntax into the PL/SQL Editor:

1. Make sure the PL/SQL Editor or Stored PL/SQL Editor is the current (most recently selected) window.

2. Place the cursor in the editor where you want to insert the syntax, then choose Tools > Syntax Palette.

3. In the Syntax Palette, click the PL/SQL tab or the Built-ins tab.

4. Choose a PL/SQL category or a built-in package from the 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.

5. 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.

6. 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.

7. 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.

See also

Section 2.6.3, "About the Syntax Palette"

### 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:

- Creating a local program unit

- Creating a stored program unit

- Deleting a program unit

- Moving a program unit between client and database server

- Creating a report trigger

- Deleting a report trigger

- Creating a database trigger

#### 3.13.3.1 Creating a local program unit

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

1. In the Object Navigator, click the Program Units node.

2. Click the Create button in the toolbar.

3. In the dialog box, type a name for the program unit in the Name text box.

4. 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)

   Note: If the radio button is not selected when you click, try clicking a different radio button, then click the desired Type again.

5. Click OK.

6. In the PL/SQL Editor, define the PL/SQL for the program unit.

See also

Section 2.6.4, "About program units"

#### 3.13.3.2 Creating a stored program unit

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

1. 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.

2. 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.

3. Click the PL/SQL Stored Program Units node, then click the Create button in the toolbar.

4. In the dialog box, type a name for the program unit in the Name text box.

5. 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)

   Note: If the radio button is not selected when you click, try clicking a different radio button, then click the desired Type again.

6. Click OK.

7. In the Stored PL/SQL Editor, select a database owner name from the Owner list to indicate where the program unit will be stored in the database, then define the PL/SQL for the stored program unit.

See also

Section 2.6.5, "About stored program units"

#### 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:

1. In the Object Navigator, expand the Program Units node.

2. Click the program unit you want to delete.

3. Click the Delete button in the toolbar.

4. In the message dialog box, click Yes to confirm the deletion.

See also

Section 2.6.4, "About program units"

#### 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:

1. 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.

2. 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.

3. In the Reports section of the Object Navigator, expand the Program Units node.

4. 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.

5. 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:

1. 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.

2. Expand the subnode that corresponds to the database user name you used to log in to the database.

3. Expand the Stored Program Units node.

4. 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.

5. Release the mouse button to insert a copy of the program unit on the client.

See also

Section 2.6.5, "About stored program units"

Section 2.6.4, "About program units"

#### 3.13.3.5 Creating a report trigger

To create a report trigger:

1. In the Object Navigator, expand the Report Triggers node.

2. Double-click the PL/SQL icon for the trigger you want to create.

3. In the PL/SQL Editor, define the PL/SQL for the report trigger.

See also

Section 2.6.13.1, "About report triggers"

#### 3.13.3.6 Deleting a report trigger

To delete a report trigger:

1. In the Object Navigator, expand the Report Triggers node.

2. Double-click the PL/SQL icon for the trigger you want to delete.

3. In the PL/SQL Editor, drag to select the PL/SQL code.

4. Choose Edit > Delete.

See also

Section 2.6.13.1, "About report triggers"

#### 3.13.3.7 Creating a database trigger

To create a database trigger:

1. Choose Tools > Database Trigger Editor.

2. In the Database Trigger Editor, choose a user name (schema) from the Table Owner list.

3. Choose a table name from the Table list.

   The Table 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 list shows only the tables to which you have been granted access.

4. (For views only) To define an INSTEAD OF trigger for an object view, click the arrow next to Table to display a list and choose View. Then choose a view name from the View list.

5. 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 list. You can modify the trigger name.

   The Name list displays a list of trigger names associated with the table (or view) shown in the Table (or View) field. The Name list displays only the names of the database triggers associated with the tables to which you have access.

6. After specifying the trigger options and action, click Save to compile the trigger.

See also

Section 2.6.13.4, "About database triggers"

### 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:

- Creating or editing a format trigger

- Creating or editing a group filter

- Creating or editing a formula column

- Creating a placeholder column

- Changing colors and patterns using PL/SQL

#### 3.13.4.1 Creating or editing a format trigger

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

1. 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.

2. Under Advanced Layout, set the Format Trigger property by clicking the ... button to display the PL/SQL Editor.

3. Define the PL/SQL for the format trigger.

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

1. 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.

2. 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.

3. 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:

1. In the Data Model view, double-click the title bar of the group to display the Property Inspector.

2. Scroll to the Group node.

3. 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.

4. 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.

5. 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.

6. Set other properties as desired.

See also

Section 2.6.9, "About group filters"

#### 3.13.4.3 Creating or editing a formula column

See Section 3.8.10, "Creating or editing a formula column".

#### 3.13.4.4 Creating a placeholder column

See Section 3.8.12, "Creating or editing a placeholder column".

#### 3.13.4.5 Changing colors and patterns using PL/SQL

See Section 3.9.6.4, "Changing colors and patterns using PL/SQL".

### 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:

- Creating an external PL/SQL library

- Adding a program unit to an open library

- Editing a program unit in a PL/SQL library

- Removing a program unit from a PL/SQL library

- Attaching a PL/SQL library

- Converting external PL/SQL libraries

See also

Section 2.6.6, "About external PL/SQL libraries"

#### 3.13.5.1 Creating an external PL/SQL library

To create an external PL/SQL library:

1. In the Object Navigator, click the PL/SQL Libraries node.

2. 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:

1. 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

2. Under the Reports node, expand the Program Units node.

3. Drag the program unit you want to add to the library below the library's Program Units node.

4. 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:

1. In the Object Navigator, expand the PL/SQL Libraries node, then the library node for the program unit.

2. Under the library's Program Units node, double-click the PL/SQL Editor view icon for the program unit you want to edit.

3. 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:

1. In the Object Navigator, expand the PL/SQL Libraries node, then the library node for the program unit.

2. Under the open library's Program Units node, click the library program unit you want to delete.

3. Click the Delete button in the toolbar.

4. In the message box, click Yes.

#### 3.13.5.5 Attaching a PL/SQL library

To attach a PL/SQL library:

1. In the Object Navigator, click the Attached Libraries node.

2. Click the Create button in the toolbar.

3. 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.

4. 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 PL/SQL libraries from one storage format to another on Windows:

- On the command line, type `%ORACLE_HOME%\BIN\rwconverter`, followed by the report name and desired arguments. See the Reference section of the Oracle Reports 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:

- Compiling a single program unit

- Compiling all program units

- Compiling all uncompiled program units

See also

Section 2.6.4, "About program units"

#### 3.13.6.1 Compiling a single program unit

To compile a single program unit:

1. 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).

2. 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.

3. In the Compile dialog box, click any error 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:

1. 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).

2. 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.

3. In the Compile dialog box, click any error 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.3 Compiling all uncompiled program units

To compile all uncompiled program units:

1. 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).

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

   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.

3. In the Compile dialog box, click any error 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:

- Debugging a report

- Running a report in debug mode

- Setting a breakpoint

- Setting a debug trigger

- Browsing debug actions

- Editing a debug action

- Disabling and enabling debug actions

- Deleting a debug action

- Running a program unit in the PL/SQL Interpreter

- Inserting a Navigator pane in the PL/SQL Interpreter

- Controlling program unit execution

- Stepping through the code

- Modifying code at runtime

- Displaying the current scope location

- Examining or changing local variables

- Modifying application variables

- Viewing subprogram references

- Tracing report execution

- Tracing report distribution

- Tracing using the SQL TRACE function

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

### 3.14.1 Debugging a report

To debug a report:

1. 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.

2. 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.

3. Close the PL/SQL Interpreter to run your report.

4. Browse your debug actions and evaluate application information.

5. After narrowing the failing code to a specific region, use the PL/SQL Interpreter to implement and test possible code fixes.

Usage notes

When debugging a JSP-based Web report in Oracle JDeveloper, you must disable any breakpoints that are not of Source type. If these breakpoints are not disabled, a deadlock is detected. You can disable breakpoints that are not of Source type in the Breakpoints window.

### 3.14.2 Running a report in debug mode

To compile and run a report in debug mode:

1. Choose Edit > Preferences.

2. In the Preferences dialog box, on the Runtime Settings page, select Run Debug. Click OK.

3. 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 Oracle Reports 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:

1. If the PL/SQL Interpreter is not already displayed, choose Tools > PL/SQL Interpreter.

2. 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.

3. 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:

1. If the PL/SQL Interpreter is not already displayed, choose Tools > PL/SQL Interpreter.

2. 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.

3. 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 Trigger).

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

4. In the PL/SQL 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 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.

5. 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:

1. In the Object Navigator, expand the Debug Actions node, then double-click the debug action icon to display the appropriate dialog box.

2. Edit the content or properties of the debug action in the dialog box.

3. 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.

  or

- In the PL/SQL Interpreter, use the ENABLE or DISABLE command to enable or disable the specific debug action.

  For example, entering .DISABLE ACTION 1 in the Interpreter pane disables the first debug action and displays the following status:

      Disabling debug action 1...

### 3.14.8 Deleting a debug action

To delete a debug action:

1. In the Object Navigator, expand the Debug Actions node, then click the action you want to delete.

2. 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:

1. If the PL/SQL Interpreter is not already displayed, choose Tools > PL/SQL Interpreter to display it.

2. 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)`;

3. 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:

1. If the Interpreter is not already displayed, choose Program > PL/SQL Interpreter.

2. In the 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.

3. 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 enables 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
GO | 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:

1. In the Object Navigator, double-click the desired program unit, menu item command, or trigger to display the PL/SQL Editor.

2. In the PL/SQL Editor, make the desired modifications.

3. Click Compile then Close to dismiss the PL/SQL Editor.

4. In the PL/SQL Interpreter toolbar, choose Go or Step Into, Over, or Out to resume program execution.

See also

Section 2.10.9, "About modifying code at runtime"

### 3.14.14 Displaying the current scope location

To display the current scope location:

- In the Object Navigator, expand the Stack node, then 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.

See also

Section 2.10.7, "About 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:

1. In the Object Navigator, expand the Stack node to show the call stack frames.

2. 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:

1. In the Object Navigator, expand the Stack node to show the call stack frames.

2. 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.

3. Click the variable value in the Object Navigator.

4. Click the variable value again to enter edit mode.

5. Change the value of the variable.

6. Click a blank area in the Object Navigator to exit edit mode and accept the changed value.

7. 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:

1. In the Object Navigator, expand the Global Variables node or the Stack node for local variables.

2. Select and expand the desired entry. The Object Navigator displays any variables associated with the entry.

3. 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:

1. In the Object Navigator, select and expand the desired subprogram.

2. 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.

- In the `rwbuilder.conf` configuration file, specify:

      <trace traceFile="trace_file_name" traceOpts="trace_all" traceMode="trace_replace"/>

For more information, including tracing for `rwserver` and `rwservlet`, see the chapters "Configuring OracleAS Reports Services" and "Tuning 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 ().

### 3.14.19 Tracing report distribution

To trace report distribution:

1. Choose Program > Tracing.

2. In the Runtime Trace Settings dialog box, specify a name for the trace file in the Trace File field.

3. Select the Distribution check box.

4. Click OK.

5. Run the report.

6. 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.

See also

Section 2.8.3, "About report distribution"

Section 3.7.12, "Distributing a report to multiple destinations"

### 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; that is, 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 Oracle Server SQL Language Reference manual.

#### 3.14.20.1 Performing a user-level trace

1. Open the report for which you want the performance data.

2. 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.

3. 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.)

4. 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

1. Insert these statements into your `init.ora` file: SQL_TRACE=TRUE and TIMED_STATISTICS=TRUE.

2. 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:

- Publishing a report as a portlet in OracleAS Portal

- Accessing non-Oracle data sources

- Integrating with the Oracle Express pluggable data source

### 3.15.1 Publishing a report as a portlet in OracleAS Portal

OracleAS Portal is integrated with Oracle Reports. This integration enables you to quickly publish a report as a portlet (that is, a small, dynamic region) or an item on a page, so that end users can easily access dynamic reports.

For general information, refer to the Oracle Technology Network (OTN): on the Oracle Reports 10g page (`http://www.oracle.com/technology/products/reports/index.html`), click Getting Started to display the Getting Started with Oracle Reports home page. In the list of topic sections on the left, click Any Format, then select Integration with OracleAS Portal.

For detailed information and tasks to publish a report as a portlet in OracleAS Portal, see the chapter "Deploying Reports in OracleAS Portal" in the Oracle Application Server Reports Services Publishing Reports to the Web manual, available on the Oracle Technology Network Oracle Reports Documentation page ().

#### 3.15.1.1 Running reports on OracleAS Portal as an item link

When you install OracleAS Portal with a nondefault language setting, some entries required to publish a report as an item link on a portal page are not installed automatically. You must install the language of your choice by using the `rwlang.sql` script.

Thus, you must run `rwlang.sql` (`ORACLE_HOME``/portal/admin/plsql/wwd/`) if:

- You have selected at least one language in addition to the default (`US`) at the time of installing OracleAS Portal.

  AND

- You want to publish a report as an item link in OracleAS Portal.

Note: By default, a report is defined in the Main section.

To run the script:

1. Change the directory to `ORACLE_HOME``/portal/admin/plsql/wwd/`.

2. Run `sqlplus`.

3. Log on to OracleAS Portal using the portal schema.

4. Run the `rwlang.sql` script with the following parameters:

   `@rwlang.sql` `language_list`

   where

   `language_list` is the list of languages separated by commas.

   For example, to install French and Japanese:

   `@rwlang.sql f,ja`

Usage notes

- There should be no space before or after the comma (,) because `sqlplus` treats the language list as two parameters, instead of one parameter separated by a comma (,).

- The header of the `rwlang.sql` script contains the complete list of all language abbreviations. Edit the script file using any text editor to find out the various abbreviations.

### 3.15.2 Accessing non-Oracle data sources

Oracle Reports enables you to access any data source. The pluggable data source (PDS) architecture replaces Oracle Open Client Adapter (OCA) and the Open Database Connectivity (ODBC) drivers, which 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:

1. 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.

2. 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.

3. 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.

4. Is the file comma-delimited?

   You can use the Text PDS included with Oracle Reports to query the data source.

5. 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.

See also

The Pluggable Data Sources section of the Oracle Reports online Help, including the topics:

- About pluggable data sources

- Adding a pluggable data source

- Connecting to a pluggable data source

- Adding online Help to a pluggable data source

- Pluggable data source interface definition

- Troubleshooting PDS problems

### 3.15.3 Integrating with the Oracle Express pluggable data source

Note: The Oracle Express pluggable data source (PDS) is deprecated in Oracle Reports 10g Release 2 (10.1.2). For information, refer to A Guide to Functional Changes Between Oracle Reports 6i and 10g, available on the Oracle Technology Network Oracle Reports 10g page (`http://www.oracle.com/technology/products/reports/index.html`). Additionally, the Oracle Express PDS does not work on any UNIX platform in the Oracle Developer Suite.

To use the Oracle Express PDS with Oracle Reports, you must connect to a supported Express Server version, which are versions 6.2.x and 6.3.x. When you install Oracle Reports, SNAPI 9.0.1 is automatically installed to enable connections to these Express Server versions.

Before creating reports using Express data, verify that you have completed all the necessary configuration steps, as detailed in the topics under Pluggable Data Sources > Express PDS on the Contents tab of the Oracle Reports online Help.

## 3.16 Administer Reports Builder

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

- Setting a database role

- Converting from one format to another

- Improving performance using SQL statements

- Improving performance using WHERE clauses

### 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 Oracle Application Developer's Guide for more information.

To set a database role for a report:

1. In the Object Navigator, double-click the properties icon for the report to display the Property Inspector.

2. Under the Report node, set the Role Name property as defined by the database administrator in the database.

3. 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.

See also

Section 2.9.1, "About database roles"

### 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 on Windows, 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 (for example, 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.