XML Report by Data Template

Developing XML Publisher Report.

Using XML Template as Data Source:

Step1:  Define Data Template:The data template is the method by which you communicate your request for data to the data engine.

The data template is an XML document that consists of four basic sections:
Define parameters: In which parameters are declared in child <parameter> elements
Define triggers:
Define data query: In which the SQL queries are defined in child <sqlStatement> elements
Define data structure: In which the output XML structure is defined

<?xml version="1.0" encoding="UTF-8"?>
<dataTemplate name="demoProductsDT" description="Empdept Report" version="1.0">
   <parameters>
     <parameter name="p_deptno" datatype="number"/>
   </parameters>
<dataQuery>
 <sqlStatement name="Q1">
 <![CDATA[
    SELECT DEPTNO,DNAME,LOC
 from scott.dept where DEPTNO=:p_deptno]]>
 </sqlStatement>
 <sqlStatement name="Q2">
  <![CDATA[
     SELECT  DEPTNO, EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,nvl(COMM,0) COMM
     from scott.EMP
 where DEPTNO = :p_deptno ]]>
 </sqlStatement>
</dataQuery>
  <dataStructure>
    <group name="G_DP" source="Q1">
      <element name="DEPTNO" value="DEPTNO"/>
      <element name="DNAME" value="DNAME"/>
    </group>

 <group name="G_DP1" source="Q2">
      <element name="EMPNO" value="EMPNO"/>
      <element name="ENAME" value="ENAME"/>
    </group>
  </dataStructure>
</dataTemplate>

-------  Save the above code of data template in xml format like "Empdept.xml"

Step2:  Create Data Definition & Associate with Data Template

  2-1 : Create Data Definition
       Enter the data definition Details and click on Apply. Note down the Code.
       The code should be used as the short name of the concurrent program.

  2-2: Associate Data Template
       View Data Definition

Step 3:            Define a Concurrent Program to generate the Data XML output.

Note:
1. Output format should be XML
2. Short Name in the concurrent program and Code in the data definition should be same and should be in capital letters.

  3-2 : Concurrent Program - Parameters
For each parameter in the Data Template, define a parameter in the concurrent program.
The Data Template parameter name should match the concurrent program parameter token

Note:
Token is p_deptno. This is the bind parameter we have used in date template. For every bind parameter used in the data template,
we have to define parameter in the concurrent program.

  3-3 : Associate the Concurrent Program to a request group.

  3-4 : Execute the concurrent program "Empdept Report"and click on the output button get the Data XML. Save the XML file. We will use it to generate the RTF

Template.

Step 4:        Define the RTF Template using the Generated Data XML

Pre-requisite : Install XML Publisher Desktop
After installation the Add-Ins tab should appear in the MS Word Application.

  4-1 :Load XML Data generated by Concurrent Program

    Data Loaded successfully Message will appear after loading the data.

Using the Table Wizard create the template of the report with the columns of Empdept Report.

Save this file with .rtf extension

Step 5:          Registering the Template with BI Publisher

Navigation: XML Publisher Administrator -> Templates -> Create Template

Step 6:          Run the concurrent program to see the output

Note:

As already mentioned output format can be anything. Here it is pdf. We can select format that we want at the runtime.

References:
http://www.oracle.com/technology/products/xml-publisher/index.htm
http://www.oracle.com/technetwork/middleware/bi-publisher/overview/index.html
http://xdo.us.oracle.com
Oracle® XML Publisher Administration and Developer's Guide