locked
CRM 2011 - Export Report to Excel - Data Type Issue RRS feed

  • Question

  • Hi,

    I have noticed something odd with CRM 2011. When I export a report to excel all of the cell's come in as "General", even date's, numbers, and currency. This means that I cannot perform calculations on them without converting them first. This was not an issue in CRM 4.0. I know that the data types changed in CRM 2011 to .NET data types, but I don't see why the reports are acting different.

    Use-case:

    Create a new report for Opportunities using the wizard. For the columns, use Topic and Est. Revenue.

    Run the Report, and export to Excel.

    Open the saved report, and all of the cell's are formatted as 'General'. If you try to add any of the Est. Revenue column, the result will be #Value!.

     

    I know you can get around this by manually changing the cell type, but I am looking to have it in the correct format once it is exported. Is this an open issue, or does something need to be specified in the report itself?

     

    Thanks,

    Rob

    Monday, April 11, 2011 6:40 PM

Answers

All replies

  • Check the Connect site and see if this is entered as a suggestion.  If so, please vote for it.  If not please create a suggestion and I'll vote for it.  You can have others vote for it as well so it will gain more attention.


    Regards, Donna

    Wednesday, April 13, 2011 8:16 PM
  • voted, thank you!

    Regards, Donna

    Friday, April 15, 2011 3:19 PM
  • No problem, Thank you also!
    Friday, April 15, 2011 5:20 PM
  • The link does not work anymore. We have the same issue. English DB, English Server, English CRM. But when we export reprots as EXcel then the numbers and date are just text.
    Tuesday, August 2, 2011 3:05 PM
  • The link works for me and has 8 votes https://connect.microsoft.com/dynamicssuggestions/feedback/details/661931/export-report-to-excel-data-type-issue

    One possible workaround is to use the Data feature called "Text to Column" and convert the date column from text back to date.  You should be able to highlight the column that you want to convert to numerical and simply select Number to convert a text column to a number.


    Regards, Donna

    • Proposed as answer by Donna EdwardsMVP Tuesday, August 2, 2011 3:19 PM
    • Marked as answer by rlanz Wednesday, August 3, 2011 1:34 PM
    Tuesday, August 2, 2011 3:19 PM
  • Page Not Found

    The content that you requested cannot be found or you do not have permission to view it.


    We tried to change the format in Excle for data and currency. For currency we had to remove $ and ,. then it worked. Will it be fixed with Rollup 3? the changes in rollup 3 are not 100% clear.
    • Edited by Markus Lacher Tuesday, August 2, 2011 4:45 PM forgot some text
    Tuesday, August 2, 2011 4:43 PM
  • I don't believe there is a fix in Rollup 3 but you can check the list.

    If the Page Not Found is referring to the Connect link, maybe you could go to the Connect site and search on the following title:

    Export Report to Excel - Data Type Issue   It was originally posted by rlanz so if you can't find it by Title maybe you can search on users or something.

    It would be great if someone could select the responses that answered the original question and mark this post as Answered.  Thank you!


    Regards, Donna

    Tuesday, August 2, 2011 4:49 PM
  • Thanks Donna, still no luck evne searching for the topic will not show up for me on connect. Maybe my account has not enough access. I will more look around. We installed rollup 3 on our DEV environment and we will test. However we need to fix our eports, they dont work anymore due the rollup.

     

    Thanks again Markus

    Tuesday, August 2, 2011 5:13 PM
  • Link still works for me.
    Wednesday, August 3, 2011 1:35 PM
  • wow got this same issue.. cant seem to find a workaround though..  - removing the £ didnt work as it did for another user...

    with the microsoft connect thing, it seems like you have to:

    • log into connect
    • search for the Microsoft Dynamics CRM group
    • Join the microsoft dynamics CRM group
    • then go back to this forum and hit the link, and then the link will work...
    Monday, September 12, 2011 4:25 PM
  • I couldn't access the link even doing what junglism told to do.

    Still can use sums or autosums or any math operation on numbers.

    Only thing is to retyoe myself, but it's a painful process. Any help would be appreciated. Thanks in advance.

    Tuesday, November 22, 2011 5:12 PM
  • Did you try the the Data feature called "Text to Column" in Excel.  If you use that you should be able to convert the field value from whatever it is to a number field.

    Regards, Donna

    Tuesday, November 22, 2011 5:47 PM
  • We are attempting to evaluate data (Est. Revenue) and we have tried all of the following to sum the data in Excel without success:

    -Text to Column

    -Number formatting

    -Paste special, Multiply

    -Removing $,

    We have also tried to reference the previously posted link - but also without success. 

    Any other suggestions?

    Friday, December 2, 2011 7:27 PM
  • -Remove $

    -Text to Column

    -General formatting

    -Highlight the column and change it to number after it is converted to general


    Regards, Donna

    Friday, December 2, 2011 8:38 PM
  • We are having this exact same issue with CSV and XLS exports.

    It turns out there is a hidden character that is only visible in a binary or a better text editor. In our case we are using notepad ++ as the character was not visible in regular notepad. Basically we copy the entire column that contains the monetary values into notepad ++ and do a find and replace on $? without providing a replacement character. We believe this extra character is being inserted due to crm’s multi- currency functionality and some of the currency characters might break the XML encoding on the export. This is only a work around and is quite a pain if you export a lot of reports. If anyone has a solution to fix this at the reporting level we would be very grateful.

     We have tried the text to columns in excel on the $ and in our case excel still does not treat the column as a number due to the hidden ? character.

    • Edited by Robert Leiker Thursday, February 2, 2012 2:16 PM weird formatting
    Thursday, February 2, 2012 2:10 PM
  • I'm with Robert Leiker. This appears to be a bug and is particuarly galling since there is no apparent workaround.

    Edit: I am on CRM 2011 on-prem with R3 and still experiencing this.

    There was another blog I found with a workaround that is UBE (ugly but effective). Using the following function:

    =RIGHT(L10,LEN(L10)-2)

    this strips out the first two characters of the number (turning "$?5000" into "5000) which makes it Excel-friendly.

    For noncurrency values the text-to-columns works.

    Wednesday, February 8, 2012 7:32 PM
  • I know this has already been answered, but I wrote a macro that will go through and fix some issues with the date and currency types. It basically will remove some of the special character nonsense that is around some of these fields. It will also find the date fields and set them. You can see the general format of how to iterate over each selected field. I hope this helps.  I really hope Microsoft fixes this issue.  We save it to the personal excel book so that it is available every time we open any excel file.

    Sub CrmConvertFields()
    '
    ' CrmConvertFields Macro
    '
        Dim txt As String
        Dim num As Double
        For Each Cell In Selection.Cells
            
            txt = Cell.Value
            txt = Replace(txt, "$", "")
            txt = Replace(txt, ",", "")
            If Len(txt) > 0 Then
                
                Dim txtNum As String
                txtNum = Right(txt, Len(txt) - 1)
                If (IsNumeric(txtNum)) Then
                    Cell.Value = Val(txtNum)
                End If
                
                If (IsDate(txt)) Then
                    Cell.Value = DateValue(txt)
                End If
                
            End If
            
        Next
    '
    End Sub

     
    Monday, March 26, 2012 7:41 PM
  • Hi Donna,

    The conversion from General to Number or currency doesn't seem to work as discussed in this thread.  Further, the Text to Columns failed to work either.  This problem occurs when exporting to CSV, Excel, Word, XML and even when copying and pasting from the Report Viewer window.  Unfortunately we don't have the luxury someone else has had with creating a custom view and exporting this way.  

    The problem is, our Gross Margin reports are taking an extra 4 hours of labour per week and product upgrades are supposed to reduce support costs and improve productivity, not the other way around.

    I'm surprised this has not been actioned in a roll-up update yet.

    Jason.


    Consultant | Nerd | Visionary. http://www.ethertech.com.au/ | http://www.deeperstates.com.au

    Sunday, April 8, 2012 8:51 AM
  • Hi,

    I came across the same issue when making a set of reports for a client. I got it working in the end by "telling" Excel that non-text columns were of a specific numeric format, e.g. integer, currency, decimal. I used the following techniques:

    1) Add calculated fields to your SSRS data set for your numeric fields and use CInt or CDec to convert the base data into a number. Use the calculated fields in your reports.

    2) Use the Language and Format properties in the report definition to force the format for your language. For example:

      • Using Language = en-GB and Format = C2 I was able to get my currency fields to be corretly exported and displayed in Excel.
      • For percentages I used Format = P1

    Cheers,
    John

    Saturday, October 13, 2012 8:24 AM
  • Hi All,

    Does anyone know if MS have sorted this issue out yet in an update rollup or if there is a simpler work-around? The text to columns didn't work, the connect link doesn't work and because the exports tend to be from advanced finds & views calculated fields cannot be added. Please help....  

    Wednesday, November 14, 2012 11:26 AM
  • Two years on and this has not been addressed?
    Monday, February 25, 2013 2:45 PM
  • Two years on and this has not been addressed?

    For anyone still working with this:

    Solution in SSRS:

    For Dates:

    Use date conversion Expression on date column:

    =CDATE(Fields!<yourdatecolumn>.Value) 

    Format Property = d


    For Currency Values:

    Strip off the left 2 characters that are causing the problem, and convert to Decimal.

    Expression:

    =CDEC(RIGHT(Fields!<yourrevenuecolumn>.Value, LEN(Fields!<yourrevenuecolumn>.Value)-2))

    Format Property of the column:  '$'#,0.00;('$'#,0.00)

    On Excel Export, Excel file will have Dates and Numbers for sorting, etc.

    Monday, June 24, 2013 2:53 PM
  • How would you adjust this macro to reflect if a number is negative to have it show as a negative number?
    • Proposed as answer by Spookspear Tuesday, January 21, 2014 10:17 AM
    • Unproposed as answer by Spookspear Tuesday, January 21, 2014 10:17 AM
    Wednesday, October 9, 2013 8:53 PM
  • As a quick fix do a find and replace (Control – H) and in “Find What” enter: $? – leave “Replace with” blank - this will strip off all the numerical formatting but better that than no summed results.

    Tuesday, January 21, 2014 10:21 AM
  • I bought ASAP Utilities, cheap, adds a tool bar to top of Excel, highlight text Select "numbers and Dates" select Option 3 "convert unrecognized text to numbers"  Done, works with $ also, does not woth othe currenct  GBP or Euros, need to find and replace on these to a blank.

    Wednesday, April 30, 2014 2:31 AM