Table of Contents

About

BIP can create a master detail report from a flat data source (such as a database) with the standard functionality BIP 10G - (Data Set|Group|Section) but when you want to create a multiple master detail report, you have to use the data template functionality.

With the data template functionality, you can link several queries and may show in the same report for instance:

  • the order information (master)
  • the lines of the order (detail of order)
  • the multiple comments of the same order (detail of order).

Example

The following example shows how to link query with the data binding technique recommended by Oracle.

Data Template

Example of Sql Query Linking with Data binding on the HR sample schema. 

<dataTemplate name="dataTemplateName" description="Template Description" 
dataSourceRef="demo" defaultPackage="employee" version="1.0">
	<dataQuery>
		<sqlStatement name="Q1">
  <![CDATA[SELECT dept.department_id as DEPTNO, department_name as DNAME, 
  loc.city as LOC FROM hr.departments dept, hr.locations loc
WHERE 
dept.location_id = loc.location_id]]>
  </sqlStatement>
		<sqlstatement name="Q2">
 <![CDATA[SELECT employee_id AS EMPNO, last_name AS ENAME, jobs.job_title as JOB
 FROM employees EMP, jobs 
where emp.job_id = jobs.job_id
and employee_id = :DEPTNO]]>
 </sqlstatement>
	</dataQuery>
	<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>
</dataTemplate>

Bip Data Template 10g

Xml Output

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. 
<?xml version="1.0" encoding="UTF-8"?>
<MYDATATEMPLATENAME>
	<LIST_G_DEPT>
		<G_DEPT>
			<DEPT_NUMBER>10</DEPT_NUMBER>
			<DEPT_NAME>Administration</DEPT_NAME>
			<LIST_G_EMP>
				<G_EMP>
					<EMPLOYEE_NUMBER>200</EMPLOYEE_NUMBER>
					<NAME>Whalen</NAME>
					<JOB>Administration Assistant</JOB>
				</G_EMP>
			</LIST_G_EMP>
		</G_DEPT>
		<G_DEPT>
			<DEPT_NUMBER>20</DEPT_NUMBER>
			<DEPT_NAME>Marketing</DEPT_NAME>
			<LIST_G_EMP>
				<G_EMP>
					<EMPLOYEE_NUMBER>201</EMPLOYEE_NUMBER>
					<NAME>Hartstein</NAME>
					<JOB>Marketing Manager</JOB>
				</G_EMP>
				<G_EMP>
					<EMPLOYEE_NUMBER>202</EMPLOYEE_NUMBER>
					<NAME>Fay</NAME>
					<JOB>Marketing Representative</JOB>
				</G_EMP>
			</LIST_G_EMP>
		</G_DEPT>
		....................
	</LIST_G_DEPT>
</MYDATATEMPLATENAME>

You get then multiple data sets and you can use the BIP 10G - (Data Set|Group|Section) instruction to create your sections.

Documentation / Reference