Home » Developer & Programmer » Reports & Discoverer » Currency Formatting In Reports (Reports 6i)
Currency Formatting In Reports [message #293730] Mon, 14 January 2008 15:06 Go to next message
Messages: 13
Registered: January 2007
Junior Member
hi all,

i have report where in i need to format based on the currency code and need to have 2 decimal places of precision.

for eg: USD ===> 2,300.00
EUR ===> 2.300.00

Could any one please provide me a solution for this.
Re: Currency Formatting In Reports [message #293736 is a reply to message #293730] Mon, 14 January 2008 16:04 Go to previous message
Messages: 20959
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm afraid that there's no a "simple" solution. A default number format, such as NNGNNNDNN (where 'G' represents a "group" separator and 'D' a decimal point character) will be fine as long as you don't have to mix EUR and USD values. In another words: if you have a report and it is run in New York, USA, it will be fine. If you run it in Paris, France, it will be fine as well - you'll just have to ask a DBA to correctly set NLS_NUMERIC_CHARACTERS.

However, if you are running this report in Dubrovnik, Croatia, where tourists every year spend both Euros and Dollars, your customers will need to display both EUR and USD in the same report, and NLS_NUMERIC_CHARACTERS will not help.

You'll have to create additional table in order to map group separator and decimal character. Something like this (a stupid example; don't pay too much attention to it):
id currency group decimal
-- -------- ----- -------
1  EUR      .     ,
2  USD      ,     .
3  CHF      .     ,
4  HRK      !     ?
Now, how to properly format numeric values? The easies way would probably be to find common groups and use such a code (in a formula column, which would return a CHARACTER, formatted as desired):
  retval VARCHAR2(20);
  IF currency IN ('EUR', 'GBP', 'HUF') THEN
     retval := TO_CHAR(:some_value, '999.999.990,00');
  ELSIF currency IN ('USD', 'ABC', 'DEF') THEN
     retval := TO_CHAR(:some_value, '999,999,990.00');

  RETURN (retval);

There might be some other, simpler way, but - at this late time of a day, nothing better comes on my mind.
Previous Topic: Reg SpreadSheet Output
Next Topic: Report Printing Problem
Goto Forum:

Current Time: Tue Jan 17 05:06:24 CST 2017

Total time taken to generate the page: 0.07244 seconds