Oracle BI Publisher Blog

When you are building reports inside BIP standalone, do you concatenate or data template? If you need to use multiple queries in your report, either in the same database or separate and you need to join that data together in the output, what do you use? Or maybe some other system is spitting out a flattened hierarchy XML.

You have a choice, either use a data template to bring the data together simplifying the template or use the concatenated data source and use some funky tricks in the layout to bring the separate data together.

Let's assume I have vendors in an Oracle db and their purchase orders in a SQL server database. I need to generate a listing report showing vendors and their POs. I have covered data templates elsewhere in this blog and I will follow up with a multi database example next week. Today, lets deal with the concatenated data source. Say I have the following data:

<DATA>
<SUPPLIERLIST>
 <G_VENDOR_NAME>
  <VENDOR_NAME>Nuts and Bolts Limited</VENDOR_NAME>
  <ADDRESS>1 El Camino Real, Redwood City, CA 94065</ADDRESS>
  <VENDOR_ID>1</VENDOR_ID>		
 </G_VENDOR_NAME>
 <G_VENDOR_NAME>
  <VENDOR_NAME>Big Bike Motorsports</VENDOR_NAME>
  <ADDRESS>10 Downing Street, London, SW1, UK</ADDRESS>
  <VENDOR_ID>2</VENDOR_ID>		
 </G_VENDOR_NAME>
</SUPPLIERLIST>
<PO_LIST>
 <G_PO>
  <PO_VENDOR>1</PO_VENDOR>
  <PO_NUM>123</PO_NUM>
  <PO_ITEM>TV</PO_ITEM>
  <PO_AMT>100</PO_AMT>
 </G_PO>
 <G_PO>
  <PO_VENDOR>1</PO_VENDOR>
  <PO_NUM>124</PO_NUM>
  <PO_ITEM>Monitor</PO_ITEM>
  <PO_AMT>300</PO_AMT>
  </G_PO>
 <G_PO>
  <PO_VENDOR>1</PO_VENDOR>
  <PO_NUM>125</PO_NUM>
  <PO_ITEM>PC</PO_ITEM>
  <PO_AMT>400</PO_AMT>
 </G_PO>
 <G_PO>
  <PO_VENDOR>2</PO_VENDOR>
  <PO_NUM>233</PO_NUM>
  <PO_ITEM>TV</PO_ITEM>
  <PO_AMT>1000</PO_AMT>
 </G_PO>
 <G_PO>
  <PO_VENDOR>2</PO_VENDOR>
  <PO_NUM>234</PO_NUM>
  <PO_ITEM>Receiver</PO_ITEM>
  <PO_AMT>340</PO_AMT>
 </G_PO>
 <G_PO>
  <PO_VENDOR>2</PO_VENDOR>
  <PO_NUM>235</PO_NUM>
  <PO_ITEM>Phone</PO_ITEM>
  <PO_AMT>150</PO_AMT>
 </G_PO>
</PO_LIST>
</DATA>

You'll notice both the vendors and the PO are at the same level in the XML hierarchy. Looks tough to bring them together in the output to get

VENDOR1
PO1
PO2
VENDOR2
PO1
PO2.

With a very simple template approach we get

Not what you need right?

Looking back at the data, you might also have noticed that both the vendor and PO share the vendor ID - we can use that. In our RTF template we are going to loop over the vendors and inside loop over the PO but checking vendor ID to ensure we are getting the right POs.

There are two pieces to the solution that are different to a regular template loop over or re-grouping over a regular hierarchical or completely flat data set.

Variable - as we loop over the vendors we need some way of tracking what vendor record we are currently processing. In my template just after the for-each for the G_VENDOR group I have field with

<?variable@incontext:v_id;VENDOR_ID?<

Its not really a variable, you can not update with a value, you can just reference it or re-declare it. Im using the @incontext command to keep the variable localized to the vendor loop.

The other piece of interest is the loop command for the POs.

<&?for-each:/DATA/PO_LIST/G_PO[PO_VENDOR= $v_id]?>

Two things of note in here:

1. The for-each is not using 'G_PO' as you might expect. Im providing the complete path to the PO group. Why? Because Im currently looping over the vendors in an outer loop. Remember POs are at the same level as the vendors so <?for-each:G_PO?> will not find any data.


2. There is an XPATH expression associated with the for-each, '[PO_VENDOR= $v_id]' - this is instructing the rendering engine to only show PO records that have a vendor id equal to the parameter, 'v_id' - remember that was set and re-set every time you hit a new vendor in that loop.

That's it, those two pieces of 'code' allow you to generate a hierarchical output from the concatenated data set.

If you need the template and some data, get em here.

Yep, its a simple demo but you get the idea. Of the two options, layout or data template - Id go with the data template every time. Get the extraction engine to do the heavy lifting and KISS on the layout template :0) Complex layout templates mean scared business users that will not touch the template even if it is in friendly ol' MSWord. Im excluding version 2007 from that term of endearment, I have had it 6 months and I still don't like it!

Enjoy yer weekend, after my week, I'll be sleeping!