SAP Knowledge Base Article - Public

1687596 - Crystal Reports removes spaces from the SQL Query when reporting off a Universe

Symptom

  • Incorrect data set returned
  • When reporting off a Universe in Crystal Reports, it does not return the expected number of records
  • When reporting off the same Universe in Web Intelligence, it returns the expected result set.
  • The SQL query Crystal Reports send to the data source is different than the one sent by the Web Intelligence document
  • Crystal Reports removes spaces from the SQL Query when reporting off a Universe

Environment

  • Crystal Reports 2008
  • Crystal Reports 2011

Reproducing the Issue

  1. Create a Universe off any data source
  2. Add one table to the Universe
  3. Create an object with an SQL that uses spaces, like:  Replace(City.city,'        ',' ')
  4. Save the Universe, and export it to the BusinessObjects Enterprise repository
  5. Create a Web Intelligence document off the Universe that uses that object
  6. Notice the SQL Query generated
  7. In Crystal Reports, create a report off the same Universe
  8. Notice in the SQL Query generated, you will see something like:

               Replace(City.city,' ',' ')

           Instead of the expected SQL to be like

               Replace(City.city,'        ',' ')      

           You notice it actually removes all the spaces, which causes the data source to return a different result set.

Cause

  • The issue has been identified and logged under Problem Report ID ADAPT01562924

 

Resolution

  • To resolve this issue, apply one of the following product update for Crystal Reports 2008:
    • Fix Pack 3.7
    • Fix Pack 4.1
    • Service Pack 5

Keywords

CR , KBA , BI-RA-CR , Crystal Reports designer or BusinessViews Manager , Bug Filed

Product

Crystal Reports 2008 V0 ; Crystal Reports 2008 V1 ; SAP Crystal Reports 2011, feature pack 03