Main

Data Templates Archives

October 18, 2007

Data Dot Defined

After a weekend (there are 3 posts on this) spent in the mountains trudging, sorry 'hiking' up hill and down dale in freezing weather with no sleep I have finally caught the 'cold' that I knew was coming - with a head thats fit to burst I sound ridiculous and 'dalk dike dat, dall der dime' - good for 'd' aliteration fanatics but Im not one of them. There is a useful post coming for those of you using data templates.


If you have moved up to 10.1.3.3 and suddenly you're data template based reports are failing with:


Data not defined


Panic ye not - we have found the issue and its easily fixable. For some reason, we got strict with you in 10.1.3.3, if you had some parameter defined in your data template that you were not passing a value to via the user inteface. If you had a data template like the following, notice the P1 parameter:


<dataTemplate name="prodTemplate" dataSourceRef="demo-hr">
 <parameters>
  <parameter name="P1" dataType="character" defaultValue="Hello"/>
 </parameters>
 <dataQuery>
  <sqlStatement name="prodStmt">
   <![CDATA[
      select last_name, first_name, salary
      from employees
      where salary > 4000
      ]]>
  </sqlStatement>
 </dataQuery>
</dataTemplate>


But you had a report definition that lacked the P1 parameter definition, see the first image.







DT1:     DT2:

It would run in 10.1.3.2 but in 10.1.3.3 we would not let you and we would throw an error.
Until we fix it, to get around the problem either define the parameter in the report, see the second image or remove the parameter from the data template.

October 23, 2007

BLOB, CLOB, RAW and Looooong

The data engine introduced some new support for data types other than the normal varchar, date and number formats in 10.1.3.3. We now have support for the long, raw, clob and blob formats - and you do not have to do anything in the data template to handle these formats.
We auto detect for BLOBs and if it contains an image we will 'base64' encode it and drop that into the XML output so you can then render the image in the output.
If it has text we will return the
escaped format of the text and we only support UTF-8
encoded output. Other BLOB formats we leave alone.
There is an issue for CLOBs around a maximum size retrieved by a data template, that has been addressed in 10.1.3.3.1.

The above applies to 'data template' based reports. If you are using straight SQL things are a little more limited. We have BLOB and CLOB support and the auto detect for images is working but we do not have support for RAW and LONG column types at the moment - we're looking into that for a future release.

January 29, 2008

Data Template Building & Performance

I have been working with data templates for the last few daze ... we are doing some work on getting some performance numbers - at last. I have been building some more complex data templates than those I have built previously on the blog. The engine is extremely powerful and can do alot of the heavy lifting for you when it comes to grouping hierarchy and summary amounts.


Here's my latest.


<dataTemplate name="EMPLOYEES" defaultPackage="" dataSourceRef="demo-hr">
 <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="LOCATIONS" dataSourceRef="">
   <![CDATA[select  COUNTRIES.COUNTRY_NAME as COUNTRY,
  REGIONS.REGION_NAME as REGION,
  LOCATIONS.STREET_ADDRESS as STREET_ADDRESS,
  LOCATIONS.POSTAL_CODE as POSTAL_CODE,
  LOCATIONS.CITY as CITY,
  LOCATIONS.STATE_PROVINCE as STATE_PROVINCE,
         LOCATIONS.LOCATION_ID as LOCATION_ID1
 from  HR.LOCATIONS LOCATIONS,
  HR.COUNTRIES COUNTRIES,
  HR.REGIONS REGIONS
 where   COUNTRIES.REGION_ID=REGIONS.REGION_ID
 and  COUNTRIES.COUNTRY_ID=LOCATIONS.COUNTRY_ID]]>
  </sqlStatement>
  <sqlStatement name="DEPTS" dataSourceRef="">
   <![CDATA[select  DEPARTMENTS.DEPARTMENT_ID as DEPARTMENT_ID1,
  DEPARTMENTS.DEPARTMENT_NAME as DEPARTMENT_NAME
from  HR.DEPARTMENTS DEPARTMENTS
where   DEPARTMENTS.LOCATION_ID = :LOCATION_ID1]]>
  </sqlStatement>
  <sqlStatement name="EMPLOYEES" dataSourceRef="">
   <![CDATA[select     EMPLOYEES.EMPLOYEE_ID as EMPLOYEE_ID,
     EMPLOYEES.FIRST_NAME||' '||EMPLOYEES.LAST_NAME as EMP_NAME,
     EMPLOYEES.EMAIL as EMAIL,
     EMPLOYEES.PHONE_NUMBER as PHONE_NUMBER,
     EMPLOYEES.HIRE_DATE as HIRE_DATE,
     EMPLOYEES.SALARY as SALARY,
     EMPLOYEES.DEPARTMENT_ID as DEPT_ID,
     JOBS.JOB_TITLE as JOB_TITLE
 from     HR.JOBS JOBS,
     HR.EMPLOYEES EMPLOYEES
 where   EMPLOYEES.JOB_ID=JOBS.JOB_ID
 and department_id = :DEPARTMENT_ID1 ]]>
  </sqlStatement>
 </dataQuery>
 <dataStructure>
  <group name="G_REGION" source="LOCATIONS" groupFilter="">
   <element name="LOC_REGION" value="REGION" function=""/>
   <group name="G_LOC" source="LOCATIONS" groupFilter="">
    <element name="LOC_ADDR" value="STREET_ADDRESS" function=""/>
    <element name="LOC_CITY" value="CITY" function=""/>
    <element name="LOC_CODE" value="POSTAL_CODE" function=""/>
    <element name="LOC_STATE" value="STATE" function=""/>
    <element name="LOC_COUNTRY" value="COUNTRY" function=""/>
    <group name="G_DEPT" source="DEPTS" groupFilter="">
     <element name="DEPT_NUM" value="DEPARTMENT_ID1" function=""/>
     <element name="DEPT_NAME" value="DEPARTMENT_NAME" function=""/>
     <element name="DEPT_SALARY" value="G_EMP.SALARY" function="sum()"/>
     <element name="DEPT_COUNT" value="G_EMP.EMPLOYEE_ID" function="count()"/>
     <group name="G_JOB" source="EMPLOYEES" groupFilter="">
      <element name="JOB_TITLE" value="JOB_TITLE" function=""/>
      <group name="G_EMP" source="EMPLOYEES" groupFilter="">
       <element name="EMPNO" value="EMPLOYEE_ID" function=""/>
       <element name="EMP_NAME" value="EMP_NAME" function=""/>
       <element name="HIRE_DATE" value="HIRE_DATE" function=""/>
       <element name="PHONE_NUMBER" value="PHONE_NUMBER" function=""/>
       <element name="EMAIL" value="EMAIL" function=""/>
       <element name="SALARY" value="SALARY" function=""/>
      </group>
     </group>
    </group>
   </group>
  </group>
 </dataStructure>
</dataTemplate> 


Maybe not such a real data case but it shows off some of what you can do with the data engine. You can see I have 3 queries using bind variables LOCATIONS -> DEPARTMENTS -> EMPLOYEES. Now, rather than just then render the three levels in the grouping hierarchy I have created 5 levels:


G_REGION
    |
    |_ G_LOCATION
            |
            |_ G_DEPT
                     |      
                     |_ G_JOB
                              |
                              |_G_EMP

I have introduced the G_REGION and G_JOB levels into the data. Even thou we only have 3 levels in the queries the engine will calculate the hierarchies to be applied to the data.

The one drawback with the data templates is the fact that we do not have a builder for creating the templates. Good news on the way, I have been testing a new data template builder. This allows you to build your queries,


DTBuilder1:


set appropriate engine flags


DTBuilder2:


then bind them together into the grouping hierarchy


DTBuilder3:


and finally set properties for the individual columns.


DTBuilder4:


Its a nice tool - needs a little tweaking but its very close - of course usual disclaimers apply and it may look completely different by the time it hits the shelves. Look for it in a release coming soon ... performance numbers will come sooner.

July 8, 2008

Multiple DataSources I

Oh yeah, another one of those posts that will drag on over a couple of daze! This one comes out of some mail exchanges with one of our Oracle University trainers, Leta, who is revamping some of the class notes for Publisher. Leta has been working on the data template section for the EBS notes and found some holes in our doc! Shock, horror - yes, Leslie (our doc writer) is not infallible! Leslie assures me that the holes were left on purpose just to see if you folks out there are actually reading the documentation :0)
Leta was particularly looking at the XML data source we support in the data template. Its not badly written up in the documentation but there is some information missing and you have to connect the dots a bit. The saving grace is a full example later in the chapter - always useful for the light bulb moment when things click
Back to the point ... data templates can be used to access databases, multiple databases in fact - thats another post thou. Today its accessing XML datasources directly. Maybe you have a servlet that can serve up XML upon request or a static XML file that's dropped into a folder on a server periodically. Either way you can pull this data in via a data template. Yes, I know you can already do that by just calling it as a datasource in an Enterprise report. But what data templates bring to the table is the ability to merge your XML data with other XML data or with a sql based XML extract.
If you navigate to our documentation and look up the Data Templates section you'll find the somewhat incomplete section on XML data sources. To take the example given
<xml name="empxml" expressionPath=".//ROW[DEPTNO=$DEPTNO]"> <url method="GET" realm="" username="" password="">file:///d:/dttest/employee.xml</url> </xml>
This would be put under the dataQuery section alongside or instead of a sqlStatement if needed.
To explain the tags and attributes:
  • name - name given to this datasource and referenced in the grouping structure.
  • expressionPath - a means to link to an existing query. Assume the XML that will be generated from the file is:
      <?xml version="1.0" encoding="UTF-8" ?>
     <ROWSET>
      <ROW>
       <NAME>Donald OConnell</NAME> 
       <FIRST_NAME>Donald</FIRST_NAME>
       <LAST_NAME>OConnell</LAST_NAME>
       <SALARY>2600</SALARY>
       <ANNUAL_SALARY>31200</ANNUAL_SALARY>
       <FED_WITHHELD>8736</FED_WITHHELD>
       <JOB_TITLE>Shipping Clerk</JOB_TITLE>
       <DEPARTMENT_NAME>Shipping</DEPARTMENT_NAME>
       <MANAGER>Kevin Mourgos</MANAGER>
      </ROW>
      <ROW>
       <NAME>Douglas Grant</NAME>
       <FIRST_NAME>Douglas</FIRST_NAME>
       <LAST_NAME>Grant</LAST_NAME>
       <SALARY>2600</SALARY>
       <ANNUAL_SALARY>31200</ANNUAL_SALARY>
       <FED_WITHHELD>8736</FED_WITHHELD>
       <JOB_TITLE>Shipping Clerk</JOB_TITLE>
       <DEPARTMENT_NAME>Shipping</DEPARTMENT_NAME>
       <MANAGER>Kevin Mourgos</MANAGER> 
       </ROW>
     <ROWSET> 
    and I have a query
    select DEPTNO, DEPT_NAME
    from depts 
    I can use the expressionPath to link the two datasources
    .//ROW[DEPTNO=$DEPTNO] 
    is basically saying in SQL speak select * from empxml where .//ROW/DEPTNO = :DEPTNO -> from the sql query
  • url method - how should we retrieve the data with a POST or GET call
  • url realm - authentication name - this is seen when you log into an online resource sometimes. The popup will ask for your usr/pwd and will list the realm to be accessed.
  • url username - does the URL Im calling need a username passed
  • url password - does the URL Im calling need a password passed
  • url value - the url to access the XML e.g.file:///d:/dttest/employee.xml gets an XML file from the directory 'dttest' on the disk. http://tim.oracle.com/getXML calls a URL on a web server to fetch the data
There is an example of how this all hangs together in the doc, to save some effort here it is with some comments:
<?xml version="1.0"
encoding="WINDOWS-1252" ?>
<dataTemplate name="Employee Listing" description="List of Employees" version="1.0">
 <parameters> - Defines a single parameter for the Department Number - with default of 20:
   <parameter name="p_DEPTNO" dataType="character" defaultValue="20"/>
 </parameters>
 <dataQuery>
  <sqlStatement name="Q1">
   <![CDATA[SELECT DEPTNO,DNAME,LOC from dept order by
deptno]]>
  </sqlStatement>
  <xml name="empxml" expressionPath=".//ROW[DEPTNO=$DEPTNO]">-
Defines name - and link to DEPTNO in Q1
   <url method="GET" realm="" username="" password="">
     file:///d:/dttest/employee.xml</url> -
Defines url for xml data
   </xml>
  </dataQuery>-
 <dataStructure> - The following section specifies the
XML hierarchy for the returning data:
 <group name="G_DEPT" source="Q1"
  <element name="DEPT_NUMBER" value="DEPTNO" />
  <element name="DEPT_NAME" value="DNAME"/>
- This creates a summary total at the department level based
- on the salaries at the employee level for each department:
  <element name="DEPTSAL" value="G_EMP.SALARY"
function="SUM()"/>
  <element name="LOCATION" value="LOC" />
  <group name="G_EMP" source="empxml">
   <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"/>
  </group>
 </group>
 </dataStructure>
</dataTemplate>
So, if you have the need to merge XML with SQL - now you know how to do it!

July 14, 2008

Lexical Reference Problems

Been in an interesting forum/email thread over the last few days with Naushad

I have a data template with a lexical parameter that is used in the SELECT caluse of the query.

DATA Template
<parameter name.............................../>
<parameter name="P_BREAK_COLUMN" dataType="varchar2"/>
<parameter name="P_BREAK" dataType="varchar2" defaultValue="gcc.segment3"/>
</parameters>
<lexicals>
</lexicals>
<dataQuery>
<sqlStatement name="Q_DATA">
<![CDATA[
SELECT &P_BREAK C_BREAK,
gcc.segment4 ,
exp.je_category ,
exp.vendor_number ,
exp.vendor_name ,
exp.invoice_number ,
exp.invoice_date ,
SUM (NVL (exp.accounted_dr, 0) - NVL (exp.accounted_cr, 0)) accounted_amt,
ffv4.description account_desc
FROM xxcus.XXGL_XGLOGEXP exp,
gl_code_combinations gcc,
fnd_flex_values_vl ffv4,
fnd_flex_value_sets ffs4
WHERE exp.code_combination_id = gcc.code_combination_id
AND ffs4.flex_value_set_id(+) = ffv4.flex_value_set_id
AND ffv4.flex_value(+) = gcc.segment4
AND ffs4.flex_value_set_name = 'ACCT_AFF'
GROUP BY &P_BREAK ,
gcc.segment4 ,
exp.je_category ,
exp.vendor_number ,
exp.vendor_name ,
exp.invoice_number ,
exp.invoice_date ,
ffv4.description
]]>
</sqlStatement>
</dataQuery>
<dataTrigger name="beforeReportTrigger" source="XXGL_XGLOGEXP_PKG.BeforeReport"/>
<dataStructure>
<group name="G_DATA" dataType="varchar2" source="Q_DATA">
<element name="C_BREAK" dataType="varchar2" value="C_BREAK"/>
<element name="SEGMENT4" dataType="varchar2" value="SEGMENT4"/>
<element name="JE_CATEGORY" dataType="varchar2" value="JE_CATEGORY"/>
...

The beforeReportTrigger looks like this:

FUNCTION BeforeReport RETURN BOOLEAN IS

BEGIN

IF P_BREAK_COLUMN = 'Location' THEN
P_BREAK := 'gcc.segment2';
ELSIF p_BREAK_COLUMN = 'Department' THEN
P_BREAK := 'gcc.segment3';
END IF;
fnd_file.put_line(fnd_file.log,'P_BREAK --> '||P_BREAK);

END;


The parameter starts with a default of "gcc.segment3". On execution of this report in EBS, the beforeReportTrigger fires and changes the value of P_BREAK to "gcc.segment2" (I can see that change in the concurrent reqs LOG file).

But when the SQL is executed, it still holds the default value. The fact that beforeReport has changed the value to segment2 is not visible to the SQL in the data XML template.

I have to admit I was fishing around for answer from development but Naushad continued to tinker with it and found the answer. Another gap in our doc we need to fill!

I found the source of my problem.
I have worked in Reports 9i for many years (since 1996 when it was version 4.5) and when I transition to XML data template seems like I have to unlearn some of that.

In Reports 9i, we define parameters in the a??User Parametersa?? section. Those parameters can be assigned different values in the a??before reporta?? trigger. Essentially, we change the value of the parameter and the new values are visible to the SQL in the Data Model.

In XML data template, the parameters defined in the section are in a read only format. Any changes by the beforeReport trigger, to the variables in the parameter section are not visible to the SQL in the section.

I was able to resolve it by commenting out the declaration of parameter P_BREAK.

P_BREAK is defined as variable in the package spec and the beforeReport trigger changes the value as required.

<parameters>
 a?|a?|a?|a?|a?|a?|a?|a?|..
<!--  <parameter name="P_BREAK" dataType="varchar2" defaultValue="gcc.segment3"/>   -->
</parameters>  
<lexicals>
</lexicals>
<dataQuery>
 <sqlStatement name="Q_DATA">
  <![CDATA[
    SELECT &P_BREAK                C_BREAK,
    gcc.segment4            ,a?|a?|
    FROM gl_code_combinations gcc, a?|..

The take away from the solution - the parameters in the data template are not updatable and you can declare public variables in your before report trigger and reference them inside the data template without the need to declare them.

About Data Templates

This page contains an archive of all entries posted to Oracle BI Publisher Blog in the Data Templates category. They are listed from oldest to newest.

SQL is the next category.

Many more can be found on the main index page or by looking through the archives.

Powered by
Movable Type and Oracle