Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Sunday, 10 September 2017

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
 

Data Template Architecture


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




 Create Data Template




2.     Register Concurrent Program



Select Executable “XDODTEXE” to process data template
Select Output type as “XML”



Register Concurrent Program with data template




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


Create Data Definition



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

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 Trigger Example


Data Template with Lexical References:
  
Data Template with Lexical References:

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




Data Template with Lexical References1






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:

SQL Group Functions in Data Template



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.



Formula Column and Place Holder2
Call the Package.function in SQL Query

Formula Column and Place Holder4
 

Share this article :

0 comments:

Post a Comment