How to Convert Currency Field Value as per logged in user’s Currency setting in SSRS

Introduction

In this blog, we have explained how to convert currency field value as per the CRM logged-in user currency setting. Thus, it would be helpful for user to get the converted amount in their selected default currency.

For example: User ‘A’ entered the amount as USD 10 and User ‘B’ have default currency as ‘INR’. In this blog, we have shown how to convert amount USD 10 into selected default currency of user ‘B’ i.e. INR 665.495

We achieved this by using SSRS, so the user can export the report and check the converted amount in their selected default currency.

FYI, you can achieve this in Script/Plugin/Workflow, by implementing the formula given below:

((Amount from record / Exchange Rate (Currency selected on record)) * Exchange Rate (logged in User Default currency))

Example: ((USD 10/1.00)* 66.5495000000)) = INR 665.495

Here, in the above example

1. USD 10 is the amount that needs to convert in logged in user currency.
2. 00 is the exchange rate of the currency (The currency selected on opportunity record).
3. 015 is the Exchange rate of the currency which is selected as the default currency of the user.

Note: – The currency is converted based on the exchange rate available in CRM.

Steps to achieve this in SSRS:

Step 1

First, create Data source for report and provide connection details as shown in the below screenshot. Here, we designed report in Dynamics 365 environment with fetch query:

Step 2

Now in second step create dataset to get ‘Exchange Rate’ of Currency which is selected as default currency. Here, we have created dataset named as ‘User’ and used the below fetch query to retrieve details:

Fetch used in User dataset:

<fetch version=”1.0″ output-format=”xml-platform” mapping=”logical” distinct=”false” >
<entity name=”usersettings” >

<attribute name=”currencysymbol” />

<attribute name=”transactioncurrencyid” />

<filter type=”and” >

<condition attribute=”systemuserid” operator=”eq-userid” />

</filter>

<link-entity name=”transactioncurrency” from=”transactioncurrencyid” to=”transactioncurrencyid” visible=”false” link-type=”outer” alias=”Currency” >

<attribute name=”exchangerate” />

<attribute name=”currencysymbol” />

</link-entity>

</entity>

</fetch>

Step 3

Also, create a second dataset to get opportunity record details and exchange rate of currency selected on record. Here, we are showing opportunity details so we have created a new dataset as named ‘Opportunity’:

Fetch used in Opportunity dataset:

<fetch version=”1.0″ output-format=”xml-platform” mapping=”logical” distinct=”false”>

<entity name=”opportunity”>

<attribute name=”estimatedvalue” />

<attribute name=”opportunityid” />

<order attribute=”name” descending=”false” />

<link-entity name=”transactioncurrency” from=”transactioncurrencyid” to=”transactioncurrencyid” visible=”false” link-type=”outer” alias=”Currency”>

<attribute name=”exchangerate” />

<attribute name=”currencysymbol” />

</link-entity>

</entity>

</fetch>

Step 4

In the next step, enter the below expression in textbox property of table. Please refer below screenshot and expression.

=First(Fields!Currency_currencysymbol.Value, “User”)+ str((Fields!estimatedvalueValue.Value/Fields!Currency_exchangerateValue.Value)*First(Fields!Currency_exchangerateValue.Value, “User”))

Step 5: Run the report.

Conclusion

In this way, using the above solution user can see the amount in the currency of their choice.