Formatting Currencies

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.

CurrFormatting1.jpg

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.

CurrFormatting2.jpg

10¢ to the first person who finds out what an MGA is!

Comments:

Malagasy Ariary

Posted by Ike Wiggins on August 27, 2008 at 01:17 PM MDT #

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 on August 28, 2008 at 02:59 AM MDT #

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 on August 28, 2008 at 03:12 AM MDT #

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 on September 03, 2008 at 01:21 AM MDT #

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 on September 03, 2008 at 02:45 AM MDT #

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 on September 03, 2008 at 07:43 AM MDT #

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 on September 05, 2008 at 09:39 AM MDT #

Nope MGA is actually Madagascar, Ariary.. Tim, I really appreciate your work here, but what I would like to bring to your notice is that the above syntax doesn't really solve my problem: Here is the scenario: I have currency code as FRF(France) and the number should be formatted according to it in the following way ex: if its 1,000,000.00 then in French format it is 1 000 000,00 (yes, no commas and decimal separator is a comma instead of dot) Your above workaround doesn't really seem to solve this issue.. I am using this ,,'lang code-country code')?> ex: For Lang codes and country codes please use the below links.. 1. Land Code: http://ftp.ics.uci.edu/pub/ietf/http/related/iso639.txt 2. Country Code: http://userpage.chemie.fu-berlin.de/diverse/doc/ISO_3166.html Thanks, -RS

Posted by RS on March 24, 2010 at 06:02 PM MDT #

Nope MGA is actually Madagascar, Ariary.. Tim, I really appreciate your work here, but what I would like to bring to your notice is that the above syntax doesn't really solve my problem: Here is the scenario: I have currency code as FRF(France) and the number should be formatted according to it in the following way ex: if its 1,000,000.00 then in French format it is 1 000 000,00 (yes, no commas and decimal separator is a comma instead of dot) Your above workaround doesn't really seem to solve this issue.. I am using this ,,'lang code-country code')?> ex: For Lang codes and country codes please use the below links.. 1. Land Code: http://ftp.ics.uci.edu/pub/ietf/http/related/iso639.txt 2. Country Code: http://userpage.chemie.fu-berlin.de/diverse/doc/ISO_3166.html Thanks, -RS

Posted by RS on March 24, 2010 at 06:04 PM MDT #

Nope MGA is actually Madagascar, Ariary.. Tim, I really appreciate your work here, but what I would like to bring to your notice is that the above syntax doesn't really solve my problem: Here is the scenario: I have currency code as FRF(France) and the number should be formatted according to it in the following way ex: if its 1,000,000.00 then in French format it is 1 000 000,00 (yes, no commas and decimal separator is a comma instead of dot) Your above workaround doesn't really seem to solve this issue.. I am using this ,,'lang code-country code') ?> ex: For Lang codes and country codes please use the below links.. 1. Land Code: http://ftp.ics.uci.edu/pub/ietf/http/related/iso639.txt 2. Country Code: http://userpage.chemie.fu-berlin.de/diverse/doc/ISO_3166.html Thanks, -RS

Posted by RS on March 24, 2010 at 06:04 PM MDT #

For some reason the xdoxslt code doesnt seem to appear in the above comments.. I'm trying to paste it here without tags.. xdoxslt:format_number(,,'lang code-country code') ex: xdoxslt:format_number(CURRENCY_CD,2,'fr-Fr')

Posted by RS on March 24, 2010 at 06:06 PM MDT #

I really realize the trouble which you have given to this information. We are looking forward for your next blog post.Please that I recently found this an informative and interesting blog. I simply hope you may make another post connected with this. That is perfectly worth reading.

Posted by Damion Fulton on July 31, 2010 at 07:18 AM MDT #

I usually read the content on sites well before I leave a short review. If I have zero to say of the piece of writing topic, I probably won't leave any views. And I love to read somebody else’s suggestions on that post too. Because I'm able to find handy ideas in the quality comments. I'd have posted “First class blog! First class posts!” then I might be spamming your blog. I also make use of a blog page, the very first thing I might do every single day once i signed onto my web page is consider feedback to my content and physically approve or reject these observations. Keywordluv not just brings you more commenters. It promotes your position in search engines for the reason that you’re not merely on several “dofollow blog lists” but in addition on a number of those “keywordluv lists”. Thank you, Kathy

Posted by Charolette Tokkesdal on August 01, 2010 at 06:49 AM MDT #

A actually very good submit by you my friend. I've bookmarked this page and will occur back following several days to examine for any new posts that you just make.

Posted by never fail list building system bonus on August 22, 2010 at 08:11 PM MDT #

Good post, thanks

Posted by Get ex back on September 20, 2010 at 09:07 PM MDT #

Very informative, thanks for clearing up some issues for me! :)

Posted by Mr Big on October 05, 2010 at 11:18 AM MDT #

This format currency works in bi publisher but not in xml publisher in r12 . I would appreciate any thoughts for this

Posted by guest on July 28, 2011 at 12:53 AM MDT #

Useful article but I am facing implementing the same in BIP 10.1.3.4.0. Inspite of doing all the required config settings (xdo.cfg) and proper usage, the output is not reflecting the formatting mentioned in config file.
Is there an issue of currency formats in 10.1.3.4.0 version ?

Posted by Rohit on December 13, 2011 at 09:41 PM MST #

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
« April 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
   
       
Today