The numeric values are shown properly when a report is generated from a 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.
The code which is generated by the CRM Online Organization is not recognized by Excel.
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 which is exported, here is the method to get the .RDL file of the report.
1. Select the report and click edit.
2. On the new dialog box click on "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", click "Cancel", and the file will Open.
3. Review the columns on the Excel file which has "#value!", and compare it with the .RDL file opened.
4. In SQL Server Report Builder there are 2 columns describing one field. The expression for each column will need to be changed by following steps a through e below:
a. 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)))
b. From this identify the field "Fields!revenueValue.Value"
c. Clear the expression, and in the same window as "category" select "Field(DSMain)"
d. Select the expression "revenuevalue" which 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".
e. Select the field "Revenue Value" in the column name of your report.
5. For each column perform repeat steps a to e.
6. Once all the field expressions are modified, save the .RDL file.
7. In the CRM Online organization, select the report, and click on 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.
Note: The steps above will need to be followed for each report that encounters this issue.
Option 2: Modify the .RDL file using BIDS.