SAP Knowledge Base Article - Public

2085350 - Report using modified SQL Query ignores part of the query in newer version of Crystal Reports

Symptom

  • Report created in Crystal Reports 8.5.x or older.
  • SQL Query was modified to create UNION ALL.
  • Running the report in newer version of Crystal Reports ignore the second part of the Union.

Environment

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

Reproducing the Issue

  1. In Crystal Reports 8.5, or lower, edit the SQL Query generated by Crystal Reports.
  2. Save the report.
  3. In Crystal Reports 9 or later, refresh the report.
    Notice it only sent the first part of the SQL Query.

Cause

  • Editing the SQL Query generated by Crystal Reports was removed from Crystal Reports 9 and later.

Resolution

  • Use a Command Object, as it allow to write your own SQL Query:
    1. In Crystal Reports, open the report.
        
    2. Under the menu "Database", select "Show SQL Query"
       
    3. Copy the SQL Query.
        
    4. Still in "Show SQL Query", click on the button"Reset" 
      ( The Reset button only display if the report was created in Crystal Reports 8.5 and lower, and the SQL Query generated by Crystal Reports was manually modified. )
        
    5. Close the window.
        
    6. Under the menu "Database", select "Set Datasource Location"

    7. In the "Set Data Source Location" window, under "Replace with", create a connection to the database.
        
    8. Expand the connection name and select "Add Command"
        
    9. Under "Current Data Source", select the Table Name, the click on the button "Update"
        
    10. Paste the SQL Query you copied and click "OK"
        
    11. Click "OK"
       
    12. Save the report.
  • Note: The above suggestion works well when the report is based on one Table, but if the original report was using multiple Tables, then the fields from all the other Tables cannot be mapped, and need to be replaced manually on the report.

See Also

  • If the report will use parameters, create the parameters in the Command Editor in  order for them to be "visible" to the command. 
  • Once the parameter has been created in the Command Editor, it can be edited in the Field Explorer to change or manage the properties.

Keywords

CR connect query union db , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem

Product

Crystal Reports 2008 V1 ; SAP Crystal Reports 10.0 ; SAP Crystal Reports 2013 ; SAP Crystal Reports 2016 ; SAP Crystal Reports 2020 ; SAP Crystal Reports 9.0 ; SAP Crystal Reports XI ; SAP Crystal Reports XI R2