| Oracle® Application Server Developer's Guide for Microsoft Office Interoperability 10g Release 3 (10.1.3.1.0) Part Number B28947-01 |
|
|
View PDF |
| Oracle® Application Server Developer's Guide for Microsoft Office Interoperability 10g Release 3 (10.1.3.1.0) Part Number B28947-01 |
|
|
View PDF |
This chapter shows how to deliver reports built with Oracle Reports to Microsoft Office. It shows how you can save report output as a Microsoft Excel spreadsheet, as a Microsoft Word document, or send as an e-mail attachment.
This chapter contains the following sections:
Oracle Reports is a powerful enterprise reporting tool that enables you to rapidly develop and deploy sophisticated Web and paper reports against any data source (including an Oracle database, JDBC, XML, text files, and Oracle OLAP). Leveraging the latest J2EE technologies such as JSP and XML, you can publish your reports in a variety of formats (including HTML, XML, PDF, spreadsheet, delimited text, PostScript, and RTF) to any destination in a scalable, efficient manner. In addition to built-in destinations such as e-mail, Web browser, OracleAS Portal, file system, FTP, and WebDAV, you can define access to your own custom destination by using the Oracle Reports Java APIs.
If you want to share your reports with other people, it is helpful to do so using a familiar format that does not require people to install additional software. Oracle Reports runs a report directly to the output format you specify, with no additional setup required. By following the step-by-step instructions in this chapter, you can send a report to anyone who has a browser and Microsoft Office installed.
|
Note: You can use Microsoft Excel as an ODBC data source using the JDBC-ODBC driver provided with Oracle Reports. For more information on the JDBC-ODBC driver, refer to the chapter "Configuring and Using the JDBC PDS" in Oracle Application Server Reports Services Publishing Reports to the Web.To enhance the out-of-the-box integration with Microsoft Office, you can use the Oracle Reports Java API to create your own custom destinations and data sources. Refer to the Oracle Reports 10g Release 2 (10.1.2) Java API documentation ( |
To perform the steps outlined in this chapter, you must first ensure the following prerequisites are available on your machine:
Oracle Application Server 10g Release 2 (10.1.2). The steps in this chapter rely on the Business Intelligence and Forms installation. When working with other install types, refer to the Oracle Application Server documentation.
Optionally (to use Reports Builder), Oracle Developer Suite 10g Release 2 (10.1.2).
Access to an Oracle Database 10g database with the Sales History sample schema installed. To access an Oracle Database 10g database, your tnsnames.ora file (in ORACLE_HOME\network\admin) must include an entry for the database. If you do not know if the database has the Sales History sample schema installed, contact your database administrator. All sample schemas provided with Oracle Database 10g installation are described in Oracle Database Sample Schemas.
Microsoft Office 2000 or later.
A Web browser that supports displaying documents in Microsoft Excel and Microsoft Word.
A running instance of OC4J to enable deployment of the report on the Web:
For Oracle Application Server installations, start Reports Server using Oracle Process Manager and Notification Server (OPMN).
For Oracle Developer Suite installations, start an OC4J instance on your machine before you submit a request, as follows:
From the Start menu, select All Programs, Oracle iDS Home, Reports Developer, and then Start OC4J Instance.
Or, open a command prompt, and enter:
cd %ORACLE_HOME%\j2ee\home runoc4j.bat
The OC4J instance starts once the containers for J2EE have been initialized.
To confirm that OC4J is running and your environment is set up correctly to run a report request on the Web, enter the following URL in your browser to display the Reports Servlet (rwservlet) help page:
http://hostname:port/reports/rwservlet
If you see the help page, you are ready to run report requests on the Web.
The pluginParam element in the server configuration file (ORACLE_HOME\reports\conf\server_name.conf) and the Reports Builder configuration file (ORACLE_HOME\reports\conf\rwbuilder.conf) specifies your outgoing SMTP mail server name. For example:
<pluginParam name="mailServer">smtpserver.mycompany.com</pluginParam>
The example report named reports\mypaperreport.rdf. For details on how to locate the example files, see Accessing the Demonstration Support Files in the Preface. The example file used in this chapter is listed and described in Table 13-1.
The steps in these sections show how to use Oracle Reports to develop reports and deploy them to Microsoft Office applications:
To create a report with Oracle Reports, you must install Oracle Developer Suite, which includes Reports Builder.
Reports Builder provides user-friendly wizards that guide you through the report design process to develop dynamic reports for the Web and e-business requirements, as well as high-fidelity printed reports. You can also edit existing JSP and HTML files in Reports Builder to add report layouts.
Refer to Oracle Reports Tutorial and Oracle Reports Building Reports to learn how to use Reports Builder to develop Web-based or paper-based reports customized to your needs, using data from any data source. Once you have developed a report, you can deploy it to any destination. The sections that follow include the steps to deploy the example report that we have provided, mypaperreport.rdf, to Microsoft Excel, Microsoft Word, and e-mail recipients.
This section illustrates the spreadsheet output capability introduced with Oracle Reports 10g Release 2 (10.1.2), which enables you to generate output with rich formatting from paper layout reports to Microsoft Excel-compatible HTML format that can be directly opened with Microsoft Excel 2000 or later.
|
Note: You can also deploy a JSP-based Web report using Reports Server under OC4J to display it in Microsoft Excel in your Web browser. For the steps to do this, refer to the chapter "Building a Report for Spreadsheet Output" in Oracle Reports Building Reports. |
To display a report in Microsoft Excel using rwservlet, perform the following steps:
As described in Section 13.2, "Prerequisites", ensure that your OC4J instance is started to enable the deployment of the report.
|
Note: In Oracle Developer Suite installations, a standalone OC4J instance is provided for testing purposes to manually deploy an Oracle Reports application for running reports withrwservlet and running JSP reports. In Oracle Application Server installations, the OC4J_BI_Forms instance automatically deploys an Oracle Reports application. |
In a Web browser (for example, Internet Explorer), enter either of the following URLs:
To generate report output that is displayed in Microsoft Excel in the Web browser:
http://hostname:port/reports/rwservlet?report=report_name &userid=username/password@database&destype=cache&desformat=spreadsheet
For example:
http://myas.us.oracle.com:8888/reports/rwservlet?report=mypaperreport.rdf &userid=sh/sh@ora10g&destype=cache&desformat=spreadsheet
The output should look as shown in Figure 13-1.
To generate an HTML file that you can open in Microsoft Excel:
http://hostname:port/reports/rwservlet?report=report_name &userid=username/password@database&destype=file&desformat=spreadsheet &desname=output_filename.htm
For example:
http://myas.us.oracle.com:8888/reports/rwservlet? report=mypaperreport.rdf&userid=sh/sh@ora10g&destype=file &desformat=spreadsheet&desname=C:\temp\myexcel_output.htm
|
Note: If therwservlet command fails, refer to Section 13.4, "Troubleshooting" to resolve the error message that is displayed. |
If you generated an HTML file that you can open in Microsoft Excel, start Microsoft Excel and open the report output file (for example, C:\temp\myexcel_output.htm).
The output should look as shown in Figure 13-1.
Figure 13-1 Report Output in Microsoft Excel
|
Note: The graph embedded in the spreadsheet output is a static image file, and is not interactive. You may notice other differences between your report layout in Reports Builder and the Microsoft Excel output. These differences are caused by the way Microsoft Excel interprets the report layout. For detailed usage notes and restrictions about spreadsheet output, see "About spreadsheet output" in the Oracle Reports online Help, or the chapter "Advanced Concepts" in Oracle Reports Building Reports. |
It is not possible to generate spreadsheet output from Reports Builder. Instead, you run the report using Reports Server clients (rwservlet or rwclient) or rwrun, with desformat=spreadsheet.
Oracle Reports 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, OracleAS Portal, and Web browser.
This section illustrates the RTF output capability available with Oracle Reports, which enables you to generate output from paper-based reports to RTF files that can be opened with Microsoft Word.
To display a report in Microsoft Word using rwservlet, perform the following steps:
As described in Section 13.2, "Prerequisites", ensure that your OC4J instance is started to enable the deployment of the report.
|
Note: In Oracle Developer Suite installations, a standalone OC4J instance is provided for testing purposes to manually deploy an Oracle Reports application for running reports withrwservlet and running JSP reports. In Oracle Application Server installations, the OC4J_BI_Forms instance automatically deploys an Oracle Reports application. |
In a Web browser (for example, Internet Explorer), enter either of the following URLs:
To generate report output that is displayed in RTF format in the Web browser:
http://hostname:port/reports/rwservlet?report=report_name &userid=username/password@database&destype=cache&desformat=rtf &mimetype=application/msword
For example:
http://myas.us.oracle.com:8888/reports/rwservlet?report=mypaperreport.rdf &userid=sh/sh@ora10g&destype=cache&desformat=rtf &mimetype=application/msword
|
Note: Themimetype=application/msword option is needed to open the RTF document with Microsoft Word. |
The output should look as shown in Figure 13-2.
To generate an RTF file that you can open in Microsoft Word:
http://hostname:port/reports/rwservlet?report=report_name &userid=username/password@database&destype=file&desformat=rtf &desname=output_filename.rtf
For example:
http://myas.us.oracle.com:8888/reports/rwservlet? report=mypaperreport.rdf&userid=sh/sh@ora10g&destype=file &desformat=rtf&desname=C:\temp\myword_output.rtf
|
Note: If therwservlet command fails, refer to Section 13.4, "Troubleshooting" to resolve the error message that is displayed. |
If you generated an RTF file that you can open in Microsoft Word, start Microsoft Word and open the report output file (for example, C:\temp\myword_output.rtf). The output should look as shown in Figure 13-2.
Alternatively, if you have Oracle Developer Suite installed, you can use Reports Builder to generate RTF output to a file, as follows:
Start Reports Builder (either by selecting Start, All Programs, iDS Home, Reports Developer, and then Reports Builder, or by opening a command prompt window and typing rwbuilder).
In the Welcome to Reports Builder dialog box, select Open an existing report, then click OK.
In the Open dialog box, locate and open the example report mypaperreport.rdf.
Select File, Connect to connect to a database that includes the Sales History schema.
Click the Run Paper Layout button in the toolbar to run the report.
To preview your report output in a Microsoft Word document, select File, Preview Format, and then RTF.
To save your report output as an RTF file, select File, Generate to File, and then RTF.
In the Save dialog box, specify a location and file name (for example, C:\temp\myword_output.rtf). Click Save.
Start Microsoft Word, and open the report output file (for example, C:\temp\myword_output.rtf). The output should look as shown in Figure 13-2.
Figure 13-2 Report Output in Microsoft Word
|
Note: For detailed usage notes about RTF output, see "About RTF output" in the Oracle Reports online Help, or the chapter "Advanced Concepts" in Oracle Reports Building Reports. |
You can e-mail reports in a variety of formats (including PDF, HTML, HTMLCSS, XML, RTF, ASCII, and delimited text) using any Internet Standard Protocol SMTP mail application, to recipients using e-mail utilities such as Microsoft Outlook.
To e-mail a report, perform the following steps:
In your browser (for example, Internet Explorer), enter the following URL:
http://hostname:port/reports/rwservlet?report=report_name &userid=username/password@database&destype=mail &desformat=pdf|html|htmlcss|xml|rtf|ascii|delimited &desname=email_address[&from=email_address]
For example:
http://myas.us.oracle.com:8888/reports/rwservlet? report=c:\orawin\examples\mypaperreport.rdf&userid=sh/sh@ora10g &from=tom.smith@oracle.com &desformat=pdf&destype=mail&desname=jan.jones@oracle.com
|
Note: If therwservlet command fails, refer to Section 13.4, "Troubleshooting" to resolve the error message that is displayed. |
The e-mail recipient(s) can open the e-mail using their e-mail utility, such as Microsoft Outlook.
If you have Oracle Developer Suite installed, you can use Reports Builder to test sending e-mail output, as follows:
Start Reports Builder (either by selecting Start, All Programs, iDS Home, Reports Developer, and then Reports Builder, or by opening a command prompt and entering rwbuilder).
|
Note: If already open, restart Reports Builder to activate the change you made torwbuilder.conf. If your outgoing mail server was already previously defined in rwbuilder.conf, it is not necessary to restart Reports Builder. |
In the Object Navigator, select or open the example report mypaperreport.rdf.
If you are not already connected, select File, Connect to connect to a database that includes the Sales History schema.
Select File, Mail.
In the Mail dialog box, specify appropriate values in each field for your e-mail report, as shown in Figure 13-3.
Click OK to send the report in the specified output format to the recipient e-mail address(es).
|
Note: You can also use Oracle Reports advanced distribution capability to burst and distribute a single report to multiple e-mail recipients in a required format. Using themail element in your distribution XML file, you can specify many additional e-mail options. For more information, see the chapter "Creating Advanced Distributions" in Oracle Application Server Reports Services Publishing Reports to the Web. |
This section discusses some of the errors or setup issues that you may encounter when performing the steps in this chapter. For additional troubleshooting information, refer to the appendix "Troubleshooting" in Oracle Application Server Reports Services Publishing Reports to the Web and to the Oracle Reports online Help for error messages, available in Reports Builder and on the Oracle Reports 10g page on the Oracle Technology Network (http://www.oracle.com/technology/products/reports/index.html).
|
Note: The recommended way to troubleshoot report issues is to enable tracing and the engine diagnosis option. For more information, refer to the chapter "Configuring OracleAS Reports Services" in Oracle Application Server Reports Services Publishing Reports to the Web. |
If you encounter any of the following errors while performing the steps in the chapter, the information in this section should help you resolve them:
REP-51002: Bind to Reports Server failed
Cause: Reports Servlet (rwservlet) is not able to locate Reports Server.
Action: This error can occur for a number of reasons. Any of the following actions may resolve the error:
If you are connected to the network through VPN or if a firewall is used, then the Oracle Reports built-in broadcast mechanism for Reports Server discovery will not work. In this case, you must configure the discovery mechanism to use the Common Object Service (COS) naming service orbd, provided by Sun Microsystems' JDK. For details on using the COS naming service, refer to the chapter "Configuring OracleAS Reports Services" in Oracle Application Server Reports Services Publishing Reports to the Web.
If you are using the Oracle Reports in-process server, check that SERVER_IN_PROCESS=YES in the servlet configuration file (ORACLE_HOME\reports\conf\rwservlet.properties).
If you are using a standalone server, check if the server is running through Oracle Enterprise Manager 11.
If the Oracle Reports built-in broadcast mechanism for Reports Server discovery (multicast) is used, find out how much time it takes to locate a Reports Server in your network using the rwdiag utility. Reports Servlet (rwservlet) may be timing out while locating the Reports Server. This scenario is most likely to occur when the Reports Server is running on a different network subnet.
For example:
$ rwdiag.sh -find server_name server_name found in the network Time taken - 101 millisecond
If the time taken is more than (timeout * retry) values in your rwnetwork.conf file, then increase the timeout value in rwnetwork.conf and restart your Reports Server and OC4J_BI_Forms instance (or standalone OC4J in Oracle Developer Suite installations). This scenario is more likely to occur when Reports Server is running on a different network subnet.
REP-110: Unable to open file 'report_name'
Cause: Reports Server is not able to locate the report definition file.
Action: Modify REPORTS_PATH to include the folder that contains your report definition file.
For example, suppose c:\myreports contains your report definition files:
REPORTS_PATH=c:\myreports;C:\OraHome_5\reports\templates...
REP-56048: Engine rwEng-0 crashed
Cause: This error occurs if memory allocated for the engine process (JVM) is not sufficient to generate spreadsheet output. If your spreadsheet report contains a large number of pages (several hundreds of pages), then default heap memory allocated is not sufficient to generate the report.
Action: Increase the heap memory for the engine process (JVM) by setting the jvmOptions attribute of the engine element in the server configuration file (ORACLE_HOME\reports\conf\server_name.conf). For example:
<engine id="rwEng" jvmOptions="-Xmx512M" class="oracle.reports.engine.EngineImpl"...
For more details on setting the jvmOptions attribute, refer to the chapter "Configuring OracleAS Reports Services" in Oracle Application Server Reports Services Publishing Reports to the Web.
|
Note: The Reports engine may fail for a number of reasons. If an engine or job in Reports Server fails or stops responding, then the recommended solution is to set theengineResponseTimeout attribute of the engine element in the server configuration file (ORACLE_HOME\reports\conf\server_name.conf), as described in the chapter "Configuring OracleAS Reports Services" in Oracle Application Server Reports Services Publishing Reports to the Web. |
REP-50159: Executed successfully but there were some errors when distributing the output
This error can occur for a number of reasons when sending report output to an e-mail destination.
Cause 1: Mail server is not configured properly.
Action 1: Configure the mail server in the server configuration file (ORACLE_HOME\reports\conf\server_name.conf) and the Reports Builder configuration file (ORACLE_HOME\reports\conf\rwbuilder.conf). The pluginParam element should specify the outgoing SMTP server name. For example:
<pluginParam name="mailServer">smtpserver.mycompany.com</pluginParam>
For more details, refer to the chapter "Configuring OracleAS Reports Services" in Oracle Application Server Reports Services Publishing Reports to the Web.
Cause 2: The mail server is not responding or is not up and running.
Action 2: Check if the mail server is up and running and is responding in a timely manner. You can use your Microsoft Outlook client to connect to the mail server and check the status.
Cause 3: A valid recipient e-mail address is not specified for the desname keyword.
Action 3: Specify a valid recipient e-mail address for the desname keyword.
Cause 4: The mail server is SSL-enabled.
Action 4: Use a non-SSL mail server. Oracle Reports does not support SSL-enabled mail servers to send e-mail.
Oracle Reports Building Reports
Oracle Application Server Reports Services Publishing Reports to the Web
Oracle Reports online Help
