24.6 Formatting the Data

After the data is retrieved from the data source, Oracle Reports generates the report layout and formats the output. The time taken for a paper layout depends on a number of factors, but generally comes down to:

  • The work required to prevent an object from being overwritten by another object.

  • The efficiency of any calculations or functions performed in the format triggers.

The rules for a Web layout are a little different as Oracle Reports does not own the Web page or control the rendering mechanism. It merely injects data into a regular JSP page.

This section discusses reviewing and tuning the format of your report:

24.6.1 Paper Layout

When generating a default paper layout, Oracle Reports places a frame around virtually every object to prevent the object from being overwritten by another object. At runtime, every layout object (frames, fields, boilerplate, and so on) is examined to determine the likelihood of that object being overwritten. In some situations (for example, boilerplate text column headings) when there is clearly no risk of the objects being overwritten, the immediately surrounding frame is removed. This reduces the number of objects that Oracle Reports must format and consequently, improves performance.

An object that is defined as variable, expanding, or contracting in either or both the horizontal or vertical directions requires extra processing. In this case, Oracle Reports must determine the instance of the object's size, before formatting that object and those around it. There is no processing overhead involved for objects assigned a fixed size, as the size and positional relationships between the objects is known.

The following guidelines helps to improve performance when creating a paper layout:

  • Make your non-graphical layout objects (for example, boilerplate text or fields with text) fixed in size by setting the Vertical Elasticity and Horizontal Elasticity properties of the field to Fixed. In particular, setting the size of repeating frames and their contents to fixed, improves performance. Variable (size) non-graphical objects require more processing overhead, because Oracle Reports Builder must determine their size before formatting them. However, the overhead for fixed non-graphical objects is less, since the additional processing is not required.

  • Make your graphical layout objects (for example, images and graphs) variable in size by setting the Vertical Elasticity and Horizontal Elasticity properties of the objects to Variable. Fixed graphical objects require more processing overhead as their contents have to be scaled to fit. Variable objects grow or shrink with the contents eliminating the need for scaling.

  • Make text fields span a line (maximum) and ensure that their contents fit within the specified width (for example, use the SUBSTR function). If a text field spans more than a line, Oracle Reports Builder must use its word wrapping algorithm to format that field. Ensuring the text field takes only one line to format avoids the processing overhead of the word wrapping algorithm.

  • Minimize the use of different formatting attributes (for example, fonts) within the same field or boilerplate text, because it takes longer to format.

  • Use the SUBSTR function in the report query to truncate the data at the database level, instead of truncating a character string from a field in the Report Builder layout.

  • For paper layout only reports, .rdf and .rep files run faster than a.jsp file, because the serialized formats of a .rdf or a .rep file do not require parsing. Additionally, a .rep file runs faster than a .rdf file as it is optimized for the current platform.

24.6.1.1 Format Triggers

Format triggers can dynamically disable, enable, and change the appearance of an object. Exercise caution when using them as they fire each time an instance of their associated object is produced and formatted (at runtime).

Consider the following example:

A tabular report includes a single repeating frame that expands vertically and has the Page Protect property set to On. As the report is formatted, there is room for one more line at the bottom of the first page. Oracle Reports starts to format the next instance of the repeating frame and fires its associated format trigger. One of the objects inside the repeating frame is found to have expanded and this instance of the repeating frame is moved to the following page. The format trigger for the repeating frame is fired again. Although the repeating frame only appears once (at the top of the second page), the format trigger has fired twice. DML should not be performed in a format trigger, because you are not sure how many times the format trigger will fire for a particular object.

With this example, had the format trigger contained an INSERT statement, then two rows of data would have been inserted.

Format triggers can be used against repeating frames to filter data. However, by introducing filtering at appropriate levels, you not only improve a report's performance but also reduce the complexity required for this type of a report.

Use the following filtering order whenever possible:

  • Modify the SQL statement to prevent the data being returned from the server.

  • Use the group filter to introduce filtering in the Data Model.

  • Use return false inside the format trigger.

Format triggers should be placed at the highest level possible in the object/frame hierarchy so that the trigger fires at the lowest possible frequency. For example:

Figure 24-1 Format Triggers

Description of Figure 24-1 follows
Description of "Figure 24-1 Format Triggers"

Maximize the efficiency of the code, whenever you define any triggers or PL/SQL program units within Oracle Reports. For example, to change the display attributes of a field dynamically to draw attention to values outside the norm, change the attributes using individual built-ins such as the SRW.SET_TEXT_COLOR built-in procedure.

Refer to the Oracle Database PL/SQL Language Reference for general PL/SQL tuning issues.

Assigning a transparent border and fill pattern to layout objects (for example, frames and repeating frames) improves performance, as these objects are not rendered as a bitmap file.

24.6.1.2 Image Outputs

You can improve the performance of reports that include images by judiciously setting environment variables related to image support.

Improving performance of graphs output to a PDF file or a printer

The REPORTS_GRAPH_IMAGE_DPI environment variable specifies a dots per inch (DPI) value for graphs output to a PDF file or a printer. The default value for this environment variable is set at 72 DPI to minimize the time taken to generate the report, as well as to reduce the report file size. If you specify a value higher than 72 DPI, you will see an improvement in the image resolution for graphs sent to a PDF file or a printer. However, this affects the time taken to generate the report output as well as the file size.

With the value of 250, the time taken to generate a report with an Oracle Reports graph increases 5 to 6 times when compared to the time taken to generate the same report with the value set to 72 DPI. The PDF file size also increases 5 to 6 times.

This functionality is currently not supported in Oracle Reports distribution functionality, as this is specific to PDF and printer outputs only.

Note:

When you set a DPI value greater than 250 and your graph is bigger than 5"x5" (approximately), you may also want to change the JVM heap size value using the REPORTS_JVM_OPTIONS environment variable to avoid the Out Of Memory error for the JVM.

For more information, refer to Section B.1.50, "REPORTS_GRAPH_IMAGE_DPI".

Improving performance of JPEG/GIF/PNG output image formats

If your input image format is JPEG, it is recommended that you do not set the REPORTS_OUTPUTIMAGEFORMAT environment variable to GIF or PNG, which will increase the image size more and might degrade the performance problem. Similarly, if your input image format is GIF or PNG, it is recommended that you do not set the REPORTS_OUTPUTIMAGEFORMAT environment variable to JPEG. For better performance, use the same format for both input and output format.

For more information, refer to Section B.1.58, "REPORTS_OUTPUTIMAGEFORMAT".

Improving performance of JPEG images

The REPORTS_JPEG_QUALITY_FACTOR environment variable specifies the level of image quality desired for JPEG images. It provides control over the trade-off between JPEG image quality and size of the image. The better the quality of the image, the greater the image file size and lower performance. If you want to improve the performance, set value to 0. The default value is 100 (highest quality). A value of 75 provides a good quality image, while ensuring a good compression ratio.

For more information, refer to Section B.1.52, "REPORTS_JPEG_QUALITY_FACTOR".

24.6.2 Web Layout and JSP Report Definition

In Oracle Reports, you can use your favorite Web authoring tool to design the static portion of your Web page and then use Oracle Reports Builder to insert the dynamic portion (data) into appropriate sections of the page. A poorly designed Web page impacts perceived performance. Alternatively, you can use pre-defined Oracle Database Web templates to build the Web page.

Avoid including Java code in a JSP file (mixing business and data access Java code with presentation logic) as it increases the JSP's footprint and limits the efficient use and management of system resources.

Customized formatting of a Web page is always an expensive operation. Any type of formatting that cannot be natively achieved through Oracle Reports (for example, change the foreground color of a data block) should be done using Java. We discourage the use of PL/SQL wrappers for formatting purposes.

A .jsp report definition can contain both a paper layout definition and a Web layout definition. Oracle Reports always formats the paper layout definition first when executing the report, since the Web layout section of a JSP report could contain an <rw:include> tag referencing a paper layout object. If your JSP report does not reference any paper layout objects at all, we recommend using the SUPPRESSLAYOUT command line keyword to prevent Oracle Reports executing the paper layout formatting.