Currency Formatting In Reports [message #293730] |
Mon, 14 January 2008 15:06 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
kumar_dnvb
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 Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 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 ! ?
etc. 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);
BEGIN
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');
ELSIF
...
END IF;
RETURN (retval);
END;
There might be some other, simpler way, but - at this late time of a day, nothing better comes on my mind.
|
|
|