Contents

Title and Copyright Information

Send Us Your Comments

Preface

1 Basic Concepts

1.1 Reports Builder

1.1.1 About Reports Builder

1.1.2 About this release

1.2 Reports

1.2.1 About reports

1.2.2 About Web reports

1.3 Report Styles

1.3.1 About tabular reports

1.3.2 About group above reports

1.3.3 About group left reports

1.3.4 About form-like reports

1.3.5 About form letter reports

1.3.6 About mailing label reports

1.3.7 About matrix reports

1.4 Wizards

1.4.1 About the Report Wizard

1.4.2 About the Report Block Wizard

1.4.3 About the Data Wizard

1.4.4 About the Graph Wizard

1.5 The Object Navigator

1.5.1 About the Object Navigator

1.5.2 About Object Navigator views

1.6 The Report Editor

1.6.1 About the Report Editor

1.6.2 About the Data Model view

1.6.3 About the Paper Layout view

1.6.4 About the Paper Design view

1.6.5 About the Paper Parameter Form view

1.6.6 About the Web Source view

1.6.7 About the tool palette and toolbar

1.7 Data Model Objects

1.7.1 About queries

1.7.2 About groups

1.7.3 About database columns

1.7.4 About data links

1.7.5 About Query Builder

1.8 Layout Objects

1.8.1 About frames

1.8.2 About repeating frames

1.8.3 About frame and repeating frame sizing

1.8.4 About fields

1.8.5 About boilerplate objects

1.9 Parameter Form Objects

1.9.1 About parameters

1.9.2 About Parameter Form fields

1.9.3 About Parameter Form boilerplate

1.9.4 About Parameter Forms for Web reports

1.10 The Property Inspector

1.10.1 About the Property Inspector

1.11 Runtime Views

1.11.1 About the Runtime Parameter Form

1.11.2 About the Previewer

1.12 Executables

2 Advanced Concepts

2.1 Reports

2.1.1 About report titles

2.1.2 About report sectioning and sections

2.1.3 About the report unit of measurement

2.1.4 About the report dimensions

2.1.5 About conditional formatting

2.1.6 About nested matrix reports

2.1.7 About matrix with group reports

2.2 Web Reports

2.2.1 About JavaServer Pages (JSPs) and Servlets

2.2.2 About previewing JSP-based Web reports

2.2.3 About HTML and PDF in Reports Builder

2.2.4 About Web links for HTML output

2.2.5 About Web links for PDF output

2.2.6 About hyperlinks

2.2.7 About hyperlink destinations

2.2.8 About bookmarks

2.2.9 About application command line links

2.2.10 About before and after escapes

2.2.11 About style sheets

2.3 Data Model Objects

2.3.1 About summary columns

2.3.2 About formula columns

2.3.3 About placeholder columns

2.3.4 About referencing columns and parameters

2.3.5 About non-linkable queries

2.3.6 About links versus groups

2.3.7 About cursor variables

2.3.8 About matrix objects

2.4 Layout Objects

2.4.1 About layout objects

2.4.2 About layout defaulting

2.4.3 About images

2.4.4 About anchors

2.4.5 About changing colors and patterns

2.4.6 About resizing objects

2.4.7 About moving and layering objects in the Paper Layout view

2.5 Parameter Form Objects

2.5.1 About Parameter Form HTML extensions

2.6 PL/SQL

2.6.1 About the PL/SQL Editor

2.6.2 About the Stored PL/SQL Editor

2.6.3 About the Syntax Palette

2.6.4 About program units

2.6.5 About stored program units

2.6.6 About external PL/SQL libraries

2.6.7 About attached libraries

2.6.8 About formulas

2.6.9 About group filters

2.6.10 About ref cursor queries

2.6.11 About built-in packages

2.6.12 About triggers

2.7 Templates

2.7.1 About the Template Editor

2.7.2 About templates

2.7.3 About template attributes

2.7.4 About applying templates

2.7.5 About inheritance in templates

2.8 Output Formats and Capabilities

2.8.1 About batch reporting

2.8.2 About report distribution

2.8.3 About event-based publishing

2.8.4 About switching the printer tray

2.8.5 About HTML output

2.8.6 About PDF output

2.8.7 About XML output

2.8.8 About RTF output

2.8.9 About delimited output

2.8.10 About text output

2.8.11 About creating an ASCII (character-mode) report

2.9 Data Sources

2.9.1 About database roles

2.9.2 About Oracle Net Services

2.9.3 About Oracle9i

2.10 Debugging Tools

2.10.1 About the debugging process

2.10.2 About the PL/SQL Interpreter

2.10.3 About the Source pane

2.10.4 About debug commands in the PL/SQL Interpreter

2.10.5 About debug actions

2.10.6 About the current execution location

2.10.7 About the current scope location

2.10.8 About debug levels

2.10.9 About modifying code at runtime

3 How To...

3.1 Access Oracle9i Reports Documentation

3.1.1 Using the online help

3.1.2 Locating other documentation

3.2 Set Properties and Preferences

3.2.1 Displaying the Property Inspector

3.2.2 Setting report properties

3.2.3 Setting report preferences

3.2.4 Setting preferences for the Object Navigator

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

3.2.6 Setting font mappings

3.2.7 Setting color palette preferences

3.2.8 Setting properties of multiple objects

3.2.9 Comparing the properties of one object to another

3.3 Perform Common Tasks:

3.3.1 Connecting to a database

3.3.2 Opening a report

3.3.3 Saving a report

3.3.4 Copying a report

3.3.5 Renaming a report

3.3.6 Deleting a report

3.3.7 Archiving a report

3.3.8 Selecting and deselecting objects

3.3.9 Deleting an object

3.3.10 Hiding or showing components

3.4 Work with the Object Navigator

3.4.1 Displaying a Report Editor view from the Object Navigator

3.4.2 Expanding and collapsing nodes

3.4.3 Searching for nodes

3.4.4 Changing Object Navigator views

3.4.5 Setting preferences for the Object Navigator

3.5 Work with Reports

3.5.1 Creating a report

3.5.2 Creating a multi-query group above report

3.5.3 Creating a nested matrix report

3.5.4 Creating a default layout for a report

3.5.5 Creating an additional report layout

3.5.6 Adding a title to a report

3.5.7 Creating an ASCII (character-mode) report

3.5.8 Preparing a multi-platform report

3.5.9 Preparing a report for translation into other languages

3.6 Work with Web Reports

3.6.1 Creating a Web report

3.6.2 Viewing the source code for a Web report

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

3.6.4 Adding a report block to a Web page

3.6.5 Adding a graph to a Web report

3.6.6 Preparing a paper-based report for the Web

3.6.7 Adding Web links to paper-based reports

3.7 Run and Dispatch a Report

3.7.1 Running and dispatching a report from the user interface

3.7.2 Running a report from the command line

3.7.3 Running a report using a command file

3.7.4 Running a report to a remote Reports Server

3.7.5 Generating HTML, HTMLCSS or PDF output

3.7.6 Generating XML output

3.7.7 Generating RTF output

3.7.8 Generating delimited output

3.7.9 Generating text output

3.7.10 Distributing a report to multiple destinations

3.7.11 Changing orientation

3.7.12 Suppressing the Parameter Form

3.7.13 Viewing report output

3.7.14 Printing a report

3.8 Work with the Data Model

3.8.1 Creating a query

3.8.2 Modifying a query

3.8.3 Using Query Builder

3.8.4 Creating a break group

3.8.5 Creating a matrix (cross-product) group

3.8.6 Creating a data link

3.8.7 Creating or editing a formula column

3.8.8 Creating a summary column

3.8.9 Creating or editing a placeholder column

3.9 Work with the Report Layout

3.9.1 General Layout Objects

3.9.2 Text Objects

3.9.3 Page Numbers or Date/Time Stamps

3.9.4 Borders

3.9.5 Anchors

3.9.6 Colors, Patterns, and Highlighting

3.9.7 Format Masks

3.9.8 Graphic or Image Objects

3.9.9 Page or Group Headers or Footers

3.9.10 Margin, Header Page, or Trailer Page Objects

3.9.11 Move Objects

3.9.12 Resize Objects

3.9.13 Change Spacing

3.9.14 Modify the Page Layout

3.10 Work with Report Sections

3.10.1 Displaying a section layout view

3.10.2 Creating a default layout for a section

3.11 Work with Parameters and the Parameter Form

3.11.1 Using a pre-defined system parameter

3.11.2 Creating a user parameter

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

3.11.4 Validating a parameter value at runtime

3.11.5 Creating a default Parameter Form

3.11.6 Selecting parameters to include in the Runtime Parameter Form

3.11.7 Displaying the Parameter Form at runtime

3.11.8 Adding more pages to the Runtime Parameter Form

3.11.9 Passing parameters to reports running in batch mode

3.11.10 Creating an HTML Parameter Form header using PL/SQL

3.11.11 Creating an HTML Parameter Form footer using PL/SQL

3.11.12 Creating HTML Parameter Form input or select events

3.11.13 Changing HTML Parameter Form input to uppercase

3.12 Define a Template

3.12.1 Creating a template

3.12.2 Defining default template attributes

3.12.3 Defining override template attributes

3.12.4 Applying a template to a report

3.12.5 Formatting the report title in a template

3.12.6 Adding items and objects to a template

3.12.7 Modifying objects in the template margin

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

3.12.9 Adding a template to the predefined templates list

3.13 Use PL/SQL in a Report or Template

3.13.1 Using a built-in package

3.13.2 Working with the PL/SQL Editor

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

3.13.4 Creating or editing object-level PL/SQL

3.13.5 Creating or editing an external PL/SQL library

3.13.6 Compiling and running program units

3.14 Debug a Report

3.14.1 Debugging a report

3.14.2 Running a report in debug mode

3.14.3 Setting a breakpoint

3.14.4 Setting a debug trigger

3.14.5 Browsing debug actions

3.14.6 Viewing debug action source

3.14.7 Editing a debug action

3.14.8 Disabling and enabling debug actions

3.14.9 Deleting a debug action

3.14.10 Running a program unit in the PL/SQL Interpreter

3.14.11 Inserting a Navigator pane in the PL/SQL Interpreter

3.14.12 Controlling program unit execution

3.14.13 Stepping through the code

3.14.14 Modifying code at runtime

3.14.15 Displaying the current scope location

3.14.16 Examining or changing local variables

3.14.17 Modifying application variables

3.14.18 Viewing subprogram references

3.14.19 Tracing report execution

3.14.20 Tracing report distribution

3.14.21 Tracing using the SQL TRACE function

3.15 Integrate with Other Products

3.15.1 Accessing non-Oracle data sources

3.15.2 Building a user exit

3.16 Administer Reports Builder

3.16.1 Setting a database role

3.16.2 Converting from one format to another

3.16.3 Improving performance using SQL statements

3.16.4 Improving performance using WHERE clauses

4 Visual Index

4.1 Visual index of the reports

4.1.1 Building a report with a barcode

4.1.2 Bursting and distributing a report

4.1.3 Building a paper report with ref cursors

4.1.4 Building a report with an XML pluggable data source

4.1.5 Building a report using Oracle Express data

4.1.6 Building a report with a text pluggable data source

4.1.7 Building a tabular report

4.1.8 Building a mailing label report

4.1.9 Building a form letter report

4.1.10 Building a master-master report

4.1.11 Building a single query, group report

4.1.12 Building a two query, group report

4.1.13 Building a summary report

4.1.14 Building a group left summary report

4.1.15 Building a group left formula report

4.1.16 Building an across break report

4.1.17 Building a wrapped break report

4.1.18 Building a header and footer report

4.1.19 Building a header with database values report

4.1.20 Building a report with graphics, text, and color

4.1.21 Building a matrix report

4.1.22 Building a nested matrix report

4.1.23 Building a matrix with group above report

4.1.24 Building a time series calculations report

4.1.25 Building a report with aggregate data

4.1.26 Building a report that renumbers pages by repeating frame

4.1.27 Building an intermixed fields report

4.1.28 Building a check printing report with spelled-out cash amounts

4.1.29 Building a report using a pre-printed form

4.1.30 Building an invoice report

4.1.31 Building a PL/SQL report

4.1.32 Building a ranking report

4.1.33 Building a report that suppresses labels when no detail records are retrieved

4.1.34 Building a conditional form letter report

4.1.35 Building a report with conditional highlighting

4.1.36 Building a report with dynamic graphics

4.2 Summary

5 Building a Report with a Barcode

5.1 Prerequisites for this example

5.1.1 Example files

5.1.2 Access to the sample schema

5.1.3 Update the REPORTS_CLASSPATH environment variable

5.2 Create a barcode report for paper

5.2.1 Import the Java classes into Reports Builder

5.2.2 Create a package to store your information

5.2.3 Create a Before Report trigger

5.2.4 Create a data model with a formula column

5.2.5 Create a layout for your report

5.3 Create a barcode report for the Web

5.3.1 Create a query in an existing HTML file

5.3.2 Create three formula columns in your data model

5.3.3 Initialize the barcode JavaBean and set its properties

5.3.4 Run your report to the Web

5.4 Summary

6 Bursting and Distributing a Report

6.1 Prerequisites for this example

6.1.1 Example files

6.1.2 Access to the sample schema

6.2 Set up an existing report for bursting

6.3 Edit the distribution XML definition

6.4 Run the report

6.5 Summary

7 Building a Paper Report with Ref Cursors

7.1 Prerequisites for this example

7.1.1 Access to the sample Human Resources schema

7.2 Define a ref cursor type

7.3 Create a ref cursor query

7.4 Refine the data model

7.5 Create links between ref cursor queries

7.6 Add summary columns

7.7 Create a layout

7.8 Move the SELECT statement into a package

7.9 Move the packages into a library

7.10 Summary

8 Building a Report with an XML Pluggable Data Source

8.1 Prerequisites for this example

8.1.1 Example files

8.1.2 Access to the sample schema

8.2 Create a report manually with SQL and XML queries

8.2.1 Create a SQL query for your new report

8.2.2 Create an XML query to access your XML data source

8.2.3 Create a data link between two queries

8.2.4 Create a layout for your report using the Report Wizard

8.2.5 Apply alternating row colors to your report

8.2.6 Filter your XML data using groups

8.3 Run your report to paper

8.4 Summary

9 Building a Report Using Express Data

9.1 Prerequisites for this example

9.1.1 Example files

9.1.2 Access to an Express Server

9.2 Create an Express report with the Report Wizard

9.3 Refine the Express query

9.4 Add summary columns and custom measures to the data model

9.4.1 Rename data objects

9.4.2 Create summary columns

9.4.3 Create a custom measure

9.5 Enhance the report layout

9.5.1 Insert summary fields in the report

9.5.2 Insert the custom measure field into the report

9.5.3 Sort dimension values

9.5.4 Make format changes in the Paper Design view

9.6 Summary

10 Building a Report with a Text Pluggable Data Source

10.1 Prerequisites for this example

10.2 Set up the textpds.conf file

10.3 Use the Report Wizard to create a report

10.4 Summary

11 Building a Tabular Report

11.1 Prerequisites for this example

11.2 Use the Report Wizard to create a report

11.3 Summary

12 Building a Mailing Label Report

12.1 Prerequisites for this example

12.2 Use the Report Wizard to create a mailing label report

12.3 Add Vertical Spacing

12.4 Summary

13 Building a Form Letter Report

13.1 Prerequisites for this example

13.2 Use the Report Wizard to create a form letter report

13.3 Summary

14 Building a Master-Master Report

14.1 Prerequisites for this example

14.2 Create a new report manually

14.3 Use the Data Wizard to create two queries

14.4 Use the Report Wizard to layout the data

14.5 Use the Paper Layout view to add white space

14.6 Format a field

14.7 Summary

15 Building a Single-Query, Group Report

15.1 Prerequisites for this example

15.2 Group report with one break column

15.2.1 Create a data model with a break group and group left layout

15.2.2 Format a field

15.2.3 Use the Property Inspector to add white space

15.3 Group report with two break columns

15.3.1 Modify the data model

15.3.2 Redefault the layout

15.4 Group report with two break groups

15.4.1 Modify the data model

15.4.2 Redefault the layout

15.5 Summary

16 Building a Two Query Group Report

16.1 Prerequisites for this example

16.2 Create a new report manually

16.3 Create a data model with a data link

16.4 Use the Report Wizard to layout the data

16.5 Format a field

16.6 Summary

17 Building a Summary Report

17.1 Prerequisites for this example

17.2 Create a data model and a group above layout

17.3 Format fields

17.4 Examine the summary column properties (optional)

17.5 Summary

18 Building a Group Left Summary Report

18.1 Prerequisites for this example

18.2 Create a new report manually

18.3 Create a data model with a data link

18.4 Use the Paper Layout view to create two layouts

18.5 Merge the two layouts

18.6 Format fields

18.7 Summary

19 Building a Group Left Formula Report

19.1 Prerequisites for this example

19.2 Use the Report Wizard to create a simple report

19.3 Create two formula columns

19.3.1 Create a formula column to calculate the tax

19.3.2 Create a formula column that calculates customer order totals

19.3.3 Add the formula columns to the report layout

19.4 Summary

20 Building an Across Break Report

20.1 Prerequisites for this example

20.2 Create two queries

20.3 Create the default layout

20.4 Run your report to paper

20.5 Summary

21 Building a Wrapped Break Report

21.1 Prequisites for this example

21.2 Create a query in the Data Model view

21.3 Create three summary columns

21.4 Create the default layout using the Report Wizard

21.5 Modify the layout of the report

21.6 Run your report to paper

21.7 Summary

22 Building a Header and Footer Report

22.1 Prerequisites for this example

22.2 Create a data model and a group left layout

22.3 Move a summary

22.4 Add a page heading

22.5 Adding white space and formatting values

22.6 Summary

23 Building a Header with Database Values Report

23.1 Prerequisites for this example

23.2 Create a data model and a group left layout

23.3 Add summary columns for the header data

23.4 Add a page heading

23.5 Summary

24 Building a Report with Graphics, Text, and Color

24.1 Prequisites for this example

24.2 Create a simple report definition

24.3 Modify the report in the Paper Layout view

24.3.1 Adding a border around the report

24.3.2 Change the font size and style

24.3.3 Add bullets your report

24.3.4 Display a line between each record

24.3.5 Add a title to your report

24.4 Summary

25 Building a Matrix Report

25.1 Prerequisites for this example

25.2 Create a single query matrix

25.3 Create a multiple query matrix

25.3.1 Create a new report manually

25.3.2 Create a data model with a cross product and a data link

25.3.3 Create the layout with the Report Wizard

25.4 Add summaries and format monetary values

25.5 Add zeroes in place of blanks

25.6 Add a grid

25.7 Summary

26 Building a Nested Matrix Report

26.1 Prerequisites for this example

26.2 Create a single query matrix

26.3 Create a multiple query matrix

26.3.1 Create a new report manually

26.3.2 Create a data model with a cross product and data links

26.3.3 Create the layout with the Report Wizard

26.4 Create a multiple query matrix with a break

26.4.1 Create a new report manually

26.4.2 Create a data model with a cross product and data links

26.4.3 Create the layout with the Report Wizard

26.5 Format monetary values

26.6 Summary

27 Building a Matrix with Group Above Report

27.1 Prerequisites for this example

27.2 Create a matrix group data model and layout

27.3 Add labels and lines for summaries

27.4 Add space between groups

27.5 Create a Web layout

27.6 Summary

28 Building a Time Series Calculations Report

28.1 Prerequisites for this example

28.2 Create a query and the layout

28.3 Modify the Web source of your JSP report

28.4 Summary

29 Building a Report with Aggregate Data

29.1 Prerequisites for this example

29.2 Create a query and the layout

29.3 Modify the Web source of your JSP report

29.4 Summary

30 Building a Report that Renumbers Pages by Repeating Frame

30.1 Prerequisites for this example

30.2 Create a data model and a group above layout

30.3 Add a second query

30.4 Redefault the layout

30.5 Set properties and format fields

30.6 Create new fields

30.7 Reference fields

30.8 Summary

31 Building an Intermixed Fields Report

31.1 Prerequisites for this example

31.2 Create a data model and a layout

31.3 Add a formula column

31.4 Add a field

31.5 Remove a redundant field

31.6 Supress redundant values

31.7 Summary

32 Building a Check Printing Report with Spelled-Out Cash Amounts

32.1 Prerequisites for this example

32.2 Create a report using the Report Wizard

32.3 Create a formula column that returns the spelled-out cash amounts

32.3.1 Create a PL/SQL function

32.3.2 Create a formula column in your data model

32.4 Create a query that will return the items in the order

32.5 Import a check image and arrange fields for printing

32.5.1 Rearrange the layout objects

32.5.2 Import the blank check image

32.5.3 Set up the check printing fields

32.5.4 Rearrange the new fields according to the blank check image

32.5.5 Modify the look and feel of the check

32.6 Create a check stub with payment information and order details

32.6.1 Create a check stub in the Paper Design view

32.6.2 Add order details to the check stub

32.7 Summary

33 Building a Report Using a Pre-Printed Form

33.1 Prerequisites for this example

33.2 Manually create the data model for your report

33.2.1 Create the queries

33.2.2 Modify your data model

33.3 Create the layout for your report

33.3.1 Set up your report layout

33.3.2 Create an address label for your form

33.3.3 Add the order item details to your report

33.3.4 Adjust the layout and add summaries

33.4 Format your report in the Paper Design view

33.5 Add page numbers (optional)

33.6 Summary

34 Building an Invoice Report

34.1 Prerequisites for this example

34.2 Create a new report manually

34.3 Create a data model with a data link

34.4 Create summary and formula columns

34.5 Prepare the layout

34.6 Insert invoice information

34.7 Summary

35 Building a PL/SQL Report

35.1 Prerequisites for this example

35.2 Create a new PL/SQL library

35.3 Create the report definition

35.3.1 Create a query

35.3.2 Create a formula column that calculates bonuses

35.3.3 Create a report-level function that calculates total compensation

35.3.4 Create a second formula column for total compensation

35.4 Create the report layout using the Report Block Wizard

35.5 Add vertical space between records

35.5.1 Create a user parameter

35.5.2 Create a summary column that counts the number of records

35.5.3 Modify the layout

35.5.4 Create a format trigger

35.6 Run your report to paper

35.7 Summary

36 Building a Report that Suppresses Labels When No Detail Records are Retrieved

36.1 Prerequisites for this example

36.2 Create the data model with two linked queries

36.3 Create a formula column and a summary column

36.3.1 Create a formula column in the detail query

36.3.2 Create a summary column in the master query

36.4 Create the layout of the report

36.4.1 Create the initial layout of your report

36.5 Add a format trigger to suppress labels

36.6 Add text to display when no records display

36.7 Summary

37 Building a Ranking Report

37.1 Prerequisites for this example

37.2 Create a data model and tabular layout

37.3 Create ranking logic for top number of customers

37.4 Add a layout object for a parameter

37.5 Create a Parameter Form

37.6 Add a percentage ranking

37.7 Summary

38 Building a Conditional Form Letter Report

38.1 Prerequisites for this example

38.2 Create the data model and layout

38.3 Add additional text

38.4 Add logic for text

38.5 Summary

39 Building a Report with Conditional Highlighting

39.1 Prerequisites for this example

39.2 Create a basic tabular report

39.3 Add conditional formatting to the report

39.4 Examine the conditional format trigger code

39.5 Summary

40 Building a Report with Dynamic Graphics

40.1 Prerequisites for this example

40.1.1 Access to the data source and installing the Pictures table

40.1.2 Graphics for the report

40.2 Create the data model with two linked queries

40.3 Create the layout of the report using the Report Wizard

40.3.1 Create the initial layout of your report using the Report Wizard

40.3.2 Modify the layout of your report

40.4 Run your report to paper

40.5 Summary

A Tool Palette and Toolbar Reference

A.1 Main Toolbar

A.2 Data Model view tool palette

A.3 Paper Layout view tool palette

Glossary

Index