In my post on Formatting Concatenated Datasources I mentioned the better way to do thangs was to use a data template to connect and execute queries on each databases and bring back a grouped/formatted data set.
So rather than the concatenated data set, thus
<DATA>
<SUPPLIERLIST>
<G_VENDOR_NAME>
<G_VENDOR_NAME>
</SUPPLIERLIST>
<PO_LIST>
<G_PO>
<G_PO>
<G_PO>
<G_PO>
<G_PO>
<G_PO>
</PO_LIST>
</DATA>
we use a data template to generate a hierarchical data set thus:
<DATA>
<G_VENDOR>
<G_PO>
<G_PO>
<G_PO>
<G_VENDOR>
<G_PO>
<G_PO>
<G_PO>
</DATA>
Remember, this is executing a query on SQLServer (or other db) to fetch the VENDOR data and executing another 'child' query on Oracle (or another db) to fetch the vendor's PO data.
This will allow you to simplify the layout template removing the need for the variables and the XPATH expression. It will also improve the performance immensely. With the concatenated data sources we were looping over the vendors. Setting a variable for the vendor and then looping over all of the POs filtering out the ones we did not want. So for every vendor we had to loop over every PO - its expensive.
With the data template approach we just following the data structures
<?for-each:G_VENDOR?>
...
<?for-each:G_PO?>
...
<?end for-each?>
<?end for-each?>
Much less expensive and simpler too and that satisfies the other mantra for layout templates, KISS!
How do you do it then?
Well first off you need to set up the oracle and SQL Server datasources. In 10.1.3.4 we ship the drivers for you for the popular non-Oracle dbs so setting them up is a relative cinch. We even provide a simple connection string for you to fill out.
NB: if you are using SQL Server 2008, you can use the 2005 driver. Worked for me.
Once you have both data sources set up its then on to the data template. As I have mentioned previously, there is no 'builder' for data templates in 10.x releases. The world becomes a much nicer place with 11g but you'll have to wait a bit longer for that. If you are just starting out with data templates, check out the Data Template Progression entry to get started. Once you are comfortable with the format come back ...
You ready? Its not that tough to do, you just need to get the structure right. I fell in to a trap putting this example together (thanks Ahmed) but I can now share that with you. Here's the DT for the SQLServer (parent) and Oracle (child) queries and their structure.
<dataTemplate name="DATA" description="SQL Server - Oracle Extraction">
<properties>
<property name="include_parameters" value="true"/>
<property name="include_null_Element" value="false"/>
<property name="include_rowsettag" value="false"/>
<property name="scalable_mode" value="off"/>
<property name="debug_mode" value="on"/>
</properties>
<dataQuery>
<sqlStatement name="VENDORS" dataSourceRef="locsqls"><![CDATA[
select VENDORS.VENDOR_ID as VENDOR_ID,
VENDORS.VENDOR_ADDRESS as VENDOR_ADDRESS,
VENDORS.VENDOR_NAME as VENDOR_NAME
from biptest.dbo.VENDORS VENDORS]]></sqlStatement>
<sqlStatement name="POS" dataSourceRef="lorcl"><![CDATA[
select po.PO_VENDOR as PO_VENDOR,
po.PO_NUM as PO_NUM,
po.PO_ITEM as PO_ITEM,
po.PO_AMT as PO_AMT
from PO_TABLE po
where po.po_vendor = :VENDOR_ID]]></sqlStatement>
</dataQuery>
<dataStructure>
<group name="G_VENDOR" source="VENDORS">
<element name="VENDOR_ID" value="VENDOR_ID"/>
<element name="VENDOR_NAME" value="VENDOR_NAME"/>
<element name="VENDOR_ADDRESS" value="VENDOR_ADDRESS"/>
<group name="G_PO" source="POS">
<element name="PO_NUM" value="PO_NUM"/>
<element name="PO_ITEM" value="PO_ITEM"/>
<element name="PO_AMT" value="PO_AMT"/>
</group>
</group>
</dataStructure>
</dataTemplate>
I have emboldened the important parts where things deviate from a regular single db data template. Notice the dataSourceRef attributes on the sqlStatement element. These names need to match the ones you defined earlier.
Then the query link, notice Im using a bind variable (:VENDOR_ID). You can not use a link structure i.e.
<link name="SQLORCL_LINK" parentQuery="VENDORS" parentColumn="VENDOR_ID" childQuery="POS" childColumn="PO_VENDOR" condition="="/>
they are not supported. Something else of note, I did not hit it here but if you had VENDOR_ID on the SQLServer table and VENDOR_ID on the Oracle table you must ensure they have a different alias e.g. VENDOR_ID and VENDOR_ID1. Although they are on different dbs when it comes to linking them
where PO.VENDOR_ID = :VENDOR_ID
will at best confuse the heck out of the extraction engine and get unexpected results.
One other trap that I somehow fell into was to have each query under its own </dataQuery> element. they need to share one.
<dataQuery>
<sqlStatement name="VENDORS" dataSourceRef="locsqls"><![CDATA[ SQL1 ]]>
</sqlStatement>
<sqlStatement name="POS" dataSourceRef="lorcl"><![CDATA[]]>
</sqlStatement>
</dataQuery>
Otherwise you get weird table does not exist errors.
Once you have the data template in your report and valid you will get the data set back you want. You can then start adding the parameters, summary columns, etc you want.
<COMBINED>
<LIST_G_VENDOR>
<G_VENDOR>
<VENDOR_ID>1</VENDOR_ID>
<VENDOR_NAME>Nuts and Bolts Limited</VENDOR_NAME>
<VENDOR_ADDRESS>1 El Camino Real, Redwood City, CA 94065</VENDOR_ADDRESS>
<LIST_G_PO>
<G_PO>
<PO_NUM>123</PO_NUM>
<PO_ITEM>TV</PO_ITEM>
<PO_AMT>100</PO_AMT>
</G_PO>
<G_PO>
<PO_NUM>124</PO_NUM>
<PO_ITEM>Monitor</PO_ITEM>
<PO_AMT>300</PO_AMT>
</G_PO>
<G_PO>
<PO_NUM>125</PO_NUM>
<PO_ITEM>PC</PO_ITEM>
<PO_AMT>400</PO_AMT>
</G_PO>
</LIST_G_PO>
</G_VENDOR>
<G_VENDOR>
<VENDOR_ID>2</VENDOR_ID>
<VENDOR_NAME>Big Bike Motorsports</VENDOR_NAME>
<VENDOR_ADDRESS>10 Downing Street, London, SW1, UK</VENDOR_ADDRESS>
<LIST_G_PO>
<G_PO>
<PO_NUM>233</PO_NUM>
<PO_ITEM>TV</PO_ITEM>
<PO_AMT>1000</PO_AMT>
</G_PO>
<G_PO>
<PO_NUM>234</PO_NUM>
<PO_ITEM>Receiver</PO_ITEM>
<PO_AMT>340</PO_AMT>
</G_PO>
<G_PO>
<PO_NUM>235</PO_NUM>
<PO_ITEM>Phone</PO_ITEM>
<PO_AMT>150</PO_AMT>
</G_PO>
</LIST_G_PO>
</G_VENDOR>
</LIST_G_VENDOR>
</COMBINED>
Nice!
In terms of performance, its a much better way to get formatted/grouped data in your layout. But as I was discussing with someone this morning. Its a balancing act, you really want the dbs and the extraction engine to do the heavy lifting in terms of nesting structure, summary columns and sorting but you also want to provide the folks who build templates for users as much flexibility as possible.
Its not an exact science by any means, I always try and at least have the data engine extract using a 'sensible' hierarchy. Most if not all users are going to want to list invoices showing header information followed by the lines. Very rarely will they want to turn that hierarchy on its head, so its a good structure to have the DT extract plus the summary columns and sorting that go with it e.g invoice total, sort by line number, etc.
If you want to see the report file, you can get it here.
Happy Data Templating!
