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
op Tuesday 01 December 2009 - 20:15:25
Note: You should follow Oracle® BI Publisher Report Designer's Guide (section “Creating an RTF Template”).
How to add markup to the template layout using Microsoft Excel
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 PlaceholdersHow to define GroupsHow to regroup the XML DataHow to define If statementsHow to define Choose statementsHow to use Extended SQL and XSL FunctionsHow to define SortingHow to use VariablesHow to define Dynamically Worksheets based on the Dataset
op Thursday 19 November 2009 - 16:08:38
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.
op Tuesday 17 November 2009 - 20:13:45
How to convert Excel XML spreadsheet into XSL template
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”.
op Sunday 15 November 2009 - 14:28:53
Monday 22 March 2010 - 07:44:22