Changing the Data Source

Many reports are very straight forward and all the tables come from the same data source. The tables that are used when you designed the report are the same tables that will be used when the report is put into production. But this isn’t always the case. It is common for the requirements to change and for a report to use a data source different from what it was originally designed for. Or, you may have a development server that is used for designing and testing reports and a production server that is used when the report is finished. Crystal Reports has a number of features that make it easier to change the location of the tables. It lets you set the location of a data source, change the name that a table is referenced by, and verify whether a database is valid.

Using the Set Location Dialog Box

There are two ways of changing the data source. You can either switch to a different database or you can change out individual tables. If you switch to a new database, then the tables in the new database should be the same tables located on the old one. Prior to switching to the new database, you want to make sure that their names match exactly. If want to stay on the same database, but change individual tables, then the table structures should be similar to each other. If some of the field names have changed or there are missing fields, you will get the chance to map the old field names to new field names in the new table.

To change a report’s data source, open the Set Datasource Location dialog box by selecting the menu options Database > Set Datasource Location. It is shown in Figure 10-15.

Figure 10-15. The Set Location dialog box.

The top of the dialog box shows the current data source. It lists the database server and the tables used. You can also click on each item to see its properties (name and type). The bottom window is labeled “Replace With”. It shows the same data sources that you would see if you were looking at the Database Expert dialog box.

If you want to have the report change to a completely different database, select that database from the Current Data Source window at the top. Go to the Replace With window at the bottom and find the new database that you want to switch to. Select it and then click the Update button.

To replace an individual table, go to the top window and locate the existing table. In the bottom window, either open the Current Connections folder or the History folder and find the new table name. Select the new table and click the Update button.

When you open the Set Location dialog box and click on one of the database servers, a list of all the available tables in the database is created. If at a later point you go into the database server to make changes to the tables (e.g. adding or deleting tables), the Set Location dialog box won’t recognize those changes. It will still show the original list of tables. It only queries the database server the first time it is opened. You can update the list by right-clicking on the Table category and selecting Refresh.

If you find that the Update button isn’t enabled, you haven’t selected data sources that are compatible with each other. For example, if you have a table selected as the Current Data Source, then you have to have a table selected in the Replace With window. You can’t select a database in one window and a table in the other window.