Table of Contents

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 System Session Variable system session variable. This value is controlled via

  • the Currency box on the My Account dialog: Preferences tab

Obiee11g Saw Preference Currency

  • or with a Dashboard Currency Prompt

Obiee11g New 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.

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

<UserCurrencyPreferences> 
<?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.

Obiee11g Physical 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.

Obiee11g Treat Numbers As Currency

Extra Configuration
Default System Currency

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>
  • 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>
Documentation / Reference