Configure Logical Columns for Multicurrency Support

You can configure logical columns to allow users to select the currency that they want to display their visualizations, analyses, and dashboards currencies columns in.

You can set up this feature so that all users see the same static list of currency options, or you can provide a dynamic list of currency options that changes based on a Logical SQL statement you specify.

When you use session variables in an expression, you must use this format: VALUEOF(NQ_SESSION.var_name). Edit any logical columns that display currency values to use the appropriate conversion factor using the PREFERRED_CURRENCY session variable.

See Create an Initialization Block and Create a Session Variable.

The following logical column expression uses the value of the NQ_SESSION.PREFERRED_CURRENCY variable to switch between different currency columns. The currency columns are expected to have the appropriate converted values. 

INDEXCOL( CASE VALUEOF(NQ_SESSION.PREFERRED_CURRENCY) WHEN 'gc1' THEN 0
WHEN 'gc2' THEN 1 WHEN 'orgc' THEN 2 WHEN 'lc1' THEN 3 ELSE 4 END,
"Paint"."Sales Facts"."USDCurrency",
"Paint"."Sales Facts"."DEMCurrency" ,
"Paint"."Sales Facts"."EuroCurrency" ,
"Paint"."Sales Facts"."JapCurrency" ,
"Paint"."Sales Facts"."USDCurrency" )
An Administrator must set up Oracle Analytics Currencies XML system setting to enable multicurrency support. For information about this configuration, see About Advanced Configurations and Set Advanced Options.
  1. Click Variables.
  2. Click Create, click Create Initialization Block, and create the session variable's initialization block.
  3. Create a session variable and name it PREFERRED_CURRENCY. Make sure to select the Enable any user to set the value field for the session variable.
  4. Click Save.
  5. Click Logical Layer Logical layer icon.
  6. In the Logical Layer pane, browse for the table with the logical column you want to configure for multicurrency.
  7. In the logical table, click the Columns tab.
  8. In the column table, click the column to highlight it, and then click Detail view to view its properties.
  9. In the logical column's Sources properties, click the Logical Expression field and the click Open Expression Editor.
  10. In the Expression Editor, create and validate a derived expression that uses the PREFERRED_CURRENCY variable.
  11. Click Save to save the expression.
  12. Optional: To provide a dynamic list of currency options, create a table in your data source that provides the entries you want to display for the user-preferred currency. This table must include the following columns:

    • The first column contains the values used to set the session variable PREFERRED_CURRENCY. Each value in this column is a string that uniquely identifies the currency (for example, gc2).

    • The second column contains currency tags from the Currencies XML system setting. The displayMessage values for each tag are used to populate the Currency box and currency prompts, for example, int:euro-1.

    • You can provide a third column that contains the values used to set the presentation variable currency.userPreference. Each value in this column is a string that identifies the currency, for example, Global Currency 2. If you omit this column, then the values for the displayMessage attributes for the corresponding currency tags located in the Currencies XML system setting are used.

    Sample user-preferred currency entries:

    • UserPreference: orgc1, CurrencyTag: loc:en-BZ, UserPreferenceName: Org currency
    • UserPreference: gc2, CurrencyTag: int:euro-1, UserPreferenceName: Global currency 2
    • UserPreference: lc1, CurrencyTag: int:DEM, UserPreferenceName: Ledger currency
    • UserPreference: gc1, CurrencyTag: int:USD, UserPreferenceName: Global Currency 1