29 Building a Report for Spreadsheet Output

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".