1
Basic Concepts

This chapter introduces the fundamental concepts of the Reports Builder component of Oracle Reports. Each topic in this chapter is also included in the Basic Concepts section of Reports Builder online help (see Section 3.1.1, "Using the online help").

Topics are grouped into the following sections:

1.1 Reports Builder

The topics in this section discuss the features and functionality in Reports Builder.

1.1.1 About Reports Builder

Reports Builder is the report-building component of Oracle Reports Developer (a component of the Oracle Developer Suite), a powerful enterprise reporting tool that enables you to rapidly develop and deploy sophisticated Web and paper reports against any data source (including an Oracle database, JDBC, XML, text files, and Oracle OLAP). Leveraging the latest J2EE technologies such as JSP and XML, you can publish your reports in a variety of formats (including HTML, XML, PDF, delimited text, Postscript, PCL, and RTF) to any destination (including e-mail, Web browser, OracleAS Portal, and file system) in a scalable, efficient manner.

Recognizing the differences between Web publishing and paper publishing, Reports Builder provides the power to develop high quality output for the Web and e-business requirements, as well as high-fidelity printed reports. Reports Builder includes:

user-friendly wizards that guide you through the report design process
pluggable data sources (PDSs), such as JDBC and XML, that provide access to data from any source for your reports
a query builder with a graphical representation of the SQL statement to obtain report data
default report templates and layout styles that can be customized if needed
a live editor that allows you to modify paper report layouts in WYSIWYG mode
the ability to add dynamic report output to an HTML page by embedding custom JSP tags within an HTML document
an integrated graph builder to graphically represent report data
the ability to generate code to customize how reports will run
tools that dynamically generate Web pages based on your data
standard report output formats such as HTML, HTMLCSS, XML, PDF, PCL, PostScript, and ASCII
client-side parameter validation using JavaScript
the ability to execute dynamic SQL statements within PL/SQL procedures
support for Oracle database objects
event-based reporting (report execution based on database events)
seamless integration of Oracle Reports Developer with OracleAS Portal for administering report security and publishing report output to portlets

1.1.2 About this release

Oracle Reports focuses heavily on Web publishing, moving more fully into its role as a universal publishing solution. In prior releases, Reports Builder's Web feature simply displayed paper reports (that is, multiple pages) in HTML or PDF. This moves corporate data onto the Web, but also results in large and somewhat inflexible HTML pages.

One of the major new features of Oracle Reports is the incorporation of JavaServer pages (JSPs). Through the use of JSP technology, Oracle Reports embeds report data directly into Web pages.

For a detailed summary of new features in this release, as well as deprecated, obsolete, and changed functionality and components, see the topic "About this release" in the Welcome section of the Reports Builder online help.

1.2 Reports

The topics in this section discuss basic concepts of reports; for more advanced concepts, see Section 2.1, "Reports".

1.2.1 About reports

A report consists of objects that collectively define the report:

data model objects (queries, groups, columns, links, user parameters)
layout objects (repeating frames, frames, fields, boilerplate, anchors)
parameter form objects (parameters, fields, boilerplate)
PL/SQL objects (program units, triggers)
references to external PL/SQL libraries, if any
code shown in the Web Source view (for JSP-based Web reports)

Using the Property Inspector, you define report properties. The document taxonomy (classification) properties (Title, Author, Subject, and Keywords) assist in cataloguing and searching a report document.

When you first start Reports Builder, you can choose to open an existing report, create a new report using the Report Wizard, or create a new report manually.

1.2.1.1 Creating a new report using the Report Wizard

Using the Report Wizard, you can quickly and easily accomplish the steps to build a report for both Web and paper layouts:

Create a new report definition.
Define the data model (choose the data, data relationships, and calculations you will use to produce the report output).
Specify a layout. You can use a default, customizing it if desired, or create your own. Reports Builder provides the default layout styles described in Section 1.3, "Report Styles".
Then, you can modify your report using the different views of the Report Editor.

1.2.1.2 Creating a new report manually

If you choose to create a new report manually, Reports Builder creates a new default report definition for you. The first window you see is called the Object Navigator. This window displays a comprehensive list of report objects. Initially, it shows all objects that Reports Builder has created for you, as part of the report definition. As you define your report, the Object Navigator provides a central location to access and modify all objects in your report(s), including attached libraries and program units. You can change the Object Navigator view to list object hierarchically or by object type.

1.2.2 About Web reports

With a focus on Web publishing, Oracle Reports has moved more fully into its role as a universal publishing solution. In prior releases, Reports Builder's Web feature simply displayed paper reports (that is, multiple pages) in HTML or PDF. This moves corporate data onto the Web, but also results in large and somewhat inflexible HTML pages.

While all the prior Web report functionality remains (that is, hyperlinks, bookmarks, hyperlink destinations, before and after escapes, and so on), Oracle Reports uses JavaServer Pages (JSPs) as the underlying technology to enable you to enhance Web pages with information retrieved using Reports Builder. This introduces the Web Source view of a report, and allows you to have both JSP-based and paper-based definitions in a single report. In other words, you can either publish your paper reports to the Web or take more advantage of Web features using the Web Source view. For example, you can create a report that has a paper PDF version and a JSP-based Web version; what you choose depends on your needs and whether you are able to produce the desired results more easily in the Web Source view or in the Paper Design view.

Oracle Reports also includes servlet technology. Servlets provide a Java-based alternative to CGI programs. Servlets provide a platform-independent method for building Web-based applications, without the performance limitations of CGI programs.

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

When you create a report using the Report Wizard, you can select on the first page of the wizard whether the report is both Web and paper, Web only, or paper only. The data model is added to both Web and paper reports, providing the data to be used to build the report. The layout for both Web and paper reports defaults in the Paper Design view. You can view the source for the Web report in the Web Source view.
Open an existing HTML document (Web page) and imbed a report in your Web page using the Report Block Wizard. This provides tremendous flexibility in creating reports that meet the demands of completely integrating multiple sources of information within a single Web page. See and Section 3.6.4, "Adding a report block to a Web page".
Display the Web Source view (see Section 3.6.2, "Viewing the source code for a Web report") and manually insert the Oracle Reports custom JSP tags. See the topic "Custom JSP tags for Oracle Reports" in the Reference section of the Reports Builder online help.
Insert an existing report into an existing Web page, by displaying the Web Source view for both the report and the Web page, then copying and pasting the report block into the desired position in the Web page.
Use the functionality available since Oracle Reports 6i to add HTML and hyperlinks to an existing paper-based report. See Section 3.6.7, "Adding Web links to paper-based reports".

To preview your report output in a Web browser, use Program > Run Web Layout in the Paper Design view to run the Web Source. This enables you to immediately see the effect of your changes on the output.

Note:

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

1.3 Report Styles

The topics in this section describe the built-in report styles in Reports Builder.

1.3.1 About tabular reports

A tabular report is the most basic type of report. Each column corresponds to a column selected from the database.

1.3.2 About group above reports

A group above report contains multiple groups in its data model. It is a "master/detail" report, where there may be a lot of information in the master group. For every master group, the related values of the detail group(s) are fetched from the database and are displayed below the master information.

1.3.3 About group left reports

A group left report also contains multiple groups in its data model, dividing the rows of a table based on a common value in one of the columns. Use this type of report to restrict a column from repeating the same value several times while values of related columns change. The data model for group above and group left reports is the same, but the layouts differ; group above reports display the master information at the top while group left reports display break columns to the side.

1.3.4 About form-like reports

A form-like report displays one record per page, displaying field values to the right of field labels.

1.3.5 About form letter reports

A form letter report contains database values embedded in boilerplate text (any text that you enter or import into a Report Editor.)

1.3.6 About mailing label reports

A mailing label report prints mailing labels in multiple columns on each page. Using the Report Wizard, you can specify the format for your mailing labels.

1.3.7 About matrix reports

A matrix (cross-product) report is a cross-tabulation of four groups of data:

One group of data is displayed across the page.
One group of data is displayed down the page.
One group of data is the cross-product, which determines all possible locations where the across and down data relate and places a cell in those locations.
One group of data is displayed as the "filler" of the cells.

Figure 1-1 Example matrix report

Text description of matrix_fin.gif follows.

Text description of the illustration matrix_fin.gif

Thus, to create a matrix report, you need at least four groups in the data model: one group must be a cross-product group, two of the groups must be within the cross-product group to furnish the "labels," and at least one group must provide the information to fill the cells. The groups can belong to a single query or to multiple queries.

A distinguishing feature of matrix reports is that the number of columns is not known until the data is fetched from the database.

With Reports Builder, you can create many different matrix reports. The four general types of matrix reports are simple matrix, nested matrix, multi-query matrix with break, and matrix break, but you are not confined to these designs.

1.3.7.1 Matrix data model

In building your matrix data model, you should consider the following:

number of queries
group structure
summary settings

1.3.7.1.1 Number of queries

Although matrix reports always require at least four groups, they can be built with any number of queries. If you build a matrix report with only one query (usually the most efficient structure), you must create at least three groups in addition to the one created by default. If you build a matrix report with multiple (three or more) queries, you still need to create at least one additional group. The figure below illustrates the two types of query structures that you can use for your matrix data model.

Figure 1-2 Matrix data models

Text description of a_mtx_rpts1.gif follows.

Text description of the illustration a_mtx_rpts1.gif

One advantage to a one-query data model is that the resulting report is generally more efficient than a report based on a multi-query data model.

Multi-query matrix. You may consider using a multi-query data model, as it often has simple queries and can be easier to maintain than the equivalent one-query data model. In addition, a multi-query data model is required for some types of matrices (e.g., some nested matrix reports).

1.3.7.1.2 Group structure

Matrix reports are built with four or more groups:

Two or more dimension groups. Dimension groups are contained within the cross product group. In the layout, the information in at least one group goes across the page, and the information in at least one group goes down the page, forming a grid. The information in these groups is sometimes referred to as "matrix labels", as they appear to provide column and row labels for the matrix.
One or more cross product groups. The cross product group represents all possible combinations of the values of the dimension groups. In the layout, the cross product group is represented by the intersection of the repeating frames for the across and down dimension groups. When the report is run, it expands, and each instance of data intersection becomes a separate cell. This concept is sometimes best understood graphically, as in the figure below. The rectangles are cells, and show where each department/job combination is valid.

Figure 1-3 Conceptual matrix

Text description of a_mtx_rpts2.gif follows.

Text description of the illustration a_mtx_rpts2.gif

One cell, or "filler" group. The cell group contains the actual information that is represented by the cells of the cross product. For each intersection of the values of the dimension groups (cell), the cell group contains zero, one, or multiple values. When the report is run, these values appear in the appropriate cells.

The following figure shows graphical representations of how groups are related for both single and multi-query data models.

Figure 1-4 Matrix data relationships

Text description of a_mtx_rpts3.gif follows.

Text description of the illustration a_mtx_rpts3.gif

Notice that for each data model the cross product group is represented by the large rectangle, while the dimension groups are the smaller rectangles enclosed by it, and the cell group is shown outside of the cross product group.

1.3.7.1.3 Summary settings

Creating a summary for a matrix requires more information than creating a summary for other kinds of reports. When you create summary columns for your matrix, you need to indicate the following:

The frequency of the summary. The frequency specifies the dimension groups for which to compute the summary.
The order in which to compute the summary. The order specifies how to traverse the matrix in calculating the summary (top to bottom or left to right).

In Reports Builder, you specify this information by setting the Product Order property for your summary. All summaries that are owned by the cross product group require that a Product Order be specified. Suppose that you have a matrix report that looks something like the one below.

Figure 1-5 Sample nested matrix report

Text description of a_mtx_rpts4.gif follows.

Text description of the illustration a_mtx_rpts4.gif

This is a nested matrix report. Assume that group G_YEAR contains the YEAR column, G_DEPT contains the DEPTNO column, G_JOB contains the JOB column, and G_CROSS is the cross product group.

To create the summary of salaries by job that appears at the bottom of the matrix for each job, you create a summary column in G_CROSS with the following property settings:

Figure 1-6 Matrix summary settings

Text description of a_mtx_rpts5.gif follows.

Text description of the illustration a_mtx_rpts5.gif

These settings specify that the summary should be calculated for each job in the G_JOB group and the summary should be reset to zero for each job in the G_JOB group.

To create the summary of salaries by year which appears at the right of the matrix directly underneath the boilerplate lines, you create a summary column in G_CROSS with the following property settings:

Figure 1-7 Matrix summary settings

Text description of a_mtx_rpts6.gif follows.

Text description of the illustration a_mtx_rpts6.gif

These settings specify that the summary should be calculated for each year in the G_YEAR group and the summary should be reset to zero for each year in the G_YEAR group. To create the summary of salaries by year and department that appears at the right of the matrix for each department row of the matrix that contains a value, you create a summary column in G_CROSS with the following property settings:

Figure 1-8 Matrix summary settings

Text description of a_mtx_rpts7.gif follows.

Text description of the illustration a_mtx_rpts7.gif

These settings specify that the summary should be calculated for each record of G_DEPT within each record of G_YEAR. G_DEPT comes after G_YEAR in the Product Order because, in this report, it changes more frequently than G_YEAR. If G_YEAR changed more frequently (i.e., years were listed for each department), it would make more sense to have a Product Order of G_DEPT, G_YEAR.

This summary is reset to zero for each record in the G_DEPT group. When you have multiple groups listed in Product Order and you want a non-running summary, the reset group should be the same as the last group in Product Order. When you have multiple groups listed in the Product Order and you want a running summary, the reset group should be a group other than the last one in the Product Order.

1.3.7.2 Matrix layout

The figure below shows the objects that make up a simple, two-dimensional matrix. All of these objects are necessary for a matrix, except for the boilerplate field labels.

Figure 1-9 Matrix Layout Model view

Text description of a_mtx_rpts8.gif follows.

Text description of the illustration a_mtx_rpts8.gif

A matrix layout model must consist of the following layout objects:

at least three repeating frames: one with the Print Direction property set to Down, one with the Print Direction property set to Across, and one for the cell in the matrix object
several group, header, and footer (if summaries are included) frames
a matrix object created for the cross product group, inside of which are the cells of the matrix (e.g., the rectangle in the figure above with R_SUMSAL and F_SUMSAL inside it)
boilerplate for each column and row of values, as well as for summaries (e.g., Job and Deptno)

Note:
Displaying the boilerplate is optional, but Reports Builder will generate it by default.

1.3.7.2.1 Matrix object

The matrix object defines the intersection of at least two repeating frames. The repeating frames are the dimensions of the matrix and the matrix object contains the repeating frame that will hold the "filler" or values of the cell group. You need one matrix object for each pair of intersecting repeating frames in the layout. One of the repeating frames must have the Print Direction property set to Down and the other must have the Print Direction property set to Across in order to form a matrix.

For more details, see Section 2.3.7, "About matrix objects".

Usage notes

You can improve the performance of matrix reports with:

fewer queries
WHERE clauses

Note:
Cross-product groups always cause "fetching ahead." The reason for this is that to cross-tabulate the data in a cross-product group, Reports Builder must first fetch all of the data.

1.4 Wizards

Reports Builder includes wizards to help you quickly and easily define a report and add objects to it. The topics in this section describe each Reports Builder wizard.

1.4.1 About the Report Wizard

The Report Wizard helps you to quickly and easily define a single-query report for both Web and paper layouts. Reports Builder uses what you specify on each page of the wizard to create a data model and layout for your report. After the wizard has created the report, you can modify it using the Report Editor views. See Section 1.6.1, "About the Report Editor".

Re-entrancy

To re-enter the Report Wizard for an existing report:

Click the report, then choose Tools > Report Wizard.

Using the Report Wizard

For help on the fields on any tab page of the wizard, click Help at the bottom of the tab page.

1.4.2 About the Report Block Wizard

The Report Block Wizard is a version of the Report Wizard. You can open an existing HTML document (Web page) and imbed a report in your Web page using the Report Block Wizard to specify the layout of the data.

Access

To display the Report Block Wizard:

In the Web Source view, choose Insert > Report Block.

Using the Report Block Wizard

For help on the fields on any tab page of the wizard, click Help at the bottom of the tab page.

1.4.3 About the Data Wizard

The Data Wizard helps you to quickly and easily define a query, break groups, and totals for a multi-query report. After the wizard has created the data model, you can:

modify the data model in the Data Model view.
use the Data Wizard to create additional queries.
use the Report Wizard to default the layout.
modify the layout in the Paper Layout view, including reordering or adding new layout sections.

Access

To display the Data Wizard:

In the Data Model view, choose Insert > Query.

Note:
The Data Wizard does not support creating links between queries. To define parent/child query relationships, you can create a data link manually.

Re-entrancy

To re-enter the Data Wizard for an existing query, do either of the following:

Right-click the query, and choose Data Wizard.
Click the query, then choose Edit > Settings.

Using the Data Wizard

For help on the fields on any tab page of the wizard, click Help at the bottom of the tab page.

1.4.4 About the Graph Wizard

The Graph Wizard produces graphs that are automatically translated into JSP tags to enable you to add graphics to Web reports. Reports Builder uses what you specify on each page of the wizard to create the desired graph.

The Graph Wizard replaces Oracle6i Graphics, or Graphics Builder. There is no separate graphics tool; all of the graphing options and controls are now available in Reports Builder through the Graph Wizard. Using the Graph Wizard, you can generate more complex graphs with a larger variety of graph types. For backward compatibility, Oracle6i Graphics charts are supported in Oracle Reports 10g.

Primary Graph Types

For most graphing needs, the following primary graph types will provide the best means to represent data:

bar graph
line graph
area graph
pie graph
combination graph

Secondary Graph Types

These are special usage or less common graphs that are associated with particular data types or ways to display unique cases of data:

scatter graph
bubble graph
radar graph
polar graph
pareto graph
stock graph
3-D graph

Reports built with previous versions of Oracle Reports containing Oracle6i Graphics charts will continue to run if the Oracle6i Graphics runtime is installed on the same machine in a separate ORACLE_HOME. For more information, see "Displaying Oracle6i Graphics charts in Oracle Reports 10g" in the Migration Issues section of the Reports Builder online help.

Access

To display the Graph Wizard for paper reports:

In the Paper Layout view, choose Insert > Graph.

To display the Graph Wizard for JSP-based Web reports:

In the Web Source view, choose Insert > Graph.

Re-entrancy

To re-enter the Graph Wizard for an existing graph in paper reports:

In the Paper Layout view, click the graph.
To display the Graph Wizard in re-entrant mode, do either of the following:
- Right-click and choose Graph Wizard.
- Choose Edit > Settings.

To re-enter the Graph Wizard for an existing graph in JSP-based Web reports:

In the Web Source view, place your cursor anywhere between the <rw:graph> and </rw:graph> tags, then choose Edit > Settings.

Using the Graph Wizard

For help on the fields on any tab page of the wizard, click Help at the bottom of the tab page.

1.5 The Object Navigator

The topics in this section discuss the Object Navigator in Reports Builder.

1.5.1 About the Object Navigator

The Object Navigator provides a hierarchical display of all objects in a report or template, including attached libraries and program units. Using the Object Navigator, you can:

create reports, parameters, PL/SQL program units, and attached libraries.
select and work with reports, queries, PL/SQL program units and libraries, data model objects, layout objects, and parameter form objects.
display Report Editor views.
expand and collapse nodes.
search for objects.
view objects by hierarchy or type.
display properties.
customize the Object Navigator.
drag and drop PL/SQL program units.

1.5.2 About Object Navigator views

The Object Navigator enables you to view objects by ownership hierarchy or by object type.

View > Change View > Ownership View displays objects in a parent-child hierarchy. For example, a query's columns would appear underneath the query and a field would appear underneath the repeating frame that encloses it. This view can be a useful debugging tool because it enables you to easily see the relationships between objects.
View > Change View > Object Type View displays objects by their type. For example, all queries would appear underneath the Queries heading and all database columns underneath the Database Columns heading. This view can be useful when you want to quickly find objects in the Object Navigator.

1.6 The Report Editor

The topics in this section discuss the Report Editor and its different views of a report.

1.6.1 About the Report Editor

The Report Editor is a work area in which you can manipulate the objects in your report directly or by changing properties in the Property Inspector. In the Report Editor window, you can navigate between different views of your report: the Data Model view, Paper Layout view, Paper Design view, Paper Parameter Form view, and Web Source view.

1.6.2 About the Data Model view

The Report Editor's Data Model view is a work area in which you create, define, and modify data model objects (queries, groups, columns, and data links) to be used in your report. In this view, objects and their property settings are represented symbolically to highlight their types and relationships. To create the query objects for your data model, you can use the Report Wizard, Data Wizard, or the Query tools in the tool palette.

Reports Builder uses the data model to determine what data to retrieve for the report. The data retrieved from the database may or may not appear in the report output.

Access

To display the Data Model view:

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

1.6.3 About the Paper Layout view

The Report Editor's Paper Layout view is a work area in which you can modify the format of your paper report. The default format for your report is defined by the information you specify in the Report Wizard. You can modify the format by working with layout objects, such as frames, repeating frames, fields, boilerplate, anchors, and graph objects. In this view, objects and their property settings are represented symbolically to highlight their types and relationships.

The Paper Layout view is similar to the Paper Design view, in that it is a work area in which you modify the format of your report. However, the Paper Design view allows you to preview your report and manipulate the actual, or live, data at the same time. In the Paper Design view, you can customize reports interactively, meaning that you can see the results immediately as you make each change. The Paper Design view is displayed only after you run a report.

Access

To display the Paper Layout view:

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

1.6.4 About the Paper Design view

The Report Editor's Paper Design view is a work area in which you can preview your paper report and manipulate the actual, or live, data at the same time. In the Paper Design view, you can customize reports interactively, meaning that you can see the results immediately as you make each change.

To edit your report, such as changing column size, the Paper Design view must be in Flex mode.

Access

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 in the toolbar 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

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

Usage notes

To speed the execution of your report, avoid "fetching ahead" when sending report output to the Paper Design view. The following items can result in fetching ahead when referenced before the data on which they rely:

total number of pages/panels
grand totals
break columns that are formulas
break columns that have Value if Null specified

Matrix (cross-product) groups also cause fetching ahead. In order to cross-tabulate the data, Reports Builder must first fetch all of the data.

It should be noted that while these items slow down the Paper Design view, they do not affect performance when writing to a file or some other destination.

Note:

A column can cause fetching ahead even if it is not displayed. For example, a grand total may not appear in the report output, but since it is in the report, fetching ahead may still occur when Reports Builder calculates it.

1.6.5 About the Paper Parameter Form view

The Report Editor's Paper Parameter Form view is a work area in which you define the format of the report's Runtime Parameter Form. To do this, you define and modify parameter form objects (fields and boilerplate). You can select pre-defined system parameters for your report using the Parameter Form Builder, or you can create your own.

When you run a report, Reports Builder uses the Paper Parameter Form view as a template for the Runtime Parameter Form. Fields and boilerplate appear in the Runtime Parameter Form exactly as they appear in the Paper Parameter Form view. If you do not define a Runtime Parameter Form in the Paper Parameter Form view, Reports Builder displays a default Runtime Parameter Form for you at runtime.

Note:

At runtime, the Runtime Parameter Form displays only when running the paper report layout. For JSP-based Web reports, the Runtime Parameter Form displays when you run your report within Reports Builder for debugging purposes, but will not display at runtime. For more information, see Section 1.9.4, "About Parameter Forms for Web reports".

Access

To display the Paper Parameter Form view:

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

1.6.6 About the Web Source view

The Report Editor's Web Source view displays the source code for your Web report, including HTML tags and JSP tags. It is a Web page where you can view and add dynamic report blocks using the Report Block Wizard, and graphs using the Graph Wizard. Experienced Web developers can edit the Web source directly in this view using the Oracle Reports custom JSP tags.

Access

To display the Web Source view, do any of the following:

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

1.6.7 About the tool palette and toolbar

The tool palette and toolbar contain tools used to manually create or manipulate objects in the Report Editor views (excluding the Web Source view). Each tool appears as an icon. Some tools, such as the Select tool, are common to tool palettes in all Report Editor views. Other tools are specific to the views in which they appear. In Reports Builder, run your cursor over a tool to display the hint text that identifies the tool.

Tool palette

The tool palette is positioned along the left side of the Report Editor views. Click a tool to activate it for a single operation, or double-click a tool to "lock" it for multiple operations. You can hide the tool palette by choosing View > Tool Palette to deselect it.

Toolbar

The toolbar is positioned along the top of the Report Editor views. To use the tools in the toolbar, click the desired tool to perform the action. All toolbar tools also have menu equivalents. If the tool is designed to perform an action on a group of objects, it appears grayed out until one or more objects are selected. Then clicking the tool performs the action on the selected object(s).

1.7 Data Model Objects

The topics in this section discuss basic concepts of data model objects; for more advanced concepts, see Section 2.3, "Data Model Objects".

1.7.1 About queries

Queries provide the data for your report. You create a query using the Report Wizard, Data Wizard, or manually using the query tools in the Data Model tool palette. Queries can select data from any data source (Oracle, XML, JDBC, Text, Oracle Express, or your own data source that you can access via the pluggable data source (PDS) API). You can also use ref cursors to create queries.

Single-query reports

Reports built using one query are the simplest reports. The most popular formats for single-query reports are tabular, mailing label, form letter, and break (either group above or group left). In one report, you can display one query's data any number of times, even in different formats. You can query data without including it in the report output. This is useful for establishing relationships between multiple queries, performing calculations, and so on.

The figure below shows the data of one query formatted various ways.

Figure 1-10 Formatting the same data in different ways

Text description of a_query2.gif follows.

Text description of the illustration a_query2.gif

Notice that the last report style (group left break) seems to use an entirely different query for its data than the first three. However, the data it displays was hidden in the other reports, and vice versa. With Reports Builder, you can query data without including it in the report output. This is useful for establishing relationships between multiple queries, performing calculations, and so on.

Multi-query reports

A report may contain any number of queries. Multi-query reports are useful when you want to:

produce multi-part unrelated query reports.
produce multi-part related query reports.
make your queries easier to maintain (often a report with a complex query containing embedded SELECT statements and joins can also be created with multiple simple queries that are linked. The latter are often easier for others to understand and maintain.)
display the same data twice in a report with different sorting criteria.

If you create a report with multiple queries, you can either leave the queries unrelated, or establish a relationship between them using a data link.

Multi-part unrelated query reports

If you do not link the queries, you'll produce a multi-part unrelated query report (commonly called a master/master report). These types of reports display lists of unrelated data. For example, in the report below, one query selects products and another selects customers. Notice that there is no relationship between the products and customers.

Figure 1-11 Unrelated queries

Text description of a_query1.gif follows.

Text description of the illustration a_query1.gif

Multi-part related query reports

In many reports, the data fetched for one part of the report is determined by the data fetched for another part. This is termed a "master/detail," or "parent/child," relationship, and is defined with a data link between two queries. When you run a master/detail report, each row of the master (or parent) query will cause the detail (or child) query to be executed, retrieving only matching rows.

Usage notes

External queries are no longer supported.
Reduce the number of queries in your report as much as possible. In general, the fewer queries you have, the faster your report will run. While multi-query data models are often easier to understand, single-query data models tend to execute more quickly.
Processing done in the query is done on the server.
To restrict the number of pages that a certain user can produce, insert a record into the PRODUCT_PROFILE table for that user.
The only times you should use multi-query data models are:
- when you're fetching many large columns from the parent and only a few small columns from the child.
- when you're trying to do things that the query type, such as a SQL query, does not support directly (e.g., multi-way outer join).
- when you have complex views (e.g., distributed queries or GROUP BY queries).
- when you need but do not have or want to use a view.

Rationale

For a single-query report, Reports Builder opens only one cursor to fetch all of the master and detail records. For a two-query report, Reports Builder opens two cursors--one for each query--after appending the detail query's link to the WHERE clause of the detail query. Therefore, for each master record fetched in the master query, Reports Builder must rebind, execute, and fetch data from the detail query.

1.7.2 About groups

Groups are created to organize the columns in your report. Groups can do two things: separate a query's data into sets, and filter a query's data.

When you create a query, Reports Builder automatically creates a group that contains the columns selected by the query. You create additional groups to produce break levels in the report, either manually in the Data Model view or by using the Report Wizard to create a group above or group left report.

Create groups when you want to treat some columns differently than others. For example, you create groups to:

produce subtotals (i.e., totals at a more granular level).
create breaks or cross products in your report output.

With the exception of cross-product groups, all user-created groups are called break groups.

Break groups

You create break groups to produce subtotals, print columns in a different direction, create breaks, and so on. A break group suppresses duplicate values in sequential records. For example, Reports Builder can select the department number for each record; however, the duplicate department numbers are not printed.

Cross-product groups

You create cross-product groups to perform mathematical cross products, which are generally used to create matrix reports.

Group filters

Filters enable you to conditionally remove records selected by your queries. Groups can have two types of filters:

Reports Builder packaged filters:
- First, to display only the first n records for the group (e.g., the first 5 records)
- Last, to display only the last n records for the group
User-created filters, using PL/SQL.

1.7.3 About database columns

A database column represents a column that is selected by the query, containing the data values for a report. For each column that you select in your query, Reports Builder automatically creates a column in the data model of your report. If you want to perform summaries and computations on database column values, you can create new columns manually in the Data Model view (for summary and formula columns) or by using the Report Wizard (for summary columns). You can also reassign one or more columns to a group or groups you've created.

In addition to the traditional column types (e.g., date, number, character), Reports Builder also supports graphic columns, which are columns whose values can be:

graphics stored directly in the database. Such a column is usually of data type LONG or LONG RAW and contains a graphic. In this case, specify the graphic's format in the column's properties.
file names. The values of such a column are pointers to files stored in the operating system. In this case, specify in the column's properties that its values are file contents (Read from File) and the format of the files. Many graphics formats are supported, including BMP, CALS, CGM, GIF, JFIF, PCD, PCX, PICT, RAS, TIFF, etc.)

1.7.4 About data links

A data link (or parent-child relationship) relates the results of multiple queries. A data link can establish these relationships:

between one query's column and another query's column.
between one query's group and another query's group (this is useful when you want the child query to know about its parent's data).

A data link causes the child query to be executed once for each instance of its parent group. The child query is executed with the values of the primary key used by the parent.

When a report with a data link is executed, the data link is converted into a SQL clause (as specified in the link's Property Inspector) and appended to the child query if Reports Builder is able to parse the query. Important note: If the query cannot be parsed, the query is not modified (for example, Reports Builder has no way to modify how the data is fetched when the query is defined for a pluggable data source).

Although links are commonly equijoins (e.g., WHERE DEPTNO =DEPTNO), you can create links with various SQL clauses (i.e., WHERE, HAVING, or START WITH) and conditions. If your database has database constraints, you can create a data link that derives its SQL clause and condition from the constraints. You can view the SELECT statements for the individual parent and child queries in Reports Builder, but can not view the SELECT statement that includes the clause created by the data link you define.

Example

For the report below, the following link was defined:

Figure 1-12 Sample data link

Text description of a_data_links1.gif follows.

Text description of the illustration a_data_links1.gif

The default group of the master query (containing the columns "Order ID" and "Customer") is the parent group; the detail query (the query to which "Item", "Product" and "Amount" belong) is the child query.

Figure 1-13 Sample data link output

Text description of a_data_links2.gif follows.

Text description of the illustration a_data_links2.gif

Note:

Reports Builder does not support data links between queries that contain column objects. If you attempt to create such a link, a message dialog box displays, which enables you to choose whether to create a group-to-group query instead (using the parent groups), or to cancel the operation. If you want to create a link between any type of column and a column object, you can manually modify the SQL statement in the child query, adding in the appropriate WHERE clause based on columns in the master query.

1.7.5 About Query Builder

Query Builder is an easy-to-use data access tool designed for analysts, managers, and other business professionals. It provides a logical and intuitive means to access information from your organization's databases for analysis and reporting.

Query Builder is designed for professionals who do not have a computer programming or database background. Because of its powerful query features and its support of Structured Query Language (SQL) statements, experienced database users and programmers will find that Query Builder serves many of their needs as well.

In Query Builder's graphical Query window, you can specify a request for data from your organization's database(s). The request for data is called a query.

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.

1.8 Layout Objects

The topics in this section discuss basic concepts of layout objects; for more advanced concepts, see Section 2.4, "Layout Objects".

1.8.1 About frames

Frames surround other objects and protect them from being overwritten or pushed by other objects. For example, a frame might be used to surround all objects owned by a group, to surround column headings, or to surround summaries.

When you default the layout for a report, Reports Builder creates frames around report objects as needed; you can also create a frame manually in the Paper Layout view. See Section 1.6.3, "About the Paper Layout view".

Create frames when you want to:

group together objects to ensure they maintain their relative positions during printing.
delineate sections in your report.

Example: You want the top of each page to have a tabular format, but the bottom of each page to have a matrix format.
protect other objects from being overwritten.

Example: A summary is centered under two repeating frames. Defaulting rules state that the summary must remain at a fixed distance from only the first object that can overwrite it; therefore, the summary is in danger of being overwritten by the second repeating frame. Enclosing both repeating frames with a frame will force the summary to maintain a fixed distance from both of them, and it will not be overwritten.
prevent an object from printing until other objects finish printing.

Example: A summary is centered under two repeating frames. According to defaulting rules, the summary will print as soon as the first repeating frame finishes printing. Create a frame around the two repeating frames to ensure the summary prints after both repeating frames have finished printing.

1.8.2 About repeating frames

Repeating frames surround all of the fields that are created for a group's columns. The repeating frame prints (is fired) once for each record of the group.

When you default the layout for a report, Reports Builder generates one repeating frame for each group in the data model, and places one field inside it for each of the group's columns. Repeating frames can enclose any layout object, including other repeating frames. Nested repeating frames are typically used to produce master/detail and break reports. For each record of the outer repeating frame, Reports Builder will format all related records of the enclosed repeating frame.

You can also create a repeating frame manually in the Paper Layout view. See Section 1.6.3, "About the Paper Layout view".

1.8.3 About frame and repeating frame sizing

For each object or record, a frame or repeating frame's size can be expandable, contractible, variable, or fixed (specified by the Horizontal Elasticity and Vertical Elasticity properties). For example, you can set the properties to specify that it should be fixed in size horizontally, but expand vertically if a record requires more space.

In the figure below, notice that there are four records for department 20, but only one record each for departments 10 and 30. The repeating frame has expanded vertically to accommodate department 20's additional records.

Figure 1-14 Repeating frame sizing

Text description of a_rframes_sizing.gif follows.

Text description of the illustration a_rframes_sizing.gif

Frames or repeating frames that contract horizontally or vertically reduce in size when an object or record requires less space than the frame or repeating frame's initial size. A frame or repeating frame may also be variable in size: it expands or contracts based on the size of the value it displays. Also, if a frame or repeating frame is fixed in size and an object or record's data requires a larger field than it can contain, the remaining data will be pushed onto the following page(s) in the same x- and y-coordinates.

1.8.4 About fields

Fields are placeholders for parameters, columns, and such values as the page number, current date, etc. If a parameter or column does not have an associated field, its values will not appear in the report output. A field is owned by the object surrounding it, which is the first enclosing object (either a frame or repeating frame).

When you default the layout for a report, Reports Builder generates one field for each column, and places each field inside of a repeating frame. You can also create a field manually in the Paper Design view, Paper Layout view or Paper Parameter Form view.

1.8.5 About boilerplate objects

A boilerplate object is any text, lines, or graphics that appear in a report every time it is run.

Reports Builder creates one boilerplate object for each label selected in the Report Wizard (it is named B_columnname). For example, if a column is named ENAME, a boilerplate object containing "Ename" is generated for the column. For some report types, Reports Builder also generates lines under the labels.

You can also create a boilerplate objects manually in the Paper Design view, Paper Layout view, or Paper Parameter Form view using any of the following tools in the tool palette:

Arc tool
Ellipse tool
File Link
Freehand tool
Line tool
Polygon tool
Polyline tool
Rectangle tool
Rounded Rectangle tool
Text tool

See Section 1.6.4, "About the Paper Design view", Section 1.6.3, "About the Paper Layout view", and Section 1.6.5, "About the Paper Parameter Form view".

Boilerplate from files

If you have text, graphics, or HTML in a file that you would like to display as boilerplate in your report, you can link to the file, as described in the following procedures:

Linking to a file means that the contents of the file are pulled into the boilerplate object each time the report is run. In this way, you can ensure that your output includes the most recent changes to the file.

You can also link a boilerplate image object to a URL where the image is located. See Section 3.9.8.10, "Linking an image object to a URL".

1.9 Parameter Form Objects

The topics in this section discuss basic concepts of Parameter Form objects; for more advanced concepts, see Section 2.5, "Parameter Form Objects".

1.9.1 About parameters

A parameter is a variable whose value can be set at runtime (e.g., from the Runtime Parameter Form or the command line). Parameters are especially useful for modifying SELECT statements and setting PL/SQL variables at runtime.

Reports Builder automatically creates a set of system parameters at runtime, but you can create your own as well. You can create parameters to replace either single literal values or entire expressions in any part of a query. You can reference parameters elsewhere in the report, such as in PL/SQL constructs providing conditional logic for the report.

Note:

While you can delete or rename a user parameter, you cannot delete or rename a system parameter.

Parameter values can be specified in these ways:

accepting the default parameter values (default values are set in the Parameter properties, and you can control whether the values are displayed at runtime on the Runtime Parameter Form)
typing the parameter value(s) as arguments on the command line (where applicable)
choosing from a list or entering the parameter value(s) in the Runtime Parameter Form

System parameters

Reports Builder is shipped with the following parameters. You can change their default values as described above.

Table 1-1 System parameter descriptions

System Parameter	Description
COPIES	Is the number of report copies that should be made when the report is printed.
CURRENCY	Is the symbol for the currency indicator (e.g., "$"). This system parameter is deprecated, and will be obsolete in a subsequent release of Reports Builder.
DECIMAL	Is the symbol for the decimal indicator (e.g., "."). This system parameter is deprecated, and will be obsolete in a subsequent release of Reports Builder.
DESFORMAT	Is either: the output format for the report (e.g., PDF, HTML, HTMLCSS, RTF, XML, or DELIMITED for bitmapped reports). the printer definition to use when formatting the report when DESTYPE=FILE and DESNAME=filename. If MODE=BITMAP, this is the name of the printer. If MODE=CHARACTER, this is the character mode printer definition file (.prt file).
DESNAME	Is the name of the output device (e.g., the file name, printer's name, mail userid).
DESTYPE	Is the type of device that will receive the report output (e.g., screen, file, printer, mail, sysout, cache, localfile, or preview (to format the report using screen fonts and size the fonts using the printer font metrics)).
MODE	Is whether the report should run in character mode or bitmap.
ORIENTATION	Is the print direction for the report (landscape, portrait, default).
PRINTJOB	Is whether the Print Job dialog box should appear before the report is run.
THOUSANDS	Is the symbol for the thousand's indicator (e.g., ","). This system parameter is deprecated, and will be obsolete in a subsequent release of Reports Builder.

User parameters

You can create a user parameter in the following ways:

create a parameter in the Object Navigator
use a bind parameter reference in a query, which causes Reports Builder to automatically create the parameter the first time it is referenced (see Section 2.3.4.1, "About bind references")

1.9.2 About Parameter Form fields

Fields in the Paper Parameter Form view (Section 1.6.5, "About the Paper Parameter Form view") act as placeholders for parameters. They define the formatting attributes for the parameters displayed in the Runtime Parameter Form. By default, one field is created for each parameter.

1.9.3 About Parameter Form boilerplate

Boilerplate in the Paper Parameter Form view refers to text and graphics that appear in the Runtime Parameter Form each time it is run; for example, a label denoting a particular parameter is boilerplate text created by Reports Builder. Lines or boxes that you create in the layout are also considered boilerplate, as well as any added text.

Boilerplate enables you to customize the Runtime Parameter Form. By default, a boilerplate label is produced for each field that appears on the Runtime Parameter Form.

1.9.4 About Parameter Forms for Web reports

You can design a Parameter Form for both Web and paper reports. However, at runtime, the Runtime Parameter Form displays only when running the paper report layout. For JSP-based Web reports, the Runtime Parameter Form displays when you run your report within Reports Builder for debugging purposes, but will not display at runtime.

Because you now have the flexibility to display your reports on any Web page, the report may be just one object on a Web page containing many other portlets and objects, and parameters for the report may be retrieved from sources other than the Parameter Form. For example, parameters might be provided by the Web page to all portlets on the page. Therefore, it does not make sense to display a Runtime Parameter Form for JSP-based Web reports before the report is formatted.

In the absence of the Runtime Parameter Form, you will need to use an alternate method to provide required parameters to a JSP-based Web report designed with a Parameter Form. For example:

When you design the report, set all parameters to a default value.
If you run your report using a URL, provide the parameters via the URL.
Create an HTML form that your report calls to provide parameter values, either as static values, or as a list of values using the Oracle Reports custom JSP tags.
If the report displays as a portlet in a Web page, you can pass the page level parameters to the report.
Use the JSP tag <rw:reports id="myReport" parameters="yourParameterList">, where yourParameterList can be a Java variable. For example:
```
<% String myParameterList = "userid=scott/tiger&p_deptno+10"; %>
<rw:report id="myReport" parameters="<%= myParameterList %>">
```

For more information on creating a Parameter Form for a JSP-based Web report, see Chapter 39, "Building a Simple Parameter Form for a JSP-based Web Report".

Note:

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 Property Inspector: set the Before Form Value property and After Form Value property for the report
PL/SQL: set format triggers using the SRW.SET_BEFORE_FORM_HTML and SRW.SET_AFTER_FORM_HTML procedures.

1.10 The Property Inspector

The topics in this section discuss the Property Inspector in Reports Builder.

1.10.1 About the Property Inspector

The Property Inspector is a windows that enables you to access the properties of the currently selected object(s) in the Object Navigator, Report Editor, and Template Editor. Every Reports Builder object (query, group, frame, parameter, etc.) has associated properties that can be viewed using the Property Inspector.

See Section 1.5.1, "About the Object Navigator", Section 1.6.1, "About the Report Editor", and Section 2.7.5, "About the Template Editor".

To get help on any property, click the property in the Property Inspector and press F1.

1.10.1.1 About making multiple selections in the Property Inspector

You can select multiple objects at the same time by using Shift+Click or Ctrl+Click in the navigators or the editors. When two or more objects are selected, a list of object names is displayed at the top of the Property Inspector.

The Intersection/Union button on the Property Inspector toolbar determines which properties are displayed in the property list when more than one object is selected. Toggling between Intersection and Union changes the list of properties displayed in the Property Inspector, but does not affect the setting of any property.

Table 1-2 Property Inspector Intersection/Union

Button	Description
Intersection	The default. Only properties common to all selected objects are displayed.
Union	All properties of every object selected are displayed.

Properties that are common to two or more objects in a multiple selection are listed only once, and the property setting is displayed as follows:

If the property has the same setting for all of the selected objects that have it in common, the common setting is displayed.
If the property is set differently for the objects that have it in common, the string ***** is displayed.

1.11 Runtime Views

The topic in this section discuss the views of a report shown at runtime.

1.11.1 About the Runtime Parameter Form

The Runtime Parameter Form is a dialog box that optionally displays at runtime in which you can override default parameter values (e.g., values that modify SELECT statements, route the report output to a specified device, etc.). You define the format of the Runtime Parameter Form in the Paper Parameter Form view. If you do not define a Runtime Parameter Form in the Paper Parameter Form view, Reports Builder displays a default Runtime Parameter Form for you at runtime.

Change the parameters as desired and then click the Run Paper Layout button in the toolbar to run the report.

Alternatively, you can click the Cancel Run button in the toolbar to cancel.

1.11.2 About the Previewer

The Previewer displays on your screen how the printed version of your report will look. In the Previewer, you can scroll though a single page of report output, page through the entire report, and split the screen to view different sections of the same report concurrently. You can also perform the following actions:

Table 1-3 Print previewer actions

To...	Click in the toolbar...
Print the report	the Print button
Specify page setup settings	the Page Setup button
Open a new Previewer	the New Previewer button
Close the Previewer	the Close Previewer button
Zoom in	the Zoom In button
Zoom out	the Zoom Out button

A physical page (or panel) is the size of a page that will be output by your printer. A logical page is the size of one page of your actual report (it can be any number of physical pages wide or long). The Previewer displays the logical pages of your report output, one at a time.

Access

To display the Previewer:

choose File > Print Preview.

Usage notes

To speed the execution of your report, avoid "fetching ahead" when sending report output to the Previewer or Paper Design view. The following items can result in fetching ahead when referenced before the data on which they rely:

total number of pages/panels
grand totals
break columns that are formulas
break columns that have Value if Null specified

Matrix (cross-product) groups also cause fetching ahead. In order to cross-tabulate the data, Reports Builder must first fetch all of the data.

It should be noted that while these items slow down the Previewer or Paper Design view, they do not affect performance when writing to a file or some other destination.

Note:

1.12 Executables

The Oracle Reports executables are:

Table 1-4 Executable descriptions

Executable	Description
`rwbuilder`	starts Reports Builder
`rwrun`	runs a report using the OracleAS Reports Services in-process server
`rwclient`	parses and transfers a command line to the specified (or default) Reports Server
`rwcgi`	translates and delivers information between either a Web server or a J2EE Container (for example, OC4J) and the Reports Server, enabling you to run a report dynamically from your Web browser (supported for backward compatibility with previous releases of Oracle Reports)
`rwserver`	invokes the Reports Server
`rwservlet`	translates and delivers information between either a Web server or a J2EE Container (for example, OC4J) and the Reports Server, enabling you to run a report dynamically from your Web browser
`rwconverter`	converts one or more report definitions or PL/SQL libraries from one storage format to another

All executables can be run from the command line. See the Reference > Command Line section of the Reports Builder online help for detailed information about the executables and command line keywords.

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.1.1 Creating a new report using the Report Wizard

1.2.1.2 Creating a new report manually

See also

1.2.2 About Web reports

See also

1.3 Report Styles

1.3.1 About tabular reports

See also

1.3.2 About group above reports

See also

1.3.3 About group left reports

See also

1.3.4 About form-like reports

See also

1.3.5 About form letter reports

See also

1.3.6 About mailing label reports

See also

1.3.7 About matrix reports

Figure 1-1 Example matrix report

1.3.7.1 Matrix data model

1.3.7.1.1 Number of queries

Figure 1-2 Matrix data models

1.3.7.1.2 Group structure

Figure 1-3 Conceptual matrix

Figure 1-4 Matrix data relationships

1.3.7.1.3 Summary settings

Figure 1-5 Sample nested matrix report

Figure 1-6 Matrix summary settings

Figure 1-7 Matrix summary settings

Figure 1-8 Matrix summary settings

1.3.7.2 Matrix layout

Figure 1-9 Matrix Layout Model view

1.3.7.2.1 Matrix object

Usage notes

See also

1.4 Wizards

1.4.1 About the Report Wizard

Re-entrancy

Using the Report Wizard

1.4.2 About the Report Block Wizard

Access

Using the Report Block Wizard

1.4.3 About the Data Wizard

Access

Re-entrancy

Using the Data Wizard

1.4.4 About the Graph Wizard

Primary Graph Types

Secondary Graph Types

Access

Re-entrancy

Using the Graph Wizard

1.5 The Object Navigator

1.5.1 About the Object Navigator

See also

1.5.2 About Object Navigator views

See also

1.6 The Report Editor

1.6.1 About the Report Editor

1.6.2 About the Data Model view

Access

1.6.3 About the Paper Layout view

Access

1.6.4 About the Paper Design view

Access

Usage notes

1.6.5 About the Paper Parameter Form view

Access

1.6.6 About the Web Source view

Access

See also

1.6.7 About the tool palette and toolbar

Tool palette

1
Basic Concepts