Symptom
- Zero not exported.
- Value zero (0), not displayed in MS Excel.
- In Crystal Reports, when exporting to MS Excel a report containing summaries equal to zero, the summary values shows as blank in MS Excel.
Environment
- SAP Crystal Reports 2008
- SAP Crystal Reports 2011
- SAP Crystal Reports 2013
- SAP Crystal Reports 2016
Reproducing the Issue
- In Crystal Reports, create a report off any data source.
- Add a couple of fields to the report, and at least one numeric field, that contains data with zero value. (0)
- Format the numeric field.
- Export the report to MS Excel format.
- When opening the report in MS Excel, notice the zeros are not displayed. It is blank, where you can zeros in Crystal Reports.
Cause
- The reason the zero values does not displayed in MS Excel, it's because the numeric field was formatted to not display decimals, and leading zero.
- In more details: Crystal Reports exports all the values, but in MS Excel, the cell format to not show leading zero, and no decimals is: #;#
Therefore, if the value is zero, MS Excel will not show the value, and display a blank.
If you select the cell in MS Excel, you will see the value zero (0) will appear.
Resolution
- To displays the value zero (0) when exporting a report to MS Excel, check the numeric field formatting option "Leading Zero"
- In Crystal Reports, open the report.
- Right click on the numeric field, and select "Format Field..."
- In the Format Editor window, under the tab "Number", click on the button: "Customize..."
- In the "Custom Style" window, under the tab "Number", check the option "Leading Zero"
- Click "OK"
- Back to the "Format Editor", click "OK"
Now, when exporting the report to MS Excel (97-2003) format, the cell formatting in MS Excel will be 0;0
which will show the zero values.
Keywords
CR, xls, xport , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem
Product
Crystal Reports 2008 V1 ; SAP Crystal Reports 2011 ; SAP Crystal Reports 2013 ; SAP Crystal Reports 2016