How to create a report with BI Publisher using a data template

In my current project but also in previous ones I make use of the BI (XML) Publisher functionality of Oracle E-Business Suite. Using this you’re able to migrate your own created SQL queries into a nice report which an end-user can run himself including custom parameters. The report can, for example, generate an XML file which can be opened with Microsoft Excel.

I’ll explain in this article how you can use this functionality. In later posts I’ll explain the other nice features of BI Publisher.

First we need to have s SQL query which you want to migrate to BI Publisher. Let’s start with a simple query which generates a list of application user names together with the name of the assigned employee.

The SQL I use is:

SELECT
    fuser.USER_NAME
    , per.FULL_NAME EMPLOYEE
FROM
    APPLSYS.FND_USER fuser
    , APPS.PER_PEOPLE_F per
WHERE
    fuser.EMPLOYEE_ID = per.person_id
    AND SYSDATE BETWEEN per.EFFECTIVE_START_DATE AND per.EFFECTIVE_END_DATE

The first thing you need to do is to create a so called Data Template. A data template is a simple xml file with a defined number of elements.

A basic data template containts the dataTemplate element where you define the name of your template and a description. The next element set is the parameters section. In this section your define the names, datatypes and possible default values for parameters used in your query. You define these parameters later also in the concurrent program definition. The dataQuery block contains the sqlStatement block where you define the SQL query (in conjunction with any parameters). Optionally your are able to run some trigger at a stage of the data generation (quite handy sometimes :-)). The dataStructure block defines the output columns of the data. When defining the xml file always make sure you close every element block.

What we want to do with our SQL query is provide the user the option to query on a user name while running the report so we’ll add one parameter to it.

Let’s build up the XML file. First we define the XML tag and the dataTemplate tag.

<?xml version="1.0"?>
<dataTemplate name="XXX_EXAMPLE" description="Example Report" Version="1.0">

Next step I’ll add the optional properties block. By default used parameters are also in the output of the resulting data file. I want to exclude those parameters in the output and add the below part to my XML file.

<properties>
   <property name="include_parameters" value="false" /> 
</properties>

I want to use one parameter in my report being the user name. The user must be able to use a wildcard to query his results (use a LIKE). I’ll add the below parameter block.

<parameters>
<parameter name="p_USER_NAME" dataType="character" />
</parameters>

After this I need to add my SQL query to the XML file within the dataQuery and sqlStatement blocks. Provide a short name to the sqlStatement within the name variable. I’ll add an additional where clause to my statement and reference my earlier defined parameter with a : before it.

<dataQuery>
<sqlStatement name="EX">
<![CDATA[SELECT
    fuser.USER_NAME
    , per.FULL_NAME EMPLOYEE
FROM
    APPLSYS.FND_USER fuser
    , APPS.PER_PEOPLE_F per
WHERE
    fuser.EMPLOYEE_ID = per.person_id
    AND SYSDATE BETWEEN per.EFFECTIVE_START_DATE AND per.EFFECTIVE_END_DATE
    AND fuser.USER_NAME LIKE decode(:p_USER_NAME,null,'%',:p_USER_NAME)
]]>
</sqlStatement>
</dataQuery>

Next I’ll define which data to output in the resulting file. The dataStructure element is opened with a group element inside it. Define a name for the group (this will be a element in the resulting XML file) and set the source of the group. The source value must contain the name of the earlier defined sqlStatement element. I’ll output the user name and the employee column from my statement. The name variable will define the name of the resulting XML element in the data XML file.

<dataStructure>
<group name="G_EX" source="EX">
<element name="USER_NAME" value="USER_NAME" />
<element name="EMPLOYEE" value="EMPLOYEE" />
</group>
</dataStructure>

As the last step we need to close the dataTemplate tag and save the XML file.

</dataTemplate>

We open E-Business Suite and go to responsibility XML Publisher Administrator.

Click on Create Data Definition and provide the required details. Remember or write down the code value you’re using. Click on Apply.

Select your data template XML file by clicking on the Add File button after Data Template.

We’re done now in BI Publisher and are ready to define the concurrent program which will run the data template you’ve just defined. Go to System Administrator – Concurrent – Program – Define.

When defining the concurrent it’s important to set the Short Name of the concurrent program to be the same as the code you’ve given to the Data Definition in BI Publisher. (You remember it or have written it down right? :-)) The concurrent manager will assign the data template to the concurrent program when started. Also important is to use executable XDODTEXE. This is the executable which is able to run your defined data template. I want an XML file as output so change the Output Format to XML.

I’ll add the parameter I defined in my data template so click on Parameters. As the Token of the parameter use the name you defined in your data template xml file.

You’re done with setting up your report. Add the concurrent program to the required Request Group and start your concurrent program. The concurrent manager will run your data template and will pass the given parameter. Your result will be a well formatted XML file with the result of the SQL query.

Tell me what you think and your experiences with using this BI Publisher functionality through the below comment form.