KB8112: How to offer multiple currency support in MicroStrategy Developer 9.x

 
In Strategy Developer 9.x there are several workarounds that can be made to offer partial support for multiple currencies. This document provides a variety of options suitable for different circumstances.
 
Method 1- Use of an Exchange Rate fact table:

1. Create a new fact table EXCH_FACT with DATE and EXCH_RATE as the two columns.
2. Insert the date and desired exchange rate into this table for all dates in the Database
3. Add this EXCH_RATE as a fact column in Strategy Desktop.

 
In Strategy Developer 9.x, users can now create a metric for EXCH_RATE as follows:

1. Definition: SUM(EXCH_RATE)
2. Dimensionality: Day level (non aggregatable - ending value)

 
Create the Basic sales(£) metric as /
 
When adding this metric to a template with an empty filter, the exchange rate is based on the ending value (i.e., the last entered date in the EXCH_FACT) at the lower levels; it will be at the end of the time period entered (so end of day, week, quarter, etc.).
 
Method 2 - Fixed conversion within a metric:
If users have a basic metric which is SUM(SALES_DOLLARS) to display the TOTAL SALES ($) and they wish to display UK Pounds next to it, users could duplicate this metric and add the conversion rate in the metric definition. For example SUM(SALES_DOLLARS)/1.6.
 
The only downside to this is the maintenance of the metric when the conversion rates changes. It depends on the timescales on which to view the data. The above would be good if users wished to see:

1. Information based on today's exchange rate
    
   or
    
   
2. Information where the exchange rate was x

 
But this would not be available if users wished a report that used the exchange rates for a particular day added together.
 
Method 3 - Store Sales Data in both Local Currency and a Common Currency:
Store the sales data in both the local currency and a common currency in the sales fact table. For example, the common currency can be United States Dollars (USD) or European Currency Unit (ECU). In addition, the exchange rate between the local and common currency is maintained in a separate lookup table and updated regularly.
 
 
Method 4 - Prompt the User for the Exchange Rate:
In this way, users would enter the exchange rate for two of the metrics (e.g., Euro and US Dollars) and this would, then, be calculated on-the-fly.
 
 
Metric Formats and Currency Symbols:
For details on how to create a custom format for each currency, refer to the Strategy Knowledge Base document below:
KB6295 (KB4000-7X0-0556): How to format a metric so that it will be scaled down by thousands or millions in Strategy Desktop 9.x
 
This assumes that the currency symbol is not available when users open the metric editor and go to Format > Currency > Symbol.
 
 
 
Start from Strategy9.3.0, the ability to apply a dynamic currency formatter to a metric in web was introduced. Please refer to Technical Document below for more details:
KB42412 -- How to Apply a Dynamic Currency Formatter to a Metric in Strategy Web 9.3.0
Article Reference Number:
KB8112