BI Direct: Oracle Business Intelligence Enterprise Edition 11.1.1.6.0 –Multicurrency Report with Currency Prompt

As discussed in my previous blog following are the steps need to carryout for multicurrency support for OBIEE 11.1.1.6.0

· Step 1 – Modify the user preferences currency file

· Step 2 – Create session variable PREFERRED_CURRENCY

· Step 3 – Create logical column for currency conversions

· Step 4 – Verification of multicurrency support configuration

The verification of multicurrency support configuration can be done by setting the preferred currency for individual account setting as stated below.

The verification of multicurrency support configuration can be done by setting the preferred currency for individual account setting as stated below and then creating and running an analysis below which has the preferred currency revenue column (remember to set the data format as currency and set Currency Symbol to User’s Preferred Currency).

Rather than changing account setting for currency and then executing the report to get the revenue values in desired currency is time consuming in case if you would like to report for available currencies.

The blog is an attempt to demonstrate how currency prompt can be used to select the currencies from pre-defined list of currencies to drive the report.

Creating Currency Prompt

Oracle BIEE allows specific prompting for currency selection. A currency prompt can be created as any other dashboard prompt by using New->Dashboard Prompt->Currency Prompt. A user’s currency selection is passed to the Oracle BI Server as a session variable that an RPD administrator can use to handle common currency-handling schemes.

Analyses (Report)

A report is been designed with following columns from the A-Sample Sales Subject area.

The definition of the columns included in the report is as follows

# of Orders - "Counts"."31 # of Orders (Cnt Distinct)"

Order Currency - "Orders"."R3 Order Currency"

Average FX Rate to Pref Currency - "FX conversion"."70 Average FX Rate to Pref Currency"

Converted Into - VALUEOF(NQ_SESSION.PREFERRED_CURRENCY)

Converted Rev (Indexcol) - "FX conversion"."120 Converted Rev (Indexcol)"

Revenue - "Base Facts"."1- Revenue"

Place the currency dashboard prompt and report designed on a dashboard page. It enables changing the currency from a list of available currencies and impacts both the format, as well as the values displayed. Choose a currency from the available options in the currency prompt and notice how the table changes. The appropriate currency symbol will be displayed in the analysis and the value will be converted to the right currency, using the historical rate in this case.

Dashboard Layout