Skip Headers

Oracle9i Reports Building Reports
Release 9.0

Part Number B10310-01
Go To Table Of Contents
Contents
Go To Index
Index

Go to previous page Go to next page

3
How To...

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

The procedures are grouped into the following sections:

3.1 Access Oracle9i Reports Documentation

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

3.1.1 Using the online help

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

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

3.1.2 Locating other documentation

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

Table 3-1 Oracle9i Reports documentation roadmap
For ... Refer to ...

an overview of Oracle9i Reports

  • Getting Started with Oracle9i Reports, available on the Oracle Technology Network. Navigate to Products > Developer Suite > Reports Developer, then click "Getting Started with Oracle9i Reports."

  • the online help topics under the Welcome node in the Help Navigator window (choose Help > Help Contents): About Reports Builder and About this release.

an overview of new features in this release

  • Oracle9i Reports New Features, available on the Oracle Technology Network. Navigate to Products > Internet Developer Suite > Reports Developer, then click "Oracle9i Reports New Features."

  • the online help topic under the Welcome node in the Help Navigator window (choose Help > Help Contents): About this release.

an overview of deprecated, obsolete, and changed functionality in this release

  • Oracle9i Reports Obsolescence Plan, available on the Oracle Technology Network. Navigate to Products > Internet Developer Suite > Reports Developer, then click "Oracle9i Reports Obsolescence Plan."

  • the online help topic under the Welcome node in the Help Navigator window (choose Help > Help Contents): About this release.

instructions and hands-on lessons about building paper and Web-based reports

  • Oracle9i Reports Tutorial, available on the Oracle Technology Network. Navigate to Products > Developer Suite > Reports Developer, then click "Getting Started with Oracle9i Reports." Use the Index to find and download the Oracle9i Reports Tutorial.

  • Building Reports with Oracle9i Reports, available on the Oracle Technology Network. Navigate to Products > Developer Suite > Reports Developer, then click "Getting Started with Oracle9i Reports." Use the Index to find and download the Building Reports with Oracle9i Reports manual.

  • the online help (choose Help > Help Contents) for specific tasks and reference information.

context-sensitive help on dialog boxes, messages, and properties

  • for dialog boxes and messages, the online help topics displayed when you click the Help button, or press F1.

  • for properties, the online help topic displayed when you click a property in the Property Inspector, then press F1.

Information about Oracle Reports Services and configuring and using the Reports Server

  • Publishing Reports to the Web with Oracle9iAS Reports Services, available on the Oracle9iAS documentation CD and on the Oracle Technology Network. Navigate to Products > Developer Suite > Reports Developer, then click "Getting Started with Oracle9i Reports." Use the Index to find and download the Publishing Reports to the Web with Oracle9iAS Reports Services manual.

information about using the Oracle9i Reports application program interface (API)

  • the Oracle9i Reports API Java documentation, available on the Oracle Technology Network. Navigate to Products > Developer Suite > Reports Developer.

examples and demos

  • Getting Started with Oracle9i Reports, available on the Oracle Technology Network. Navigate to Products > Developer Suite > Reports Developer, then click "Getting Started with Oracle9i Reports.". Use the Index to find the list of all examples and demos for Oracle9i Reports.

  • A subset of the examples is also available on the Oracle Developer Suite product CD:

    Tutorial example files

    Express example files

    Building a Report with a Barcode Bean example

    Building a Report with an XML Pluggable Data Source example

    Building a Report with a Text Pluggable Data Source example

    Bursting and Distributing a Report example

    Oracle9i Reports Demos

other resources, such as white papers

  • the Oracle Technology Network.

3.2 Set Properties and Preferences

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

3.2.1 Displaying the Property Inspector

To display the Property Inspector for an object:

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 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.7, "Creating an ASCII (character-mode) report"

3.2.6 Setting font mappings

To alter the default font mappings, edit the uifont.ali file. The location of this file varies according to your operating system.

For more information on the files you will need to install Reports Builder, see your installation instructions.

See also

The Font Mapping section of the Reports Builder online help

3.2.7 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. The active report will then be shown accurately, although the appearance of the inactive reports may suffer. You must choose Editable if you want to modify the color palette or import or export a color palette.

    • Read Only - Shared. (Default) Reports Builder continues to append each active report's color palette to your system's original palette until there is no room left in the palette for any more colors. That palette becomes the single palette that all open reports share. If you create a report that uses entirely different colors, it may not be shown accurately.

    • Read Only - Private. Each time you make a report active, Reports Builder appends that report's color palette to your system's original palette until there is no room left in the palette for any more colors. That palette becomes the palette that is used whenever that report is active. The active report is then shown accurately, although the appearance of the inactive reports may suffer.

  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 Reports Builder online help

3.2.8 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.9 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, click the second object, then choose Property Inspector from the pop-up menu.

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

3.3 Perform Common Tasks:

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

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:

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.

Usage notes

Beginning with Oracle9i Reports, 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 Oracle9i Software Configuration Manager (SCM).

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.

Usage notes

Beginning with Oracle9i Reports, 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 Oracle9i Software Configuration Manager (SCM).

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.

Usage notes

Beginning with Oracle9i Reports, 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 Oracle9i Software Configuration Manager (SCM).

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 (e.g., 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.

Usage notes

Beginning with Oracle9i Reports, 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 Oracle9i Software Configuration Manager (SCM).

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

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. The cancel the delete operation, click Cancel.

3.3.7 Archiving a report

To archive a report in Oracle9i Reports, it can be saved into source control using the Check In and Check Out capabilities of Oracle9i 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. Oracle9i Reports is integrated with Oracle9i SCM, which allows you to easily control your reports in a seamless integrated environment.

3.3.8 Selecting and deselecting objects

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

3.3.8.1 Selecting single objects

To select a single object in the Object Navigator:

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:

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 on one of the grouped objects.

3.3.8.5 Selecting all objects in a report region

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

3.3.8.6 Selecting overlapped objects

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

  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:

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

3.3.8.8 Deselecting multiple objects

To deselect all selected objects:

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

3.3.10 Hiding or showing components

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

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:

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:

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:

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

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:

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

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

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 multi-query 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.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.6, "About nested matrix reports"

Section 2.1.7, "About matrix with group reports"

3.5.2 Creating a multi-query group above report

See the example report in Chapter 16, "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.9.13.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.2, "About templates"

3.5.7 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 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 page, set Destination Unit to Character.

  6. Click OK.

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

See also

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

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

3.5.8 Preparing a multi-platform 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:

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.

See also

The Font Mapping section of the Reports Builder online help

3.5.9 Preparing a report for translation into other languages

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

3.6 Work with Web Reports

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

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:

3.6.2 Viewing the source code for a Web report

To view the source code for a Web report:

See also

Topic "Custom JSP tags for Oracle9i Reports" in the Reference section of the Reports Builder 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. If the Web page has never had a report added to it, you must first add the data to be used to build the report, as described above.

  5. After you have added data to be used to build the report, double-click the view icon next to the Web Source node to display the source code for the Web page in the Web Source view.

  6. Locate the section in the source code where you want to add the report block.

    Note:

    Adding some comment text such as "Place the report block here" to your Web page allows you to easily locate the correct position for your report block.

  7. Choose Insert > Report Block.

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

3.6.5 Adding a graph to a Web report

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

See also

Section 3.9.8.5, "Editing a graph in a Web report"

Section 3.9.8.2, "Adding a graph to a paper report"

3.6.6 Preparing a paper-based report for the Web

To prepare a paper-based report for the Web:

  1. Optionally, add desired Web links to a new or existing report:

  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.13.4, "Displaying report output in your Web browser"

See also

Section 1.2.2, "About Web reports"

Section 2.2.3, "About HTML and PDF in Reports Builder"

Section 2.2.4, "About Web links for HTML output"

Section 2.2.5, "About Web links for PDF output"

3.6.7 Adding Web links to paper-based reports

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

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

3.6.7.1 Using the user interface

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

See also

Section 1.2.2, "About Web reports"

Section 2.2.3, "About HTML and PDF in Reports Builder"

Section 2.2.4, "About Web links for HTML output"

Section 2.2.5, "About Web links for PDF output"

Section 2.2.10, "About before and after escapes"

3.6.7.1.1 Creating an HTML document header using the Property Inspector

Note:

This procedure is for HTML output only.

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

  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 or select an HTML file to import.

See also

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

3.6.7.1.2 Creating an HTML document footer using the Property Inspector

Note:

This procedure is for HTML output only.

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

  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.7.2.2, "Creating an HTML document footer using PL/SQL"

3.6.7.1.3 Creating an HTML page header using the Property Inspector

Note:

This procedure is for HTML output only.

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

  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 or select an HTML file to import.

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

3.6.7.1.4 Creating an HTML page footer using the Property Inspector

Note:

This procedure is for HTML output only.

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

  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 or select an HTML file to import.

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

3.6.7.1.5 Creating an HTML Parameter Form header using the Property Inspector

Note:

This procedure is for HTML output only.

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

  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 or select an HTML file to import.

See also

Section 2.5.1, "About Parameter Form HTML extensions"

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

3.6.7.1.6 Creating an HTML Parameter Form footer using the Property Inspector

Note:

This procedure is for HTML output only.

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

  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 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.7.2.6, "Creating an HTML Parameter Form footer using PL/SQL"

3.6.7.1.7 Creating a hyperlink using the Property Inspector

To add a hyperlink to your report:

  1. Create a hyperlink destination, 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 Reports Builder online help.

    Note:

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

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

See also

Section 2.2.6, "About hyperlinks"

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

3.6.7.1.8 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.7.2.8, "Creating a hyperlink destination using PL/SQL"

3.6.7.1.9 Creating an application command line link using the Property Inspector

Note:

This procedure is for PDF output only.

To associate a command with an object in your report:

  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.7.2.9, "Creating an application command line link using PL/SQL"

3.6.7.1.10 Creating a bookmark using the Property Inspector

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

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

    • Under Web Settings, 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.7.2.10, "Creating a bookmark using PL/SQL"

3.6.7.1.11 Creating a bookmark on break columns using the Property Inspector

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

  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.7.2.10, "Creating a bookmark using PL/SQL"

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

Note:

This procedure is for HTML output only.

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

  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.5.1, "About HTML page streaming"

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

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

3.6.7.1.13 Linking an image object to a URL

Note:

This procedure is for HTML output only.

To link an image object to a URL:

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

  2. Click and drag a rectangle.

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

  4. Under the Link File Boilerplate node:

    • Set the Source File Format property to Image URL.

    • Set the Source Filename property to the URL where the image is located with the re quired protocol.

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

Note:

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

See also

Section 2.4.3, "About images"

Section 1.8.5, "About boilerplate objects"

Section 3.9.8.9, "Linking an image object to a file"

Section 3.9.8.8, "Selecting an image URL from the database"

3.6.7.1.14 Creating a boilerplate text object for HTML tags

Note:

This procedure is for HTML output only.

To create a boilerplate text object for HTML tags:

  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.

    Note:

    The text you type will format for HTML output only; for all other output formats, the text object will not appear in the report.

  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.

Example 1

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

Create the following as a boilerplate text object:

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

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

Example 2

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

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

This is output to HTML as:

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

See also

Section 1.8.5, "About boilerplate objects"

Section 2.5.1, "About Parameter Form HTML extensions"

3.6.7.1.15 Linking an HTML object to a file

Note:

This procedure is for HTML output only.

To link an HTML object to a file:

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

  2. Click and drag a rectangle.

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

  4. Under the Link File Boilerplate node:

    • Set the Source File Format property to Text.

    • Set the Source Filename property to the name of the file containing the HTML tags.

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

See also

Section 1.8.5, "About boilerplate objects"

3.6.7.1.16 Selecting HTML tags from the database

Note:

This procedure is for HTML output only.

t

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.

3.6.7.2 Using PL/SQL

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

See also

Section 1.2.2, "About Web reports"

Section 2.2.3, "About HTML and PDF in Reports Builder"

Section 2.2.4, "About Web links for HTML output"

Section 2.2.5, "About Web links for PDF output"

Section 2.2.10, "About before and after escapes"

3.6.7.2.1 Creating an HTML document header using PL/SQL

Notes:

This procedure is for HTML output only.

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 also

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

3.6.7.2.2 Creating an HTML document footer using PL/SQL

Notes:

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.7.1.2, "Creating an HTML document footer using the Property Inspector"

3.6.7.2.3 Creating an HTML page header using PL/SQL

Notes:

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.7.1.3, "Creating an HTML page header using the Property Inspector"

3.6.7.2.4 Creating an HTML page footer using PL/SQL

Notes:

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.7.1.4, "Creating an HTML page footer using the Property Inspector"

3.6.7.2.5 Creating an HTML Parameter Form header using PL/SQL

Notes:

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.7.1.5, "Creating an HTML Parameter Form header using the Property Inspector"

3.6.7.2.6 Creating an HTML Parameter Form footer using PL/SQL

Notes:

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.7.1.6, "Creating an HTML Parameter Form footer using the Property Inspector"

3.6.7.2.7 Creating a hyperlink using PL/SQL

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

  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.7.2.8, "Creating a hyperlink destination using PL/SQL"

See also

Section 2.2.6, "About hyperlinks"

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

3.6.7.2.8 Creating a hyperlink destination using PL/SQL

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

  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.7.2.7, "Creating a hyperlink using PL/SQL"

See also

Section 2.2.7, "About hyperlink destinations"

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

3.6.7.2.9 Creating an application command line link using PL/SQL

Note:

This procedure is for PDF output only.

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

  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.7.1.9, "Creating an application command line link using the Property Inspector"

3.6.7.2.10 Creating a bookmark using PL/SQL

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

  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.7.1.10, "Creating a bookmark using the Property Inspector"

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

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

Note:

This procedure is for HTML output only.

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

See also

Section 2.8.5.1, "About HTML page streaming"

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

Section 3.6.7.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:

3.7.1 Running and dispatching a report from the user interface

To run and dispatch a report:

  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: Tip: When you create the report, you must use the Parameter Form Builder (choose Tools > Parameter Form Builder) to select the system and user parameters you want to appear in the Runtime Parameter Form.

    • To preview output in the Paper Design view using screen fonts: set Destination Type to Screen.

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

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

      Note:

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

      .

      Note:

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

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

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

  5. Optionally, display output in your Web browser (see Section 3.7.13.4, "Displaying report output in your Web browser").

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

  7. If a dialog box appears, enter required information.

3.7.1.0.12 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.11, "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.

See also

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:

  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=HTML), open or print the file to verify the output.

See also

Section 2.8.1, "About batch reporting"

Section 1.12, "Executables"

Topic "rwrun" in the Reference section of the Reports Builder online help

3.7.3 Running a report using a command file

To run a report using a command file:

  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, typefilename.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.1, "About batch reporting"

Section 1.12, "Executables"

Topic "rwrun" in the Reference section of the Reports Builder online help

3.7.4 Running a report to a remote Reports Server

To run a report to a remote Reports Server:

The Publishing Reports to the Web manual provides additional information.

3.7.5 Generating HTML, HTMLCSS or PDF output

To generate HTML, HTMLCSS or 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.

      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. Specify the output format:

    • 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), or File > Preview Format > PDF.

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

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

    See also

    Section 2.2.3, "About HTML and PDF in Reports Builder"

    Section 2.8.5, "About HTML output"

    Section 2.8.6, "About PDF output"

    Section 2.8.5.1, "About HTML page streaming"

    Section 2.2.11, "About style sheets"

    3.7.6 Generating XML output

    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 (see the Reports Builder Release Notes for more information).

    2. Optionally, in the Paper Design view, choose File > Preview Format > XML to preview XML data in your default XML viewer.

      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.

    4. Save the XML output file to the directory of your choice.

      Note:

      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.

    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=[name].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 output"

    Topic "XML tag list" in the Reference section of the Reports Builder online help)

    3.7.7 Generating RTF output

    To generate RTF output:

    1. In the Object Navigator, click the report name.

    2. Choose File > Generate to File > RTF.

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

    See also

    Section 2.8.8, "About RTF output"

    3.7.8 Generating delimited output

    To generate delimited output:

    1. In the Object Navigator, click the report name.

    2. Choose File > Generate to File > Delimited.

    3. In the Delimited Output dialog box, choose from the drop-down list or type the delimiter that will separate the cells in your report output.

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

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

    6. Click OK.

    See also

    Section 2.8.9, "About delimited output"

    3.7.9 Generating text output

    To generate text output:

    1. In the Object Navigator, click the report name.

    2. Choose File > Generate to File > Text.

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

    See also

    Section 2.8.10, "About text output"

    3.7.10 Distributing a report to multiple destinations

    To distribute a report to multiple destinations, use any of the following methods to define the distribution:

    To distribute the report, you can either:

      • Specify DISTRIBUTE=YES on the command line.

      • Choose File > Distribute.

    Example

    In addition to normal file names, you can add destination names for any of the three sections of a report (header, body, trailer) using the following syntax:

    filename&<group_name>.ext
    
    

    where group_name is the report level group on which to repeat for the section and ext is any valid filetype when DESTYPE is set to File.

    For example, report&<deptno>.pdf generates several PDF files, one for each department, provided the Repeat On property is set to G_DEPTNO on that section.

    The result of a distribution run will be files named:

      • report10.pdf - containing information for employees belonging to department 10

      • report20.pdf - containing information for employees belonging to department 20

      • report30.pdf - containing information for employees belonging to department 30 and so on.

    See also

    "Bursting and Distributing a Report" chapter in the Building Reports with Oracle9i Reports manual.

    See also

    Section 2.8.2, "About report distribution"

    Section 3.14.20, "Tracing report distribution"

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

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

      • Set the width and height properties as desired (e.g., 11 (or 15) width x 8.5 height for landscape or 8.5 width x 11 (or 15) height for portrait). Make sure that you subtract the margin depths (e.g., for an 8.5 x 11 page with top, bottom, left, and right margins of .25, set Width to 8 and Height to 10.5).

      • Set the Orientation property to the desired value.

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

    See also

    Section 2.1.2, "About report sectioning and sections"

    3.7.12 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.13 Viewing report output

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

    3.7.13.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.13.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:

    See also

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

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

    See also

    Section 1.11.2, "About the Previewer"

    Section 3.7.14.2, "Printing a report from the Previewer"

    3.7.13.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. Specify the output format:

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

    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.14.3, "Printing a report from your Web browser"

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

      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.5.1, "About HTML page streaming"

      3.7.13.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.13.7 Splitting the viewing region

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

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

      • In the Previewer, drag the grey bar above the vertical scroll bar (to split horizontally), or to the left of the horizontal scroll bar (to split vertically)

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

      3.7.13.8 Magnifying or reducing the output

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

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

        • In the Previewer, click the Zoom In button in the toolbar.

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

        • In the Data Model view, Paper Layout view, Paper Design view, or Paper Parameter Form view, choose View > Zoom > Zoom Out or Normal Size.

        • To magnify the output displayed in the viewing region,

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

      3.7.14 Printing a report

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

      3.7.14.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.13.2, "Displaying report output in the Paper Design view"

      3.7.14.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, a warning appears that you should run the report with a Destination Type of Preview before printing. Preview creates PostScript output, which is typically more desirable for printing bit-mapped reports.

      4. Click OK.

      See also

      Section 3.7.13.3, "Displaying report output in the Previewer"

      3.7.14.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 (e.g., 11 (or 15) width x 8.5 height for landscape or 8.5 width x 11 (or 15) height for portrait). Make sure that you subtract the margin depths (e.g., for an 8.5 x 11 page with top, bottom, left, and right margins of .25, set Width to 8 and Height to 10.5).

            Set the Orientation property to the desired value.

      2. Display your report output in your Web browser (see Section 3.7.13.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.14.4 Printing a report on a pre-printed form

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

      3.7.14.5 Switching the printer tray

      To switch the printer tray:

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

      2. Use SRW.SET_PRINTER_TRAY to define the desired trigger:

        See also

        Section 2.8.4, "About switching the printer tray"

        3.7.14.6 E-mailing a report

        To e-mail a report from Reports Builder without first previewing it:

        1. Log onto 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 onto 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, etc.

        3.8 Work with the Data Model

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

        3.8.1 Creating a query

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

          • Report Wizard (single-query reports only)

          • Data Wizard

          • SQL Query tool (to create a query that selects data from an Oracle relational database)

          • XML Query tool (to access an XML data file, if you have the DTD file)

          • JDBC Query tool (to access any JDBC-enabled data source)

          • Text Query tool (to create a query that selects data from a text pluggable data source)

          • Express Server Query tool (to create a query that selects data from an Express Server)

          • Ref Cursor Query tool (to use ref cursors)

        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: In this release, 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 SQL Query.

          • To enter the SELECT statement yourself, type it in the SQL Query Statement area. Tip: Select the columns in the order you want them to appear in the report output.

        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 a Text 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: 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:

        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; 
          

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

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

      See also

      Section 1.7.5, "About Query Builder"

      3.8.4 Creating a break group

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

      To create a break group manually:

      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.

      2. Select the column that you want to use to divide your report (e.g. if you want to group a list of employee data by department number, select the department number column) and drag it out of and above the group to create a new group. This is the break column.

      3. Double-click the title bar of 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.5 Creating a matrix (cross-product) group

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

      1. In the Data Model view, right-click the query object, then 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:

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

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

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

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

      5. 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.8, "About matrix objects"

      Section 1.3.7, "About matrix reports"

      Section 2.1.6, "About nested matrix reports"

      Section 2.1.7, "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.6 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.7 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.8 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 drop-down list.

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

      See also

      Section 2.3.1, "About summary columns"

      3.8.9 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.3, "About placeholder 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:

      3.9.1 General Layout Objects

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

      3.9.1.1 Creating a field object

      To create a field object manually in the report layout:

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

      Text description of plmtxobj.gif follows.

      Text description of the illustration plmtxobj.gif

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

      See also

      Section 2.3.8, "About matrix objects"

      Section 1.3.7, "About matrix reports"

      Section 2.1.6, "About nested matrix reports"

      Section 2.1.7, "About matrix with group reports"

      Section 3.8.5, "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 Builder, you can use a barcode JavaBean that automatically generates the barcode for you. See the example report in Chapter 5, "Building a Report with a Barcode".

      To create a barcode using a barcode font:

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

        Note: For portability between platforms, you can use a PostScript barcode font to allow you to print your report to any PostScript-enabled output device.

      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.5, "About conditional formatting"

      Chapter 3.9.6.7, "Highlighting a value"

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

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

        3. To cut portions of text, click and drag to mark the text you want to cut, then press the Delete key.

      To cut, copy, or paste text:

        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.

        3. Choose Edit > Cut or Edit > Copy.

        4. To paste text, choose Edit > Paste.

      3.9.2.4 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 (e.g., &<fieldname>Oracle). If the field reference follows other text, no angle brackets are needed (e.g., Oracle&fieldname). You can include field references right next to each other with no spaces in between and without angle brackets (e.g., &field1&field2&field3).

      Usage Notes

      • A reference to a field includes the field's properties. Therefore, if the Horizontal Elasticity property is set to Fixed, any extra spaces in the field will appear in the report output. For example, if field f_sal is fixed horizontally, $&<f_sal>/week may produce $800 /week in the report output.

      • In addition to referencing fields in a boilerplate text object, you can directly reference a database column (e.g., &SAL). For example, $&<SAL>/week may produce $800/week in the report output.

      See also

      Section 1.8.4, "About fields"

      Section 1.8.5, "About boilerplate objects"

      3.9.2.5 Linking a boilerplate text object to a file

      To link a boilerplate text object to a file:

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

      2. Click and drag a rectangle.

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

      4. Under the Link File Boilerplate node:

        • Set the Source File Format property to Text.

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

      See also

      Section 1.8.5, "About boilerplate objects"

      3.9.2.6 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 properties:

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

      3.9.2.7 Changing text attributes

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

      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.8 Changing text attributes using PL/SQL

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

      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 Reports Builder 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:

      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 drop-down 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.4, "Referencing a field in boilerplate text") using &fieldname.

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

      3.9.3.2 Resetting page numbers

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

      1. In the Paper Layout view or Paper Design view, click the default page number field, then choose Edit > Clear 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 the Field node, double-click the Page Numbering property field (labeled...).

      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 and/or date stamp

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

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

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

      3. Click the desired date and/or time format, or click Custom to define your own format.

      3.9.4 Borders

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

      3.9.4.1 Showing or hiding object borders

      To show or hide the borders around an object:

      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 the solid black 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.9.11.3, "Adjusting parent borders automatically"

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

      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 (e.g., shift) key when moving the anchor.

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

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

      2. Set the Child Edge Percent property and/or Parent Edge Percent property to a new value.

      3. To change the edge on which the anchor is positioned, set the Child Edge Type property and/or Parent Edge Type property as desired.

      3.9.6 Colors, Patterns, and Highlighting

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

      3.9.6.1 Setting color palette preferences

      See Section 3.2.7, "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 fill.

      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 drop-down 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 Reports Builder 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 Reports Builder online help

      3.9.6.5 Modifying the color palette

      To modify the color palette:

      1. First, make the color palette editable:

      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. Select a color from the palette to alter it.

      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 Reports Builder 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:

      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.

      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 Reports Builder 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 shoud 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.5, "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.5, "About conditional formatting"

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

      3.9.7 Format Masks

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

      3.9.7.1 Specifying date and time format masks

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

      Table 3-2 Data and time format masks
      Format Mask Description

      SCC or CC

      Century, abbreviated;'S' prefixes "BC" with (-)

      SYYYY or YYYY

      Year;'S' prefixes "BC" "BC" date with a (-)

      I or IY or IYY

      Last 1, 2, or 3 digit(s) of year

      Y or YY or YYY

      Last 1, 2, or 3 digit(s) of year

      Y,YYY

      Year with comma

      SYEAR or YEAR

      Year, spelled out; 'S' prefixes "BC" date with (-)

      RRRR or SRRRR

      Year; 'S' prefixes "BC" date with a (-)

      RR

      Last 2 digit(s) of year

      BC, AD, or B.C., A.D.

      Century indicator

      Q

      Quarter of year (Jan-Mar= Quarter 1)

      MM

      Month in digits (Jan = 01)

      MONTH or MON

      Name of month, or 3-letter abbreviation

      WW, IW

      Week in year

      W

      Week in Julian days

      J

      Julian day; the number of days since January 1,4712 BC

      DDD, DD, or D

      Day in year, month, or week

      DAY

      Day of week fully spelled out (e.g., MONDAY)

      DY

      Name of day, 3-letter abbreviation (e.g., MON)

      AM, PM, or A.M., P.M.

      Meridian indicator

      HH or HH12

      Hour of day (1-12)

      HH24

      Hour of day (0-23)

      MI

      Minute

      SS; SSSSS

      Second in minute; seconds in day

      FM

      Toggles fill mode which replaces multiple spaces before or between dates, numbers, or words with a single space

      The following suffixes may be added to the format masks:

      Table 3-3 Data format mask suffixes
      Suffix Description

      TH

      Suffixed number ("DDth" for "4th")

      SP

      Spelled out number ("DDSP" for "FOUR")

      SPTH or THSP

      Spelled and suffixed number ("DDSPTH" for "FOURTH")

      Examples

      Table 3-4 Date format mask examples
      Sample Date Format Date Displayed

      MM/DD/RR

      03/04/02

      DD MON RRRR

      04 MAR 2002

      Mon. DD, RRRR

      Mar. 4, 2002

      Day Month DD fmHH:MI AM

      Monday March 4 11:35 AM

      Dy Mon ddth fmHH24:MI:SS

      Mon Mar 4th 23:35:22

      Day "the" ddthsp "of" Month

      Monday the fourth of March

      3.9.7.2 Specifying number format masks

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

      Table 3-5 Tokens for number format masks
      Format Token Description

      0

      Prints one digit.

      N

      Prints one digit, unless it is a leading zero to the left of the decimal point or a trailing zero to the right of the decimal point.

      *

      Prints one digit, unless it is a leading zero to the left of the decimal point, in which case an asterisk (*) is printed. Trailing zeros to the right of the decimal point are printed.

      9

      Prints one digit, unless it is a leading zero to the left of the decimal point, in which case a space is printed. Trailing zeros to the right of the decimal point are printed.

      +

      Prints a leading plus (+) for positive values and a leading minus (-) for negative values. This token must lead the mask.

      -

      Prints a leading minus (-) for negative values and nothing for positive values. This token must lead the other tokens.

      MI

      Prints a minus (-) after negative values and a space after positive values. This token must trail the other tokens.

      S

      Prints a minus (-) for negative values and a plus (+) for positive values (wherever the S appears in the mask). This token must lead or trail the other tokens.

      PR

      Prints angle brackets (<>) around negative values and spaces around positive values. This token must trail the other tokens.

      ( )

      Prints parentheses around negative values and spaces around positive values. The parentheses must surround the other tokens.

      DB

      Prints a "DB" after positive values. This token must trail the other tokens.

      CR

      Prints a "CR" after negative values. This token must trail the other tokens.

      Y

      Causes no sign symbol to be printed for negative or positive values.

      V

      Causes the number to be multiplied by 10N, where N is the number of 0, 9, *, and S tokens that appear to the right of the V.

      EEEE

      Causes the number to be printed in scientific notation. All digit tokens refer to digits of the mantissa. There must be exactly one digit to the left of the decimal point (displayed or implicit). The token EEEE prints as E followed by a plus (+), if the ordinate is positive or zero, and a minus (-), if the ordinate is negative, and two digits representing the ordinate (e.g., E-99).

      "string"

      Prints the string between the double quotes. To have double-quotes inside the string, type double-quotes back to back ("").

      . (period)

      Prints a period (.) to separate the integral and fractional parts of a number.

      D

      Prints the local decimal character to separate the integral and fractional parts of a number.

      , (comma)

      Prints a comma (,) as the group/thousands separator.

      G

      Prints the local group/thousands separator.

      $

      Prints $

      L

      Prints the local currency symbol.

      C

      Prints the ISO currency symbol.

      %

      Prints %.

      " "

      Prints a blank space. (Do not include quotes in mask.)

      v

      Prints a blank space for all zero values, regard-less of other tokens.

      K

      Prints a blank space.

      <>

      Delineates the beginning and ending of the decimal-aligned region (i.e., that part of the number that you want aligned with the decimal point in the format mask). Angle brackets indicate that the number should always occupy the same amount of space. If necessary, values are padded with blanks to the left or right of the decimal point.

      RN, rn

      Prints values in uppercase or lowercase Roman numerals, respectively. You cannot enter any other tokens with this token.

      Restrictions
      • For number format masks, if the actual value is longer than the specified format mask, the value will appear as a string of asterisks in the report output, regardless of the field's width. For example, if a fixed field's width is 8, the value is 1234567, and the format mask is <NNNNNN>, your output will be *******.

      • Similarly, if the number format mask causes the value to be larger than the field width, asterisks will appear in place of the value. For example, if a fixed field's width is 6, the value is 1234, and the format mask is -99999999, your output will be ******. This occurs because the format token 9 prints a blank for leading zeros to the left of the decimal. As a result, the value is too long to be displayed in a field of width 6.

      • If you do not specify a sign token in the format mask, positive values are preceded by a space and negative values are preceded by a minus (-).

      • After you create a format mask it will display in the list of values only if it is an appropriate format mask for the Datatype of the Source --i.e., format masks for numbers are displayed when the Source is a number, and format masks for dates are displayed when the Source is a date.

      • Format masks that print spaces for zeros (e.g., 9) increase the number of bytes per page taken up by your output.

      Examples

      Table 3-6 Number format mask examples
      Cap Sigma SymbolAlpha SymbolµPi SymbolLambdaa SymbolEpsilon Symbol Cap Nu SymbolUpsilon SymbolµBeta SymbolEpsilon SymbolRho Symbol Cap Phi SymbolOmicron SymbolRho SymbolµAlpha SymbolTau Symbol Cap Nu SymbolUpsilon SymbolµBeta SymbolEpsilon SymbolRho Symbol Cap Nu SymbolUpsilon SymbolµBeta SymbolEpsilon SymbolRho Symbol Cap Delta SymbolIota SymbolSigma SymbolPi SymbolLambdaa SymbolAlpha SymbolPsi SymbolEpsilon SymbolDelta Symbol

      -0000

      7934

      "7934"

      -7934

      "-7934"

      -00000

      7934

      "07934"

      -NNNN

      7639

      "7639"

      535

      "535"

      -NNN

      7639

      "****"

      -****

      7902

      "7902"

      -*****

      7902

      "*7902"

      +NNNN

      100

      "+100"

      -99

      "-99"

      (NNNN)

      1600

      " 1600 "

      -800

      "(800)"

      NNNNPR

      1600

      " 1600 "

      -800

      "<800>"

      NNNNMI

      1600

      "1600 "

      -800

      "800-"

      NNNVNN

      343

      "34300"

      N.NNEEEE

      7369

      "7.37E+03"

      "SRW"-0000

      7782

      "SRW7782"

      -$NNNN.NN

      800

      "$800"

      1600

      "$1600"

      -%NNN

      10

      "%10"

      -NN NNN.NN

      3000

      "3 000"

      +KKNNNNN.00

      1950

      "+ 1950.00"

      900

      "+ 900.00"

      $<NNNNN.00>

      1200

      "$ 1200.00"

      500

      "$ 500.00"

      $<NNNNN.00> DB

      1200

      "$ 1200.00 DB"

      -500

      "$ 500.00"

      $<NNNNN.00> CR

      1200

      "$ 1200.00"

      -500

      "$ 500.00 CR"

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

      3.9.7.3 Applying a format mask to a numeric object

      To apply a format mask to a numeric object:

      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. To apply the currency format to the object, click the Currency button in the toolbar.

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

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

      5. Choose Tools > Property Inspector.

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

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

      3.9.8.1 Creating a drawing object

      To create a drawing object:

      1. In the Paper Layout view, click a drawing tool in the tool palette (e.g., Rectangle, Ellipse, Polyline, etc.).

      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 (i.e., perfect circles and squares, etc.), hold down the constrain key (e.g., the shift key) when drawing the graphic.

      5. Double-click the drawing object.

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

      3.9.8.2 Adding a graph to a paper report

      To add a graph to your paper report:

      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. Follow the wizard to create the desired graph and position it in your report.

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

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

        • Right-click, and choose Graph Wizard.

        • Click the graph, then choose Edit > Settings.

      3.9.8.3 Editing a graph in a paper report

      To edit a graph in a paper report:

      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. Do either of the following:

        • Right-click and choose Graph Wizard.

        • Choose Edit > Settings.

      3.9.8.4 Adding a graph to a Web report

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

      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: Adding some text such as "Place the graph here" to your Web page allows 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.

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

      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.

      3.9.8.5 Editing a graph in a Web report

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

      • Between the <rw:graph> and </rw:graph> tags that define the graph, make changes directly to the JSP and XML tags. Note: This is not recommended as errors can result in messages or an unreadable graph.

      • Place your cursor anywhere between the <rw:graph> and </rw:graph> tags, then choose Edit > Settings to re-enter the Graph Wizard. Note that any manual changes and additions you've made to the graph XML will be lost if you click Finish, which redefaults the graph to the definition in the Graph Wizard.

      • Delete and re-create the graph as follows:

        1. Delete all lines from the <rw:graph> tag to the </rw:graph> tag.

        2. Leaving the cursor in the position where the graph was cut, choose Insert > Graph and follow the Graph Wizard to re-create the graph.

      3.9.8.6 Importing a drawing or image

      To import a drawing or image:

      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.

      See also

      Section 2.4.3, "About images"

      3.9.8.7 Selecting an image from the database

      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. Under the Column node, set the File Format property to Image.

      See also

      Section 2.4.3, "About images"

      Section 1.7.3, "About database columns"

      3.9.8.8 Selecting an image URL from the database

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

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

        • Set the Read from File property to Yes.

        • Set the File Format property to Image URL.

      See also

      Section 2.4.3, "About images"

      Section 2.2.3, "About HTML and PDF in Reports Builder"

      3.9.8.9 Linking an image object to a file

      To link an image object to a file:

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

      2. Click and drag a rectangle.

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

      4. Under Link File Boilerplate:

        • Set the Source File Format property to Image.

        • Set the Source Filename property to the name of the file containing the image.

      See also

      Section 2.4.3, "About images"

      Section 1.8.5, "About boilerplate objects"

      3.9.8.10 Linking an image object to a URL

      Note: This procedure is for HTML output only.

      To link an image object to a URL:

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

      2. Click and drag a rectangle.

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

      4. Under the Link File Boilerplate node:

        • Set the Source File Format property to Image URL.

        • Set the Source Filename property to the URL where the image is located with the required protocol.

      Example 1
      HTTP://www.oracle.com/images/logo.gif
      
      Example 2
      HTTP://&<P_SERVER_NAME>/images/logo.gif 
      
      

      where P_SERVER_NAME is a user parameter of type CHAR

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

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

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

      See also

      Section 2.4.3, "About images"

      Section 2.2.3, "About HTML and PDF in Reports Builder"

      3.9.9 Page or Group Headers or Footers

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

      3.9.9.1 Creating a text heading

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

      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.

      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 that you specify a format mask, create a hidden field object in the margin area (see Section 3.9.1.1, "Creating a field object"):

        • In the Property Inspector, under the Field node, set the Source property to the source column for the value, set the Visible property to No, and set the Format Mask property as desired.

        • Under the General Layout node, set the Horizontal Elasticity property to Variable.

        Note: if you do not need to specify a format mask, you can simply reference the value directly in Step 6, and you do not need to create this hidden field.

      6. Create a boilerplate text object in the margin area, and reference the field and/or totals you want to include in the heading. See Section 3.9.2.1, "Creating a boilerplate object for text" and Section 3.9.2.4, "Referencing a field in boilerplate text".

      See also

      The example report in Chapter 23, "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:

      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.

      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:

      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.9.13.1, "Changing columns labels or widths"

      Chapter 3.9.13.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:

      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:

      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.9.11.4, "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 gridpoint; the result may be that smaller changes to these values are not evident in the report output.

      See also

      Chapter 3.9.11.4, "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 Parent Frame 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 to leave between row in the report output (e.g., 0.25).

      3.9.13.5 Adding blank lines between groups of rows

      To add blank lines between groups of rows:

      1. Create a summary column (see Section 3.8.8, "Creating a summary column"). On the Totals page of the Data Wizard (or Report Wizard), select any field that appears in each row of output, and Count as the calculation. On the Fields page, remove the summary from the displayed fields column.

      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 (e.g., 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 field (labeled ...) 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:

      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:

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

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


        Cautions:

        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 combo box as a text field, 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.12.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: In Oracle9i Reports, 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 (e.g., data ranges) to reports running in batch mode:

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

      Query:

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

      Runtime:

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

      3.11.10 Creating an HTML Parameter Form header using PL/SQL

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

      3.11.11 Creating an HTML Parameter Form footer using PL/SQL

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

      3.11.12 Creating HTML Parameter Form input or select events

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

      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 via 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 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 properties:

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

        • Set the Before Form Value property the following JavaScript code:

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

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

        Please enter a numeric value.
        
        Example 2: Select validation

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

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

            • Under Parameter Form Field, set the Source property to DESFORMAT.

            • Under Web Settings, set the Additional Attributes (HTML) property to the following JavaScript event handler:

              onChange="this.value=this.value.toUpperCase()"
              
              

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

            3.12 Define a Template

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

            3.12.1 Creating a template

            To create a template:

            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.1, "About the Template Editor"

            Section 2.7.2, "About templates"

            Section 2.7.3, "About template attributes"

            Section 2.7.4, "About applying templates"

            Section 2.7.5, "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 drop-down 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 that any changes you make for an individual report style will override the attributes defined under the Default node.

            See also

            Section 2.7.2, "About templates"

            Section 2.7.3, "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 (e.g., 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 drop-down 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 that any changes you make for an individual report style will override the attributes defined under the Default node.

            See also

            Section 2.7.2, "About templates"

            Section 2.7.3, "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 predefined 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.2, "About templates"

            Section 2.7.4, "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.2, "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.1, "About the Template Editor"

            Section 2.7.2, "About templates"

            Section 2.7.3, "About template attributes"

            Section 2.7.5, "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.1, "About the Template Editor"

            Section 2.7.2, "About templates"

            Section 2.7.3, "About template attributes"

            Section 2.7.5, "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 the Template Editor"

            Section 2.7.2, "About templates"

            Section 2.7.3, "About template attributes"

            Section 2.7.5, "About inheritance in templates"

            3.12.9 Adding a template to the predefined templates list

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

            1. In a text editor (e.g., Wordpad), open the preferences file. (On Windows, open ORACLE_HOME\CAUPREFS.ORA (user preferences) if it exists; otherwise, open ORACLE_HOME\CAGPREFS.ORA (global preferences). On UNIX, open your_home_directory/prefs.ora (user preferences) if it exists; otherwise, open $ORACLE_HOME/tools/admin/prefs.ora (global preferences).)

            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. For each report style (tabular, group left, etc.) for which the template is defined, apply the template to a sample report.

            2. Use a screen capture tool to capture a portion of each sample report (no larger than 2 inches wide and 3 inches high) in .bmp format using the following naming convention:

              yyyz.bmp

              where

              yyy

              matches the filename specified in the Reports.xxx_Template_File list

              z

              identifies the report style, as follows:

              a = Group Above

              f = Form-like

              g = Matrix with Group

              l = Group Left

              m = Mailing Label

              r = Form Letter

              t = Tabular

              x = Matrix

            3. Copy or create the .bmp file you want to use as a default (i.e., displays if no bitmap exists for a specific report style) and name it yyy.bmp.

            4. Copy each .bmp file to ORACLE_HOME/REPORTS/TEMPLATES.

            See also

            Section 2.7.2, "About templates"

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

            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.11, "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:

            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 are going to be replacing 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:

            Automatic indent

            When you press the Enter key at the end of a line, the next line is automatically indented.

            Color syntax highlighting

            Keywords, comments, strings, and symbols such as := and || are colored differently.

            Column and line selection

            You can select columns of text as well as lines of source code.To select a column, press the ALTkey, then click and drag horizontally.To select a line, click on the extreme left margin of the line in the Source pane.

            Drag and drop text manipulation

            Selected text may be copied or moved by dragging and dropping.To copy text, press the CTRL key and drag the selected text.To move text, simply drag the selected text.

            Indent/Outdent lines

            The Indent/Outdent commands in theEdit menu enable you to indent or outdent selected source lines.

            Multiple split views

            You can create up to four separate views of the current program unit.To create horizontal views, place the cursor on the split bar at the top of the vertical scroll bar. Then click and drag the split bar down.To create vertical views, place the cursor on the split bar at the far left of the horizontal scroll bar. Then click and drag the split bar to the right.To remove a horizontal or vertical view, double-click the split bar that separates the views.

            Printing

            Choose File Print to print the current program unit.

            Unlimited undo/redo

            The Undo/Redo commands on theEdit menu enable you to undo or redo changes as far back as the last save operation.

            Note:

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

            3.13.2.3.2 Using the keyboard in the PL/SQL Editor

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

            To do the following

            Use Keystroke

            On Platform

            Move cursor left one character

            Left arrow key

            Windows and UNIX

            Select character as cursor moves left

            Shift + Left arrow key

            Windows and UNIX

            Move cursor right one character

            Right arrow key

            Windows and UNIX

            Select character as cursor moves right

            Shift + Right arrow key

            Windows and UNIX

            Copy selected characters or words

            Ctrl + C

            Windows and UNIX

            Cut selected characters or words

            Ctrl + X

            Windows and UNIX

            Paste from the clipboard

            Ctrl + V

            Windows and UNIX

            Delete character on the right of current cursor position, or delete selected characters or words

            Delete key

            Windows and UNIX

            Delete character on the left of current cursor position

            Backspace

            Windows and UNIX

            Move cursor to the end of the program unit

            Ctrl + End

            Windows

            Move cursor to the start of the program unit

            Ctrl + Home

            Windows

            Select source lines from the current cursor position to the end of the program unit

            Ctrl + Shift + End

            Windows

            Select source lines from the current cursor position to the start of the program unit

            Ctrl + Shift + Home

            Windows

            Move cursor to the end of current line

            End

            Windows

            Move cursor to the start of current line

            Home

            Windows

            Select characters from the current cursor position to the end of the current line

            Shift + End

            Windows

            Select characters from the current cursor position to the start of the current line

            Shift + Home

            Windows

            Indent selected line, or indent characters on the right of current cursor position

            Tab Key

            Windows and UNIX

            Outdent selected line

            Shift + Tab key

            Windows and UNIX

            Move cursor down one line

            Down arrow key

            Windows and UNIX

            Select line as cursor moves down

            Shift + Down arrow key

            Windows and UNIX

            Move cursor up one line

            Up arrow key

            Windows and UNIX

            Select line as cursor moves up

            Shift + Up arrow key

            Windows and UNIX

            Scroll down the program unit by the number of lines that are shown in the Source pane

            PageDown key

            Windows and UNIX

            Scroll down the program unit by the number of lines that are shown in the Source pane and selects the lines at the same time

            Shift + PageDown key

            Windows and UNIX

            Scroll up the program unit by the number of lines that are shown in the Source pane

            PageUp key

            Windows and UNIX

            Scroll up the program unit by the number of lines that are shown in the Source pane and selects the lines at the same time

            Shift + PageUp key

            Windows and UNIX

            Undo most recent action

            Ctrl + Z

            Windows

            Revert most recent undo action

            Ctrl + Y

            Windows

            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:

            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 drop-down list.

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

            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:

            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)

            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)

            6. Click OK.

            7. In the Stored PL/SQL Editor, select a database owner name from the Owner drop-down list to indicate where the program unit will be stored in the database, then define the PL/SQL for the stored program unit.

            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 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.12.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.12.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 drop-down list.

            3. Choose a table name from the Table: drop-down list.

              The Table drop-down list shows a list of table names owned by the user shown in the Table Owner field. If you select a user name other than your own in the Table Owner field, the Table drop-down list shows only the tables to which you have been granted access.

            4. (For views only) To define an INSTEAD OF trigger for an object view, click the arrow next to Table: to display a drop-down list and choose View: Then choose a view name from the View drop-down list.

            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 drop-down list. You can modify the trigger name.

              The Name drop-down list displays a list of trigger names associated with the table (or view) shown in the Table (or View) field. The Name drop-down list displays only the names of the database triggers associated with the tables to which you have access.

            6. After specifying the trigger options and action, click Save to compile the trigger.

            See also

            Section 2.6.12.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:

            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.7, "Creating or editing a formula column".

            3.13.4.4 Creating a placeholder column

            See Section 3.8.9, "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:

            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 report definitions or PL/SQL libraries from one storage format to another:

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

            3.13.6 Compiling and running program units

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

            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, then click GoTo to navigate to the source of the error in the program unit.

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

            3.13.6.2 Compiling all program units

            To compile all program units:

            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, then click Go To to navigate to the source of the error in the program unit.

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

            3.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, then click Go To to navigate to the source of the error in the program unit.

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

            3.14 Debug a Report

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

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

            See also

            Section 2.10.1, "About the debugging process"

            Section 2.10.5, "About debug actions"

            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.

            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 section of the Reports Builder online help, under Command Line Arguments.

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

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

            4. In the Trigger dialog box, define the trigger:

              • If you want the trigger to fire at different location than the current program unit, select a location from the Location drop-down list.

              • Type the debug trigger in the Trigger Body text box.

                For example, to create a debug trigger that interrupts program execution if the local NUMBER variable my_sal exceeds 5000, enter the following as the trigger body:

                 IF Debug.Getn('my_sal') > 5000 THEN
                  raise Debug.Suspend;
                 END IF;
                
                

                Note: To create a debug trigger that contains multiple lines of text, include a BEGIN and an END statement around the code.

                You must raise the DEBUG.SUSPEND exception from the DEBUG package if you want the PL/SQL Interpreter to appear when this line is executed. Otherwise, Reports Builder executes the code silently and the PL/SQL Interpreter does not appear.

            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 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 Viewing debug action source

              To view debug action source:

              1. In the Object Navigator, expand the Debug Actions node.

              2. Click the desired debug action to display the source in the Source pane of the PL/SQL Interpreter.

              3.14.7 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.8 Disabling and enabling debug actions

              To disable/enable a debug action:

              1. In the Object Navigator, double-click the desired debug action to display the Breakpoint/Trigger dialog box.

              2. Check/uncheck the Enabled check box to enable/disable the debug action.

              3.14.9 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.10 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.11 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. 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.12 Controlling program unit execution

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

              STEP

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

              • execute the next statement (optionally descending into subprogram calls)

              • resume execution until the current subprogram has returned

              • continue execution until the specified source location is reached

              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.13 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 that 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.14 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.15 Displaying the current scope location

              To display the current scope location:

              1. In the Object Navigator, expand the Stack node.

              2. 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.16 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.17 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.18 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.19 Tracing report execution

              To set tracing options, do one of the following:

                • Choose Program > Tracing and fill out the Runtime Trace Settings dialog box as desired.

                • On the command line, specify tracing options using rwbuilder or rwrun with the TRACEFILE, TRACEMODE, and TRACEOPTS keywords.

                • Specify tracing options via the Reports Builder built-in package using SRW.TRACE_START, SRW.TRACE_END, SRW.TRACE_ADD_OPTIONS, SRW.TRACE_REM_OPTIONS.

              3.14.20 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.2, "About report distribution"

              Section 3.7.10, "Distributing a report to multiple destinations"

              3.14.21 Tracing using the SQL TRACE function

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

              There are two ways to trace your reports:

              • user level

              • system level

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

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

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

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

              3.15.1 Accessing non-Oracle data sources

              Oracle9i Reports allows you to access any data source. The new pluggable data source (PDS) architecture replaces Oracle Open Client Adapter (OCA), and the Open Database Connectivity (ODBC) drivers are no longer supported in Oracle9i 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?

                Oracle9i 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 Oracle9i Reports to query.

              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 the Reports Builder. If the PDS does not exist, use the API Reference to help you build one.

              Examples

              Oracle9i Reports ships with XML data source sample files.

              See also

              The Pluggable Data Sources section of the Reports Builder 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.2 Building a user exit

              In Oracle9i Reports, you can call Java methods using the ORA_JAVA built-in package and the Java importer. This reduces the need to have user exits in a report and allows for a more open and portable deployment. You may also use the ORA_FFI built-in package, which provides a foreign function interface for invoking C functions in a dynamic library. With the availability of these new built-in packages, the use of user exits is being deprecated in Oracle9i Reports, though makefiles will still be supplied to permit you to continue to work with existing user exits.

              3.16 Administer Reports Builder

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

              3.16.1 Setting a database role

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

              To set a database role for a report:

              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.


                  Warning:

                  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:

                • On the command line, type ORACLE_HOME\BIN\rwconverter, followed by the report name and desired arguments.

              3.16.3 Improving performance using SQL statements

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

              • use a WHERE clause instead of a group filter or format trigger to exclude records

              • use the SUBSTR function to truncate character strings instead of truncating in Reports Builder

              • perform calculations directly in your query rather than in a formula or summary

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

              3.16.4 Improving performance using WHERE clauses

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

              Q_Dept
              SELECT DEPTNO FROM DEPT
              WHERE DEPTNO < 100
              
              Q_Job
              SELECT DISTINCT JOB FROM EMP
              WHERE DEPTNO < 100
              
              Q_Filler

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

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

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

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


Go to previous page Go to next page
Oracle
Copyright © 2002 Oracle Corporation.

All Rights Reserved.
Go To Table Of Contents
Contents
Go To Index
Index