Oracle® BI Publisher (formerly Oracle® XML Publisher) is a multifunctional report solution. Unfortunately, this product has got some functional limitations. For instance, in versions before 10.1.3.4.1 you cannot design Microsoft Excel templates and create reports based on these templates.

Of course, you can design RTF template using Microsoft Word and publish report in Microsoft Excel based on this template, but there are some limitations of this method:

• It’s impossible to use existing Excel templates. You ALWAYS MUST design RTF template. You can’t import existing spreadsheet from Excel to Word.
  
• There is a tabs limitation. It’s impossible to publish report using RTF template on separately tabsheets.
  
• You cannot create a wide spreadsheet by RTF template if the number of columns is more than 64.
  
• Published Microsoft Excel report differs from the designed template (especially for complex cross table reports).
  
• Output report is published in HTML format. Microsoft Excel opens such files with a considerable delay.

All of these limitations described in details here.

So we’ve come to the conclusion: the reviewed limitations practically disable the design of complex Excel reports by Oracle® BI Publisher in versions before 10.1.3.4.1. However, design of such reports is one of the largest part of Oracle® e-Business Suite report developers’ work.

So we’ve got next objectives:

• Give a possibility to design templates using Microsoft Excel
  
• Publish reports based on these templates using Microsoft Excel

Note: You should follow Oracle® BI Publisher Report Designer's Guide (section “Creating an RTF Template”).

We use for template design Microsoft Excel 2003 or higher within the framework of our solution. Load the existing multisheet template into Microsoft Excel and add markup to the template layout to create the mapping between the layout and the data source.

Then save the finished template as “XML Spreadsheet”.


How to create Placeholders
How to define Groups
How to regroup the XML Data
How to define If statements
How to define Choose statements
How to use Extended SQL and XSL Functions
How to define Sorting
How to use Variables
How to define Dynamically Worksheets based on the Dataset

The solution path is base on the following background:

1. Microsoft Excel (version 2003 and higher) supports “XML Spreadsheet” data format. This format is a XML file which contains Excel table structure and data. So this format can be used both to design and store templates and to publish reports.

2. BI Publisher uses XSL templates for publishing reports. These templatess can be generated manually or by custom software.


So we should carry out sequence of operations to create a template and publish a report:


1. Open Microsoft Excel XLS file (by Excel 2003 or higher).

2. Add markup to the template layout using Oracle BI Publisher Report Designer’s Guide.

3. Save the template as “XML Spreadsheet”.

4. Convert the XML spreadsheet into the XSL template.

5.1. Install the XSL template into Oracle BI Publisher.
-- OR --
5.2. Install the XSL template into Oracle XML Publisher for Oracle e-Business Suite.

6. Launch report generating and publishing process.

7. View the output report via Microsoft Excel.

Use our XLS Processor Engine for Oracle® BI Publisher utility to convert Excel XML spreadsheet into XSL template. Trial version of this utility is distibuted free of charge but it's got the following limitations:

• parse of the first tabsheet of template only;
• period of 21 days for the sole purpose of evaluating.

Actually, you can convert the template via single click. You can choose XML Speadsheet template by clicking Open And Process button.

At runtime the program is logging errors and warnings of conversion process (you can see it on the main program window).


As a result of the conversion there’ll be created XSL template in source directory. The name of XSL template is equal to the source file and the extension is “XSL”.