I bet $10 that many of you have reports that have numeric content that need to be formatted according to their currency. As many of you know you can use the formats that are provided by MS Word or our own dialog.
There are a limited number of formats but you can add/remove elements to the formats. For example you can add a '$' or any other currency symbol to any of them so '#,##0.00' can be updated to '$#,##0.00'. All very well if you have a report with a single, known currency, what can you do if there are multiple currencies or unknown currencies at design time?
We do have a format-number function thats documented, again it suffers if you dont know the formats you need. We now have (5.6.3+) a format-currency function - this is format-number on plenty of caffeine. It takes the format:
<?format-currency:value;currency_code;'true|false'?>
Only thing that needs explanation is the true|false - this turns the currency symbol on or off. If its 'false', you'll get the correct number formatting but no symbol.
You can of course substitute in XML values for the currency_code. Assuming we have the following XML.
<G_INVOICES> <TRX_NUMBER>510262</TRX_NUMBER> <TRANS_TYPE>Standard</TRANS_TYPE> <TRANSACTION_DATE>13-DEC-05</TRANSACTION_DATE> <CURRENCY>EUR</CURRENCY> <TRANS_AMOUNT>19349.69</TRANS_AMOUNT> </G_INVOICES>
we can use the following for the currency formatting field.
<?format-currency:TRANS_AMOUNT;CURRENCY;'true'?>
this will give us the correct currency formatting and add the currency symbol if available, otherwise we'll drop in the currency code provided. You can now generate the following types of output.
10ยข to the first person who finds out what an MGA is!
Comments (7)
Malagasy Ariary
Posted by Ike Wiggins | August 27, 2008 7:17 PM
Posted on August 27, 2008 19:17
We are trying to use multi-currency in Peoplesoft generated XMLP report, is there a way we can accomplish multi-currency formatting. i dont think we in peoplesoft can use format currency.
Thanks
Posted by kranthi | August 28, 2008 8:59 AM
Posted on August 28, 2008 08:59
Kranthi
I think you will need to log a service request to get the PS team to pick up this patch into your libraries
Tim
Posted by Tim | August 28, 2008 9:12 AM
Posted on August 28, 2008 09:12
I have been waiting for this. When will this be available for EBS users who are currently on 5.6.3 (with 11.5.10.2)?
Thanks for all your hard work.
Donn
Posted by Donn Shumway | September 3, 2008 7:21 AM
Posted on September 3, 2008 07:21
Donn
Assuming you are on 11i? Its available for R12.
You need to log an SR with support and request a back port, sorry I thought it had already been done for 11i. I was mistaken!
Tim
Posted by Tim | September 3, 2008 8:45 AM
Posted on September 3, 2008 08:45
Hey Tim......
I am with a little problem with BI Publisher that sounds like this article that you wrote.
I have a lot of graphs in one RTF file that are functioning ok. I'm getting just this boring problem. In my graphs, I had put a tag like this to show the VALUES in the graph too, not only images:
#MarkerText visible="true" markerTextPlace="MTP_CENTER"/#
But the values presented in the graph are driving me crazy... They are all correct, but I cant define any format to them. Sometimes it appears with 2 decimals, other with 3.... But number is not even a float value, it is an INTEGER and I want to present it without ANY DECIMAL HOUSES.
I tryed to use the FORMAT-NUMBER function used into some XSL documents, but it doesn't work to me too. Just like the above tag:
#xsl:value-of select="format-number(sum($G1[(./descricacao=current()/descricacao)]/Total_Visitas_Registros),'#########')" /#
PS.:The # signals in tags are equivalent to "HIGHER" and "LOWER" signals that is not appearing. Sorry about my very bad english.
I will really appreciate any help from you.
Thank you very much for your attention, with the best regards.
Paulo Tolosa
Posted by Paulo Tolosa | September 3, 2008 1:43 PM
Posted on September 3, 2008 13:43
You need somethiing like
In your chart def - check the DTD for the graph xml here
http://www.oracle.com/technology/products/reports/htdocs/getstart/whitepapers/graphdtd/graph_dtd_technote_2.htm
Look for the ViewFormat entry to see how you can manipulate the number formatting
Posted by Tim | September 5, 2008 3:39 PM
Posted on September 5, 2008 15:39