Saturday 1 December 2012

Dynamics CRM 2011 - Export Report data to Excel - Data type issues

One of my customers has developed a custom report via report wizard in Dynamics CRM 2011 online. She had exported the report data to excel. Surprisingly, the currency data was exported in text format and hence she was unable to perform arithmetic calculation on that. She tried to convert them to numbers in excel but no success. So, she knocked on my door and explained me the problem.

After doing some research I found that this is a defect in Dynamics CRM 2011. You can refer the same on Connect site with help of below URL

https://connect.microsoft.com/dynamicssuggestions/feedback/details/661931/export-report-to-excel-data-type-issue

Workaround

Follow the below steps to convert the text data to numbers

1. Insert a new column beside the currency data column

2. Apply below formula in newly created column besides every data row

=VALUE(RIGHT(G2,LEN(G2)-2))

Here in above example I have used cell no as G2. You need to use your data column cell positions.

Reference

http://social.microsoft.com/Forums/is/crm/thread/43d38c71-722a-4a65-8e18-340a01c0d31b

I hope this will be helpful.