SAP Knowledge Base Article - Public

2939999 - Summaries are not exported to Excel summary function, when exporting a report to MS Excel - Data Only, from Crystal Reports

Symptom

  • Total show as a value in excel.
  • Total not exported to MS Excel formula function.
  • Report with few values exports the summary to MS Excel as Excel function, like: SUM(A1;A4;A7,... )
  • When exporting a report to MS Excel - Data Only (.xls) with a larger data set, and selecting the option "Use worksheet functions for summaries", the summary shows the values, instead of the expected Excel function, like: SUM(A1;A4;A7,... )

Environment

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

Reproducing the Issue

  1. In Crystal Reports, create a report based on any data sources. 
  2. Insert a group on the report.
  3. Add in the Details section fields to summarize.
  4. Add Subtotals for the fields, for the Goup, as well as Grand Total for the report.
  5. Export the report to MS Excel - Data Only (xls), and select the option "Use worksheet functions for summaries"
  6. Notice the Grand Totals, does not use MS Excel formula function to summarize the values like: SUM(A1, A2,... )
    It only display the total, like: 12345

Cause

  • MS Excel format (.xls), have formula limitation of 30 arguments for functions, and when there is more than 30 values to summarize for the report, it export the summary as a value, instead of using a MS Excel formula function.
        
  • In brief: This occurs due to a MS Excel (.xls) functions arguments limit of 30.

Keywords

CR,xls , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem

Product

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