Numeric values from CRM reports are replaced by "#value!" in the exported Excel file

This article provides resolutions to solve the issue that numeric values from Microsoft Dynamics CRM reports are replaced by #value! in the exported Excel file.

Applies to:   Microsoft Dynamics CRM
Original KB number:   3087887

Symptoms

The numeric values are shown properly when a report is generated from a Microsoft Dynamics CRM Online organization, but when the report is exported to Excel in CSV format the numeric values are replaced by #value! in the exported file.

Cause

The code that is generated by the Microsoft Dynamics CRM Online Organization is not recognized by Excel.

Resolution

There are two ways to fix this issue.

Option 1 - Modify the .RDL file by SQL Server Report Builder tool

To fix this issue, we need a .RDL file of the report that is exported, here's the method to get the .RDL file of the report.

1. Select the report and select edit.
2. On the new dialog box, select actions, and in the drop-down select download report.
3. Save the .RDL file.

Now we need to modify the .RDL file for this we need the SQL Server Report Builder.

1. Open the .RDL file in SQL Server Report Builder.

2. When the dialog box prompts Connect to Online SQL Server, select Cancel, and the file will open.

3. Review the columns on the Excel file that has #value!, and compare it with the .RDL file opened.

4. In SQL Server Report Builder, there are two columns describing one field. The expression for each column will need to be changed by following steps a through e below:

   1. Right-click on the "<expr>" select expression in the drop-down, now new window appears which has expressions for that column:

      =IIf(IsNothing(Fields!revenue.Value), Nothing, IIf(("CDbl"="CDate" and CDate(Fields!revenueValue.Value) < CDate("1/1/1900")),Csr(Fields!revenueValue.Value),CDbl(Fields!revenueValue.Value)))

   2. From this identify the field Fields!revenueValue.Value.

   3. Clear the expression, and in the same window as category select Field(DSMain).

   4. Select the expression that we need from the values.

      Note

      Here there are two values for each name of a field, and we need to select the field which has value in its name.

      In this example, the two fields are revenue, and revenue value.

   5. Select the field Revenue Value in the column name of your report.

5. For each column, repeat steps a to e.

6. Once all the field expressions are modified, save the .RDL file.

7. In the Microsoft Dynamics CRM Online organization, select the report, and select Edit.

8. Select the Report type drop-down, select existing file, and then select Save and Close.

9. Run the report, and export the report to Excel.

10. The numeric value will now be seen. If you see the value ####, increase the width of the column in Excel.

Option 2 - Modify the .RDL file using BIDS