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
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.
This does look like a workable workaround. If data is large then a macro will be more useful.
ReplyDeleteThanks for the tip.
Regrads
Ronald
I can't believe this is still an issue in 2013! They should have fixed it by now, but thank you for the work around.
ReplyDeleteWe're also having a problem of Date fields exporting as Data&Time with a letter T in the middle and "+01:00" at the end, making it completely unworkable in excel at the moment. I don't want to have to double up every field with a formula for exports, but I guess I might have to.
When you add the column of the value in the report, select the check box: "Include Unformatted value column". Once you run the report, you are going to get two columns, the one with the label unformatted value let you do arithmetic calculations as currency, whole number or decimal field.
ReplyDeleteWe have the same issue here. Does anyone know when and if Microsoft will address this issue in CRM 2011 and CRM 2013? Thanks!
ReplyDeleteThe workaround works, but it is time consuming for the users.