Bi Publisher and nested tables and loops

One thing I have noticed while developing RTF templates is that it always seems to be in your favor to try to use tables to structure your data. Not only this, but using or not using tables, depending on your XSL logic, can actually greatly change the output of your BI publisher report.

The following post will illustrate a few things I went through while developing a report that had a strict heirarchy. Now, this report is based off of an Oracle Apps Bill of Lading, and combined a bit with a Sales Order Acknowledgement document, so it might not look familiar. The point is, there are a few steps in understanding how to get your report looking right.

1. Understand what data needs to be on the report and HOW it needs to look and be structured.
2. Become familiar with the data and how it is structured hierarchically or if at all.
3. Become intimately familiar with your query or tool (like Oracle Reports) that needs to generate
the XML that your BI Publisher template will merge with.
4. Review your XML that is merging with the template so you truly understand what data is at what
level in the hierarchy, and where it might repeat as a group.
5. Learn to re-structure your report or application that serves the XML so it fits your needs.
6. Finally, learn to use BI Publisher's templating ability (and tables) to produce the output in the right
format.

That being said, for our example we will look at a heirarchy as follows:
LPN (license plate number) is the master node or top-most level.
- Item Number and Item Description are just underneath LPN, and there might be multiple
entries PER LPN.
- Quantity, Lot Number, Heat Number, and Mechanical Number are all attributes of an item
number, and thus they can all appear multiple times PER item number.

The XML data might look like:

LPN- GDN0000124
Item Desc- 2.0000.PL.7050.T7451.ALUM.BARE.48.5000.144.5000
Item #- 600296
Heat Number- 398662
Mechanical Number- 339394
Lot Number- 027.500/R014.2506276-A00
Quantity- 77
Unit of Measure- LBS

Note that this is the easiest situation, 1 LPN, 1 'set' of item description and item number, and 1 'set' of the item's attributes underneath. We need to design the BI Publisher template for a repeating possibility though, like:

LPN 1
ITEM 1
ITEM Attributes 1
.
.
ITEM Attributes N
.
.
ITEM N
.
.
.
LPN N

So multiple possible LPNs each with multiple possible items, each with multiple possible attributes. And yes, the people that read this report want it grouped nicely so that each attribute belonging to each item belonging to each LPN is 'grouped' and structured hierarchically. For any experienced reporting developer or any data analysis professional, this isn't too bad. However mastering this in BI Publisher with MS Word as your RTF Template tool, you really should master nested tables. The following is a screenshot of how we can get this to work in a report. It is a simple shot of the most basic situation with only 1 LPN, 1 item and 1 description. note the nested tables in this output:


The next shot is a more complicated output using the SAME template, this one having multiple items and descriptions for 1 LPN:



Again, notice the structuring of the tables I purposely left so you can see how this is built from top to bottom. You need to nest your tables hierarchically to get this structure to work.
Finally, the template and the XSL and code:


A few things to note in this quick template, we are indeed using barcodes as you might have noticed. Our initial for-each tag (in aqua) is outside the main table and will repeat basically for every single occurence of an LPN. Then we have a table nested inside the LPN table. This next FE (for-each) is shown in the 2nd nested table and it will repeat for each occurrence of an item number and item description pair. Finally, we nested a 3rd table inside the item's table, and it holds the item's attributes. The 3rd and final FE (for-each) will repeat for each Quantity, Lot, Heat, and Mechanical ID attributes it finds for the current item. You'll then see the EFE (end for-each) that stops the repeating for each of the 'FE' sections.

We also have a XSL tag that says split-by-page-break:
This is a tag that needs to be experimented with and placed JUST BEFORE the right EFE tag to see what it does. If done right, it will page break at the end of each of the elements for the EFE it proceeds. In this case, the tag is just before the last EFE which is related to the initial FE which if for the repeating LPN. So it will page break for each LPN we encounter...and it does. It keeps all items and attributes together but only makes a new page when the LPN changes.

Hope this helps!