SAP Knowledge Base Article - Public

2344277 - SQL Expression Fields and Crystal Reports - Master KBA

Symptom

  • What is an SQL Expression Fields?
  • What SQL Expression Fields are used for?
  • What are the common issues with SQL Expression Fields in Crystal Reports?

Environment

  • SAP Crystal Reports 2008
  • SAP Crystal Reports 2011
  • SAP Crystal Reports 2013
  • SAP Crystal Reports 2016

Resolution

  • What is an SQL Expression Field?
    • SQL Expression Fields are similar to a formula, but written in SQL specific to the database you are connecting to.
    • SQL Expression Fields are used to concatenate or modify text, or to perform calculation at a data row level only, as it is executed for each row of data.
    • The main purpose of an SQL Expression Field is to improve report performance, as the SQL is executed on the database server, instead of locally like a formula.
             
               
  • SQL Expression Fields General Information
    • 1217871 - What is the intended use of 'SQL Expression Fields' in Crystal Reports?
    • 1200183 - How to use a parameter in a SQL Expressions Fields in Crystal Reports?
    • 1216498 - Can Teradata functions be used in a SQL Expression Fields in Crystal Reports?
    • 2345406 - Can we use aggregate function in a SQL Expression Field in Crystal Reports?
    • 2345437 - Can we use a subquery in a SQL Expression Field in Crystal Reports?
              
         
  • Troubleshooting SQL Expression Fields Issues
    • Most SQL Expression Fields issues are related to:
      • Incorrect SQL syntax used.
      • Misunderstanding of the purpose of SQL Expression Fields.
          
    • Therefore, when troubleshooting SQL Expression Fields issues, the suggestions are:
         
      1. Test the SQL used on the database side first to confirm the syntax is correct for your specific database. 
            
      2. Confirm the SQL Expression is used for database row string manipulation or calculation, because SQL Expression Field is not design for using aggregate or subqueries. For more details, see the SAP Knowledge Base Articles: 2345406 and 2345437
               
      3. Verify if the issue is in the list of issues below.
                       
                
  • SQL Expression Fields Issues           
    • 1217791 - SQL Expression Fields unavailable
    • 1255767 - Unable to create a SQL Expression when reporting off a Universe
    • 1554236 - SQL Expression Fields, truncated after 65,534 characters
    • 1407830 - Error: 'illegal zero-length identifier'
    • 1669367 - Error: 'Record Selection: {Database Field: <null>}'
    • 2031161 - SQL Expressions disappears from the Field Explorer when adding a second data source to a report

Keywords

CR, Expression Field, SQL Expression , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem

Product

Crystal Reports 2008 V1 ; SAP Crystal Reports 2011 ; SAP Crystal Reports 2013 ; SAP Crystal Reports 2016