|Oracle® Fusion Middleware Oracle Reports User's Guide to Building Reports
11g Release 1 (11.1.1)
Part Number B32122-02
|PDF · Mobi · ePub|
The topics in this section discuss the various output formats and capabilities in Oracle Reports Builder.
Note:When you run a report to preview format, empty browser windows are launched to display the output. To avoid opening such empty browser windows, complete the following steps in Internet Explorer 7:
From the Internet Explorer Tools menu, select Internet Options
Go to the General tab > Tab > Settings.
From the Open Links from Other Programs in section, select the Current tab or Window radio button.
If you are using browsers other than Internet Explorer, read their help information to modify this setting.
In prior releases, Oracle Reports formatted the sections of a report in sequential order: Header section, followed by Main section, followed by Trailer section. This release introduces the capability to change the order in which the three sections of a report are formatted.
Note:Regardless of the order in which the report sections are formatted, the output order is unchanged: Header section first, then Main section, then Trailer section.
The format order can be set in either of the following ways:
This feature is useful for formatting any report section first to retrieve information that is known only at the time of formatting, such as page numbers, then using that information in the formatting of a previous section.
For example, to create a table of contents (TOC) for a report, you can format the Main section first and use report triggers to build a table containing the TOC entries. When the first element for the TOC is formatted, a trigger fires and creates a row in the TOC table containing the TOC entry and the page number. After the Main section has completed formatting, the format order setting can define that the Header section is formatted next. The Header section can contain a report block based on the TOC table. After formatting, you can output your report with a TOC (the Header section), followed by the report body (the Main section), followed by the Trailer section.
For the steps to create a TOC for a report, see the example reports in Chapter 35, "Building a Paper Report with a Simple Table of Contents and Index" and Chapter 36, "Building a Paper Report with a Multilevel Table of Contents".
A note about page numbering:
The page numbering of a report follows the format order. For example, in a report with a Header section of 2 pages, a Main section of 8 pages, and a Trailer section of 3 pages, with Format Order set to Main-Trailer-Header, the page numbering will be as follows in the report output: 12, 13 (Header pages, which were formatted last), 1, 2, 3, 4, 5, 6, 7, 8, (Main pages, which were formatted first) 9, 10, 11(Trailer pages, which were formatted second).
If it is not necessary to examine report output in the Previewer (for example, you may have to generate large volumes of output from a fully-tested report or run several reports in succession), you can run your report in batch using
rwrun. This leaves you free to pursue other tasks while your reports are running.
You can run reports in batch mode from the command line, or use a command file to specify arguments. A command file can save you a great deal of typing if you wish to run several reports using the same set of arguments.
You can also use the Reports Server to batch process reports by specifying
BACKGROUND=YES on the command line (valid for
rwservlet) to run reports asynchronously (the client sends the call to the server, then continues with other processes without waiting for the report job to complete; if the client process is killed, the job is canceled).
The Reference > Command Line section of the Oracle Reports online Help (for information about
Report distribution enables you to design a report that can generate multiple output formats and be distributed to multiple destinations from a single run of the report. You can create distributions for an entire report, and also burst to distribute individual sections of the report. For example, in a single run of a report, you can generate HTML output, send a PostScript version to the printer, and also e-mail any or all sections of the report to a distribution list.
To distribute a report, you first define the distribution, then enable the distribution, as described in Section 4.7.12, "Distributing a report to multiple destinations".
For an example of using section-level distribution, see Chapter 37, "Bursting and Distributing a Report". This chapter covers defining distribution of a single section to multiple destinations, using the Repeat On property and distribution XML file.
You cannot mix character mode and bit-mapped output in one report. The
MODE system parameter can only be set to one value per the entire report (
In order to use the same report definition file to burst and distribute to data-driven formats such as
DELIMITEDDATA, as well as to layout-driven formats such as
ENHANCEDSPREADSHEET, you must ensure the following requirements are met:
The distribution XML file must specify the
include element. For example:
The Repeat On property must be set appropriately for the section(s) specified in the distribution XML file.
The section(s) specified in the distribution XML file in the report paper layout must not be empty.
You can use sectioning and distribution to publish your report output in HTML, and also send a PostScript version to the printer.
You can send an executive summary of a report to senior management, and also e-mail detailed breakdowns to individual managers. In this example, a single report with two report sections needs to be created: a portrait-sized summary section and a landscape-sized detail section. Use the Repeat On property to associate the detail section with a data model group that lists the managers and then alter the destination to burst the report on each instance of the data model group to send the output to the appropriate managers.
Chapter "Creating Advanced Distributions" in the Oracle Fusion Middleware Publishing Reports to the Web with Oracle Reports Servicesmanual.
As an alternative to defining the distribution for a report or report section in the Distribution dialog box, you can also create a DST file and specify its name on the command line with the
DESTINATION keyword to distribute the report.
Note:DST files are supported for backward compatibility with prior releases; the preferred and recommended method of distributing reports is with the Distribution dialog box or using XML.
If a DST file is specified on the command line, the distribution that it defines overrides the distribution defined using the Distribution dialog box.
Note:If you trace report distribution to identify distribution errors (see Section 4.14.19, "Tracing report distribution"), the trace file format is very similar to the DST file, so you can cut and paste to generate a DST file from the trace file.
The format of each line of a DST file is as follows:
dist_ID is an identifier for a distribution destination.
output_def is a series of
rwclient command line keywords that specify the distribution definition. In addition, the following parameter is valid:
LEVEL specifies the scope of the distribution.
Values for LEVEL
REPORT means that the distribution applies to the entire report.
Header_Section means that the distribution applies to the header section only.
Main_Section means that the distribution applies to the main (body) section only.
Trailer_Section means that the distribution applies to the trailer section only.
The definition in this example sends report output to an HTML file, 3 copies of the main section to a printer, and the header section to a PDF file.
;dst file (specified with the DESTINATION keyword on the command line) DEST1: DESNAME=dst1.HTM DESTYPE=file DESFORMAT=HTML COPIES=1 LEVEL=Report DEST2: DESNAME=prt1 DESTYPE=printer DESFORMAT=BITMAP COPIES=3 LEVEL=Main_Section DEST3: DESNAME=SECT1.pdf DESTYPE=file DESFORMAT=pdf COPIES=1 LEVEL=Header_Section
Note:All parameters for each distribution destination in a DST file (
DEST3in the example above) must be specified on a single line. Specifying parameters on a new line results in failed distribution with error message
Pluggable destinations can be used to distribute any content that an engine (not only the Oracle Reports engine) has created in the Reports Server's cache. Oracle Reports provides the following out-of-the-box destinations:
You can also define access to your own custom destination by using the Oracle Reports Java APIs to implement a new destination component in the Reports Server. You can choose for your jobs to use an out-of-the-box destination or your customized destination to determine the destination for the output in the cache.
For information and steps to implement and register a destination class, then use the destination with Oracle Reports, see the Oracle FTP Destination tutorial available on the Oracle Reports Plugin Exchange on the Oracle Technology Network (OTN): on the Oracle Reports page (
http://www.oracle.com/technology/products/reports/index.html), click Plugin Exchange.
Note:Currently there is no support for FTP and WebDAV destinations from the Oracle Reports Builder environment. However, these destinations are supported from the Reports Runtime and the Reports Server environments.
Chapter "Configuring Destinations for Oracle Reports Services" in the Oracle Fusion Middleware Publishing Reports to the Web with Oracle Reports Services manual.
Event-driven publishing enables you to set up a report to execute when a certain action has been performed. For example, when an employee submits an expense report, new data is being inserted into the database. When this insert event (for example, a database trigger or an Advanced Queuing (AQ) message) occurs, a report is sent to the employee's manager through their portal page or e-mail notifying them to approve/reject this expense report.
Event-driven publishing functionality includes making report objects available inside Oracle Workflow.For detailed information, refer to the chapter "Using Event-Driven Publishing" in the Oracle Fusion Middleware Publishing Reports to the Web with Oracle Reports Services .
Using the Before Report, Between Pages, or format triggers, you can switch to different printer trays as your report formats. This enables you to easily print pages of the same report on different sheets of paper (for example, letterhead, forms, colored).
Oracle Reports uses XML (Extensible Markup Language) in the following ways:
XML tags are used to define Web-based reports (see the topic "Oracle Reports XML tags" in the Reference section of the Oracle Reports online Help).
XML is used to define tag-delimited, structured information.
XML is a supported pluggable data source (PDS). For more information, see Chapter 45, "Building a Report with an XML Pluggable Data Source".
XML is a form of encoding text formats that can be read by many different applications. The XML tags can be used to output information or as a basis for building a pluggable data source to exchange electronic data with a third-party application (EDI).
You may change the XML properties that control XML output for your report at three levels: report, group, and column. Note that in any Oracle Reports Builder-generated XML file, your output mimics the data model, structured by groups and columns. For information on how to view your changes in XML output, see Section 4.7.7, "Generating XML output".
For detailed information about using XML for report distribution and customizing reports through XML, see the chapters "Creating Advanced Distributions" and "Customizing Reports with XML" in the Oracle Fusion Middleware Publishing Reports to the Web with Oracle Reports Services .
The XML PDS section of the Oracle Reports online Help
Oracle Reports Builder can generate report output to Hypertext Markup Language (HTML) and Hypertext Markup Language with a Cascading Style Sheet (HTMLCSS) files, containing the formatted data and all objects. When you generate your report output to an HTML or HTMLCSS file, you can distribute the output to any HTML destination, including e-mail, printer, Oracle Portal, and Web browser.
Note:This topic discusses the HTML generated when you run a paper-based report to an HTML or HTMLCSS file. This is unrelated to HTML that might be generated when you run a JSP-based Web report (when you click the Run Web Layout button in the toolbar, or choose Program > Run Web Layout).
You apply style sheets and user-defined styles to your report or report objects for HTMLCSS output, using the following properties:
Style Sheets property
CSS Class Name property
CSS ID property
You can include HTML formatting in all bitmap output supported by Oracle Reports (including PDF, RTF, HTML, HTMLCSS, and PostScript), as described in Section 2.8.9, "About HTML formatting".
HTML and HTMLCSS output pass HTML 4.01 syntax checkers.
Note:If you modify your report's Before Report Value property or After Report Value property, you must make sure that you specify valid HTML. If the HTML is not valid, the generated HTML report output may not pass HTML 4.01 syntax checkers.
You can preview your HTML or HTMLCSS report output in your Web browser by choosing File > Preview Format > Paginated HTML (or Paginated HTMLCSS).
Bookmarks cause multiple HTML files to be created. One master file is created with two frames: one for bookmarks and one for the report output. One HTML file is created for each of these frames. The master document filename is the name specified in the DESNAME parameter. The bookmark filename is
b.htm. The report output filename is
Linked images, image fields, and graphs in a report cause GIF files to be created and referenced from the HTML document. Note that even if the linked boilerplate or image field refers to an external GIF file, a new GIF file is generated.
If an image is stored in the database, one GIF file may be generated for each occurrence of the image in the report. If an image is stored in a file (for example, imported images, linked boilerplate, or image fields that reference files), only one GIF file will be generated per image regardless of how many times it is repeated in the report.
Any GIF image files generated for HTML output have a number sequence (for example,
A comment block in the master document contains the names of all of the files that are associated with the master document.
Table 2-1 Report to HTML font sizes
|Report font size||HTML font size|
less than 8
8 through 9
10 through 12
13 through 15
16 through 20
21 through 30
more than 30
Note:If the font used in the report is non-proportional, TeleType mode is turned on for the generated HTML file.
HTML does not have the concept of a page. A separator line is placed between each page of the report. Depending upon the browser, you may or may not need to scroll to see the entire report page. Furthermore, if you print the HTML document from your browser, the printer will not necessarily print the separator lines at the bottom of each page. If you do not want the separator line or you want to use a different separator line, you can use SRW.SET_AFTER_PAGE_HTML to change it.
For HTMLCSS, graphics and text can be overlapped.
Once you have generated your report to an HTML or HTMLCSS file, the data model and looping tags are removed and replaced with the data. You can open the HTML or HTMLCSS file in Oracle Reports Builder, but it will be a static text file and not a report.
For information about paginating HTML or HTMLCSS output, see Section 220.127.116.11, "About HTML page streaming".
Objects cannot overlap one another. For example, you could not have text on top of an image.
If objects overlap slightly (two characters or less), then the underneath object is truncated to prevent overlap.
If objects overlap significantly (or one is completely on top of the other), then the underneath object is removed altogether. In this case, any linking information of the removed object is transferred using the same rules as if it were a frame (see the rules about frames below).
Text always takes precedence over horizontal lines, regardless of which is on top. This prevents the line underneath a column label from eliminating the label text.
When multiple output files are generated (for example, when bookmarks are used), any file except the master file will be overwritten without confirmation. For example, GIF files and bookmark files would be overwritten without prompting.
Report frames are not visually represented in the HTML output. Any fill or border attributes of frames do not appear in HTML output.
If the frame in a report is the target of a link or a bookmark, that attribute is transferred to the visible object nearest to the upper left corner of the frame in HTML output. If the frame object is a hyperlink, that attribute will be transferred to all the child (interior) objects that are not hyperlinks. If the hyperlink attribute cannot be transferred to the child objects, the frame's hyperlink is lost.
The only drawn object supported in HTML is a solid, black, horizontal line. The line width specified in the report may be honored depending upon the browser. All other drawn objects (for example, rectangles or circles) in the report layout will not show up in the HTML output. Space for these drawn objects is reserved, but there is no visible representation in the HTML output. HTMLCSS output supports some additional drawn objects (for example, rectangles).
Background (fill) and border (line) colors/patterns for text are not available in HTML. Bold, italic, underline, and foreground (text) color are supported if the browser supports them.
The PDF action attribute is ignored for HTML output.
Any browser customizations that have been made will affect how the generated HTML is displayed.
HTML page streaming enables you to display individual pages of your HTML/HTMLCSS report output in your Web browser, without having to download the entire report. From the first page of the report, you can navigate to any page in the rest of the report. When you click a bookmark or hyperlink with a destination:
within the report, the frame that contains the current page will update with the destination page.
outside the report, the entire base frame (including the bookmark frame, the page, and the navigation frame) will reload.
with PL/SQL (SRW
.SET_PAGE_NAVIGATION_HTML in a Before Report trigger)
in the Report Property Inspector with the Page Navigation Control Type and Page Navigation Control Value properties
Output file names
With HTML page streaming, each page is formatted as a separate HTML document. If your HTML file is named
myreport.htm and there are no bookmarks, the new files are named as follows:
myreport.htm (for the base frame)
.htm (for the bookmark file, present only if bookmarks are used in the HTML files)
.htm( for the pages)
Scope of HTML output
To specify HTML to be displayed on only the first (header) or last (footer) pages of your report, set the Before Report or After Report properties or use the
SRW.SET_AFTER_REPORT_HTML PL/SQL procedures. To specify global HTML to apply to the entire report, such as background colors or images, set the Before Page properties or
SRW.SET_BEFORE_PAGE_HTML PL/SQL procedure. The Oracle Reports Builder-generated HTML logo appears only on the last page of your report.
Enabling page-streamed output
To enable page streaming when you format your report as HTML or HTMLCSS output, you must specify
PAGESTREAM=YES on the command line. This option cannot be set using the Oracle Reports Builder user interface.
Oracle Reports allows a variety of fonts, styles, and colors for text objects in your reports. Through the use of the
SRW built-in procedures (see the Reference section of the Oracle Reports online Help), you can allow end users to change text attributes at runtime to a whole object, but not to a section of the text (for example, different formatting for different parts of a text object).
Alternatively, you can use a defined set of HTML formatting tags to format text style (bold, italics, underline, and strikethrough) and text attributes (font name, font color, and font size), and generate formatted text objects in all bitmap output formats supported by Oracle Reports when the objects' Contains HTML Tags property is set to Yes.
The text objects can have static values as in boilerplate text objects, or dynamic values as in field objects or text file link objects. A boilerplate text object can also have dynamic values if it references a field object or a report-level column. For boilerplate text, the formats specified at design-time are merged with rich text formats obtained at runtime. This feature also supports multibyte text containing HTML tags.
In the Paper Design view or Paper Layout view, you can choose the objects to which HTML formatting should be applied by setting their Contains HTML Tags property to Yes. If you run the report from the command line with
CONTAINSHTMLTAGS=NO, Oracle Reports will not interpret the HTML tags for the report, regardless of the object's Contains HTML Tags property setting. For HTML and HTMLCSS ouput, the browser will interpret the HTML formatting tags; for other output formats, the HTML tags themselves will appear as is in the report output.
The following HTML tags are interpreted by Oracle Reports if they appear in a boilerplate text, text file link, or field object. Any HTML tags or attributes not listed appear as is in non-HTML/HTMLCSS report output.
Table 2-2 Supported HTML tags
Text enclosed in either of these tags will be bold.
For example:If the text is formatted as
Text enclosed in either of these tags will be italics.
For example:If the text is formatted as
the output will appear as
Text enclosed in this tag will be underlined.
If the text is formatted as
the output will appear as
Text enclosed in either of these tags will be struck through.
If the text is formatted as
the output will appear with a line struck through it.
Generic style container, used to specify font family, size, and color attributes, as described in Table 2-3.
the output will appear in Times New Roman font, size 10 point, in red color.
The following are the supported properties for the
Table 2-3 Supported properties for style attribute of span tag
This property specifies the font name. Oracle Reports provides support for specifying only one font name. If you specify a comma-separated list containing more than one font name, Oracle Reports uses the system default font (usually Courier, or its equivalent) to render this tag, without generating a warning.
This property describes the size of the font.CSS level 2 specifies the following options for this property:
Oracle Reports provides support for only
This property describes the color of the enclosing element's text content.
Oracle Reports supports the following options for this property:
where color names and rgb equivalents are as follows:
is equivalent to:
The supported output formats are: PDF, RTF, HTML, HTMLCSS, and PostScript.
HTML formatting supports interleaving and nesting of tags. For example, if you open an italics tag, it is applied to the point of its corresponding closing tag. If a tag is opened several times, it must be closed the same number of times for its effect to cease.
Format attributes are not inherited across instances of a text object of the same name or type (for example, if they are enclosed in a repeating frame). If, however, a text object contains multiple lines, formatting instructions are inherited across the lines of this object only.
This feature supports HTML tags that are hard-coded or referenced in a boilerplate text object. The tags can also be retrieved from a table column with a field object. A file link object that points to a text file which in turn contains HTML tags is also supported.
Oracle Reports tries to find the closest match according to the following criteria for fonts with the same character set:
font face > font size > font style > font weight > font width
If Oracle Reports cannot match the font face, it will try to match the font size; if it cannot match the font size, it will try to match the font style; and so on. For the font lookup alogrithm, refer to the chapter "Managing Fonts in Oracle Reports" in the Oracle Fusion Middleware Publishing Reports to the Web with Oracle Reports Services . In font lookup, it is possible that the font used does not have all the required attributes (such as style, size, and so on) embedded in data. In this case, the unavailable font attributes do not appear in the report output.
A tag must be specified in its entirety on a single line. If the tag continues to the next line, it is not supported.
Only one font name can be specified in the
If you specify a comma-separated list containing more than one font name, Oracle Reports uses the system default font (usually Courier, or its equivalent) to render this tag, without generating a warning.
Entities such as
" are not supported.
<span style="font-family: "Courier New";">my format!</span>
You must use literal double quote (") instead.
Example 1: Inline font change
In a boilerplate text object, you can specify the text boilerplate as follows:
With the Contains HTML Tags property set to Yes, the formatted output will look like this:
Example 2: Single line text with multiple formats
Assume a span of text (in a boilerplate object or database column) that formats on one line, such as:
<b>My <span style="font-family:Courier">HTML</span>
</b><u><span style="font-family:Times New Roman">Formats</span></u>
With the Contains HTML Tags property set to Yes, the formatted output will look like this:
Example 3: Multiline text with multiple formats
Assume a span of text that formats on multiple lines, such as:
Line 1: <i>My<span style="font-family:Courier">
Line 2: HTML</span></i><span style="font-family:Times New Roman><u>
Line 3: Formatting</u></span>.
With the Contains HTML Tags property set to Yes, the formatted output will look like this:
Line 1: My
Line 2: HTML
Line 3: Formatting.
Note in the preceding example that the formats are maintained in successive lines.
Oracle Reports Builder can generate report output to PDF files, containing the formatted data and all objects. When you generate your report output to a PDF file, you can distribute the output to any PDF destination, including e-mail, printer, Oracle Portal, and Web browser.
Document taxonomy (classification) for PDF output is provided by the report properties Title, Author, Subject, and Keywords, which assist in cataloging and searching a report document.
With font subsetting, the PDF file includes the font information needed to render the PDF, regardless of the availability of that font on the machine used to view the report. PDF font subsetting works for single byte, multibyte, and Unicode fonts, and is the preferred method of creating multibyte reports.
Oracle Reports 10g Release 2 (10.1.2) provided the following enhancements to PDF output:
Direct subsetting of True Type fonts (as a Type0 font), no longer converting to Type3 fonts. As a result, PDF output is clearer, smoother, searchable, and accessible.
Improved True Type Collection (TTC) support, allowing you to use the zero-based index to indicate the specific TTC font file to pick up. In prior releases, Oracle Reports picked up the first font file in the True Type Collection (TTC).
Oracle Reports 11g Release 1 (11.1.1) adds more enhancements to PDF output:
Ability to run the reports designed on Windows on Unix platforms by simply copying the font file to the
With the new font model, no misalignment will be seen in the PDF subsetted report output.
For detailed information about PDF enhancements and capabilities in Oracle Reports, refer to the chapter "Using PDF in Oracle Reports" in the Oracle Fusion Middleware Publishing Reports to the Web with Oracle Reports Services .
You can preview your PDF report output in your Web browser by choosing File > Preview Format > PDF.
If you are building a multibyte report for multibyte languages, such as Chinese or Japanese, and you need to alias the font in PDF output, you need the CID fonts named within the Acrobat 4.0 packs. Otherwise, you do not need the CID fonts in the Acrobat 4.0 packs.
Oracle Reports does not support Windows UDC for PDF output. For the user-defined characters to be printed or rendered, all the glyphs must be within a single TTF or TTC file.
Graphics and text can be overlapped.
The foreground color of the object will be used as the fill color (regardless of a specified pattern).
You can modify the PDF file, if you have:
the fonts used in your report installed on your machine.
a PDF writer.
For PDF output, the bit-mapped drivers (for example, PostScript) for the currently selected printer are used to produce the output.
Oracle Reports Builder can generate report output to Rich Text Format (RTF) files, containing the formatted data and all objects. RTF can be read by many different word processing software packages, such as Microsoft Word. You can use the software's editing and graphics features to modify and enhance your report output. When you generate your report output to an RTF file, you can distribute the output to any RTF destination, including e-mail, printer, Oracle Portal, and Web browser.
Graphics and text can be overlapped.
Text can only be rotated by 90-degree variations.
As with PDF output, the foreground color of the object will be used as the fill color (regardless of a specified pattern).
Note:When you view the report in Microsoft Word in Office 95, you must choose View > Page Layout to see all the graphics and objects in your report.
Oracle Reports can generate report output that includes a delimiter to delimited files (for example, files that contain comma-separated or tab-separated data), which are easily imported into spreadsheets or for use with word processors.
Oracle Reports provides two options for generating delimited output:
Delimited: uses the paper layout to generate the output.
DelimitedData (for use when you have problems running large volume reports with Delimited): does not take into account the paper layout, and directly runs off the data model (that is, all fields that are available in the data model will appear in the output, not only those that are displayed in paper layout).
You can specify a delimiter (a character or string of characters) to separate the data (boilerplate or field objects) in your report output in either of the following ways:
On the command line using the
In the Delimited Output dialog box or DelimitedData Output dialog box (displayed with File > Generate to File > Delimited or File > Generate to File > DelimitedData) in Oracle Reports Builder.
Note:If you do not specify a delimiter, the default delimiter is a tab.
You can further distinguish the cells by using a cell wrapper. A cell wrapper can consist of any series of characters, such as a comma or parentheses.
For example, if the data in your report output include the same character as the delimiter (for example, a comma), you can use the parentheses cell wrapper to distinguish each cell:
When you generate a report to DelimitedData output, only data (as defined by the report data model) displays in the output. Any formatting changes defined in the layout are not reflected in the DelimitedData output.
You can set the following column properties to alter column names and exclude columns from the DelimitedData output file:
The XML Tag property can be used to enter a column alias.
The Exclude from XML Output property can be used to exclude the column from the DelimitedData output.
To generate report output that preserves the report layout information, see Section 2.8.13, "About Enhanced Spreadsheet output".
You can distribute and burst a report in DelimitedData output format, specified either in a distribution XML file or in the Distribution dialog box.
When you generate a report to Delimited output, the data displays according to the positions of the objects in the Paper Layout view.
If you place A above or to the left of B (where A and B are any boilerplate or field objects) Oracle Reports Builder displays each instance of A before each instance of B in every line of output.
Figure 2-8 Delimited output of A above B
If you create a frame that contains a boilerplate object (A) and encloses a repeating frame that contains a field object (B), each instance of A displays with each instance of B.
Figure 2-9 Delimited output of frame enclosing repeating frame
In the previous layout, if you add another frame that contains a boilerplate object (C) and encloses another repeating frame that contains a field object (D), A displays for every instance of B, and then C displays for every instance of D.
Figure 2-10 Delimited output of two frames enclosing repeating frames
If you create a matrix in your report, be sure to align your objects carefully according to the grid in the Layout Model view. If the objects are not aligned, Oracle Reports Builder may interpret the extra space as an extra row or column and disrupt your report output.
If you create a boilerplate object outside of a matrix, each instance of the boilerplate repeats with every row (not column) of the matrix. Note that boilerplates contained in the matrix will not be repeated with field objects outside of the matrix.
Figure 2-11 Delimited output of boilerplate outside matrix
If the text file contains a field labeled
ID (in uppercase) as the first field, you will be unable to open the file in Microsoft Excel. The following delimited output causes an error in Excel:
ID, name, title, dept
If you want to generate delimited output that contains an
ID field, try changing the database column name to lowercase (that is,
id), or re-arranging the order of the fields.
Report distribution and bursting do not support Delimited output format. You cannot specify
DELIMITED as an output format in a distribution XML file or in the Distribution dialog box.
Note: You can distribute and burst a report in DelimitedData output format, specified either in a distribution XML file or in the Distribution dialog box.
Oracle Reports 11g Release 1 (11.1.1) introduces a new spreadsheet output format,
ENHANCEDSPREADSHEET, which enables you to burst and distribute reports to the spreadsheet format, as well as generate large data sets (up to 75,000 rows) to spreadsheets.
ENHANCEDSPREADSHEET output formats generate output from paper layout report to HTML files that can be directly opened with Microsoft Excel 2000. You can:
Generate report output to spreadsheet format from existing paper layout reports saved in any format (
rwrun or Reports Server clients (
rwservlet). See the Example below.
Preserve the rich layout formatting such as colors, fonts, conditional formatting, graphs, and images.
DESFORMAT=ENHANCEDSPREADSHEETbehavior is the same as the functionality of the
rw:includeJSP tag. Enhanced Spreadsheet output is not paginated; no page setup information is written in the output, and it is formatted as a single worksheet. Enhanced Spreadsheet output is driven by the layout; the reports ouput displays objects that are in the body area of each section (Header, Main, and Trailer) of the paper layout, and does not format any content in the margin, header, or footer areas.
Microsoft Excel 2000 or higher (on the client machine for viewing the output). Note that this software is not needed on the machine where Oracle Reports Services is running.
Enhanced Spreadsheet output requires more memory than DelimitedData output. This is an important consideration for long-running and large reports. To change the name of the output worksheet, set the report's Title property.
Only bitmapped reports can be generated to spreadsheet output; you cannot generate a character-mode report to Enhanced Spreadsheet output.
The following paper layout objects are supported in Enhanced Spreadsheet output:
text (boilerplate and field)
images (any format), including images that are imported, linked, or stored in the database
imported drawings, as well as CGM and OWF drawings
The following paper layout objects are not supported in Spreadsheet output: graphic arc, polygon, rectangle, rounded rectangle, stretchable line, and OLE external object (for reports developed prior to Oracle9i Reports (9.0.2) ). Space for these drawn objects is reserved, but there is no visible representation in the output. This limitation does not apply to horizontal lines.
The following font attributes are supported in Spreadsheet output: size, face, color, style (italic, oblique, underline, overstrike, outline, shadow), weight (bold, normal, extra bold, ultra bold).
The following Web report properties are supported in Spreadsheet output:
Hyperlink Destination property
Additional Attributes (HTML) property (for Parameter Form fields)
Table Attributes (HTML) property (for frames and repeating frames)
Alternative Text property (for images)
Contains HTML Tags property (for boilerplate text or field objects that include HTML tags)
The following report taxonomy properties are supported in spreadsheet output:
Graphs embedded in Spreadsheet output are static image files, and are not interactive. Thus, the Graph Hyperlink property is not supported in Spreadsheet output.
For reports that support globalization, the metadata for
charset must be provided in a before report escape, because this information is not written to the generated output, by default. For example, you can define a before report escape as follows:
<html> <META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=&encoding"> <body dir=&Direction bgcolor="#ffffff">
The frames and repeating frames defined in the paper layout are translated to tables in the output. You can specify table attributes such as
width using the Table Attributes (HTML) property for frames and repeating frames.
For reliable formatting of spreadsheet output, the whole layout area should be enclosed in a frame. This prevents the possibility of parallel objects displaying in different vertical positions, one below the other.
The order of the report sections (Header, Main, and Trailer) is preserved in spreadsheet output; that is, the Header section output appears first, followed by the Main section, followed by the Trailer section.
If boilerplate objects or fields are not contiguous to each other; that is, if there is a gap between their boundaries, this might result in empty cells in the spreadsheet output.
If your report defines conditions that may change cell positions when the report is formatted, these changes are not reflected in spreadsheet output format. For example, if a report defines a condition that causes column 2 to display at the position of column 1 when column 1 is empty, Spreadsheet output will always display both columns, even when the condition is met. Oracle Reports generates the HTML table that forms the spreadsheet immediately after the report is compiled, not during formatting. During formatting of spreadsheet output, the table cells are filled with values.
The Oracle Reports color palette is used while designing the report. When you generate your report output to spreadsheet output, Microsoft Excel will show the closest matching color from its color palette.
If you rotate a boilerplate object in the paper layout, the object will appear horizontal in the spreadsheet output.
The following cell properties are not supported in spreadsheet output: background pattern, border color, and border pattern.
Images included in the paper layout of the report will appear in the spreadsheet output only if the Reports Server is running in non-secure mode. In the case of a secure Reports Server, images will not appear in the spreadsheet output. This is because generating images in the output involves multiple calls to the Reports Server (one call per image). Once the user is authenticated, Oracle Reports passes the user's identity between the browser and the secure Reports Server using cookies. However, Excel does not support cookies. As a result, the call to the secure Reports Server seems like a call from a non-authenticated user. Thus, the Reports Server refuses to pass on the images. As a workaround, you can generate the spreadsheet output from a secure Reports Server to a URL using WebDAV.
Summary and formula columns will be shown as values, not as dynamic Excel formulas. Also, the page-level summary columns are reduced to report-level summary columns, since there is no page concept in Excel.
If you set the Vertical Elasticity property of a frame to Fixed, the output in Excel will show only as many records as could appear on the first page of the paper output. Since Excel does not have a page concept, it is not able to "overflow" the remaining rows to the next "page".
Since spreadsheet output is not paginated, only the before report escape and after report escape are supported (see Section 2.2.10, "About before and after escapes").
In an after report escape, adding an HTML command outside the body does not reflect when the report is generated to spreadsheet output, even though the HTML command is written in output properly. For example:
<table><tr><td><B> the text to be bolded </B></td><tr></table> </html>
Adding the HTML command before the closing body tag reflects correctly when the report is generated to spreadsheet output. For example:
<table><tr><td><B> the text to be bolded </B></td><tr></table>
For a matrix report, if the horizontal panels per page is set to a value higher than 1, then running the report in the ENHANCEDSPREADSHEET output format would result in the following error:
REP-1223: THE PAGE SIZE IS INVALID
You can set the horizontal panels per page to a value higher than 1 for a matrix report for all
Spreadsheet format is supported in
rwservlet commands such as
SHOWJOBS. It is also supported in Enterprise Manager's job queue, and in Oracle Portal's access object definition.
You can distribute a report in Enhanced spreadsheet output format, specified in a distribution XML file.
XML-based bursting is supported in
ENHANCEDSPREADSHEET output format only.
To generate the paper layout of your report to Microsoft Excel output through a URL (using
rwservlet), you can type a URL that includes the following in your browser:
http://hostname:port/reports/rwservlet?report=report_name +server=server_name+userid=db_connect_string+destype=file +desformat=Enhancedspreadsheet+desname=C:\temp\myexcel_output.htm
Note:Microsoft Office 2000/2002/2003 supports Hypertext Markup Language (HTML) as a native file format. Enhanced Spreadsheet output relies on producing HTML output, which can be understood by Microsoft Excel as a file with valid Excel format.
Other capabilities for generating Spreadsheet Output
You can also generate spreadsheet output as implemented in releases prior to Oracle Reports 11g Release 1 (11.1.1), in the following ways:
Generate a report to delimited output, as described in Section 4.7.10, "Generating delimited output":
Advantage: Delimited output can be generated from existing paper layout reports saved in any format (
.jsp), either directly from Oracle Reports Builder, or by running the report from the command line.
Disadvantage: Only data (as defined by the report Data Model), no layout information, displays in the output.
Deploy a report with the Reports Server under Oracle WebLogic Server to display it in Microsoft Excel inside your Web browser, using either of the following methods:
When you do not have an existing paper layout: open an Excel HTML template file in Oracle Reports Builder, add the data, save the report as a JSP file, then deploy the report. This method enables you to use JSP coding to include dynamic Excel formulas (not just static values of Reports summary and formula columns), dynamic graphs (not just static Reports graph images).
When you do have an existing paper layout: starting with a paper layout report, edit the Web Source view to add a
rw:include tag to include the group frame of your paper layout in the Web source view as JSP code, save the report as a JSP file, then deploy the report. For the steps to implement this method, see Chapter 29, "Building a Report for Enhanced Spreadsheet Output".
Advantage: You can use rich formatting such as colors and fonts so that you generate report layout, not just data, in Microsoft Excel output inside a Web browser.
Disadvantages: You must add custom JSP coding to get the output in Excel. The report can only be run through the JSP engine and not through
rwservlet, thus this method can only display the Microsoft Excel output inside a Web browser.
For choosing the appropriate delimited output solution for given requirements, see "Displaying Report Output in Microsoft Excel" in the appendix "Troubleshooting Oracle Reports Services" in the Oracle Fusion Middleware Publishing Reports to the Web with Oracle Reports Services .
Oracle Reports Builder can generate report output to text files, containing the formatted data and all objects. When you generate your report output to text, and the running mode is character (MODE=CHARACTER, or MODE system parameter Initial Value property set to Character), the result is pure text output, which can be read by many different applications. If the running mode is bitmap (MODE=BITMAP, or MODE system parameter Initial Value property set to Bitmap), the result is PostScript output, which can be read and rendered only by PostScript-compatible applications (such as a PostScript printer).
To create a character-mode report, you first create a bit-mapped report, then convert that report to an ASCII (character-mode) report. The process will create a new character-mode version of your bit-mapped report; the original bit-mapped report remains unchanged.
After conversion, many of your fields and text objects may need to be resized. Also, graphical objects such as images and drawings will not be included in your character-mode report. The following lists summarize what is supported in each output format:
Table 2-4 Supported items in bit-mapped and character-mode reports