Home » Fusion Middleware & Colab Suite » Business Intelligence » XML Tag Amounts (XML Publisher 10.1.3.4.2 )
XML Tag Amounts [message #526016] Fri, 07 October 2011 12:02 Go to next message
Apps11i
Messages: 14
Registered: August 2008
Junior Member
Hello Guru's,

Can some one please help me out on the below issue?

i have a requirement that i need to display the totals at the last page. i am carry forwarding the totals to the last page but the totals are incorrect. from my investigation i found that the problem is, it is excluding the amounts where it has comma in it.

Ex. <Amount>10</Amount>
<Amount>20</Amount>
<Amount>1,000</Amount>

the total should be 1030 but it is excluding the 1,000 and showing the total as 30.

thanks,
Re: XML Tag Amounts [message #526039 is a reply to message #526016] Fri, 07 October 2011 13:47 Go to previous messageGo to next message
Littlefoot
Messages: 19630
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I don't use XML Publisher so this is probably useless, but nevertheless ... maybe it'll give you a hint.

Can't you use the REPLACE function here, and replace a comma with nothing? Here's an example: I created a table that contains your sample data; here's query that selects amounts:
SQL> select extractvalue(col, 'root/Amount[' || level || ']') val
  2  from test
  3  connect by level <= (select count(*)
  4                       from test,
  5                            table(xmlsequence(extract(test.col, '//Amount'))));

VAL
----------------------------------------------------------------------------------

10
20
1,000

SQL>

Now, apply the REPLACE function to the 'VAL':
SQL> select replace(extractvalue(col, 'root/Amount[' || level || ']'), ',', '') val
  2  from test
  3  connect by level <= (select count(*)
  4                       from test,
  5                            table(xmlsequence(extract(test.col, '//Amount'))));

VAL
------------------------------------------------------------------------------------

10
20
1000

SQL>

That's much better; now, let's sum those values:
SQL> select sum(replace(extractvalue(col, 'root/Amount[' || level || ']'), ',', '')) val
  2  from test
  3  connect by level <= (select count(*)
  4                       from test,
  5                            table(xmlsequence(extract(test.col, '//Amount'))));

       VAL
----------
      1030

SQL>


Shortly, you'd SUM ( REPLACE ( EXTRACTVALUE ) ).
Re: XML Tag Amounts [message #526045 is a reply to message #526039] Fri, 07 October 2011 14:17 Go to previous messageGo to next message
Apps11i
Messages: 14
Registered: August 2008
Junior Member
Thanks for the reply. As you said we can do it in oracle by using functions but in XML Publisher i tried in doing the sum and replacing. nothing worked for my result.
Re: XML Tag Amounts [message #526051 is a reply to message #526045] Fri, 07 October 2011 14:47 Go to previous messageGo to next message
Littlefoot
Messages: 19630
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Sorry, I can't help any better. Hopefully, someone who knows XML Publisher will assist.
Re: XML Tag Amounts [message #526397 is a reply to message #526051] Mon, 10 October 2011 21:40 Go to previous messageGo to next message
Apps11i
Messages: 14
Registered: August 2008
Junior Member
Never Mind. i got the solution. issue has been resolved.
Re: XML Tag Amounts [message #526402 is a reply to message #526397] Tue, 11 October 2011 00:05 Go to previous messageGo to next message
Littlefoot
Messages: 19630
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Would you mind to share it with the community? Someone else might have the same problem and would benefit from your answer.
Re: XML Tag Amounts [message #526537 is a reply to message #526402] Tue, 11 October 2011 09:04 Go to previous message
Apps11i
Messages: 14
Registered: August 2008
Junior Member
Sure thing. Created a variable at the top of the RTF and by using translate function removed the comma to add the amounts.
<?init-page-total:Total?> (At starting of the RTF).

Use the below to remove the comma and add the amounts.
<?add-page-total:Total;translate(LINE_AMOUNT,',','')?>

Thanks,
Previous Topic: problem with RTF upload after migrating it to another instance
Next Topic: Customize Oracle BI Dashboard
Goto Forum:
  


Current Time: Sun Sep 21 21:53:17 CDT 2014

Total time taken to generate the page: 0.07024 seconds