Skip Headers

Oracle® Reports Building Reports 10g Release 2 (10.1.2) B13895-01 |
| Library | Product | Contents | Index

Previous | Next |

# 29 Building a Report for Spreadsheet Output

In this chapter, you will learn about spreadsheet reports. By following the steps in this chapter, you can generate the report output shown in Figure 29-1.

Figure 29-1 Spreadsheet report output

Description of "Figure 29-1 Spreadsheet report output"

Concepts

- This chapter provides steps for building a JSP-based Web report and deploying it with Reports Server under OC4J 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 10g page (`http://www.oracle.com/technology/products/reports/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 Excel. The Web report can only be run as JSP in OC4J and not through `rwservlet`, thus this method can only display the Microsoft Excel output inside a Web browser.

- Beginning with 10g Release 2 (10.1.2), Oracle Reports introduces a new output format, which enables you to generate output from paper layout report to HTML files that can be directly opened with Microsoft Excel 2000. Using the new `DESFORMAT=SPREADSHEET`, you can:

  - Generate report output to spreadsheet output from existing paper layout reports saved in any format (`.rdf`, `.xml`, `.jsp`), using `rwrun` or Reports Server clients (`rwcgi`, `rwclient`, `rwservlet`).

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

  For the steps to implement this new functionality to directly generate spreadsheet output, see Section 2.8.13, "About spreadsheet output" and Section 3.7.11, "Generating 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
--- | ---
`Examples\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.
`Examples\papertoexcel\myexcelreport.jsp` | This JSP-based Web report contains the modifications to the paper report you will make in Reports Builder.
`Examples\papertoexcel\papertoexceldata_code.txt` | The SQL for the query you need to enter.

## 29.1 Prerequisites for this example

To build the example in this chapter, you must have the example files we have provided (see "Example Scenario", above), as well as access to the Human Resources sample schema (HR) provided with the Oracle Database. If you do not know if you have access to this sample schema, contact your database administrator. You must have Microsoft Excel 2000 or higher to complete this example.

## 29.2 Create a query and the layout

The steps in this section will show you how to build a simple data model and report layout in the Report Wizard, which you can then use to generate either a JSP-based Web report or a paper report. In the next section, you will modify the JSP so that the appropriate information displays in your Web report.

To create a data model and layout:

1. Launch Reports Builder (or, if already open, choose File > New > Report).

2. In the Welcome or New Report dialog box, select Use the Report Wizard, then click OK.

3. If the Welcome page displays, click Next.

4. On the Report Type page, select Create Paper Layout only, then click Next.

5. On the Style page, select Tabular, then click Next.

6. On the Data Source page, click SQL Query, then click Next.

7. On the Data page, enter the following `SELECT` statement in the Data Source definition field:

       SELECT EMPLOYEES.EMPLOYEE_ID, EMPLOYEES.FIRST_NAME, EMPLOYEES.LAST_NAME,
         EMPLOYEES.HIRE_DATE, EMPLOYEES.SALARY, DEPARTMENTS.DEPARTMENT_NAME,
         LOCATIONS.CITY
         FROM EMPLOYEES, DEPARTMENTS, LOCATIONS
         WHERE EMPLOYEES.DEPARTMENT_ID=DEPARTMENTS.DEPARTMENT_ID
         AND DEPARTMENTS.LOCATION_ID=LOCATIONS.LOCATION_ID
         ORDER BY CITY

   Note: You can enter this query in any of the following ways: Copy and paste the code from the provided text file called`papertoexceldata_code.txt` into the Data Source definition field. Click Query Builder to build the query without entering any code manually. Type the code in the Data Source definition field.

8. Click Next.

   Note: If you are not already connected to a database, you will be prompted to connect to the database when you click Query Builder or Next. Ensure that you connect to a database that has the appropriate schema for this example. Section 29.1, "Prerequisites for this example" describes the sample schema requirements for this example.

9. On the Fields page, click the double right arrows (>>) to move all of the fields to the Displayed Fields list, then click Next.

10. On the Totals page, click Next.

11. On the Labels page, click Next.

12. On the Template page, select Predefined Template and click Wine, then click Finish to display your report output in the Paper Design view. It should look something like this:

    Figure 29-2 Paper design output

    Description of "Figure 29-2 Paper design output"

13. Save your report as `mypaperreport_``your_initials``.rdf`.

## 29.3 Modify the Web source of your JSP report

Now that you have created your paper report layout, you will learn how to take the same report and generate a JSP-based Web report that looks the same as your paper report.

To modify your JSP-based Web report:

1. In the Report Editor, click the Web Source button in the toolbar to display the Web Source view.

   In the Web Source view, you need to change the dummy JSP code created by Reports Builder.

2. In the Web Source view, find the text:

       <rw:dataArea id="yourDataArea">
          <!-- Report Wizard inserts the default jsp here -->
       </rw:dataArea>

   Tip: Choose Edit > Find and Replace, then type `dataArea``` in the Find what text box.

3. Ensure that you place the cursor in between the opening and closing `<rw:dataArea>` tags, and choose Insert > Frame. This inserts the following code in your Web Source view:

       <rw:include id="include" src="..."> </rw:include>

   Note: The `rw:include` tag enables you to include the outer frame of your paper layout in the Web Source view as JSP code. Adding a `rw:include` tag to the `rw:dataArea` tag enables you to include and reuse the existing paper layout in a Web layout.

4. In the Object Navigator, locate the top level layout object frame of your paper layout. Specify its name in the `src` attribute of `rw:include` tag. The line should look like this:

       <rw:dataArea id="yourDataArea">
          <!-- Report Wizard inserts the default jsp here -->
       <rw:include id="mypaperreport_id" src="M_G_EMPLOYEE_ID_GRPFR">
       </rw:include>
       </rw:dataArea>

5. Click the Run Web Layout button in the toolbar to display your new JSP-based Web report in your browser. The report should look something like this:

   Figure 29-3 Web design output

   Description of "Figure 29-3 Web design output"

   The report displays, in Web format, the employee data you specified. Here, you can see employee details such as their name, email, hire date, salary, department to which they belong and their location.

6. Save your report as `myexcelreport_``your_initials``.jsp`.

## 29.4 Modify your JSP report to display in Microsoft Excel

Now that you have created your JSP-based Web report, which looks the same as your paper report, you will modify it further to display the report in Microsoft Excel in your Web browser.

First, copy `myexcelreport_``your_initials``.jsp` to the deployment directory of your Application Server. For testing purposes, in this example, we will use the OC4J instance shipped with the Oracle Developer Suite.

For more information on deploying a JSP-based Web report, refer to the section "Deploying Your Reports" in the chapter "Running Report Requests" in the Oracle Application Server Reports Services Publishing Reports to the Web manual, available on the Oracle Technology Network Oracle Reports Documentation page ().

To modify the report to display in Microsoft Excel:

1. In the Report Editor, click the Web Source button in the toolbar to display the Web Source view.

   In the Web Source view, you will change the content type of the HTTP response using the JSP directive `contentType`, so that the browser opens Microsoft Excel to display your report output.

2. In the Web Source view, find the following text:

       <%@ taglib uri="/WEB-INF/lib/reports_tld.jar" prefix="rw" %>
       <%@ page language="java" import="java.io.*" errorPage="/rwerror.jsp"
       session="false" %>
       <%@ page contentType="text/html;charset=ISO-8859-1" %>
       <rw:report id="report">
       <rw:objects id="objects">
       </rw:objects>

   and modify it to read as follows:

       <%@ taglib uri="/WEB-INF/lib/reports_tld.jar" prefix="rw" %>
       <%@ page language="java" import="java.io.*" errorPage="/rwerror.jsp"
       session="false" %>
       <%@ page contentType="application/vnd.ms-excel;charset=ISO-8859-1" %>
       <rw:report id="report">
       <rw:objects id="objects">
       </rw:objects>

3. Save your report.

4. Copy the Web report (`myexcelreport_``your_initials``.jsp)`into the following directory:

       ORACLE_HOME\reports\j2ee\reports_ids\web

   Note: `ORACLE_HOME` is the directory in which Developer Suite is installed.

5. Start your OC4J instance to enable the deployment of your report.

   - On Windows, you can do either of the following:

     - From the Start menu, choose Programs > Oracle Developer Suite - oracle_home_name >Reports Developer > Start OC4J Instance.

     - From the command line, execute:

       `IDS_HOME``\j2ee\DevSuite\startinst.bat` (for Developer Suite installations)

       or

       `ORACLE_HOME``\j2ee\home\runoc4j.bat` (for standalone Reports Server installations)

   - On UNIX, start the shell script from the command line:

     - `IDS_HOME``/j2ee/DevSuite/startinst.sh`

   Tip: The OC4J instance starts once the containers for J2EE have been initialized.

6. Type the following URL to display your report in Microsoft Excel in your Web browser:

       http://your_computer:port/reports/yourexcelreportname.jsp?userid=hr/hr@db

   In our example, we use:

       http://mycomputer-pc:8888/reports/my_excel_report.jsp?userid=hr/hr@orcl

   Note: The connect string you type in the URL is for the database you used to create the data model in Section 29.2, "Create a query and the layout". For the purposes of this example, we have used plain text to pass the connect string. For information on using security, refer to the Securing Oracle Reports white paper on the Oracle Technology Network (OTN): on the Oracle Reports 10g page (`http://www.oracle.com/technology/products/reports/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 White Papers & How To Documents, and click Search. In the list that displays, click Securing Oracle Reports.

7. The report runs and the browser displays your report in Microsoft Excel.

   Note: If you want your report to display the same colors in Excel as in your report definition, you must use colors supported by the Excel color palette. Otherwise, Excel tries to match unsupported colors, but the match may not be what you expect.

   Your final report output should look something like this:

   Figure 29-4 Microsoft Excel output of your report

   Description of "Figure 29-4 Microsoft Excel output of your report"

## 29.5 Summary

Congratulations! You have created Excel output of your report. You now know how to:

- create a simple paper layout report.

- modify a Web layout to use an existing paper layout report.

- test and deploy a JSP-based Web report to Microsoft Excel output using OC4J.

For more information on any of the wizards, views, or properties used in this example, refer to the Oracle Reports online Help, which is available in Reports Builder or hosted on the Oracle Technology Network (OTN), as described in Section 3.1.1, "Using the Oracle Reports online Help".