SAP Knowledge Base Article - Public

1215186 - How to dynamically change the table name using a parameter in Crystal Reports?

Symptom

  • Dynamic table name.
  • How to dynamically change the table the report uses?
  • How do prompt for a table using a parameter in Crystal Reports?

  • For example: A database has a Sales table for each year from 2000 to 2002 named Sales2000, Sales2001, and Sales2002.
      
    Each table has the same fields and these fields have the same structure. Only the data is different. When the report is refreshed, a prompt is required to allow the user to choose to which table the report will connect. How can it be done?
      
       
  • Note: Images and data in this SAP Knowledge Base Article is from SAP internal systems, sample data, or demo systems. Any resemblance to real data is purely coincidental.

Environment

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

Resolution

  • This type of report can be created using a command object with a parameter. A command object allows a custom SQL query to retrieve data. The parameter is then added to the command object.  The following example uses the Xtreme sample database.
       
    1. Create a copy of the Xtreme database. Name it Xtreme2.
        
    2. Copy the Purchases field twice in the Xtreme2 database. Name one field Purchases 2005 and the other Purchases 2006.
        
    3. Perform a global search to change all dates in Purchases 2005 to 2005. Do the same for Purchases 2006.
        
    4. Create a new report as a Blank report.
        
    5. Navigate to the datasource. Double-click Add Command. The Add Command to Report dialog box appears.
        
    6. Click Create. The Command Parameter dialog box appears. Type "TableParam" in the Parameter Name field.
        
    7. Click String in the Value Type drop-down list. Enter a default value. Click OK.
        
    8. Enter a SQL query similar to the following:
        
      SELECT TableParam.Product ID, TableParam.Order Date, TableParam.Received, TableParam.Paid
      FROM TableParam TableParam
       
      NOTE:
      Consult the appropriate database manual for the correct syntax of the data source used.
        
        
    9. Click OK. The Enter Parameter Values dialog box will appear. Type "Purchases" in this dialog box. Click OK through the dialog boxes to close the Database Expert.
        
    10. Design the report. Using the above example, the fields Product ID, Order Date, Received, and Paid are available for design.
          
      When the report is refreshed, a prompt now allows users to choose to which table the report will connect.


  • NOTE: The above technique only works if the tables have identical structure and same fields.  Also, the fields must have the same names, lengths, and data types.

Keywords

CR , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , How To

Product

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