Looking for PowerObjects? Don’t worry, you’re in the right place! We’ve been part of HCL for several years, and we’ve now taken the final step in our acquisition journey: moving our website to the HCL domain. Nothing else is changing – we are still fanatically focused on Microsoft Business Applications!

PowerObjects Blog 

for Microsoft Business Applications

|

Microsoft Dynamics CRM Online – Currency Fields in FetchXML Reports

Post Author: Joe D365 |

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!

Joe CRM
By 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.

One comment on “Microsoft Dynamics CRM Online – Currency Fields in FetchXML Reports”

  1. Hi Joe ,
    I want to hide currency symbol displayed on the currency field, I want only the value to be displayed without prefix symbol. Can you please help me on this

PowerObjects Recommends