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?

Environment

  • Crystal Reports XI
  • Crystal Reports XI R2
  • Crystal Reports 2008
  • Crystal Reports 2011

Reproducing the Issue

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?

 

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 demo 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

ADD COMMAND OBJECTS SWITCH TABLE DYNAMIC REFRESH PARAMETER SQL CHOOSE TABLES Crystal Reports SQL Query Command object Multiple tables , c2012030 , KBA , BI-RA-CR , Crystal Reports designer or BusinessViews Manager , How To

Product

Crystal Reports 2008 V0 ; Crystal Reports 2008 V1 ; SAP Crystal Reports 2011, feature pack 03 ; SAP Crystal Reports 9.0 ; SAP Crystal Reports XI ; SAP Crystal Reports XI R2