BI Publisher Data Template

BI Publisher Data Template

The BI Publisher data engine enables you to rapidly generate any kind of XML data structure against any database in a scalable, efficient manner. 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.
The data engine supports the following functionality:

• Single and multiple data queries
  
• Query links
  
• Parameters
  
• Aggregate functions (SUM, AVG, MIN, MAX, COUNT)
  
• Event triggers
  
• Multiple data groups

The XML output generated by the data engine supports the following:

• Unicode for XML Output
  Unicode is a global character set that allows multilingual text to be displayed in a single application. This enables you to develop a single multilingual application and deploy it worldwide.
  
• Canonical format
  The data engine generates date elements using the canonical ISO date format: YYYY-MM-DDTHH24:MI:SS.FF3TZH:TZM for a mapped date element, and ######.## for number elements in the data template XML output.
  

  The Data Template Definition

  The data template is an XML document that consists of four basic sections: define parameters, define triggers, define data query, define data structure. This structure is shown in the following graphic:
  
  
  

  How to Define Parameters

  The <parameter> element is placed between the open and close <parameters> tags. The <parameter> element has a set of related attributes. These are expressed within the <parameter> tag. For example, the name, dataType, and defaultValue attributes are expressed as follows:
  

  <parameters>
     <parameter name="dept" dataType="number" defaultValue="10"/>
  </parameters>

  Data Query Section

  The <dataQuery> section of the data template is required.
  
  

  How to Define SQL Queries

  The <sqlStatement> element is placed between the open and close dataQuery tags. The <sqlStatement> element has a related attribute, name. It is expressed within the <sqlStatment> tag. The query is entered in the CDATA section. For example:
  

  <dataQuery>
    <sqlStatement name="Q1">
    <![CDATA[SELECT DEPTNO,DNAME,LOC from dept]]>
    </sqlStatement>
  </dataQuery>

  
  
  
  
  

  How to Define a Data Link Between Queries

  If you have multiple queries, you must link them to create the appropriate data output. In the data template, there are two methods for linking queries: using bind variables or using the <link> element to define the link between queries.
  Tip: To maximize performance when building data queries in the data template:
  BI Publisher tests have shown that using bind variables is more efficient than using the link tag.
  The following example shows a query link using a bind variable:
  

  <dataQuery>
   <sqlstatement name="Q1">
   <![CDATA[SELECT EMPNO, ENAME, JOB from EMP
     WHERE DEPTNO = :DEPTNO]]>
   </sqlstatement>
  </dataQuery>

  The <link> element has a set of attributes. Use these attributes to specify the required link information. You can specify any number of links. For example:
  

  <link name="DEPTEMP_LINK" parentQuery="Q1" parentColumn="DEPTNO" childQuery="Q_2" childColumn="DEPARTMENTNO"/>

  Attribute Name	Description
  name	Required. Enter a unique name for the link.
  parentQuery	Specify the parent query name. This must be the name that you assigned to the corresponding <sqlstatement> element. See How to Define Queries.
  parentColumn	Specify the parent column name.
  childQuery	Specify the child query name. This must be the name that you assigned to the corresponding <sqlstatement> element. See How to Define Queries.
  childColumn	Specify the child column name.

  Using Data Triggers

  Data triggers execute PL/SQL functions at specific times during the execution and generation of XML output. Using the conditional processing capabilities of PL/SQL for these triggers, you can do things such as perform initialization tasks and access the database.
  Data triggers are optional, and you can have as many <dataTrigger> elements as necessary.
  The <dataTrigger> element has a set of related attributes. These are expressed within the <dataTrigger> tag. For example, the name and source attributes are expressed as follows:
  

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

  Attribute Name	Description
  name	The event name to fire this trigger.
  source	The PL/SQL <package name>.<function name> where the executable code resides.

  The location of the trigger indicate at what point the trigger fires:
  
• Place a beforeReport trigger anywhere in your data template before the <dataStructure> section.. A beforeRepot trigger fires before the dataQuery is executed.
  
• Place an afterReport trigger after the <dataStructure> section. An afterReport trigger fires after you exit and after XML output has been generated.

 Data Structure Section

In the data structure section you define what the XML output will be and how it will be structured. The complete group hierarchy is available for output. You can specify all the columns within each group and break the order of those columns; you can use summaries, and placeholders to further customize within the groups. The dataStructure section is required for multiple queries and optional for single queries. If omitted for a single query, the data engine will generate flat XML.

• Defining a Group Hierarchy

  In the data template, the <group> element is placed between open and close <dataStructure> tags. Each <group> has a set of related elements. You can define a group hierarchy and name the element tags for the XML output.
  

  Creating Break Groups

  Use a break group to produce subtotals or add placeholder columns. A break group suppresses duplicate values in sequential records. You should set an Order By clause in the SQL query to suppress duplicate values.
  Assign a name to the group, and declare the source query, then specify the elements you want included in that group. When you specify the element, you assign it a name that will be used as the XML output tag name, and you declare the source column as the value. If you do not assign a name, the value (or source column name) will be used as the tag name.
  For example:
  

  <dataStructure>
      <group name="G_DEPT" source="Q1" ">
          <element name="DEPT_NUMBER" value="DEPTNO"  />
          <element name="DEPT_NAME"   value="DNAME"/>
           <group name="G_EMP" source="Q2">
                  <element name="EMPLOYEE_NUMBER" value="EMPNO" />
                  <element name="NAME" value="ENAME"/>
                  <element name="JOB" value="JOB" />
           </group>
       </group>        
     </dataStructure>

  The following table lists the attributes for the <group> element tag:
  

  Attribute Name	Description
  name	Specify any unique name for the group. This name will be used as the output XML tag name for the group.
  source	The name of the query that provides the source data for the group. The source must come from the name attribute of the <sqlStatement> element.

  The following table lists the attributes for the <element> element tag:
  

  Attribute Name	Description
  name	Specify any name for the element. This name will be used as the output XML tag name for the element. The name is optional. If you do not specify a name, the source column name will be used as the XML tag name.
  value	The name of the column that provides the source data for the element (from your query).

  Applying Group Filters

  It is strongly recommended that you use a WHERE clause instead of a group filter to exclude records from your extract. Filters enable you to conditionally remove records selected by your queries, however, this approach impacts performance. Groups can have user-created filters, using PL/SQL.
  The PL/SQL function must return a boolean value (TRUE or FALSE). Depending on whether the function returns TRUE or FALSE, the current record is included or excluded from the XML data output.
  For example, a sample PL/SQL function might be:
  

  function G_EMPFilter return boolean is
  begin
    if sal < 1000 then
      return (FALSE);
    else
      return (TRUE);
  end if;
  end;

  An example of the group filter in your data template definition would be:
  

  <group name="G_DEPT" source="Q1" groupFilter="empdata.G_EMPFilter(:DEPTSAL)">
     <element name="DEPT_NUMBER" value="DEPTNO"  />
     <element name="DEPT_NAME"   value="DNAME"/>
     <element name="DEPTSAL"     value="G_EMP.SALARY" function="SUM()"/>

  Creating a Summary Column

  A summary column performs a computation on another column's data. Using the function attribute of the <element> tag, you can create the following summaries: sum, average, count, minimum, and maximum.
  To create a summary column, you must define the following three attributes in the element tag:
  

  Attribute	Description
  name	The XML tag name to be used in the XML data output.
  source	The name of the column that contains the data on which the summary calculation is to be performed. The source column remains unchanged.
  function	The aggregation function to be performed. The type tells the XDO data engine how to compute the summary column values. Valid values are: SUM(), AVG(), COUNT(), MAX(), and MIN().

  The break group determines when to reset the value of the summary column. For example:
  

  <group name="G_DEPT" source="Q1">
     <element name="DEPT_NUMBER" value="DEPTNO"  />
     <element name="DEPTSAL" value="G_EMP.SALARY" function="SUM()"/>
         <group name="G_EMP" source="Q2">
            <element name="EMPLOYEE_NUMBER" value="EMPNO" />
            <element name="NAME" value="ENAME"/>
            <element name="JOB" value="JOB" />
            <element name="SALARY" value="SAL"/>
          </group>
  </group>

  
  Example 
  
  The below example is based on the table EMP.
  
  Data Template Code
  
  <?xml version = '1.0' encoding = 'UTF-8'?>
  <dataTemplate name="EMP" description="List of Employees" dataSourceRef="ORCL_DB1" defaultPackage=""  version="1.0">
     <properties>
        <property name="xml_tag_case" value="upper"/>
        <property name="include_null_Element" value="true" />
        <property name="debug_mode" value="on" />
     </properties>
      <dataQuery>
         <sqlStatement name="Q1">
             <![CDATA[SELECT  EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,nvl
               (COMM,0) COMM,DEPTNO
                   from EMPLOYEE
                   ]]>
         </sqlStatement>
             </dataQuery>  
         <dataStructure>
             <group name="G_EMP" source="Q1">
                <element name="EMPLOYEE_NUMBER" value="EMPNO" />
                <element name="NAME" value="ENAME"/>
                <element name="JOB" value="JOB" />
                <element name="MANAGER" value="MGR"/>
                <element name= "HIREDATE" value="HIREDATE"/>
                <element name="SALARY" value="SAL"/>
                <element name="DEPT" value="DEPTNO"/>
              </group>   
     </dataStructure>
  </dataTemplate>