De-normalising Templates

There was a question on the forum this week that I thought would be worth 1. answering and 2. sharing that answer here. Frederic was asking:

I've got a master/detail report (vendor/shipments lines) that I would like to output to excel but I'm not able to have on each line, header columns and detail columns ie header columns must be repeated for each detail line. I tried using TemplateBuilder/Insert/TableForm/Advanced but cannot see how to specify that header columns have to be repeated ?


Essentially the question was how to denormalize the master/detail data into a single level where the header data is repeated for every detail record.  My answer was to use a for loop around the detail and then an XPATH expression (../) to get back up a level to the header. This did not work for Frederic and now I have his XML I can see why.


The XML came from an Oracle Report and thats why a single '../' was not working. By default, Oracle Reports will wrap an extra level around all of your groups, named 'LIST_' followed by the group name, unless you tell it not to.

ORGrp:

For all of the EBS canned reports this is true i.e. we did not turn it off. Why would we, those reports were written a long time ago, a time before we ever thought we would expose columns names to you folks ... and thereby hangs another tale. Now if you were just going to replicate the master/detail layout in your template then the extra level would not matter, it could be ignored. Lets say we have the following XML structure:

<POXRVODS>
 <LIST_G_COMPANY>
  <G_COMPANY>
   <LIST_G_VENDOR>
    <G_VENDOR>
     <LIST_G_PO_DETAILS>
      <G_PO_DETAILS>


If we wanted to build a template following the hierarchy to show, COMPANY -> VENDOR -> DETAILS we can ignore the LIST groups and have :

<?for-each:G_COMPANY?>
 ...
 <?for-each:G_VENDOR?>
  ...
  <?for-each:G_PO_DETAILS?>
   ...
   <?end for-each?>
  <?end for-each?>
<?end for-each?>
The template would work no problem, we just ignore the LIST levels and move on down.


However, if, as in Frederic's case we wanted to 'de-normalise' the levels into a single level using my method above we can not ignore the LIST_ levels.  So if we created a loop at the lowest level i.e. G_PO_DETAILS and wanted to go up the tree to retrieve data from the G_VENDOR or G_COMPANY levels a simple '../' is not enough we need to go up one more level to get past the LIST_ layer i.e. '../../'.


Let's a take a small portion of the XML tree:

  <G_VENDOR>
   <VENDOR>SUPPLIER_1</VENDOR>
   <VENDOR_SITE>LONDON</VENDOR_SITE>
   <TELEPHONE_NUMBER>-</TELEPHONE_NUMBER>
   <PARENT_VENDOR_ID>1001</PARENT_VENDOR_ID>
   <PARENT_VENDOR_SITE_ID>21</PARENT_VENDOR_SITE_ID>
   <PARENT_VENDOR_CONTACT_ID>-100</PARENT_VENDOR_CONTACT_ID>
   <VENDOR_CONTACT> </VENDOR_CONTACT>
   <LIST_G_PO_DETAILS>
    <G_PO_DETAILS>
     <P_DATE>18-OCT-06</P_DATE>
     <BUYER>Buyer, Miss Rosy</BUYER>
     <PO_NUMBER>76</PO_NUMBER>


To flatten this data out we would have the following:













Supplier Name Site Buyer PO#
<?for-each:G_PO_DETAILS?><?../../VENDOR?>
<?../../VENDOR_SITE?>
<?BUYER?>
<?PO_NUMBER?><?end for-each?> 

Notice the '../../' for the first two columns to get to the grandparent level in the data from the details level getting past the defunct LIST_ level. Run this template against our data and we get the flattened struct Frederic was looking for in Excel, or any other format for that matter.


XMLGrp2:


Now, I admit you had to go in and put the ../../, why does the Template Builder not know where you are in the template and do it for you? Good question, one that I hope we'll resolve real soon!
Hopefully more of an answer than Fredric could ever hope for :o) 

Comments:

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
« July 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
31
  
       
Today