3412223 - Unable to edit the SQL Query of reports in Crystal Reports

• Cannot modify the SQL Query a report.
• Able to view the SQL Query of a report, but cannot edit it.
• How to edit the SQL Query of a report in Crystal Reports?

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

1. In Crystal Reports, create a report based on Tables, or Views.
2. Under the menu "Database", select "Show SQL Query"
3. It shows the SQL Query, but there is no option to edit the SQL Query.
   How to modify the SQL Query of the report?

• When Database Tables, or Views are added to a report, Crystal Reports generates the SQL Query based on the Tables, Views, and database fields used on the report, as well as it generates a WHERE clause based on the Record Selection Formula.
• Since Crystal Reports generates the SQL Query, then by design, there is no option to modify the SQL Query of the report.

• For reports based on only 1 Table, or View, it is possible to convert the report to use a Command Object.
• A Command Object is a way to write your own SQL Query for a report.
     
• To update the report to use a Command Object in Crystal Reports:
  
  
  1. In Crystal Reports, open the report that have one Table or View, that you will like to modify the SQL Query.
  2. Under the menu "Database", select "Show SQL Query..."
  3. Copy the SQL Query.
  4. Close the Show SQL Query window.
  5. Under the menu "Database", select "Set Datasource Location..."
  6. In "Set Datasource Location", in the section "Replace with", under "Create New Connection", connect to the same data source as the current report.
  7. Under the database connection, select "Add Command"
  8. At the top, under "Current Data Source", select the Table, or View of the report, and on the right side, click on the "Update" button.
  9. In the "Add Command To Report" window, paste the SQL Query copied earlier.
  10. Make the desired modification to the SQL Query, and click "OK"
  11. Back to "Set Datasource Location", click "Close"
  12. Save your report.
      
      Your report is now based on a Command Object, and you can go back and edit the report SQL Query by selecting "Database - Database Expert", then right click on the object under "Selected Tables" section, and select "Edit Command"
        
         
• Note
  • For reports using multiple Tables, or Views, if you convert the report to use a Command Object, it can only map the database fields for one Table, and therefore all the other database fields based on other Tables will be removed from the report. It could be simpler to re-create the report based on a Command Object.

• 1215594 - How to set location from a Table to a Command Object in Crystal Reports?
• 2142332 - How to rename a Command Object in Crystal Reports?
• 1531012 - How to create a Command Object in Crystal Reports?
• 3302831 - Unable to View or Edit a Command Object of a report in Crystal Reports
• 2930165 - Database error when adding a SQL Query in a Command Object in Crystal Reports

CR, edit query , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem

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