When we developers create analyses/canned reports, we often include data that shows currency, such as American dollars. As the administrator, you can perform various tasks that affect currency options that are available to users.
Oracle Business Intelligence users can select the currency in which they prefer to view currency columns in analyses and dashboards. They select the currency in the Currency box in the My Account dialog box, Preferences tab. You define the currency options that are to be displayed in the Currency box in the userpref_currencies.xml file.
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. (These currency options must be for currencies to which your installation can convert columns.) 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.
For the user-preferred currency options to take effect, the following configuration also must be done in the Oracle Business Intelligence repository:
Oracle Business Intelligence users can select the currency in which they prefer to view currency columns in analyses and dashboards. They select the currency in the Currency box in the My Account dialog box, Preferences tab. You define the currency options that are to be displayed in the Currency box in the userpref_currencies.xml file.
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. (These currency options must be for currencies to which your installation can convert columns.) 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.
For the user-preferred currency options to take effect, the following configuration also must be done in the Oracle Business Intelligence repository:
Modify the userpref_currencies.xml file
Navigate to,
C:\Middleware\instances\instance1\config\OracleBIPresentationServicesComponent\coreapplication_obips1
Before modifying userpref_currencies.xml, make a copy of it in the same directory.
Remove the first <UserCurrencyPreferences> tag and the <!-- comment marker.
Remove the --> comment marker and the last </UserCurrencyPreferences> tag.
Your file should look similar to the following screenshot:
<Config>
<UserCurrencyPreferences currencyTagMappingType="static">
<UserCurrencyPreference sessionVarValue="USD" displayText="USD America" currencyTag="int:USD" />
<UserCurrencyPreference sessionVarValue="EUR" displayText="Euro Dollars" currencyTag="int:euro-l" />
<UserCurrencyPreference sessionVarValue="JP" displayText="Japnees Currency" currencyTag="loc:ja-JP" />
<UserCurrencyPreference sessionVarValue="ORGC" displayText="Org Currency" currencyTag="loc:en-BZ" />
<UserCurrencyPreference sessionVarValue="lc1" displayTag="int:DEM" currencyTag="int:DEM" />
</UserCurrencyPreferences>
</Config>
Save and close the userpref_currencies.xml file.
Creation of the PREFERRED_CURRENCY session variable
Go to your repository
Manage > Variables.
Select Action > New > Session > Initialization Block.
Name the initialization block IB_PREFERRED_CURRENCY.
Click Edit Data Source.
Select Default initialization string.
Write a below query to set a session variable value.
select 'VALUEOF(NQ_SESSION.PREFERRED_CURRENCY)' from dual
Click Edit Data Target
Give it a name as, PREFERRED_CURRENCY
Set an appropriate and dedicated connection pool.
Select Enable any user to set the value. Select this option to set session variables after the initialization block has populated the value (at user login) by calling the ODBC store procedure NQSSetSessionValue(). For example, this option lets non-administrators to set this variable for sampling.
Put 'EUR' as a default initializer
Make sure you IB reflect the below image.
Conversion setup of logical currency columns in the Business Model and Mapping layer
Create a logical columns for converting a currency values from one to another. Typically, this currency conversion should be calculated as part of your data warehouse extract, transform, and load (ETL) process.
Euro Currency: "SupplierSales"."Fact-Sales"."Dollars" * .75
Japnees Currency: "SupplierSales"."Fact-Sales"."Dollars" * .89
Org Currency: "SupplierSales"."Fact-Sales"."Dollars" * 1.57
Create a logical column to use the appropriate conversion factor using the PREFERRED_CURRENCY session variable.
Create a new logical column named as, Preferred Currency
Select Derived from existing columns using an expression.
Put the below formula,
IndexCol( CASE VALUEOF(NQ_SESSION."PREFERRED_CURRENCY")
WHEN 'USD' THEN 0
WHEN 'EUR' THEN 1
WHEN 'JP' THEN 2
WHEN 'ORGC' THEN 3
ELSE 4 END,
"Supplier Sales"."D1_ORDERS2"."ACTLEXTND",
"Supplier Sales"."D1_ORDERS2"."Euro Currency",
"Supplier Sales"."D1_ORDERS2"."Japnees Currency",
"Supplier Sales"."D1_ORDERS2"."Org Currency",
"Supplier Sales"."D1_ORDERS2"."ACTLEXTND")
Drag this Preferred Currency column to the physical layer.
Set default currency as per your preferences
- Login to OBIEE > My Account > Preferences
This shows how the values that are generated dynamically from the SQL statement in the userpref_currencies.xml file are displayed in a drop-down list of currency options for the Currency box on the Preferences tab of the My Account dialog. The drop-down list is similar to what is displayed for a prompt on a dashboard page.
- Set currency as Euro
Create analysis and confirm the Preferred Currency column displays the preferred currency selected on the My Account page (Euro, in this example).
Take for example,
Year, Dollars, Preferred Currency in to your analysis.
- For Preferred Currency, goto Column Properties > Data Format
- Select Override Default Data Format.
- Set Treat Numbers As to Currency.
- Set Currency Symbol to User's Preferred Currency
- Confirm that the Preferred Currency column displays the preferred currency selected on the My Account page (Euro, in this example).
- Click on results.
Try changing currency from my account again.
Happy blogging.. :-)
