- Dynamic table name
- How to dynamically change the table the report uses?
- How do prompt for a table using a parameter in Crystal Reports?
- Crystal Reports XI
- Crystal Reports XI R2
- Crystal Reports 2008
- Crystal Reports 2011
Reproducing the Issue
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?
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.
- Create a copy of the Xtreme database. Name it Xtreme2.
- Copy the Purchases field twice in the Xtreme2 database. Name one field Purchases 2005 and the other Purchases 2006.
- Perform a global search to change all dates in Purchases 2005 to 2005. Do the same for Purchases 2006.
- Create a new report as a Blank report.
- Navigate to the datasource. Double-click Add Command. The Add Command to Report dialog box appears.
- Click Create. The Command Parameter dialog box appears. Type "TableParam" in the Parameter Name field.
- Click String in the Value Type drop-down list. Enter a default value. Click OK.
- Enter a SQL query similar to the following:
SELECT TableParam.Product ID, TableParam.Order Date, TableParam.Received, TableParam.Paid;
FROM TableParam TableParam;
Consult the appropriate database manual for the correct syntax of the data source used.
- 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.
- 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.
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.
A D D C O M M A N D O B J E C T S S W I T C H T A B L E D Y N A M I C R E F R E S H P A R A M E T E R S Q L C H O O S E T A B L E S C r y s t a l R e p o r t s S Q L Q u e r y C o m m a n d o b j e c t M u l t i p l e t a b l e s , c 2 0 1 2 0 3 0 , KBA , BI-RA-CR , Crystal Reports designer or BusinessViews Manager , How To