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:


Currency Fields in FetchXML Reports

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.

Currency Fields in FetchXML Reports

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.

Currency Fields in FetchXML Reports








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.

Currency Fields in FetchXML Reports

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.

If you are still having questions please reach out and talk to one of the Microsoft Dynamics CRM Experts at PowerObjects – your #1 Microsoft Dynamics CRM partner in the world.

Happy CRM’ing!

Avatar for Joe D365

Joe D365

Joe D365 is a Microsoft Dynamics 365 superhero who runs on pure Dynamics adrenaline. As the face of PowerObjects, Joe D365’s mission is to reveal innovative ways to use Dynamics 365 and bring the application to more businesses and organizations around the world.