OraFAQ Forum: Reports & Discoverer » Currency Formatting In Reports

Home » Developer & Programmer » Reports & Discoverer » Currency Formatting In Reports (Reports 6i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Currency Formatting In Reports [message #293730]

Mon, 14 January 2008 15:06

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.

Report message to a moderator

Re: Currency Formatting In Reports [message #293736 is a reply to message #293730]

Mon, 14 January 2008 16:04

Littlefoot
Messages: 21823
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.

Report message to a moderator

Previous Topic:	Reg SpreadSheet Output
Next Topic:	Report Printing Problem

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Nov 26 20:34:26 CST 2024