Dynamics CRM Multiple Currencies and Reporting
In a recent post I covered common misconceptions about CRM multiple currency capabilities. In this post, I’m going to expand on how you can use multiple currencies in CRM reports.
If you use multiple currencies in you CRM environment, a common request is to adjust reports, like pipeline reports, to the currency of the user viewing the report.
As mentioned in the previous post, CRM users are linked to currencies via user settings. This determines the default currency of records created by the user, but it does not automatically adjust money values to the currency of the user viewing the data.
There are several approaches that you can take to adjust SSRS reports to the currency of the user viewing the report.
1. The user entity Exchange Rate field. The CRM user entity (systemuser) contains a standard exchange rate field, which contains the current exchange rate of the user’s currency. However, the downside to this field is that the user currency is a user setting, and cannot be easily administered by a system administrator.
2. Add a custom user currency lookup to the user record. Transactioncurrency, like most other entities, supports adding additional relationships. By creating a N:1 relationship between systemuser and transactioncurrency, you can create a lookup field for currency that is easily updatable by a system administrator (and can be automatically updated via a data load when the user is created). This relationship will also create a data structure where you can easily retrieve the current exchange rate of the selected currency in your report query.
Let’s look at an example.
We create a custom currency lookup field on the user record called “new_localcurrency” and populate it with the currency of the user.
In our report, add a dataset for user currency. If using Fetch XML, the following query will work.
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
Next, in the report define a parameter for Exchange Rate. We do this since Exchange Rate won’t be part of the primary data set, a parameter is easier to call in an expression. Specify the exchange rate as the default value for the parameter.[responsive][/responsive]
Finally, in your report, multiply money base values by the value of the Exchange Rate parameter. This will adjust the value of money fields, like Opportunity Estimated Revenue to the user’s currency, based on the current exchange rate. Be sure you use the base value so the adjusted numbers are consistent.