1218813 - Subreport out of alignment when report is exported to MS Excel Data-Only from Crystal Reports

SAP Knowledge Base Article - Public

1218813 - Subreport out of alignment when report is exported to MS Excel Data-Only from Crystal Reports

Symptom

  • Subreport appears in a different rows.
  • A subreport placed on a main report to align with other fields. However, when the report is exported to Microsoft Excel (Data only) format from Crystal Reports, the subreport appears on its own line and is out of alignment. Why does this behavior occur, and how do you work around it?

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 subreport to the report, and insert it in one column of the report.
  3. Export the report to MS Excel Data-Only format.
         
    When opening the exported report in MS Excel, notice the subreport displays the information on a different row, instead of displaying on the column in which it was added.

Cause

  • By design, when exporting to MS Excel Data-Only format, a subreport will always export in a different row.

Resolution

  • To export the subreport as it looks like in Crystal Reports, you can perform one of the following:
    • Export the report to MS Excel format; or
    • Modify the report and use shared variables to display the data on the main report.
         
  • Export the report to MS Excel format:
    • Use the WYSIWYG Microsoft Excel 97-2000 format.  For optimal results, ensure that the design of the report resembles a Microsoft Excel spreadsheet as much as possible before the export is performed.
          
  • Modify the report and use shared variables to display the data on the main report.
    • If you use the Data Only method, you can bring the fields from the main report into the subreport as shared variables.  Any field brought into the subreport in this fashion will now appear on the same line as the rest of the subreport data.  Note that the subreport will still require its own line with any objects not brought into it.  Below are suggestions for working with the Microsoft Excel (Data only) format:
      1. Open the report in Crystal Reports and click EditSubreport Links
         
      2. Select the fields that you want to display on the same line as the subreport from the Available Fields section, and add them to the Field(s) to link to section.
         
      3. For all the fields you select, make sure the option for select data in subreport based on field is clear. 
         
      4. Click OK.
         
      5. In the main report, delete the fields that you just selected in step 2. 
         
      6. Expand the subreport as necessary to fill in this extra space.
         
      7. Open the subreport. 
         
      8. All of the fields you added in step 2 will now appear under Parameter Fields. Select these and place them in the report.
         
      9. Run the report.  The report should now look the same.  However, when exported to MS Excel Data-Only format, the fields will all appear on one line.

Keywords

EXPORTING SUB REPORT MS XLS FORMATTING Crystal Reports Exporting Microsoft Excel , c2017620 , 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