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
- In Crystal Reports, create a report based on any data sources.
- Insert a group on the report.
- Add in the Details section fields to summarize.
- Add Subtotals for the fields, for the Goup, as well as Grand Total for the report.
- Export the report to MS Excel - Data Only (xls), and select the option "Use worksheet functions for summaries"
- 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