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!

Comments:

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?

Posted by François GREGOIRE on September 21, 2009 at 07:41 PM MDT #

This is absolutely awesome :) Thankyou for putting this out there.

Posted by wireless network extender on December 14, 2009 at 01:11 AM MST #

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.

Posted by convert powerpoint to flash on March 21, 2010 at 08:54 PM MDT #

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?

Posted by Raleigh Losey on April 18, 2010 at 03:51 PM MDT #

Hi,

In this scenario how to call vendor_id in po_num row.

Posted by guest on February 11, 2013 at 12:02 AM MST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Follow bipublisher on Twitter Find Us on Facebook BI Publisher Youtube ChannelDiscussion Forum

Join our BI Publisher community to get the most and keep updated with the latest news, How-to, Solutions! Share your feedback and let us hear your voice @bipublisher on Twitter, on our official Facebook page, and Youtube!

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today