There is a recent issue we ran into while dealing with currency fields in reports with FetchXML queries. The issue is that when a money field is added to a report with FetchXML query, excel formulas does not seem to be working properly on these fields with a currency symbol attached to them. Whenever a currency field is listed in a FetchXML query, it pulls back 2 columns. One with a currency symbol attached to it in the data with the original column name and the other with just the decimal value of the currency field with a “Value” postfix attached to the column name as shown in the screenshot below:
As you can see, there are 2 columns returned from the query with one money field being it – “estimatedvalue” is the original column name used and that is the column returning the value with the currency symbol in it and the other “estimatedvalueValue” without the currency symbol and a ‘Value’ postfix in the column name. When using this information in the report if you use the field with the currency symbol in it (or the field without the “Value” postfix in the column name) and export the report into excel – there are issues with using formulas for calculations against this column.
Excel does not recognize this column as a numeric field and no matter how it is formatted. It can be seen in the screenshot below from the exported report after applying some calculation formulas.
You should be using the second field without the currency ($) fields (the one with the “Value” prefix in it). This is the field with just the decimal value of the money field from database.
The reason that the column with the currency symbol is not working is probably because it has the currency localization info attached to it making it a special column making the formulas and calculations void on these types of columns. So, this is important to note to show the correct field in the report as some of our clients use a lot calculations after exporting the data from a report into excel. Hope this helps someone out there and saves some time in debugging this issue.
Latest posts by JoeCRM (see all)
- How to Assign a Territory to a Lead in Dynamics CRM - May 17, 2013
- Out of the Box Report: Dynamics CRM User Summary - May 16, 2013
- Dynamics CRM / XRM Integration with GIS and PowerMap - May 15, 2013