BI Publisher Enterprise Edition Data Templates

BI Publisher Enterprise Edition Data Templates
By Shahed Munir


BI Publisher EE Data Templates - If any of you are from a XML Publisher Background you will be used to creating data templates.. They are really cool if you have more than 1 query that brings back different results and want to display the results within the same report. This is real old school development...


These are the physical SQL Statement to retrieve data from the database.
sqlstatement 1- This can be any sql query
sqlstatement 2 - This can be any sql query


Within the sql statements you can join the 1st sql query and the 2nd sql query , This is used if you want for example Invoice Header and Invoice Lines but in two querys.


You will create a field in the 1st sql query with a unique name such as invoice_id1
This can then be called in the second sql query as invoice_id = :invoice_id1 , The field where you are calling for a join has to be unique..


: are used for parameters within sql querys.


The groups are required to display the fields from your sql querys above. You dont have to display the fields from the sql query if you exclude them from your groups xml.


Groups 1  - These are the fields from SQL Query 1
Groups 2 - These are the fields from SQL Query 2


Here is a sample based on Oracle Sample HR Schema.  This needs to be pasted into the Data Model and the type needs to be Data Model.


Default Package needs to be filled in if you are calling a packaged function within your datatemplate. There is loads you can do... But here is a good start...

<dataTemplate name="HR" defaultPackage="" dataSourceRef="demo">
 <properties>
  <property name="include_parameters" value="true"/>
  <property name="include_null_Element" value="true"/>
  <property name="include_rowsettag" value="false"/>
  <property name="scalable_mode" value="off"/>
  <property name="db_fetch_size" value="300"/>
 </properties>
 <parameters/>
 <lexicals/>
 <dataQuery>
  <sqlStatement name="Q1">
   <![CDATA[SELECT HIRE_DATE, EMPLOYEE_ID,FIRST_NAME,
LAST_NAME, SALARY, COMMISSION_PCT, JOB_ID,
EMAIL, PHONE_NUMBER, DEPARTMENT_ID, MANAGER_ID 
FROM EMPLOYEES]]>
  </sqlStatement>
  <sqlStatement name="Q2" dataSourceRef="">
   <![CDATA[SELECT HIRE_DATE, EMPLOYEE_ID,FIRST_NAME,
LAST_NAME, SALARY, COMMISSION_PCT, JOB_ID,
EMAIL, PHONE_NUMBER, DEPARTMENT_ID, MANAGER_ID 
FROM EMPLOYEES WHERE ROWNUM = 1]]>
  </sqlStatement>
 </dataQuery>
 <dataStructure>
  <group name="G_EMP1" source="Q1">
   <element name="EMPLOYEE_ID" value="EMPLOYEE_ID"/>
   <element name="FIRST_NAME" value="FIRST_NAME"/>
   <element name="LAST_NAME" value="LAST_NAME"/>
   <group name="G_EMPINFO" source="Q1">
    <element name="JOB_ID" value="JOB_ID"/>
    <element name="HIRE_DATE" value="HIRE_DATE"/>
    <element name="EMAIL" value="EMAIL"/>
    <element name="PHONE" value="PHONE_NUMBER"/>
    <element name="SALARY" value="SALARY"/>
    <element name="COMMISSION_PCT" value="COMMISSION_PCT"/>
    <element name="DEPARTMENT_ID" value="DEPARTMENT_ID"/>
    <element name="MANAGER_ID" value="MANAGER_ID"/>
   </group>
  </group>
  <group name="G_EMP2" source="Q2">
   <element name="EMPLOYEE_ID" value="EMPLOYEE_ID"/>
   <element name="FIRST_NAME" value="FIRST_NAME"/>
   <element name="LAST_NAME" value="LAST_NAME"/>
   <group name="G_EMPINFO" source="Q2">
    <element name="JOB_ID" value="JOB_ID"/>
    <element name="HIRE_DATE" value="HIRE_DATE"/>
    <element name="EMAIL" value="EMAIL"/>
    <element name="PHONE" value="PHONE_NUMBER"/>
    <element name="SALARY" value="SALARY"/>
    <element name="COMMISSION_PCT" value="COMMISSION_PCT"/>
    <element name="DEPARTMENT_ID" value="DEPARTMENT_ID"/>
    <element name="MANAGER_ID" value="MANAGER_ID"/>
   </group>
  </group>
 </dataStructure>
</dataTemplate>