CRM-SDK FetchXML- Currency behavior while retrieve group by queries

Hi all I have faced an issue while getting aggregate sum of a currency field from Microsoft dynamic CRM. The Currency values saved Microsoft dynamic CRM is in US Dollar . While I am getting from Microsoft dynamic CRM using 'QueryExpression' , getting correct values as like it saved in dynamic CRM , after a while I had to get all the data in a group by format. I had to group the currencies based on the month in a year.

     For this requirement I was not able to use the 'QueryExpression'. I have done some research and finds out that the 'fetchxml' Is the best method for scenarios like this , so I have decided to use the 'fetchxml' aggregate queries. 

       It is nothing but a method of querying data from dynamic CRM in our desired format .While we are using 'fetchxml' method , we could understand that which gives us good flexibility over the CRM data manipulation. We could get the data in our desired format. I have used fetchxml for querying Data from Dynamic CRM in a group by format.

You can find out how we could write the 'fetchxml' for grouping purposes in my previous post http://grandhah.blogspot.in/2017/06/crm-sdk-aggregate-fetch-xml.html .

  

             In case of getting currency data from dynamic CRM , I have faced, the currency data fetched using 'QueryExpression' is different from currency data which is fetched using fetchxml. Then I understood that the fetchxml aggregate returned currency data in base currency (currency which is set as base currency for the organization in dynamic CRM . We can finds out the base currency in dynamic CRM on Settings-> Business management->Currencies. In that usually , base currency exchanges rate is set as '1.0') .

   After this I have decided to multiply exchange rate with the fetchxml currency field result. For that, we need to get the exchange rate when the data is saved in Microsoft dynamic CRM (Exchange rate always changes) . I have added exchange rate also in the result for group the currency Sum based exchange rate in addition to other required grouping . By multiplying the exchange rate with the result I was able to produce sum as like in 'QueryExpression' result. Please check the fetchxml I have used .

PS : If you are not getting the currency sum in base currency , you can also use [currencyFiledName]_base . Which will give you currency in a common format (base currency format). 

<fetch distinct='false' mapping='logical' aggregate='true'>

<entity name='saleshistory'>

<attribute name='salesamount' alias='SaleAmount' aggregate='sum' />

<attribute name='exchangerate' alias='exchangerate' groupby='true' />

<filter type='and'>

<condition attribute='po_fiscaldate' operator='in-fiscal-year' value='2017' />

<condition attribute='po_accounttosaleshistoryid' operator='eq' value='[accoutid]' />

</filter>

</entity>

</fetch>