29 Building a Report for Enhanced Spreadsheet Output

In this chapter, you will learn about Enhanced Spreadsheet reports. Oracle Reports generates spreadsheet output using the ENHANCEDSPREADSHEET format. The ENHANCEDSPREADSHEET output format is new in Oracle Reports 11g Release 2 (11.1.2) and provides the following benefits:

• Large data sets (up to 75,000 rows) can be generated to the ENHANCEDSPREADSHEET output format.

• ENHANCEDSPREADSHEET output format allows for bursting and distributing reports to spreadsheet output format.

• In ENHANCEDSPREADSHEET output, the complete report data is shown as one unbroken chunk of information in Microsoft Excel, not broken up by page breaks, as happens in other output formats, such as PDF and RTF that are restricted by horizontal and vertical page sizes.

For more information about Enhanced spreadsheet output, refer to Section 2.8.13, "About Enhanced Spreadsheet output".

By completing the steps in this chapter, you can generate the Enhanced Spreadsheet report output, as shown in Figure 29-1.

Figure 29-1 Enhanced Spreadsheet report output

Description of "Figure 29-1 Enhanced Spreadsheet report output"

Concepts

• This chapter provides steps for building a JSP-based Web report and deploying it with Reports Server under Oracle WebLogic Server to display it in Microsoft Excel inside your Web browser. You can use an existing paper layout report to create a new Web layout.

• If you want to generate Microsoft Excel output from your Web report, but you do not have an existing paper layout, refer to the slideshow demonstration on the Oracle Technology Network (OTN): on the Oracle Reports page (http://www.oracle.com/technetwork/middleware/reports/overview/index.html), click Getting Started to display the Getting Started with Oracle Reports home page. In the list of topic sections on the left, click Index. In the Collateral Type list, choose Demonstrations, and click Search. In the list of demos that displays, click Output to Excel with Oracle Reports.

  Note:

  The steps in both this chapter and the Getting Started demonstration allow you to 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. However, you must add custom JSP code to get the output in Microsoft Excel. The Web report can only be run as JSP in Oracle WebLogic Server and not through rwservlet, thus this method can only display the Microsoft Excel output inside a Web browser.

• The ENHANCEDSPREADSHEET output format enables you to burst and distribute reports to spreadsheet format, as well as generate large data sets (up to 75,000 rows) to spreadsheets.

  The ENHANCEDSPREADSHEET output formats generate output from paper layout reports to HTML files that can be directly opened with Microsoft Excel 2000. You can:

  • Generate report output to Enhanced spreadsheet format from existing paper layout reports saved in any format (.rdf, .xml, .jsp), using rwrun or Reports Server clients (rwclient, rwservlet). See the following example scenario.

  • Preserve the rich layout formatting, such as colors, fonts, conditional formatting, graphs, and images.

  For the steps to implement this functionality to directly generate spreadsheet output, see Section 2.8.13, "About Enhanced Spreadsheet output" and Section 4.7.11, "Generating Enhanced spreadsheet output".

Example Scenario

In this example, you will generate a report to Microsoft Excel output. The output is generated after integrating the paper layout into a Web layout by modifying the Web source of your report.

As you build this example report, you will:

• Create a Query and the Layout using the Report Wizard to create a simple query for a paper layout report.

• Modify the Web source of Your JSP Report to generate the JSP-based Web report.

• Modify Your JSP Report to Display in Microsoft Excel.

To see a sample report, open the examples folder named papertoexcel, then open the Oracle Reports example named mypaperreport.rdf. For details on how to open it, see "Accessing the Example Reports" in the Preface. The example files used in this chapter are listed and described in Table 29-1.

Table 29-1 Example Report Files

File	Description
papertoexcel\mypaperreport.rdf	The sample paper report. Running this RDF in Reports Builder will display the result of your paper report in the Paper Design view.
papertoexcel\myexcelreport.jsp	This JSP-based Web report contains the modifications to the paper report you will make in Reports Builder.
papertoexcel\papertoexceldata_code.txt	The SQL for the query you need to enter.