Before using the currency mechanism of OBIEE, you must configure the userpref_currenciesxml in order to make it available to the user. The following elements are made available:
The currency value is stored in the OBIEE - System session variables (reserved variables) system session variable. This value is controlled via
The value is send back to the BI Server and permit to create logical expression in a logical column that control the currency.
You define the currency options that are to be displayed in the Currency box and in a currency prompt in the userpref_currencies.xml file.
Defining the currency options also controls whether the Currency box is available on the My Account dialog: Preferences tab and whether the Currency Prompt option is available on the Definition pane of the Prompt editor.
To define a static or a dynamic choice for all user, you must modify the file userpref_currencies.xml located in the following directory:
ORACLE_INSTANCE\config\OracleBIPresentationServicesComponent\coreapplication_obips1
For each currency option to be displayed in the Currency box or in currency prompts, add a UserCurrencyPreference element between the
<?xml version="1.0" encoding="utf-8" ?>
<Config>
<UserCurrencyPreferences currencyTagMappingType="static">
<UserCurrencyPreference sessionVarValue="USD" displayText="Global Currency 1" currencyTag="int:USD" />
<UserCurrencyPreference sessionVarValue="EUR" displayText="Global Currency 2" currencyTag="int:euro-l" />
<UserCurrencyPreference sessionVarValue="AUD" displayText="Global Currency 3" currencyTag="int:AUD" />
</UserCurrencyPreferences>
</Config>
Where:
To define user-preferred currency options using a dynamic mapping:
<UserCurrencyPreferences currencyTagMappingType="dynamic">
<UserPrefCurrencyLogicalSQL>
SELECT column1, column2, column3 FROM userCurrencyPreference
</UserPrefCurrencyLogicalSQL>
</UserCurrencyPreferences>
where in the logical sql:
Sample Logical SQL Results
“Markets”.“UserPreference” | “Markets”.“CurrencyTag” | “Markets”.“UserPreferenceName” |
---|---|---|
char | char | char |
orgc1 | loc:en-BZ | Org currency |
gc2 | int:euro-1 | Global currency 2 |
lc1 | int:DEM | Ledger currency |
gc1 | int:USD | Global Currency 1 |
Example of Logical Column expression with the indexcol function that use the PREFERRED_CURRENCY system session variable to handle the currency.
INDEXCOL(
CASE VALUEOF(NQ_SESSION."PREFERRED_CURRENCY")
WHEN 'USD' THEN 0
WHEN 'EUR' THEN 1
WHEN 'AUD' THEN 2 END,
"01 - Sample App Data (ORCL)"."Catalog"."BISAMPLE"."F19 Rev. (Converted)"."Revenue_Usd",
"01 - Sample App Data (ORCL)"."Catalog"."BISAMPLE"."F19 Rev. (Converted)"."Revenue_Eur",
"01 - Sample App Data (ORCL)"."Catalog"."BISAMPLE"."F19 Rev. (Converted)"."Revenue_Aud"
)
In Answers, you can change the currency format in the properties of the column.
To set the default currency:
<Currency tag="int:euro-l" type="international" symbol="€" displayMessage="kmsgCurrencyEuroLeft" digits="2" format="$ #"
displayName="kmsgCurrencyEuroName">
<negative tag="minus" format="-$ #" />
</Currency>
<Currency tag="int:wrhs" type="international" placeholder="true" symbol="$" format="$#" digits="2"
displayMessage="kmsgCurrencySiebelWarehouse">
<negative tag="minus" format="-$#" />
</Currency>
<Currency tag="int:wrhs" type="international" symbol="€" displayMessage="kmsgCurrencyEuroLeft" digits="2" format="$ #"
displayName="kmsgCurrencyEuroName">
<negative tag="minus" format="-$ #" />
</Currency>