|
Oracle® Reports Building Reports
10g Release 2 (10.1.2) B13895-01 |
|
 Previous |
 Next |
|
Oracle® Reports Building Reports
10g Release 2 (10.1.2) B13895-01 |
|
|
Previous |
Next |
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 the Oracle Reports online Help (see Section 3.1.1, "Using the Oracle Reports online Help").
Topics are grouped into the following sections:
The topics in this section discuss the features and functionality in 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 enables 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, RTF, spreadsheet, 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
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 Oracle Reports online Help. Additionally, refer to the Oracle Reports 10g page on OTN (http://www.oracle.com/technology/products/reports/index.html).
The topics in this section discuss basic concepts of reports; for more advanced concepts, see Section 2.1, "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 cataloging 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.
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.
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, triggers, and program units. You can change the Object Navigator view to list objects hierarchically or by object type.
See also
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 for paper-based reports (see Section 3.6.10, "Adding Web links to paper-based reports"), Oracle Reports can also use 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 enables 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 in various output formats, or take more advantage of Web features by adding JSP coding in 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:
In the Report Wizard, select whether the report layout is both Web and paper, Web only, or paper only. The layout for both Web and paper reports defaults in the Paper Design view. You can view the source code 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 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 "Oracle Reports JSP tags" in the Reference section of the Oracle Reports online Help.
Insert an existing report into an existing Web page, by displaying the Web Source view for the report and source code of 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 report to create a paper-based Web report (HTML file). See Section 3.6.10, "Adding Web links to paper-based reports".
To preview your report output in a Web browser, use Program > Run Web Layout. This enables you to immediately see the effect of your changes on the output.
|
Notes:
|
See also
Section 1.9.4, "About Parameter Forms for Web reports"
Section 2.8.8, "About HTML and HTMLCSS output"
Section 2.8.10, "About PDF output"
Section 3.6.1, "Creating a Web report"
Section 3.7.16.4, "Displaying report output in your Web browser"
The topics in this section describe the built-in report styles in Reports Builder.
A tabular report is the most basic type of report. Each column corresponds to a column selected from the database.
See also
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.
See also
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.
See also
A form-like report displays one record per page, displaying field values to the right of field labels.
See also
A form letter report contains database values embedded in boilerplate text (any text that you enter or import into a Report Editor.)
See also
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.
See also
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.
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, multiquery matrix with break, and matrix break, but you are not confined to these designs.
For a detailed example, see Chapter 25, "Building a Matrix Report".
In building your matrix data model, you should consider the following:
number of queries
group structure
summary settings
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.
One advantage to a one-query data model is that the resulting report is generally more efficient than a report based on a multiquery data model.
Multiquery matrix. You may consider using a multiquery data model, as it often has simple queries and can be easier to maintain than the equivalent one-query data model. In addition, a multiquery data model is required for some types of matrices (for example, some nested matrix reports).
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.
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 multiquery data models.
Figure 1-4 Matrix data model relationships
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.
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.
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:
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:
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:
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 (that is, 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.
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.
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) frame.
A matrix object created for the cross product group, inside of which are the cells of the matrix (for example, the rectangle in the figure above with F_SUMSAL inside it).
Boilerplate for each column and row of values, as well as for summaries (for example, Job and Deptno). Displaying the boilerplate is optional, but Reports Builder will generate it by default.
|
Note: When running a JSP-based Web report that includes a matrix object, the JSP<rw:include> tag expects the matrix object to format on only one page. However, if the cells in the across repeating frame expand, the column headers of the matrix may be forced onto a second page regardless of the page size. Therefore, these matrix headers will not appear in the report output. Additionally, in this situation, the paper layout does not display correctly as header fields move to subsequent pages where the cell values remain on the first page. To resolve this, it is important to carefully define in the Report Editor the space available to the across repeating frame to allow for displaying the largest values, then set the Vertical Elasticity property of the across repeating frame to Fixed.
|
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. |
See also
Section 2.3.7, "About matrix objects"
Section 2.1.7, "About nested matrix reports"
Section 2.1.8, "About matrix with group reports"
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.
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.
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 Paper Layout view or Web Source view, choose Insert > Report Block.
In the Paper Layout view, click the Report Block tool in the tool palette, then click in an open area of the Paper Layout view.
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.
The Data Wizard helps you to quickly and easily define a query, break groups, and totals for a multiquery 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.
The Graph Wizard provides an easy way for you to add a wide variety of graphs to both paper-based and JSP-based Web reports. Reports Builder uses the information you specify on each page of the wizard to create an XML definition that describes the graph and renders to resulting look-and-feel of the graph at runtime.
While the Graph Wizard enables you to create and edit the attribute values and layout of a graph, there are additional attributes that are not exposed through the Graph Wizard. To customize your graph XML definition beyond the scope of the Graph Wizard, you can directly edit the XML in the graph's Graph Settings property (for paper-based reports), or in the Web Source view (for JSP-based Web reports). The full list of attributes available is defined in the DTD file, graph.dtd.
|
Note: Thegraph.dtd is available in the following locations:
|
Reports built with previous versions of Oracle Reports containing Oracle Graphics 6i charts will continue to run if the Oracle Graphics 6i runtime is installed on the same computer in a separate ORACLE_HOME. However, this is not a supported configuration, and Oracle will not fix bugs that result from this configuration.
Usage notes
When you specify a graph title, subtitle, footnote, or axis title in the Graph Wizard, you can insert lexical references (to user parameters, system parameters, and columns) in the text that will display their value at runtime.
When you specify dates in your graphs, the date values may display in a different format in a graph than in other fields in your report output.
In prior releases, the Graph Wizard's Type panel included a 3DEffect check box to allow you to specify whether to display the graph with the appearance of depth. To implement this effect in Oracle Reports 10g, you will need to edit your graph XML definition as shown in bold font in the following example for a bar graph:
<rw:graph id="CT_1" src="G_EMPNO" groups="ENAME" dataValues="SAL">
<?xml version="1.0" ?>
<Graph version="3.2.0.22" depthAngle="50" depthradius="8" pieDepth="30" pieTilt="20"/>
...
-->
</rw:graph>
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
Access
To display the Graph Wizard for paper reports:
In the Paper Layout view, choose Insert > Graph.
In the Paper Layout view, right-click in the main area (canvas region) and choose Graph Wizard.
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 the graph, and choose Graph Wizard.
Click the graph, then 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.
See also
Section 3.9.8.2, "Adding a graph"
The topics in this section discuss the Object Navigator in Reports Builder.
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.
Access
To display the Object Navigator:
Click Finish or Cancel in the Report Wizard.
Open an existing report.
Choose Tools > Object Navigator.
Press F5.
See also
Section 3.2.4, "Setting preferences for the Object Navigator"
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.
See also
Section 3.2.4, "Setting preferences for the Object Navigator"
The topics in this section discuss the Report Editor and its different views of a report.
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.
The Report Editor's Data Model view is a work area in which you create, define, and modify data model objects (queries, groups, queries, groups, formula columns, summary columns, placeholder columns, system and user parameters, 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:
In any of the Report Editor views, 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.
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 (boilerplate text and graphic objects, frames, repeating frames, graphs, fields, anchors, file links, and report blocks). 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 displays only when you run your report, allowing 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.
Access
To display the Paper Layout view:
In any of the Report Editor views, 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.
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:
In any of the Report Editor views, 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. |
Click the Paper Design view button in the toolbar.
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. |
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:
In any of the Report Editor views, 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.
The Report Editor's Web Source view displays the source code for your JSP-based Web report, including HTML, JSP, and XML 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 JSP tags, HTML, Java, and XML.
Access
To display the Web Source view, do any of the following:
In any of the Report Editor views, choose View > Change View > Web Source.
Click the Web Source button in the toolbar.
In the Object Navigator, double-click the Web Source view icon next to the Web Source node.
Usage notes
In the Web Source view, the following languages may appear garbled: Arabic, Central European languages, Cyrillic, Greek, Hebrew, Japanese, Thai, and Turkish. To work around this issue, you can set the font names for Reports Builder in uifont.ali as follows:
[rwbuilder].....AR8MSWIN1256="Courier New" .....CL8MSWIN1251="Courier New" .....EE8MSWIN1250="Courier New" .....EL8MSWIN1253="Courier New" .....IW8MSWIN1255="Courier New" .....JA16SJIS="MS Gothic" .....TH8TISASCII="Andale Duospace WT" .....TR8MSWIN1254="Courier New"
You can download a copy of the Andale Duospace WT (fixed-width) font from Metalink (http://metalink.oracle.com). The ARU number is 2638552.
When performing a Find/Replace operation in the Web Source view and running in an Asian language (for example, Japanese) on Windows, Reports Builder fails. This problem does not occur on UNIX.
See also
Section 1.2.2, "About Web reports"
Section 2.2.1, "About JavaServer Pages (JSPs) and servlets"
Section 3.6.3, "Adding report data to an existing Web page (HTML file)"
Section 3.6.4, "Adding a report block to a Web page"
Section 3.9.8.2.2, "Adding a graph to a JSP-based Web report"
"Oracle Reports JSP tags" in the Reference section of the Oracle Reports online Help
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).
See also
The topics in this section discuss basic concepts of data model objects; for more advanced concepts, see Section 2.3, "Data Model Objects".
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, Oracle OLAP, or your own data source that you can access through the pluggable data source (PDS) API). You can also use a REF CURSOR to create queries (see Section 2.6.10, "About REF CURSOR 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
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.
Multiquery reports
A report may contain any number of queries. Multiquery reports are useful when you want to:
produce multipart unrelated query reports.
produce multipart 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.
Multipart unrelated query reports
If you do not link the queries, you will produce a multipart 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.
Multipart 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 multiquery data models are often easier to understand, single-query data models tend to execute more quickly.
Queries are processed 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 multiquery 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 (for example, multiway outer join).
when you have complex views (for example, 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.
See also
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 (that is, 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 (for example, the first 5 records)
Last, to display only the last n records for the group
User-created filters, using PL/SQL.
See also
Section 2.3.6, "About links versus groups"
Section 2.6.9, "About group filters"
Section 1.3.2, "About group above reports"
Section 1.3.3, "About group left reports"
Section 2.1.8, "About matrix with group reports"
Section 3.5.1, "Creating a report"
Section 3.5.2, "Creating a multiquery group above report"
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 have created.
In addition to the traditional column types (for example, date, number, character), Reports Builder also supports graphics or images columns, which are columns whose values can be:
graphics or images stored directly in the database. Such a column is usually of data type LONG or LONG RAW and contains a graphics or images. 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 or images formats are supported, including BMP, CALS, CGM, GIF, JFIF, PCD, PCX, PICT, RAS, TIFF, and so on.
See also
Section 3.8.1, "Creating a query"
Section 3.8.5, "Selecting an image from the database"
Section 2.3.4, "About referencing columns and parameters"
Section 2.3.1, "About summary columns"
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 (for example, WHERE DEPTNO=DEPTNO), you can create links with various SQL clauses (that is, 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 cannot 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:
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.
|
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. |
See also
Section 2.3.6, "About links versus groups"
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.
See also
The topics in this section discuss basic concepts of layout objects; for more advanced concepts, see Section 2.4, "Layout Objects".
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.
See also
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".
See also
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.
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.
See also
Fields are placeholders for parameters, columns, and such values as the page number, current date, and so on. 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.
|
Note: If you create a field object in a template, the field is renamed when the template is applied to a report. For example, if you create a field namedF_1, the field is renamed to F_1_SEC2 when the template is applied to a report. Thus, if you reference F_1 in boilerplate (&F_1), these references will need to be updated.
|
See also
Section 1.9.2, "About Parameter Form fields"
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
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.1.3, "Linking an image object to a URL".
|
Note: These procedures are for paper-based reports only. File Link objects are not implemented in JSP-based Web reports. |
See also
Section 1.9.3, "About Parameter Form boilerplate"
Section 3.9.2.1, "Creating a boilerplate object for text"
Section 3.9.2.2, "Creating a boilerplate object for text that displays every other page"
The topics in this section discuss basic concepts of Parameter Form objects; for more advanced concepts, see Section 2.5, "Parameter Form Objects".
A parameter is a variable whose value can be set at runtime (for example, 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). Parameters specified on the command line override the equivalent system parameter values set in the report definition. For detailed information about command line arguments, see the "Command Line Keywords" in the Oracle Application Server Reports Services Publishing Reports to the Web manual, or the Oracle Reports online Help.
Choosing from a list or entering the parameter value(s) in the Runtime Parameter Form.
Oracle Reports is shipped with the following system parameters. You can change their default values as described above.
Table 1-1 System parameter descriptions
| System Parameter | Description |
|---|---|
|
Is the number of report copies that should be made when the report is printed. |
|
|
Is either:
|
|
|
Is the name of the output device (for example, the file name, printer's name, mail user ID). |
|
|
Is the type of device that will receive the report output (for example, |
|
|
Is whether the report should run in |
|
|
Is the print direction for the report ( |
|
|
Is whether the Print Job dialog box should appear before the report is run. |
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").
See also
Section 2.3.4, "About referencing columns and parameters"
Section 3.11.1, "Using a pre-defined system parameter"
Section 3.11.2, "Creating a user parameter"
Section 3.11.3, "Creating a list of values (LOV) for a parameter"
Section 3.11.4, "Validating a parameter value at runtime"
Section 3.11.6, "Selecting parameters to include in the Runtime Parameter Form"
Section 3.11.9, "Passing parameters to reports running in batch mode"
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 that you select in the Parameter Form Builder.
See also
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.
See also
Section 1.8.5, "About boilerplate objects"
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 through 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 41, "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: |
See also
Section 1.2.2, "About Web reports"
Section 1.6.5, "About the Paper Parameter Form view"
The topics in this section discuss the Property Inspector in Reports Builder.
The Property Inspector is a window 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, and so on.) has associated properties that can be viewed using the Property Inspector. To get help on any property, click the property in the Property Inspector and press F1.
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.
See also
Section 3.2.2, "Setting report properties"
Section 3.2.5, "Setting properties for an ASCII (character-mode) report"
Section 3.2.7, "Setting properties of multiple objects"
Section 3.2.8, "Comparing the properties of one object to another"
The topic in this section discuss the views of a report shown at runtime.
The Runtime Parameter Form is a dialog box that optionally displays at runtime in which you can override default parameter values (for example, values that modify SELECT statements, route the report output to a specified device, and so on). 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.
See also
Section 1.6.5, "About the Paper Parameter Form view"
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. To cross-tabulate the data, Oracle Reports 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: 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 Oracle Reports calculates it. |
See also
Section 3.7.16.3, "Displaying report output in the Previewer"
The Oracle Reports executables are:
Table 1-4 Executable descriptions
| Executable | Description |
|---|---|
|
Starts Reports Builder. |
|
|
Runs a report using the Oracle Application Server Reports Services in-process server. |
|
|
Parses and transfers a command line to the specified (or default) Reports Server. |
|
|
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). |
|
|
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. |
|
|
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 Oracle Reports online Help for detailed information about the executables and command line keywords.
See also
