SAP Knowledge Base Article - Public

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

Symptom

  • Exporting to multiple Excel worksheets.
  • How to export data to different tabs in MS Excel?
  • How to export the groups of a report to separate 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 the group displaying USA to one worksheet,  the group for France to another worksheet, etc... within the same Excel workbook.

Environment

  • SAP Crystal Reports 2013
  • SAP Crystal Reports 2016
  • SAP Crystal Reports 2020

Reproducing the Issue

  1. In Crystal Reports, create a report based of 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 export data for each group to separate worksheet when exporting a report to MS Excel format from Crystal Reports.
         
  • Note: When exporting to MS Excel format 97-2003, a new worksheet is created when the data exceeds the number of rows limit of MS Excel 2003, which is 65,536 rows.

Resolution

  • There is unfortunately not option to export groups to multiple Excel worksheets in Crystal Reports.
     
  • 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 based on a data source.    
        
    2. Add a group to the report.  For example: Group by Country. 
       
    3. Create a Parameter that will be used to only display the desired group.
       
    4. Create a Record Selection formula to filter the data for the field you grouped by. 
      Under the menu 'Report', select 'Selection Formulas - Group', and enter a formula like:
         
          {Customer.Country} = {?Country}
          
    5. Refresh the report, and when prompted by the parameter, enter one value for the group to display.
      For example: Enter "USA" for the parameter value. The report will show a list of records for the USA only.
         
    6. Export the report to Excel and save the exported file to disk. Name the file for the specific group value that are on the report. For example: USA.xls
         
    7. Refresh the report and enter a different value for the group.
      For example: Enter "France" for the parameter value. The report will show a list of records for France only.
       
    8. Export the report to Excel and save the exported file to disk. Name the file for the specific group value that are on the report. For example: 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, select 'Import External Data' > 'Import Data'. Import the first spreadsheet we exported. For example: USA.xls
       
    11. In the same Excel workbook, click 'Sheet 2' in the bottom-left of the workbook.
       
    12. On the 'Data' menu, select 'Import External Data' > 'Import Data'. Import the first spreadsheet we exported. For example: France.xls

The MS Excel workbook will now have the USA data from Crystal Reports 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 Business View Manager , Problem

Product

SAP Crystal Reports 2013 ; SAP Crystal Reports 2016 ; SAP Crystal Reports 2020