2653074 - Zero values displays as blank, when exporting a report to MS Excel from Crystal Reports

SAP Knowledge Base Article - Public

2653074 - Zero values displays as blank, when exporting a report to MS Excel from Crystal Reports

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

  1. In Crystal Reports, create a report off any data source.
  2. Add a couple of fields to the report, and at least one numeric field, that contains data with zero value. (0)
  3. Format the numeric field.
  4. Export the report to MS Excel format.
  5. 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: #;#
           
    Excel Export - Zero 01.png
          
        
    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.
         
    Excel Export - Zero 02.png
      

          

Resolution

  • To displays the value zero (0) when exporting a report to MS Excel, check the numeric field formatting option "Leading Zero"
        
    1. In Crystal Reports, open the report.
        
    2. Right click on the numeric field, and select "Format Field..."
         
    3. In the Format Editor window, under the tab "Number", click on the button: "Customize..."
       
       Excel Export - Zero 03.png 
          
         
    4. In the "Custom Style" window, under the tab "Number", check the option "Leading Zero"
        
       Excel Export - Zero 04.png
        
         
    5. Click "OK"
          
    6. 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 BusinessViews Manager , Problem

Product

Crystal Reports 2008 V1 ; SAP Crystal Reports 2011 ; SAP Crystal Reports 2013 ; SAP Crystal Reports 2016