KentW Limited

My notes for making a simple Excel report in XML Publisher without the need for a developer.

I know it is now called BI Publisher but within E-Business Suite it is mostly referred to as XML Publisher.

I feel the need for this note as there are plenty of blogs just showing examples from the examples supplied by Oracle without really understanding how it works (or why it does not work).

The example below has been tested on a R12.2.9 instance.

Some overall rules:

• Install the BI Publisher add-in as per instructions below
• Use .xls Excel 2003 format only
• Only use the Excel add-in for adding fields and doing preview

BI Publisher Report Process

Overview:

1. Develop and test SQL
2. Create Data Template
3. Register Data Definition
4. Register Concurrent Program
5. Produce Preview XML Data
6. Create Template File in Excel
7. Register Data Template
8. Test Final Report

Develop and Test SQL

Rules:

• Keep it simple
• Use column names in select

Create SQL and ensure it works, something like this:

select user_name,description,start_date,email_address
from fnd_user
where 1=1
and end_date is null
and encrypted_user_password !='INVALID'
;

This should yield something like this:

Create Data Template

Rules:

• Data template name should match what you want to register it as
• Always have at least one group
• Paste your SQL into the dataQuery CDATA field and remove the “;” if any
• List all fields in “dataStructure” tag
• Save file as {data template name}.xml
• If you use parameters then note they are case sensitive
• Some best practices to avoid mixing up fields later on:
  • Parameters: start with “P_”
  • Column aliases starts with: “T_” or “A_”
  • Elements starts with: “E_”
  • SQL Statement name: starts with “Q_”
  • Group name: starts with “G_”

<?xml version="1.0" encoding="WINDOWS-1252" ?>
<dataTemplate name="XXKWI_TEST" description="Test Report" Version="1.0">
	<dataQuery>
		<sqlStatement name="Q_USER">
			<![CDATA[
				select user_name t_user_name,description t_description,start_date t_start_date,email_address t_email_address
				from fnd_user
				where 1=1
				and end_date is null
				and encrypted_user_password !='INVALID'
			]]>
		</sqlStatement>
	</dataQuery>
	<dataStructure>
		<group name="G_USER" source="Q_USER">
			<element name="E_USER_NAME" value="T_USER_NAME" />
			<element name="E_DESCRIPTION" value="T_DESCRIPTION" />
			<element name="E_START_DATE" value="T_START_DATE" />
			<element name="E_EMAIL_ADDRESS" value="T_EMAIL_ADDRESS" />
		</group>
	</dataStructure>
</dataTemplate>

Example section with parameters:

<?xml version="1.0" encoding="WINDOWS-1252" ?>
<dataTemplate name="XXKWI_IF_TRX" description="Transaction Errors" Version="1.0">
	<parameters>
		<parameter name="P_OBJECT_NAME" dataType="character" />
		<parameter name="P_OBJECT_ID" dataType="number" />
	</parameters>
...

Register Data Definition

Rules:

• Ensure you have access to responsibility XML Publisher Administrator
• Navigate \home\Data Definitions
• Use name same as data template name above

The new entry should look like this:

Register Concurrent Program

This enables us to run the above SQL code in the concurrent manager.

Rules:

• Login with responsibility System Administrator
• Navigate \Concurrent\Program\Define
• Ensure Short Name is same as Data Definition Name
• XDODTEXE is the XDO XML processor

Produce Preview XML Data

We will need some example XML data to create the output template that converts raw XML data to Excel format.

Rules:

• Navigate \View\Requests
• Submit the {user friendly name} from above
• Save the output as a .xml file like xxkwi_test_output.xml
• Check the output contains the required fields and groups

Result should look something like this:

<?xml version="1.0" encoding="UTF-8"?>
<XXKWI_TEST>
<LIST_G_USER>
<G_USER>
<E_USER_NAME>SYSADMIN</E_USER_NAME>
<E_DESCRIPTION>System Administrator</E_DESCRIPTION>
<E_START_DATE>1951-01-01T00:00:00.000-05:00</E_START_DATE>
<E_EMAIL_ADDRESS/>
</G_USER>
<G_USER>
<E_USER_NAME>CBROWN</E_USER_NAME>
<E_DESCRIPTION>Casey Brown</E_DESCRIPTION>
<E_START_DATE>1995-02-19T00:00:00.000-05:00</E_START_DATE>
<E_EMAIL_ADDRESS>nobody@localhost</E_EMAIL_ADDRESS>
</G_USER>
</LIST_G_USER>
</XXKWI_TEST>

Save the file as a XML file.

Create Template File in Excel

Now we have the data XML file we can create the template.

Rules:

• Only use Excel .xls 2003 format
• Keep it simple
• Avoid formatting using the BI Publisher
• Report output can later the formatted using Excel alone

The following steps must be done:

1. Upload sample XML output
2. Create headings
3. Create field names
4. Create groups
5. Preview output

Upload sample XML output:

• Add your XML output to the Sample XML:

Create headings:

• Type headings for example in A1

Create field names (automatic method):

• Click field:

• Click on A2 and add the data field you want
• BI Publisher will then create the hidden XDO_METADATA sheet:

• Select field to create:

• Ensure the field is having a name like: XDO_?XDOFIELD1?
• This is translated in the in the XDO_METADATA sheet to <?E_USER_NAME?>

• Try to click “Names” to verify BI Publisher created a name for this field:

Create field names (manual method):

I prefer this as BI Publisher very easily garble the XDO_METADATA sheet and named ranges.

• Do not mix manual and automated field names
• Create the sheet XDO_METADATA manually
• This sheet should not have any data below “Data Constraints:”
• When done hide the sheet

• Click on A2 and enter example data like SYSADMIN
• Click names (name manager)
• Add a name like XDO_?{field_name}?

• The above example should look like this: XDO_?E_USER_NAME?
• “E_USER_NAME” matches the field name used in the XML template, hence why we need naming standards
• Click OK

Create Groups:

BI Publisher add-in does not handle groups well so only do this manually.

• You must have at least one group to create vertical reports
• If the group is missing the preview will show data horizontally
• Click on A2
• If you have more than one field select the full range like A2:B2 but exclude the heading at A1:B1
• If you display parameters then ensure the group excludes these as well
• Click names (name manager) and new
• Enter a group name like XDO_GROUP_?{group_name}?
• This name must match the name in the XML template, hence the need for naming standards

• The above name should look like this: XDO_GROUP_?G_USER?
• G_USER matches the group tab in the XML template
• Name manager should now look like:

Preview output:

• Click Preview

• Expected output:

• If you get errors at this point check all your settings and in worst case clear the template as described below and start all over

Register Data Template

Rules:

• Ensure you have access to responsibility XML Publisher Administrator
• Navigate \home\Templates
• Name should be same as previous name

Upload your Excel .xls template:

Test Final Report

Run the program:

Run the concurrent program and verify the output by clicking “Open”:

Should yield something like this:

All done and it works.

Issues

Excel add-in issue overview

• Only works with Excel 2003 format .xls
• It is not possible to use the xmlpserver login on R12.2.9 Vision instance (if you know please tell me how)
• Adding groups using the add-in easily destroys your template; so do it manually
• The add-in install is dodgy at best (see below)
• Strange preview output when XDO_METADATA or Excel names are garbled

How to clean-up the Excel sheets

BI Publisher add-in often garbles both the XDO_METADATA sheet and the named ranges:

• Delete all XDO related names like the XDO_?XDOFIELD1? name below:

• Unhide the XDO_METADATA sheet and delete all rows under “Data Constraints:”, like the row with XDO_?XDOFIELD1? and <?USER_NAME?> below

Excel Preview all horizontal

If you are missing a group, it looks like this; where data is rendered horizontally:

Excel add-in install

This is most of what I went through to install the add-in (some of the issues below are addressed in Doc ID 2106715.1):

1. Download from: https://www.oracle.com/middleware/technologies/bi-publisher/downloads.html
2. Ensure to download “BIPublisherDesktop32.exe” for a 32-bit Microsoft Office Installation, the one you should use anyway to be compatible with WebADI
3. Right-click and select “Run as Administrator” otherwise the installation fails
4. Once the installations is complete CD to the directory: C:\Program Files (x86)\Oracle\BI Publisher\BI Publisher Desktop
5. CD to subdirectory: DotNetInstallFiles
6. Double-click on ETBAddInSetup to install 32-bit Excel add-in
7. Once installed go to Start Menu and click: Enable or Disable Template Builder
8. First click OK on:
   
9. Then click OK on:
   
10. Run it again and click on Button1:
    
11. Click on Button1 again:
    
12. Now try and open Excel and look for the “BI Publisher” ribbon:
    
13. If it is still missing then try to go to options add-ins:
    
14. If present then select Manage COM add-ins and click Go…:
    
15. Uncheck the BI Publisher add-in and click OK:
    
16. Go back to options and manage add-ins and re-check the add-in and click OK:
    
17. That should do the trick otherwise retry from start as it is easy to miss a step
18. To use the Oracle supplied examples copy the following to a local directory: Template Builder for Word\samples\Excel templates
19. Install Java jre 8.x and add this to the options:

Excel add-in shows “Preview Failed”

This is caused by using Excel .xlsx format. Save the document as .xls and retry.

After clicking:

you get this:

Start Excel Preview
only open: false
mTemplate: C:\Users\Kent\AppData\Local\Oracle\BIPublisher\TemplateBuilderforExcel\tmp\tmp\tmp.xls
mTmpTemplate: C:\Users\Kent\AppData\Local\Oracle\BIPublisher\TemplateBuilderforExcel\tmp/tmp.xls
java.lang.NullPointerException
	at oracle.xdo.excel.user.ximpl.XCellImpl.setStyle(XCellImpl.java:65)
	at oracle.xdo.template.excel.render.BookDataWriter.copyCell(BookDataWriter.java:743)
	at oracle.xdo.template.excel.render.BookDataWriter.finishCell(BookDataWriter.java:696)
	at oracle.xdo.template.excel.render.CellHandler.endElement(CellHandler.java:111)
	at oracle.xdo.template.excel.render.BookDataParser.endElement(BookDataParser.java:215)
	at oracle.xml.parser.v2.XMLContentHandler.endElement(XMLContentHandler.java:211)
	at oracle.xml.parser.v2.NonValidatingParser.parseElement(NonValidatingParser.java:1303)
	at oracle.xml.parser.v2.NonValidatingParser.parseRootElement(NonValidatingParser.java:340)
	at oracle.xml.parser.v2.NonValidatingParser.parseDocument(NonValidatingParser.java:307)
	at oracle.xml.parser.v2.XMLParser.parse(XMLParser.java:298)
	at oracle.xdo.template.excel.render.BookDataParser.parserXMLDocument(BookDataParser.java:146)
	at oracle.xdo.template.excel.ExcelController.processActionLanguage(ExcelController.java:415)
	at oracle.xdo.template.excel.ExcelController.process(ExcelController.java:268)
	at oracle.xdo.template.ExcelProcessor.process(ExcelProcessor.java:244)
	at ExcelPreview.runXDO(ExcelPreview.java:110)
	at ExcelPreview.main(ExcelPreview.java:87)

Comments are closed.