X

An Oracle blog about BI Publisher

Formatting Concatenated Datasources

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

ConcatDS1.jpg

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.

ConcatDS3.jpg


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.

ConcatDS2.jpg


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!

Join the discussion

Comments ( 5 )
  • Fran&ccedil;ois GREGOIRE Tuesday, September 22, 2009
    Hi, thank you, very nice and very usefull, BUT, there is always a BUT...
    I must deal with a xml template generated by JDE, I'm trying to do exactly what you did in this post, but what if you don't have the vendor_id field?
    In the sub group I dont' have any "shared" parameter.
    I can't get to display the values in my tab when using for-each inside a for-each.
    Any ideas?
  • wireless network extender Monday, December 14, 2009
    This is absolutely awesome :) Thankyou for putting this out there.
  • convert powerpoint to flash Monday, March 22, 2010
    The reason why RIM and their berry devices are unable to catch up with the competion and therefore almost or seeming doomed for oblivion is because of sites such as Crackberry.com who bash on the iPhone and Android, and gives all praises to Blackberry devices and keeps putting RIM onto a pedestal. When will they realize that the have not given RIM the criticisms it deserves or perhaps they have just not gotten an iPhone and experience the UI, OS, and app and be able to realize that they have been living a lie. I鈥檓 tired of people at Crackberry.com proudly announcing 鈥溾€lackberry devices are the best, 鈥溾€?ohh I sooo love my blackberry, and iphone sucks鈥? 鈥測eah鈥IM forever!鈥? People at Crackberry.com are living a lie.
  • Raleigh Losey Sunday, April 18, 2010
    I have been searching for a nice product to help me get to sleep too. Melatonin has bad side effects. I read about Calms Forte 4 Kids. That link goes to a review. You think its as good as they say?
  • guest Monday, February 11, 2013

    Hi,

    In this scenario how to call vendor_id in po_num row.


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.