SAP Knowledge Base Article - Public

1972949 - Formatting for object that returns a NULL value display in MS Excel, when exporting a report to MS Excel Data-Only from Crystal Reports

Symptom

  • Excel spreadsheet displays objects that are not on the report.
  • When exporting a report to MS Excel Data-Only, and selecting the option to export object formatting, the objects formatting that returns a NULL value will be part of the output in the MS Excel spreadsheet, where in previous version of Crystal Reports, it was not.
      
            
  • Note: Images and data in this SAP Knowledge Base Article is from SAP internal systems, sample data, or demo systems. Any resemblance to real data is purely coincidental.

Environment

  • 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 that contains data with NULL values.
  2. Insert a database field that contains NULL data in the details section of the report.
  3. Format the database field background to a color. For example, set the background color to blue.
  4. Export the report to "Microsoft Excel (97-2003) Data-only" format, and check the exporting option: "Export object formatting"
      
    Excel_Export_01.png   
           

  5. When opening the exported report in MS Excel, notice the rows where the database field value is null, it exports the object formatting.
        
    In the example below based on Demo data from the Xtreme sample database, it displays the background color blue in the MS Excel spreasheet for objects that are NULL, but on the report it does not display anything when the database field value is NULL. The expectation is the exporting document should not export the formatting if the object value is NULL like in Crystal Reports designer.
     
    Excel_Export_02.png

Cause

  • The issue has been identified and logged under Problem Report ID ADAPT01711885 

Resolution

  • The issue is resolved in the following product updates, and higher:
       
    • Crystal Reports 2011:
      • Support Pack 09
           
    • Crystal Reports 2013:
      • Support Pack 03
            
                   
  • After applying the product update if necessary, then to change the behavior on how Crystal Reports export object formatting for NULL values to MS Excel Data-Only format, follow the steps below:
         
    WARNING: The following resolution involves editing the registry. Using the Registry Editor incorrectly can cause serious problems. Use the Registry Editor at your own risk. Refer to the SAP Knowledge Base Article 1323322 for more information.
      
    1. Apply the product updates to Crystal Reports.
        
    2. Open the Microsoft Registry Editor, by selecting in MS Windows, "Run", under the menu "Start", and type: "Regedit"
         
    3. In the MS Windows Registry Editor, navigate to the following path:
         
      • For 32bit version of MS Windows:
        • HKEY_LOCAL_MACHINE\SOFTWARE\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\Export\Excel
             
      • For 64bit version of MS Windows:
        • HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\Export\Excel
                 
      • Note: If the keys do not exist, create the appropriate keys.
          
    4. Add the registry key: KeepObjectFormattingWhenNullValue
         
    5. Set the value to: 0
        
      The valid values for KeepObjectFormattingWhenNullValue, are:

         0 : To not export objects formatting for NULL values to MS Excel, like it used to be in older version of Crystal Reports.
         
         1 : To export the object formatting for NULL values to MS Excel Data-Only format.

Keywords

CR, XL, NULL, Empty value , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Bug Filed

Product

SAP Crystal Reports 2011 ; SAP Crystal Reports 2013