Create XML Report By using Data Template

Oracle is planning to remove Oracle Reports from the tech stack in the fusion timeframe.

New extraction engine needs to extract millions of rows to build tens of thousands of pages for some reports.

The data templates are useful when you need to create a XML Publisher report without using the RDF. The XML Publisher data engine enables you to rapidly generate any kind of XML data structure against any database in a scalable, efficient manner which you can easily use in your templates.

The data template is the method by which you communicate your request for data to the data engine. It is an XML document whose elements collectively define how the data engine will process the template to generate the XML

Data Template benefits:

Data Templates performance is better than Oracle Reports

Data Template allows:

 Multiple Queries/Joins

 Event Triggers (Currently pl/sql support)

 Flex field Support

 Formula/ Summary columns/ Placeholder columns

 Data Structure

 Group filters

 Rich Java API layer - call a data template from your jsp or java app

 

XML Data Template Architecture:

<?xml version="1.0"?>

<dataTemplate name="XXEMP" defaultPackage="XX_EMP" version="1.0">

<parameters>

    <parameter name="P_DEPTNO"   dataType="NUMBER" />

</parameters>

<DataQuery>

<sqlStatement name="Q_XXEMP">

<![CDATA[

 SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP WHERE DEPTNO = :P_DEPTNO

 ]]>

</sqlStatement>

</DataQuery>

<dataTrigger name="beforeReport" source="XX_EMP.CHECKDEPTNO(:P_DEPTNO)"/>

<dataStructure>

<group name="G_XXEMP" source="Q_XXEMP">

    <element name="EMPNO"                   datatype="NUMBER"    value="EMPNO"/>

    <element name="ENAME"            datatype="VARCHAR2"    value="ENAME"/>

    <element name="SAL"          datatype="NUMBER"    value="SAL"/>

    <element name="DEPTNO"          datatype="NUMBER"    value="DEPTNO"/>

</group>

</dataStructure>

</dataTemplate>

Architecture Header Section:

<dataTemplate name="XXEMP" defaultPackage="XX_EMP" version="1.0">

Arribute Name Description Name (required) Enter the data template name Default package This attribute is required if your data template contains lexical references or any other calls to PL/SQL Version (Required) Enter version number for this data template
Architecture Parameter Section: <parameters>     <parameter name="P_DEPTNO"   dataType="NUMBER" /> </parameters>

Arribute Name	Description
Name	Required a keyword unique within a given data template that identifies the parameter
Datatype	Optional specify the parameter data type as 'character','date' or Number, Default value is 'Character'. for the 'date' data type the following three format (based on the canonical ISO date format) are supported YYYY-MM-DD(Example: 2017-10-24) YYYY-MM-DD HH24:MI:SS (example 2017-10-24 12:00:00) YYYY-MM-DDTHH24:MI:SS:FF3TZHlTZM

Architecture Dataquery Section:

<DataQuery>

<sqlStatement name="Q_XXEMP">

<![CDATA[

 SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP WHERE DEPTNO = :P_DEPTNO

 ]]>

</sqlStatement>

</DataQuery>

Arribute Name	Description
DataQuery(Required)	Consists of one or more<sqlstatment>or<xml>elements
Sqlstatment(Required)	Name(Required)-the unique query identifier. Note that this name identifier will be the same across the data template. Enter the query inside the CDATA section

Architecture Datatrigger Section:

 <dataTrigger name="beforeReport" source="XX_EMP.CHECKDEPTNO(:P_DEPTNO)"/>

Arribute Name	Description
Datatrigger	Name(required)-the event name to fire this trigger Source(Required)-the PL/SQL<package name>.<function name>

Architecture DataStructure Section:

Note: Parent Group included to give more clarification

<dataStructure>

<group name="G_XXDEPT" source="Q_XXDEPT">
 <element name="DEPTNO"                   datatype="NUMBER"    value="DEPTNO"/>

   <group name="G_XXEMP" source="Q_XXEMP">

    <element name="EMPNO"                   datatype="NUMBER"    value="EMPNO"/>

    <element name="ENAME"            datatype="VARCHAR2"    value="ENAME"/>

    <element name="SAL"          datatype="NUMBER"    value="SAL"/>

    <element name="DEPTNO"          datatype="NUMBER"    value="DEPTNO"/>

</group>
</group>

</dataStructure>

Arribute Name	Description
Parent Group	Group name G_XXDEPT and source as Q_XXDEPT it is Same Query Source
Child Group	Group name G_XXEMP and source as Q_XXEMP  it is Same Query Source
Element	Element name EMPNO you can define as you wish but values should be  same as data source query column name EMPNO

Joining multiple queries in Data template:

We can join two ways 

        1. By using data link
        2. By using column 

1. By using data link:

By following tags we can give link two queries 

<link name="DE_LINK" parentQuery="Q_EMPNO" parentColumn="DEPTNO" childQuery="Q_DEPTNO" childColumn="DEPTNO"/>

  

Sample Data Template Architecture with Multiple queries:

<dataQuery>

 <sqlStatement name="Q_DEPT">

 <![CDATA[

    SELECT DEPTNO,DNAME,LOC from dept

   ]]>

 </sqlStatement>

 <sqlStatement name="Q_EMP">

  <![CDATA[

     SELECT  DEPTNO, EMPNO,ENAME,JOB,MGR,HIREDATE,SAL, COMM

     from EMP ]]>

 </sqlStatement>

<link name="DE_LINK" parentQuery="Q_EMPNO" parentColumn="DEPTNO" childQuery="Q_DEPTNO" childColumn="DEPTNO"/>

</dataQuery>

  
2. By using column: 

<dataQuery>

 <sqlStatement name="Q_DEPT">

 <![CDATA[

    SELECT DEPTNO,DNAME,LOC from dept

   ]]>

 </sqlStatement>

 <sqlStatement name="Q_EMP">

  <![CDATA[

     SELECT  DEPTNO, EMPNO,ENAME,JOB,MGR,HIREDATE,SAL, COMM

     from EMP WHERE DEPTNO=: DEPTNO]]>

 </sqlStatement>

</dataQuery>

Create sample Data Template:

Steps to create data template

1.     Create Data Template

2.     Register Concurrent Program

3.     Register Data Definition

4.     Generate XML Data

5.     Create Layout Template

6.     Publish the Report

1.     Create Data Template

2.     Register Concurrent Program

Select Executable “XDODTEXE” to process data template

Select Output type as “XML”

3. Create Data Definition

Create Data Definition and upload Data Template file by using “Add File” button

Make sure that concurrent program short name and Data definition Code is same

4. Generate XML Data

Once Create data Definition you can run the concurrent program to get xml tags

based on xml tags create Layout of report and register as shown below

5.     Create Template

6. Publish the Report

Now you can run the program to output 

Data Template Triggers:

Data triggers execute a PL/SQL functions at specific time during the execution and generation of XML Data

Arribute Name	Description
Name (Required)	Event name to fire a trigger
Source (Required)	PL/SQL <package name>.<function name>

<dataTrigger name="beforeReport” source="employee.beforeReport()"/>

<dataTrigger name=”afterReport” source="employee.afterReport()"/>

Data Template Trigger Example:

Data Template with Lexical References:

  

Use lexical references to replace the clauses appearing after SELECT, FROM,WHERE, GROUP BY, ORDER BY and HAVING clauses

SQL Group Functions in Data Template:

Using XML element Function attribute we can create group functions.

Data template supports following group functions

<element name="DEPTSAL" value="G_EMP.SALARY" function="SUM()"/>

Arribute Name	Description
Name	The XML tag name to be used in xml data output
Source	The name of the column that contains the data on which the summary calculation is to be performed. The source remains unchanged
Function	The aggregation function to be performed. The type tells the XDO Engine how to compute the summary column values. Valid Values are :- COUNT(), SUM(), AVG(), MAX() andMIN()

 

Example:

Formula Column and Place Holder:

Requirement – Report need column segment1 and description in Report Output.

Technical Solution (RDF)– 

Create one Place Holder Column.

Create Report Formula Column which will return Segment1 information and

Assign Description into Place Holder Column.

Technical Solution (Data Template)–

Place Holder Variable - Create one Database Package Variable.

Place Holder - Create Database Function which will return Package Variable to calling Program.

Formula Column – Create Database Function which will assign value to Place Holder Package Variable.

SQL Query will call Database Function (Formula Column) and Database Function ( Formula Column) to return both the value to Report.

 

Call the Package.function in SQL Query