1255767 - Unable to create a SQL Expression when reporting off a Universe in Crystal Reports

SAP Knowledge Base Article - Public

1255767 - Unable to create a SQL Expression when reporting off a Universe in Crystal Reports

Symptom

  • SQL Expression fields are not available
  • Unable to create a SQL Expression Fields in the Crystal Reports designer when a report is connecting to a Universe.

Environment

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

Reproducing the Issue

  1. In Crystal Reports, create a report off a Universe.
  2. In the Field Explorer pane, notice that the 'SQL Expression' option is not available so SQL Expression fields cannot be created.

Cause

  • When using a Universe as a datasource for the a report all of the SQL generation is being performed within the Universe itself and Crystal Reports will use the SQL Statement that the Universe sends to the Crystal Report Designer. Crystal Reports is unable to modify the SQL Statement being generated by the Universe.
           
  • When connecting to a table or view within the Crystal Reports Designer (instead of a Universe), Crystal Reports is able to modify the SQL Statement and this will allow Crystal Reports to use a function within a SQL Expression field and place this information into the Select clause of the SQL Statement.

Resolution

  • If a specific function or expression is required for a report, this function would need to be created within the Universe itself as a calculated member instead of a SQL Expression within Crystal Reports.
        
  • Another option is to connect directly to the database instead of using a Universe for the datasource and then the SQL Expression field will be available within Crystal Reports.

Keywords

KBA , BI-RA-CR , Crystal Reports designer or BusinessViews Manager , How To

Product

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