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).
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>
No comments:
Post a Comment