Oracle BI Publisher Blog

While I have been away I provided two days on in depth techie training to Oracle Consulting in EMEA, they worked me hard (very hard) ranging from inserting barcodes to bursting, all good fun and informative I hope. The second day we spent some time sharing experiences of developing extractions, templates, etc and Im indebted to Serge Vervaet from Oracle Belgium for some great tips. The first of which was on building a data template. The XMLP extraction engine is fast, you can migrate your Oracle Reports to the format relatively easily but there is currently no tool to help you build them so once you have your query sorted out its off to Notepad or an XML editor. For big extracts with lots of columns even I have to admit its very tedious typing out those grouping structures, Serge came up with a way to reduce the tedium and speed up the process by doing away with the grouping section all together - use inline CURSORS in your queries. This creates all the hierarchy you need in your query without the need for the grouping section.

Lets take our old friends the EMP and DEPT queries:

SELECT DEPTNO,
DNAME,
LOC 
FROM DEPT
ORDER BY DEPTNO

SELECT  DEPTNO,
EMPNO,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
NVL(COMM,0) COMM
FROM EMP

To get a hierachical XML output we need to add the following data structure.  

<dataStructure>
  <group name="G_DEPT" source="Q1" groupFilter="">
   <element name="DEPT_NUM" value="DEPTNO" function=""/>
   <element name="DEPT_NAME" value="DNAME" function=""/>
   <element name="LOCATION" value="LOC" function=""/>
   <group name="G_EMP" source="Q2" groupFilter="">
    <element name="EMPNO" value="EMP_NUM" function=""/>
    <element name="EMP_NAME" value="ENAME" function=""/>
    <element name="JOB_TITLE" value="JOB" function=""/>
    <element name="MANAGER" value="MGR" function=""/>
    <element name="HIRE_DATE" value="HIREDATE" function=""/>
    <element name="SALARY" value="SAL" function=""/>
    <element name="COMMISSION" value="COMM" function=""/>
   </group>
  </group>
 </dataStructure>

SELECT DEPTNO,
  DNAME,
  LOC, 
  CURSOR(SELECT  EMPNO,
           ENAME,
           JOB,
           MGR,
           HIREDATE,
           SAL,
           nvl(COMM,0) COMM  
      from EMP ) as EMP 
  from dept 
  where deptno = nvl(:p_DeptNo,deptno)

Notice the CURSOR command and the 'as EMP' the latter will create the group G_EMP in the XML data:

<EMPLOYEES>
 <PDEPTNO>10</PDEPTNO>
 <LIST_DEPT>
 <DEPT>
  <DEPTNO>10</DEPTNO>
  <DNAME>ACCOUNTING</DNAME>
  <LOC>NEW YORK</LOC>
  <LIST_G_EMP>
   <G_EMP>
    <EMPNO>7369</EMPNO>
    <ENAME>SMITH</ENAME>
    <JOB>CLERK</JOB>
    <MGR>7902</MGR>
    <HIREDATE>1980-12-17T00:00:00.000-08:00</HIREDATE>
    <SAL>800</SAL>
    <COMM>0</COMM>
   </G_EMP>
    ...
   <LIST_G_EMP>
  </DEPT>
 </LIST_DEPT>
</EMPLOYEES>

So we saved some time and effort and still got structure we needed. Sample DT here. I have to say we have not exhaustively tested the performance but Serge is getting good results and reducing his development time :o)