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!

5 comments:

Lavina said...

Hi Gareth,

I tried the above solution for the Aging Report and the fields won't display the data outside the @section group when using bursting.The control file is set to burst at the @section group level.However when I do a view Output on the Report(this uses the XDOREPPB program), I see all the data on the fields that use ../../(fields above the XML tree section group).I'm not sure why it won't come up with bursting.Was wondering if you could help.

Thanks,
Lavina Fernandes

Gareth said...

Hi Lavina,

I haven't hit that yet. Send me your RTF, XML and control file (see my profile) and I'll give it a shot. In the meantime would recommend logging a SR - I'm keen to hear your experience with Oracle Support logging this SR to, so keep me posted.

Gareth

Lavina said...

Hi Gareth,

I have emailed you all the files.
I'm hoping you will find something that I might have overlooked.

Thanks,
Lavina Fernandes

Gareth said...

Hi Lavina,
You need to put all the nodes/elements you need in the bursting control file request select line, using the or "|" xpath function, e.g.

select="/ROWSET/ROW/G_GROUP1 | /ROWSET/PARAM"

I've put an example (see the Issues) on my bursting blog post

Gareth

Sridevi Koduru said...

Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b