Multi Currency Reports

If you are writing reports that will show currency data across multiple currencies then templates can get complex all to quickly. With the introduction of the Euro-currency things have gotten simpler. I rememeber writing Oracle Reports for my old team EMEA Financials and having to handle multiple currencies, even revaluation reports to get amounts to a single currency. We had some nice user exits to format currencies for us to make life simlper. You'll be pleased to hear that publisher can help you there too - no matter what flavor you are running. Life is admittedly easier for EBS users, and will shortly be as easy for standalone with PS and JDE following along later.

The xdo.cfg file can contain all the formatting information required any currency you happen to be reporting on, the entries for the currencies are very straight forward.

      <currency code="USD" mask="999D99"/>
      <currency code="JPY" mask="999D99XX"/>
For the actual formats just refer to the user guide on currency formatting.

Once you have defined the formats you can then reference them in your templates thus:


When the formatting engine executes it will pick up your formats and apply them to your currency values. If you apply a mask that does not exist then you'll get good ol 2 d.p. output ... not good if you want Yen!

I mentioned that life was easier for EBS users, you get a nice user interface to create the formats in the configuration manager tab, its strightforward stuff.

  1. Navigate to the Currencies sub-tab under the Administration tab. Click on Create Currency Format Sets.

    Enter a name and a code for this set. The code is a unique identifier and cannot be changed later.

  2. The View Currency Formats page shows the currencies formats available in this set. You can also update the currency set (changing its name), or update/delete any currency formats already created. Click on Add Currency Format.

  3. Select a currency from the LOV. This list is generated from the FND currency table so you can add more currencies if needed from System Adminstrator responsibility (but all iso currencies should be covered).

    The format mask should be in Oracle number format. The format should be MLS compliant so 'D' and 'G' should be used instead of explicitly specifying the separators. In fact use only '9', '0', 'D', and 'G' to compose the number format. (Some information regarding Oracle number formats can be found at this page:

    There is currently no validation of the format mask. This may be included in a future release with APIs from the i18n library.

  4. Once the currency format set is created, you can assign it to the Currency Format Set property in the configuration page (under category FO Processing).

Standalone, JDE and PS users, you need to wait a while and keep plugging away at that config file for now. 


Hello can we use this with peoplesoft we are in a situation where we need to use multi currency thanks a lot

Posted by kranthi on August 28, 2008 at 03:09 AM MDT #

gratefulness you extranormally much for your awarenesss on getting people to job upons on our headway/blog. Do you diverge a start up beyond in one's afrepresented by there can be some strangeized or usbent practicalitys why people do not fortransport inspects as unexcitedly?

Posted by Robxpdem on February 17, 2009 at 12:12 AM MST #

Hi actualy what I need is the way to select in the sql region of the Oracle BI publisher using the dataset regionI am trying to select using the oracle formart to_char(amount, L9,999.00) or fomart(amount,2) as my db is in mysql but I get en error H ave you had such problems or know how to sort them out thanks

Posted by Calvin Omari on February 24, 2010 at 04:09 PM MST #

Post a Comment:
  • HTML Syntax: NOT allowed

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!


« June 2016