X

An Oracle blog about BI Publisher

  • RTF
    August 27, 2008

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!

Join the discussion

Comments ( 18 )
  • Ike Wiggins Wednesday, August 27, 2008
    Malagasy Ariary
  • kranthi Thursday, August 28, 2008
    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
  • Tim Thursday, August 28, 2008
    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
  • Donn Shumway Wednesday, September 3, 2008
    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
  • Tim Wednesday, September 3, 2008
    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
  • Paulo Tolosa Wednesday, September 3, 2008
    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
  • Tim Friday, September 5, 2008
    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
  • RS Thursday, March 25, 2010
    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
  • RS Thursday, March 25, 2010
    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
  • RS Thursday, March 25, 2010
    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
  • RS Thursday, March 25, 2010
    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')
  • Damion Fulton Saturday, July 31, 2010
    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.
  • Charolette Tokkesdal Sunday, August 1, 2010
    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
  • never fail list building system bonus Monday, August 23, 2010
    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.
  • Get ex back Tuesday, September 21, 2010
    Good post, thanks
  • Mr Big Tuesday, October 5, 2010
    Very informative, thanks for clearing up some issues for me! :)
  • guest Thursday, July 28, 2011

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


  • Rohit Wednesday, December 14, 2011

    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 ?


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.