SAP Knowledge Base Article - Public

1531012 - How to create a Command Object in Crystal Reports?

Symptom

  • How to report off your own SQL Query?
  • Where can we enter a manually written SQL Query in Crystal Reports?
  • How to create a Command Object to enter your own SQL Query in Crystal Reports, or Crystal Reports for Enterprise?

Environment

  • SAP Crystal Reports 2008
  • SAP Crystal Reports 2011
  • SAP Crystal Reports 2013
  • SAP Crystal Reports 2016
       
  • SAP Crystal Reports for Enterprise 4.1
  • SAP Crystal Reports for Enterprise 4.2

Resolution

  • Command Objects allows the report developer to write his own SQL Query which will be represented in Crystal Reports as a table. 
     
    Important Notes:  
       
    Command Object is a powerful feature that enable a report developer to enter an SQL Query manually to retreive data, but with power, comes responsabilities, therefore when using a Command Object to enter an SQL Query, it is the report developer responsability to ensure the SQL Query syntax is correct. If you need assistance in writting the SQL Query, contact you database administrator, or consult your database documentation.
     
    Also, Crystal Reports supports SQL Query Language in a Command Table to retreive data from a data source like: SELECT, FROM, WHERE, GROUP BY, ORDER BY, UNION. And does not support SQL Query Language for data manipulation like: CREATE TABLE, ALTER TABLE, INSERT, UPDATE, DELETE, DROP TABLE... This is because Crystal Reports is a reporting tool, and not a data manipulation tool.
           
        
  • To ceate a report off a Command Object:
      
       
    • In Crystal Reports
         
      1. In Crystal Reports, create a report off your data source.
          
      2. In the "Database Expert", under the connection to your data source, double-click on "Add Command"
          
        Command Objects - 02.png
             
           
      3. In the "Add Command To Report" window, enter the SQL Query manually, like:
          
             Select Product_Name, Country, Cost
             From Product
             Where Cost <= 15        
            
      4. Click "OK" to create the Command Object.
         
            
               
    • In Crystal Reports for Enterprise 
       
      1. In Crystal Reports for Enterprise, create a new report, and connect to your data source.
            
      2. Still in the "Choose a Data Source Connection" window, under "Available Tables", click on the button: "Add Command"
         
        Command Objects - 01.png  
          
          
      3. Enter a name for the Command Object, or use the default name proposed, and click "OK"
           
      4. In the "Add Command Table" window, enter the SQL Query manually, like:  
          
             Select Product_Name, Country, Cost
             From Product
             Where Cost <= 15        
                
      5. Click "OK" to create the Command Object.          

Keywords

Command Object Table, SQl Server 2005, SQL Server 2008, Crystal Report 2008 , KBA , BI-RA-CR , Crystal Reports designer or BusinessViews Manager , How To

Product

Crystal Reports 2008 V1 ; SAP Crystal Reports 2011 ; SAP Crystal Reports 2013 ; SAP Crystal Reports 2016