1198296 - Column misaligned when exporting a report to Excel from Crystal Reports

SAP Knowledge Base Article - Public

1198296 - Column misaligned when exporting a report to Excel from Crystal Reports

Symptom

  • Columns misaligned.
  • Data does not match the headers.
  • When exporting a report to MS Excel format, from Crystal Reports, the columns are misaligned, and there is blank columns.

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. Insert a couple of database fields in the Details section, and headers in the Page Header section.
  3. Export the report to MS Excel format.
  4. When opening the exported report in MS Excel, notice some of the column header does not match the data under.

Cause

  • Columns are misaligned because the way the report is designed.

  • Crystal Reports and MS Excel are two different format. In Crystal Reports you can insert fields and objects anywhere in a section, where in MS Excel, it is a column and row format. Due to the difference in format, objects will not align as expected if the report is not designed in a way that will ensure each objects are aligned together.

Resolution

  • To design a report that better export to MS Excel format, the suggestions are:
        
    • Ensure each header align with the database field for each column line up exactly on the left and that each of the objects, and ensure it is the same width. ( Use guidelines in the ruler to align text object and fields )
         
    • Ensure each database fields, text fields, in a row line up exactly.  ( Use guidelines in the ruler to align text object and fields )
        
    • Ensure  all the objects in a row have data in them.
         
      If any object is blank, the objects to the right of it will move to the left to fill up the column. Below are suggestions to avoid this:
        
      • Set the default values on numeric fields so that they always show up as zeros; or
      • Account for nulls in any formula used on the report to make sure they return either a zero or a space; or
      • Sometimes I’ve had to put a tilde (~) or other meaningless character in a text block that is the same size and location as the object that may be blank. Set the font to the same color as the background so that it doesn’t show up when viewing the report and suppress the text block when the object it’s replacing is not null.
           
    • If your column header is more than one row high, all of the column headers have to be the same height.
      Just adding carriage-returns, doesn’t solve the issue, there has to be at least a blank space on the line.
          
    • When exporting with formatting, the best way to help prevent merged cells is to do the following:
      • Use guidelines in the ruler at the top of the report to mark the left and right of the first column.
      • Using the guide, start the next column at the exact same spot where the previous column ended.
      • Place a guide to mark the right side of this new column.
      • Repeat until all columns are in place.
      • For rows, make sure that the objects are placed at the very top of the section –right click on an object; select “Size and Position”; set the Y coordinate to 0.0. Then align all of the other objects in the row with the top of the one just set.
      • Move the bottom edge of the section up to the bottom of the objects in the row.
          
          
  • For more information, I will suggest to read the following document:

    http://scn.sap.com/docs/DOC-39608

    Also, see the more detailed document on how to design a report that better export to MS Excel format, available at:
       
    http://scn.sap.com/docs/DOC-6687

Keywords

crystal report export into excel , 3385260 , 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