... even without using XML Publisher!

Excel 2003 (and higher) has the possibility to open XML files immediately using the report's plain column structure. So if your customer is not using XML Publisher, or you simply want a quick workaround, it is possible to open the Oracle output directly into Excel 2003 (and higher).

Finance department usually work extensively with Microsoft Excel ... and even though we are implementing Oracle, the question of publishing in Excel usually pops up sooner or later. In general, the answer is: yes, it is possible by creating an XML Publisher template (see How to create XML Publisher templates from Standard Reports). But recently I found out that Excel 2003 imports XML files directly, whereas the structure of the report keeps maintained. There are a few steps to follow to achieve this.

1. Create new Viewer Option

Navigate to System Administrator > Install > Viewer Options. Add a new entry.

File Format: XML
Mime Type: application/vnd.ms-excel
Description: XML in Microsoft Excel
Allow Native Client Encoding: Yes

2. Set Profile Options

Query profile options: Viewer% on the appropriate levels (site, user, ...)

Set profile Viewer: Text to Browser
Set profile Viewer: Application for XML to XML in Microsoft Excel

3. Copy Concurrent Request

In order to use both the Standard Report and the Standard Report in Excel, the best solution is to copy the concurrent request definition, and suffix the new request name with (Excel).

Navigate to System Adminstrator > Concurrent > Program > Define

Query the report. In this exampel I will use ARXAGE (Aging - 4 Buckets Report)

Press button Copy to ...

Program: Aging - 4 Buckets Report (Excel)
Short Name: ARXAGE_XML
Application: Receivables
Include Incompatible Programs: Yes
Include Parameters: Yes

Press button OK.

Change the Output Format from Text to XML and save the record (ignore the warning).

4. Add Concurrent Program to Request Group

Navigate to System Administrator > Security > Responsibility > Request

Query the appropriate group(s): Receivables All

Add Aging - 4 Buckets Report (Excel) as concurrent program in this group and save.

5. Run the report

Run the Aging - 4 Buckets Report (Excel) as usual. The output will appear directly in Excel.

This funcitionality only works for Excel 2003 and higher

Excel has a limit of 255 columns. If the dataset within the report selects more than 255 fields, the remainder will be cut during the import