{"id":20270,"date":"2019-08-16T11:36:52","date_gmt":"2019-08-16T11:36:52","guid":{"rendered":"https:\/\/www.inogic.com\/blog\/?p=20270"},"modified":"2019-08-16T11:36:52","modified_gmt":"2019-08-16T11:36:52","slug":"convert-currency-field-value-as-per-logged-in-users-currency-setting-in-ssrs","status":"publish","type":"post","link":"https:\/\/www.inogic.com\/blog\/2019\/08\/convert-currency-field-value-as-per-logged-in-users-currency-setting-in-ssrs\/","title":{"rendered":"How to Convert Currency Field Value as per logged in user\u2019s Currency setting in SSRS"},"content":{"rendered":"

Introduction<\/strong><\/h2>\n

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.<\/p>\n

For example<\/strong>: User \u2018A\u2019<\/strong> entered the amount as USD 10 and User \u2018B\u2019<\/strong> have default currency as \u2018INR\u2019<\/strong>. In this blog, we have shown how to convert amount USD 10 into selected default currency of user \u2018B\u2019<\/strong> i.e. INR 665.495<\/p>\n

\"SSRS\"<\/p>\n

We achieved this by using SSRS, so the user can export the report and check the converted amount in their selected default currency.<\/p>\n

FYI, you can achieve this in Script\/Plugin\/Workflow, by implementing the formula given below:<\/p>\n

((Amount from record \/ Exchange Rate (Currency selected on record)) * Exchange Rate (logged in User Default currency))<\/em><\/strong><\/p>\n

Example: ((USD 10\/1.00)* 66.5495000000)) = INR 665.495<\/p>\n

Here, in the above example<\/p>\n

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

    Note: – The currency is converted based on the exchange rate available in CRM.<\/p><\/blockquote>\n

    Steps to achieve this in SSRS<\/strong>:<\/p>\n

    Step 1<\/strong><\/p>\n

    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:<\/p>\n

    \"SSRS\"<\/p>\n

    Step 2<\/strong><\/p>\n

    Now in second step create dataset to get \u2018Exchange Rate\u2019<\/strong> of Currency which is selected as default currency. Here, we have created dataset named as \u2018User<\/strong>\u2019 and used the below fetch query to retrieve details:<\/p>\n

    Fetch used in User dataset:<\/p>\n

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

    <attribute name=”currencysymbol” \/><\/p>\n

    <attribute name=”transactioncurrencyid” \/><\/p>\n

    <filter type=”and” ><\/p>\n

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

    <\/filter><\/p>\n

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

    <attribute name=”exchangerate” \/><\/p>\n

    <attribute name=”currencysymbol” \/><\/p>\n

    <\/link-entity><\/p>\n

    <\/entity><\/p>\n

    <\/fetch><\/p>\n

    Step 3<\/strong><\/p>\n

    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 \u2018Opportunity\u2019:<\/p>\n

    Fetch used in Opportunity dataset:<\/p>\n

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

    <entity name=”opportunity”><\/p>\n

    <attribute name=”estimatedvalue” \/><\/p>\n

    <attribute name=”opportunityid” \/><\/p>\n

    <order attribute=”name” descending=”false” \/><\/p>\n

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

    <attribute name=”exchangerate” \/><\/p>\n

    <attribute name=”currencysymbol” \/><\/p>\n

    <\/link-entity><\/p>\n

    <\/entity><\/p>\n

    <\/fetch><\/p>\n

    Step 4<\/strong><\/p>\n

    In the next step, enter the below expression in textbox property of table. Please refer below screenshot and expression.<\/p>\n

    \"SSRS\"<\/p>\n

    =First(Fields!Currency_currencysymbol.Value, “User”)+ str((Fields!estimatedvalueValue.Value\/Fields!Currency_exchangerateValue.Value)*First(Fields!Currency_exchangerateValue.Value, “User”))<\/p>\n

    Step 5: <\/strong>Run the report.<\/p>\n

    \"SSRS\"<\/p>\n

    Conclusion<\/strong><\/h2>\n

    In this way, using the above solution user can see the amount in the currency of their choice.<\/p>\n

    \"click2clone\"<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

    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 \u2018A\u2019 entered the amount as USD 10 and User \u2018B\u2019 have default currency as\u2026 Read More »<\/a><\/span><\/p>\n","protected":false},"author":13,"featured_media":20280,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[16,45,58],"tags":[1924,1923],"class_list":["post-20270","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-dynamics-365","category-power-bi","category-ssrs-reports","tag-convert-currency-field","tag-ssrs"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/posts\/20270","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/users\/13"}],"replies":[{"embeddable":true,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/comments?post=20270"}],"version-history":[{"count":0,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/posts\/20270\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/media\/20280"}],"wp:attachment":[{"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/media?parent=20270"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/categories?post=20270"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/tags?post=20270"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}