Xls Pe How To Tutorials Tips & Tricks

XLS Processor Engine for Oracle® BI Publisher How To Tutorials/ Tips & Tricks

Introduction Oracle BI Publisher (formerly XML Publisher) by Oracle Corporation (the part of Oracle EBusiness Suite technological stack) is a multifunctional report designer. Unfortunately, this product has got some functional limitations. For instance, 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 shortcomings of this method: ● ● ● ● ●

It’s impossible to use existing XLS 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 Microsoft Word template on separately tabsheets. ou 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 shortcomings will be considered in detail afterwards. So we’ve come to the conclusion: the reviewed shortcomings practically disable the design of complex Excel reports by Oracle BI Publisher. 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 And publish reports based on these templates using Microsoft Excel

Solution path 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. XML Publisher uses XSL-XML 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. 2. 3. 4. 5. 6. 7. 8.

Open Microsoft Excel XLS file (by Excel 2003 or higher). Add markup to the template layout using Oracle XML Publisher Report Designer’s Guide. Save the template as “XML Spreadsheet”. Convert the XML template into the XSL-XML template. Upload the XSL-XML template into Oracle XML Publisher. Set up a Viewer Options for Oracle e-Business Suite. Launch report generating and publishing process. View the output report via Microsoft Excel.

Let’s examine each operation in detail below.

How to add markup to the template 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. Notice: You should follow Oracle XML Publisher Report Designer's Guide[/link] (section “Creating an RTF Template”). Then save the finished template as “XML Spreadsheet”.

How to convert XML template into XSL-XML template Try to use our XLS Processor Engine for Oracle® BI Publisher utility to convert XML template into XSL-XML 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 14 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-XML template in source directory. The name of XSL-XML template is equal to the source file and the extension is “XSL”.

How to upload XSL-XML template to Oracle® BI Publisher You should choose template type XSL-XML when you upload your template by Template Manager

How to set up Viewer Options for Oracle e-Business Suite You will need set up Viewer Options correctly via System Administrator responsibility to view published reports properly. Step 1 (Install –> Viewer Options) You’ll need to create a new record for File Format XML (Mime Type = application/vnd.ms-excel, Description = Microsoft Excel)

Step 2 (Profile -> System) You’ll also need to set the value “Microsoft Excel” for the System Profile Option “Viewer: Application for XML” and set the value “Browser” for the System Profile Option “Viewer: Text”

How to create Placeholders XLS Processor Engine for Oracle BI Publisher converts the formatting that you apply in your spreadsheet processing application to XSL-XML. You add markup to create the mapping between your layout and the XML file and to include features that cannot be represented directly in your format. The most basic markup elements are Placeholders, to define the XML data elements; and Groups, to define the repeating elements. BI Publisher provides tags to add markup to your template. Creating Placeholders The Placeholder maps the template field to the XML element data field. At runtime the Placeholder is replaced by the value of the element of the same name in the XML data file. XLS Processor Engine for Oracle BI Publisher support only basic method to parse Placeholders. Enter the Placeholder syntax in your document where you want the XML data value to appear. Enter the element's XML tag name using the syntax: Note: The placeholder must match the XML element tag name exactly. It is case sensitive. In the example, the template field "Full Name" maps to the XML element FULL_NAME. In your document, enter: The entry in the template is shown in the following figure (download the example):

How to define Groups By defining a group, you are notifying BI Publisher that for each occurrence of an element, you want the included fields displayed. At runtime, BI Publisher will loop through the occurrences of the element and display the fields each time. To designate a group of repeating fields, insert the grouping tags around the elements to repeat. Insert the following tag before the first element: Insert the following tag after the final element: Grouping scenarios Note that the group element must be a parent of the repeating elements in the XML input file. • • • • •

If you insert the grouping tags around text or formatting elements, the text and formatting elements between the group tags will be repeated. If you insert the tags around a table, the table will be repeated. If you insert the tags around text in a table cell, the text in the table cell between the tags will be repeated. If you insert the tags around two different table cells, but in the same table row, the single row will be repeated. If you insert the tags around two different table rows, the rows between the tags will be repeated (this does not include the row that contains the "end group" tag).

Defining Groups XLS Processor Engine for Oracle BI Publisher support only basic method to parse Groups. Enter the tags in your document to define the beginning and end of the repeating element group. To create the "Year of birth" group in the example, insert the tag before the "Year of birth" field that you previously created. Insert in the document after the summary row.

The following figure shows the "Employee breakdown by year of birth" with the basic Grouping and Placeholder markup (download the example):

How to define If statements Use an If statement to define a simple condition; for example, if a data field is a specific value. 1. Insert the following syntax to designate the beginning of the conditional area: 2. Insert the following syntax at the end of the conditional area: For example, to set up the "Employee breakdown by year of birth" to display emloyees only when the year of birth is more that 1970, insert the syntax 1970?> before the YEAR_OF_BIRTH field on the template. Enter the tag after the emloyees table. This example is displayed in the figure below (download the example). Note that you can't insert the syntax in form fields, only directly into the template:

If Statements in Boilerplate Text Assume you want to incorporate an If statement into the following free-form text: The program was (not) successful. You only want the "not" to display if the value of an XML tag called equals "N". To achieve this requirement, you must use the BI Publisher context command to place the if statement into the inline sequence rather than into the block (the default placement).

If you construct the code as follows: The program was not successful. The following undesirable result will occur The program was not successful. because BI Publisher applies the instructions to the block by default. To specify that the if statement should be inserted into the inline sequence, enter the following: The program was not successful. For example, to display in cell 'No Number' message when the employee number is empty, insert the syntax No Number before the EMPLOYEE_NUMBER field on the template. This example is displayed in the figure below (download the example):

How to define Choose statements Use the choose, when, and otherwise elements to express multiple conditional tests. If certain conditions are met in the incoming XML data then specific sections of the template will be rendered. This is a very powerful feature. In regular XSL programming, if a condition is met in the choose command then further XSL code is executed. In the template, however, you can actually use visual widgets in the conditional flow (in the following example, a cell borders). Use the following syntax for these elements: This example is displayed in the figure below (download the example). Note that you can't insert the syntax in form fields, only directly into the template:

Choose Statements in Boilerplate Text Assume you want to incorporate an Choose statement into the following free-form text. For example, to display in cell 'No Number' message when the employee number is empty, insert the syntax No Number

This example is displayed in the figure below (download the example):

How to use Extended SQL and XSL Functions BI Publisher has extended a set of SQL and XSL functions for use in templates. The syntax for these extended functions is for extended SQL functions or for extended XSL functions. Note: Using XLS Processor Engine for Oracle® BI Publisher you CAN mix xdofx and xdoxslt statements with XSL expressions in the same context. For example, assume you had two elements, FULL_NAME and EMPLOYEE_NUMBER, that you wanted to concatenate into a 40-character field and right pad the field with the character "x". You CAN use the following: because concat is an XSL expression. Also, you CAN use the following: Certainly, you CAN also use the following: This example is displayed in the figure below (download the example):

How to define Sorting You can sort a group by expression of any element within the group. Insert the following syntax within the group tags: For example, to sort the Employee breakdown by Employee Number (EMPLOYEE_NUMBER), enter the following after the tag: To sort a group by multiple fields, just insert the sort syntax after the primary sort field. To sort by Full Name and then by Employee Number, enter the following This example is displayed in the figure below (download the example):