locked
Field errors when exporting reports to Excel RRS feed

  • Question

  • In CRM Online, I have created reports using the report wizard with columns containing data from a currency field.
    When running the report and exporting to Excel, the text '#VALUE!' is displayed in the column rather than the value. The actual CRM report shows the data correctly.

    To reproduce the issue: -
    1. Use a CRM 2015 Online org containing opportunity data, or has the CRM sample data loaded
    2. Create a new report using the report wizard
    3. For primary record type, select Opportunities
    4. Add the columns Opportunity and Est. Revenue
    5. Run the new report and note that the values in the Est. Revenue column are correct
    6. Export the report to Excel
    7. Open the report and note that the values in the Est. Revenue column have been replaced with #VALUE!

    The problem seems to be that the formula in the RDL file is comparing the currency value to a date (I've highlighted the text which shouldn't be there): -

    =IIf(IsNothing(Fields!estimatedvalue.Value), Nothing, IIf(("CDbl"="CDate" and CDate(Fields!estimatedvalueValue.Value) < CDate("1/1/1900")),Cstr(Fields!estimatedvalueValue.Value),CDbl(Fields!estimatedvalueValue.Value)))

    Correcting the formula as below and uploading the RDL makes the export to Excel work correctly, but the report is then not editable in the CRM report wizard.

    =IIf(IsNothing(Fields!estimatedvalue.Value), Nothing, CDbl(Fields!estimatedvalue.Value))

    Can anyone else reproduce this and confirm? We're not really getting anywhere with Microsoft Support.



    Neil - My CRM Blog




    • Edited by Neil McD Monday, July 27, 2015 3:23 PM
    Thursday, July 23, 2015 9:58 PM

Answers

  • Neil,

    I am seeing the same thing. I have not gone into the code yet but assume I will see what you are seeing.

    Thanks

    Jay

    • Marked as answer by Neil McD Monday, July 27, 2015 3:27 PM
    Monday, July 27, 2015 1:02 PM

All replies

  • Neil,

    I am seeing the same thing. I have not gone into the code yet but assume I will see what you are seeing.

    Thanks

    Jay

    • Marked as answer by Neil McD Monday, July 27, 2015 3:27 PM
    Monday, July 27, 2015 1:02 PM
  • Thanks for trying it Jay.

    It appears to be a bug, so I've logged it on Microsoft Connect in addition to support. Hopefully they'll fix it soon.

    Neil.


    Neil - My CRM Blog

    Monday, July 27, 2015 3:27 PM
  • Hi Neil

    Did you raise it with Microsoft ? I am getting the same issue after my upgrade.

    All other formats display correctly, it is just Excel that seems to have the fields display "#VALUE!"

    Thanks

    Dennis

    Wednesday, August 5, 2015 1:13 PM
  • Hi Dennis,

    Yes, it has been raised on Connect as a bug and we have an open support ticket.

    Support is proving to be a fairly painful experience with them telling us it's working as intended, referring us to the Excel team, and suggesting we export to Word and then copy/paste into Excel. It also happens with the CSV exports which can be opened without Excel, so it's not their problem.

    It's quite frustrating as the error is clearly the incorrect formula in the RDL file as we've pointed out to them.

    I've fixed our affected reports manually by correcting the formulas using Report Builder 3 for now.

    If you still have access to a 2013 system, I would imagine that downloading the working RDL (edit the report, Actions\Download Report) and uploading it to 2015 as a new report via Report Wizard\Existing file will work also.

    Neil.

     


    Neil - My CRM Blog

    Wednesday, August 5, 2015 3:05 PM
  • Need a Solution on this asap, do you know if CRM support is trying to solve it?

    BRs

    Tuesday, December 1, 2015 6:55 PM
  • I try your workaround and it fix it

    Thanks in advance

    Tuesday, December 1, 2015 7:55 PM