2034916 - Reports based off a command object return no data after upgrading to a newer version of Crystal Reports

• Blank report.
• Subreport does not return data after upgrading.
• When passing multiple values to a command object parameter, the reports returns no data in Crystal Reports.
• When entering the same string of values in previous version of Crystal Reports, it returns the expected data set.

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

1. In Crystal Reports, create a report off any data source.
    
2. Add a Command Objects to the report.
    
3. In the WHERE clause of the SQL Query of the command object, add the IN operator to return multiple values for a specific database fields.
      
4. Create a string parameter, and add it to the WHERE clause of the SQL Query surrounded by quotations and round parenthesis like:
       
          Select Country, CustomerName
          From Customer
          Where Country in ('{?MyParameter}')
      
5. When refreshing the report, enter in the parameter the values like: France','USA','India
       
   Before it would have returned the 3 countries on the report, but after upgrading, it returns no data.

• This situation occus because when a string with multiple values separated by single quotes is sent to the command object parameter like: France','USA','India
  but omitted the starting single quote and ending quote, because the start end end quote was part of the SQL Query code, Crystal Reports simply substituted the values entered in the parameter in the SQL Query, but in the newer version of Crystal Reports it is escaping the quotations when the parameter in the SQL Query is surounded by single quotes, and therefore sending a different string of values.
    
  For example: If the SQL Query code was:
      
         Select Country, CustomerName
         From Customer
         Where Country in ('{?MyParameter}')
       
  Then Crystal Reports substitute the parameter with the value entered, and send the following SQL Query to the database:
  
         Select Country, CustomerName
         From Customer
         Where Country in ('France','USA','India')
   
  Notice the multiple values in the WHERE clause is surounded by single quotes.
  
  But in the latest version of Crystal Reports, if the parameter is surrounded by single quotes in the SQL Query of the Command Object, it will escape all the quotations, and therefore sending a SQL Query like:
    
         Select Country, CustomerName
         From Customer
         Where Country in ('France","USA","India')
     
  Which will return nothing in the report, or subreport because this value does not exist in the database.
     
     
• The change in behavior was performed to enhance the product to ensure parameter values that contains single quote, like: Smith's bicycle
  will be sent to the database successfully when using a Command Object with a SQL Query that contains a string parameter in the WHERE clause.
  This change was tracked under ADAPT01700722
       
  After the product enhancement, if a string parameter is added to a SQL Query of a Command Object, and the parameter is surrounded by quotations in the SQL Query, then values entered in the parameter that contains single quote will be escaped to ensure it is returning the expected values.
      
  But if if the parameter is not surrounded by single quotes in the SQL Query, then Crystal Reports will simply pass the value entered in the parameter to the SQL Query without performing any modification.

• To be able to return multiple values for a string parameter, on a report that uses a Command Object with an SQL Query, you have to:
• Do not add single quotes around the parameter in the SQL Query.
• Enter in the parameter each values surrounded by single quotes, like:  'France', 'India', 'Mexico'  when using the IN operator in a WHERE clause of a SQL Query.
    
      
• Therefore if your report was designed in earlier versions of Crystal Reports, you will have to edit the report command object, and:
• Remove the quotations around the parameter.
• Enter the parameter values where each value is surrounded by single quotes, when using the IN operator.
   
  Therefore the SQL Query will need to be modified from:
       
         Select Country, CustomerName
         From Customer
         Where Country in ('{?MyParameter}')
     
  To the following: ( Simply remove the single quotes around the parameter, and enter the single quotes in the parameter value )
   
         Select Country, CustomerName
         From Customer
         Where Country in ({?MyParameter})

Update, empty data, no data, blank subreport, after patch , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem

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