OBIEE 11G - Currency

About

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 box on the My Account dialog: Preferences tab
the Currency Prompt option on the Definition pane of the Prompt editor.

The currency value is stored in the OBIEE - System session variables (reserved variables) system session variable. This value is controlled via

the Currency box on the My Account dialog: Preferences tab

or with a Dashboard Currency Prompt

The value is send back to the BI Server and permit to create logical expression in a logical column that control the currency.

Articles Related

Configuration

userpref_currencies.xml

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

Static

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:

sessionVarValue=“sessionVarValue ” sets the session variable PREFERRED_CURRENCY. For its value, specify a string that uniquely identifies the currency.
(optional) displayText=“displayTextvalue” sets the presentation variable currency.userPreference to a value that is not localized. To localize the display, you must use the displayMessage tag. See Localizing Messages for Users' Preferred Currency.
currencyTag=“currencyTagvalue” identifies the Currency Tag in the currencies.xml file whose displayMessage value is to be used to populate the Currency box on the My Account dialog: Preferences tab and currency prompts. (The currencies.xml file, which is located in ORACLE_HOME\bifoundation\web\display, provides currency formats.)

Dynamic

To define user-preferred currency options using a dynamic mapping:

Add a UserCurrencyPreferences element as follows:

<UserCurrencyPreferences currencyTagMappingType="dynamic">
   <UserPrefCurrencyLogicalSQL>
      SELECT column1, column2, column3 FROM userCurrencyPreference
   </UserPrefCurrencyLogicalSQL>
</UserCurrencyPreferences>

where in the logical sql:

column1 contains the values that are used to set the session variable PREFERRED_CURRENCY. Each value in this column is a string that uniquely identifies the currency.
column2 contains the currency tags in the currencies.xml file whose displayMessage values are to be used to populate the Currency box and currency prompts, for example, int:euro-1. (The currencies.xml file, which is located in ORACLE_HOME\bifoundation\web\display, provides currency formats.)
(optional) column3 contains the values used to set the presentation variable currency.userPreference. Each value in this column is a string that identifies the currency, such as Global Currency 2. If you omit column3, then the values for the displayMessage attributes for the corresponding currency tags in the currencies.xml file are used.

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

Logical Expression

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"
)

Answers

In Answers, you can change the currency format in the properties of the column.

Extra Configuration

Default System Currency

To set the default currency:

Open the currencies.xml file in the directory (Fusion Middelware|Weblogic|OPMN) - Directory Structure and Home\bifoundation\web\display.
Search for the currency to make the default, for example, euro left and copy it

<Currency tag="int:euro-l" type="international" symbol="€" displayMessage="kmsgCurrencyEuroLeft" digits="2" format="$ #"
 displayName="kmsgCurrencyEuroName">
      <negative tag="minus" format="-$ #" />
</Currency>

Search for the tag int:wrhs, located near the top of the file.

<Currency tag="int:wrhs" type="international" placeholder="true" symbol="$" format="$#" digits="2"
 displayMessage="kmsgCurrencySiebelWarehouse">
      <negative tag="minus" format="-$#" />
</Currency>

Select the entire element and replace it by pasting the copied element over it.
Replace the tag attribute so it reads int:wrhs.

<Currency tag="int:wrhs" type="international" symbol="€" displayMessage="kmsgCurrencyEuroLeft" digits="2" format="$ #"
 displayName="kmsgCurrencyEuroName">
      <negative tag="minus" format="-$ #" />
</Currency>

Restart the service for Oracle BI Presentation Services.

Documentation / Reference