While multi-currency support in Salesforce is very robust, CRM Analytics doesn't support multi-currency whatsoever. In the following article, I will explain how to display amounts in the corporate currency, record currency, user currency, as well as to implement currency picker that recalculates amounts based on user selection.

For the purpose of this demonstration, I have set EUR as the corporate currency, activated multi-currency (both in Setup -> Company Information), and added CZK to the list of active currencies (Setup -> Manage Currencies). Advanced Currency Management is turned off (Dated Exchange Dates are not considered).

Default behavior

Let's start with a description of what's going to happen with currency-type fields in CRM Analytics if we don’t make any modifications.

When CRM Analytics accesses the data, it always happens in the context of Analytics Integration User. As a result, the sync converts all of the currency-type fields into the currency selected on the Analytics Integration User.

It doesn't matter if CurrencyIsoCode on Opportunity was set to Central African Francs or Indian Rupee; if currency on your Analytics Integration User is set to EUR, the value in all currency-type fields will be in EUR. If we want to see the values in the record currency, we need to transform it back.

Currency Type

To convert the currency into other currencies, we will need the exchange rates. Exchange rates used during the conversion are maintained in SObject CurrencyType. Technically, you can use any source for the conversion, but from my experience, users are very concerned, when CRM Analytics and Salesforce Record Page show different numbers.

So, before we do anything else, we need to add the Currency Type object into Connection and sync it. It is useful to add filter for active currencies only.

I also recommend setting Analytics Integration User currency to corporate currency, so you avoid complicated magic when calculating conversions.

Record Currency

The easiest situation is when you want to display amounts in the record currency. For this purpose, we will create a recipe that will join Opportunity with Currency Type and calculate amounts for each row in the Opportunity dataset.

1. Create a recipe and add Currency Type and Opportunity as inputs.
2. Join Opportunity with Currency Type on CurrencyIsoCode.

3. Add a custom transformation formula: "ConversionRate.ConversionRate" * ExpectedRevenue and save the result as a number(18,2).

4. Save the output into the dataset.

For testing purposes, you can create a Dashboard with a column chart that will display the sum of Expected Revenue in corporate and record currency grouped by year and month and CurrencyIsoCode.

User Currency

To display the user's currency or to create a currency picker (the next use case), you can either leverage cross join in recipe (duplicating each Opportunity per each active currency) or do all the conversions in SAQL. User currency use case will be demonstrated with a recipe, and the currency picker with SAQL.

1. Create a recipe and add Currency Type and Opportunity as inputs.
2. Join Opportunity with Currency Type using cross join.

3. Add a custom transformation formula: "ConversionRate.ConversionRate" * ExpectedRevenue and save the result as a number(18,2).
4. Save the output into the dataset.

I will stop here to point out one significant disadvantage – if you use this method on an object with high volume, it will waste a lot of storage in CRM Analytics. Additionally, because we will require adding some bindings, we will lose the ability to control the charts (that we are enhancing with multicurrency support) from UI anyway.

Right now, we have every Opportunity as many times in the dataset as the number of currencies we have. Therefore, we will need to filter all charts in which we want to use this dataset by the currency set on the User record. To achieve that, we first need to query the current user information. We will use JSON editor to create the query. Then we will use binding to get the user currency into the charts.

1. Create a dashboard.
2. Open JSON editor (CTRL + E) and add the following query into the steps:
   

   "CurrentUser": {
                   "broadcastFacet": true,
                   "groups": [],
                   "numbers": [],
                   "query": "SELECT Id, Name, DefaultCurrencyIsoCode FROM User Where Id = '!{User.Id}'",
                   "receiveFacetSource": {
                       "mode": "all",
                       "steps": []
                   },
                   "selectMode": "single",
                   "strings": [],
                   "type": "soql",
                   "useGlobal": true
               }


3. Create a table and add Close Date, Currency Iso Code (from Currency Type, not of the Opportunity) and Expected Amount (Converted).

4. Add filter for Currency Iso Code to filter any value you want.
5. Open the table in Advanced Editor.
6. Update your filter with the following binding:
   

   {{cell(CurrentUser.result, 0, \"DefaultCurrencyIsoCode\").asString()}}

Everything is working now and we can review the results.

Currency Picker

In this use case, we will create a currency picker for the users to select the currency in which they want to view our Expected Revenue field. The currency picker will be prepopulated with the user's currency. We will start by retrieving the user's currency in the same way we did in the previous use case.

1. Create a dashboard.
2. Open JSON editor (CTRL + E) and add the following query into the steps:

   "CurrentUser": {
                   "broadcastFacet": true,
                   "groups": [],
                   "numbers": [],
                   "query": "SELECT Id, Name, DefaultCurrencyIsoCode FROM User Where Id = '!{User.Id}'",
                   "receiveFacetSource": {
                       "mode": "all",
                       "steps": []
                   },
                   "selectMode": "single",
                   "strings": [],
                   "type": "soql",
                   "useGlobal": true
     			}

Next, we will a place currency picker with the user's currency as the default selection. The list selector will also be used to retrieve the conversion rates. To make this happen, we will need to introduce a few workarounds. I will explain these workarounds next to the instructions.

1. Add a list selector to the dashboard.

2. Select Currency Type (from Salesforce Objects) as the dataset.
3. Then, select the field CurrencyIsoCode.
4. Add second field Conversion Rate into the bar length.
   
5. Double-click on the list selector, select query mode, and update the widget.
   (CRM Analytics will not allow us to set an initial value unless there is SOQL or SAQL in the underlying query. It would display the following error message: "Start value bindings are not supported for this query type.")
   
6. Open the list selector in the Advanced Editor and remove the attribute "sobject" from the query JSON.
   (Otherwise, you will receive the message "Unrecognized field sobject" upon saving the dashboard.)
7. In the Advanced Editor, add a binding for the initial value:

   "start": "{{cell(CurrentUser.result, 0, \"DefaultCurrencyIsoCode\").asString()}}"

   .

Now, we have a currency picker with the user's currency as the preselected value and conversion rate available for us to bind.

Conversions with SAQL

In the next set of steps, we will create a chart that will display the sum of Expected Revenue in the selected currency, grouped by Close Date (year and month). The calculation will be defined in the SAQL; therefore we cannot use Salesforce object for this chart, but we will use the dataset created in the Record Currency use case.

1. Add a bar chart to the dashboard.
2. Use OpportunityRecordCurrency (the one from the first use case) as a source dataset.
3. Add Close Date (Year-Month) and Expected Revenue to the table.

4. Save it (just to be sure) and reopen it in query mode.
5. We will adjust the line generating the output to multiply sum(q.'ExpectedRevenue') by conversion rate {{cell(IsoCode_1.selection, 0, "expr1").asString()}}.
   The whole query will look like this:
   

   q = load "OpportunityRecordCurrency";
   q = group q by ('CloseDate_Year', 'CloseDate_Month');
   q = foreach q generate q.'CloseDate_Year' as 'CloseDate_Year', q.'CloseDate_Month' as 'CloseDate_Month', sum(q.'ExpectedRevenue') * {{cell(IsoCode_1.selection, 0, "expr1").asString()}} as 'Expected Revenue (Converted)';
   q = order q by ('CloseDate_Year' asc, 'CloseDate_Month' asc);
   q = limit q 2000;

And we are finished. When you click on the Preview, you can see that the currency picker is preselected with user’s currency, whenever you pick a different currency, Expected Revenue is immediately recalculated. I have used all of the use cases above in my projects as there is always time and place to use them.

Looking for an experienced Salesforce Architect?

• Are you considering Salesforce for your company but unsure of where to begin?
• Planning a Salesforce implementation and in need of seasoned expertise?
• Already using Salesforce but not fully satisfied with the outcome?
• Facing roadblocks in your Salesforce implementation and require assistance to progress?

Feel free to review my profile and reach out for tailored support to meet your needs!