SAP Knowledge Base Article - Public

2345406 - Can we use aggregate function in a SQL Expression Field in Crystal Reports?

Symptom

  • How to create report summaries using SQL Expression Fields?
  • Can we use summary functions like SUM, AVG, MAX, MIN, COUNT,... in a SQL Expression Fields in Crystal Reports?

Environment

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

Resolution

  • It is not supported to use of aggregate functions in SQL Expression Fields.
        
  • SQL Expression Fields are not design to use aggregate functions to summarize the report data, because when using functions like: SUM, AVG, MAX,... it requires a GROUP BY clause for the SQL Query, and Crystal Reports will not generate a GROUP BY clause.       
               
  • SQL Expression Fields are design to be used to concatenate or modify text, or to perform calulcation at a data row level only, as it is executed for each row of data. For more information, see the following SAP Knowledge Base Article:
          
     1217871 - What is the intended use of 'SQL Expression Fields' in Crystal Reports?
                 
  • Note: If you were to create an SQL Expression Fields using an aggregate function, like:  SUM({Sales}), and add it to the report,  it will return the expected result if you only have that SQL Expression Field on the report and nothing else, but as soon as you add other database fields it will fail since Crystal Reports will not be adding a GROUP BY clause. This is normal behavior because SQL Expression Fields with aggregate functions is not supported.

Keywords

CR, SQL Expression , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , How To

Product

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