*** >800 hits and ONLY 3 ratings!!   If you like it let us know!!  ***

Ok, so you may have found that in Oracle analytics currency fields are always reported in the user or base currency, one or the other!

Not great when you want to have a report that shows the base and local currency value of say your pipeline. Oracle provide a tech note (749419.1) that confirms this. The only workaround that I have found is to 'hardcode' the exchange rate into your reports formula. But doing that means everytime the exchange rates change, you need to change all the expressions in all the reports that report dual currencies.

Here is a different workaround that might just get you want you want.

In this example I need a report that shows base currency of opportunity revenue, together with local (entered) currency.

eg.

In this case the exchange rate is applied based on the oppty currency code.

For my client I created a dashboard to hold a simple listing report. This report as you can see had values in base (AUD) and 'local' currency.

What's important is the way the report converts back to local currency. To do this I setup a formula as such;

You can see it uses a 'CASE' statement to choose the correct exchange rate, the @{XRATE_USD} is a presentation variable that will come from the dashboard.

The dashboard has a set of prompts at the bottom of the first page. And looks like this when setting up.

You can see it puts each an exchange rate into the presentation variable. It also defaults the exchange rate, this means that

1. the user doesnt need to define the exchange rate each time the report is run

2. The rate is changed once in the dashboard prompt and can effect all reports referring to it on the dashboard, or multiple dashboards! Easy maintenance!!

You will note the prompt fields needn't be specific fields for the purpose, just use any number field, provided your report doesn't use it to filter on.

You can set this up for as many currencies as you like, you just need to 'work' the expression. This wont work however if you need a 'spot' currency rate, or your rate changes too frequently (eg intra-day) to update the values in the prompt, but if you have monthly changes its not an onerous overhead.

If you like this please rate it above.