1216567 - How to export a report to multiple Excel worksheets in Crystal Reports?

SAP Knowledge Base Article - Public

1216567 - How to export a report to multiple Excel worksheets in Crystal Reports?

Symptom

  • Exporting to multiple MS Excel worksheets.
  • Is it possible to export separate groups within a report to separate Microsoft Excel worksheets, when exporting a report to MS Excel from Crystal Reports?
  • For example: If you have a report grouped by Country. and want to export USA group to one worksheet,  the group France to another worksheet, ... within the same Excel workbook.

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 group on the report.
  3. Export the report to MS Excel format.
     
    When opening the exported report in MS Excel, all the data is is one MS Excel worksheet.
    How to export each group to a separate MS Excel worsheet in the workbook?

Cause

  • There is no option to specify generating a separate worksheet for each group when exporting to MS Excel format from Crystal Reports.
       
  • But note that when exporting to MS Excel format, it generates a new worksheets when the data exceeds the number of rows limit of MS Excel 2003 and below of 65,536 rows.

Resolution

  • There is unfortunately not option to export groups to multiple Excel worksheets.
     
  • A suggestion will be to export each group separately, then import them into a single MS Excel workbook.
     
    Below is an example on how to use this suggestion:
       
    1. In Crystal Reports, create a report off any data source.    
       
    2. Add a group to the report.  For example group by Country. 
       
    3. Create a String Parameter, that will be used to only display the desired group.
       
    4. Create a Record Selection formula to narrow filter the data.On the 'Report' menu, click 'Selection Formulas' > 'Group'. Enter the following formula:
         
          {Customer.Country} = {?Country}
          
    5. Refresh the report and enter "USA" in the 'Enter Parameter Values' dialog box. The report will show customers for the USA group only.
       
    6. Export the report to Excel and save the exported file to disk. Name the file USA.xls.
       
    7. Refresh the report and enter "France" in the 'Enter Parameter Values' dialog box. The report will show customers for the France group only.
       
    8. In Crystal Reports, export the report to Excel and save the exported file to disk. Name the file France.xls.
       
    9. In Excel, create a new workbook and then click 'Sheet 1' in the bottom-left of the workbook.
       
    10. On the 'Data' menu, click 'Import External Data' > 'Import Data'. Import the file USA.xls.
       
    11. In the same Excel workbook, click 'Sheet 2' in the bottom-left of the workbook.
       
    12. On the 'Data' menu, click 'Import External Data' > 'Import Data'. Import the file France.xls.

The Excel workbook will now have the USA data from CR in one worksheet and the France data in another. You can repeat these steps with other groups and add more worksheets.

Keywords

EXCEL EXPORT WORKSHEET WORKBOOK MULTIPLE SPECIFY MS GROUPS GROUPING GROUP Crystal Reports for Visual Studio Export to Excel Multiple worksheets , c2014059 , 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