Monday, February 25, 2008

Disappearing Parameters / Top Level Elements in Template after adding @section to BI / XML Publisher Report

A few people have run into this one so worthy of a post.

When you group with a section, you effectively "set" the base level of your XML to the level at which you group'ed. Hmm, better explained with an example.

Let's say your BIP'ing AR Statements and so you have something like this XML (fragment):

<ARXSGPO_CPG>
<LIST_G_SETUP>
  <G_SETUP>
  <COMPANY_NAME>Vision Operations (USA)</COMPANY_NAME>
  <COA_ID>101</COA_ID>
  <FUNCTIONAL_CURRENCY>USD</FUNCTIONAL_CURRENCY>
  <FUNCTIONAL_CURRENCY_PRECI>2</FUNCTIONAL_CURRENCY_PRECI>
  <LIST_G_STATEMENT>
   <G_STATEMENT>
    <SEND_CUSTOMER_NAME>My Valued Customer</SEND_CUSTOMER_NAME>
    <STATEMENT_DATE>25-JAN-02</STATEMENT_DATE>
    <BUCKET1_HEADING>Current</BUCKET1_HEADING>
    <BUCKET2_HEADING>1-30 Days</BUCKET2_HEADING>
    <BUCKET3_HEADING>31-60 Days</BUCKET3_HEADING>
    <BUCKET4_HEADING>61-90 Days</BUCKET4_HEADING>
    <BUCKET5_HEADING>Over 90 Days</BUCKET5_HEADING>
    <BUCKET1>379431.9</BUCKET1>
    <BUCKET2>267494.31</BUCKET2>
    <BUCKET3>0</BUCKET3>
    <BUCKET4>0</BUCKET4>
    <BUCKET5>130291.76</BUCKET5>
    ...

And in your template you "group" and "section" the statement:

<?for-each@section:G_STATEMENT?>
<?SEND_CUSTOMER_NAME?>
<?end for-each?>

And you want to refer to the element <?COMPANY_NAME?> in the Header section, but if you put <?COMPANY_NAME?> then it doesn't appear (is null).

Why? When you section on G_STATEMENT then that becomes the "base" level, so you need to go back up the XML tree to get to your Parameter / Company Name etc.

In this case you'd need to put

<?../../COMPANY_NAME?>

in your Header section. I.e. the parent (G_SETUP) of the parent (LIST_G_STATEMENT) of the current group (G_STATEMENT).

Update:Alternatively you can use xpath from the root node, in this case you could use

<?/ARXSGPO_CPG/LIST_G_SETUP/G_SETUP/COMPANY_NAME?>

in your Header section.

PS. Its been a while since I posted. Busy helping a number of people with BIP and other such sexy things. Means I have a good post pipeline though!