SAP Crystal Reports 2011 is a popular report generation application included within the SAP BusinessObjects suite of products that allows a report designer to create reports from a variety of data sources with a minimum amount of written code. It can access data from most widely-used databases, including Microsoft Excel spreadsheets, Oracle databases, Microsoft SQL Server databases, IBM DB2 databases, and Microsoft Access databases. Crystal Reports can also integrate data from multiple databases within one report using Open Database Connectivity (ODBC) built into the Windows operating system.

Designers of Crystal Reports can select and link tables from a wide variety of data sources. It is often necessary to set the data source location of a Crystal Report so that the report connects to a different data source than the original data source that was used to design the report. When developing a Crystal Report, it is common to develop a report with a development data source, perform quality assurance for the report with a test data source, and finally execute the report in a production environment with a production data source. 

Because Crystal Reports allows for data sources for the reports to be easily changed, migration of the reports from environment to environment is straightforward.  Once the report is migrated to a new environment, you can update the data source configuration to reflect the correct data source for the environment.

I explain how to change the data source, first using the Crystal Reports Designer interface and then using the Central Management Console (CMC).

Using the Crystal Reports Designer to Change Data Sources

Within the Crystal Reports Designer, you can use the Set Datasource Location command to change the location of a database that is active in a report. The Set Datasource Location command provides a simple way to indicate the new name or location of data source of the Crystal Report. It automatically converts the database driver of the report to the database driver of the new data source. For example, when using this command, a Crystal Report can automatically convert a direct access data source to an ODBC data source.

The following steps describe how to use the Set Datasource Location command within the Crystal Reports Designer to update database connections for Crystal Reports.

Step 1. Open the report in Crystal Reports from the Start Page using File > Open. This opens a pop-up window that allows the user to choose a report. Select the report you want to modify and click Open, which opens the report in Crystal Reports Designer.

Figure 1

Choose a Crystal Report to modify

Step 2. In the subsequent screen, Select Database > Set Database Location (Figure 2) to open the Set Datasource Location window.

Figure 2

Open the Set Datasource Location window

Step 3. The Set Datasource Location window shown in Figure 3 appears, allowing the report designer to update the data source for the report. Options in this window include: 

• Current Data Source. This list displays the database and tables currently used in the report. The main report is shown as the root node. Expanding the properties node for a data source or for a table displays detailed information about the item. The entries in the Properties node include data source name, database type, user ID, and schema user ID.
• Replace with. This list displays all of the available data sources and connections to data sources.
• Update. Click the Update button to replace the data source information in the Current Data Source list with the newly selected data source.

Figure 3

The Set Datasource Location window

Step 4. Click the Close button to go back to Crystal Reports Designer.

Using the CMC to Change Data Sources 

The CMC of BusinessObjects 4.0 is a Web-based tool that you use to perform administrative functions and manage the 4.0 environment. In addition, the CMC allows the administrator to set and update the data connection for any Crystal Report that is included within the 4.0 system. A useful feature is the ability to select multiple Crystal Reports objects for which you want to change data source settings. However, only those report objects that have the same data source connection are updated.

The following steps describe how to use the CMC to update data source connections for Crystal Reports.

Step 1. Open the CMC of BusinessObjects 4.0. Connect to the CMC with the URL https://{servername}:8080/BOE/CMC.  Use an administrator account to log on. The CMC home page opens (Figure 4).

Figure 4

Log on to the CMC home page

Step 2. Navigate to the folders management area of the CMC by clicking Folders (Figure 5).

Figure 5

Open the folders management area

Step 3. Navigate to a folder that contains Crystal Reports (Figure 6).

Figure 6

The Folders Management Area of the CMC

Step 4. Select either one or multiple report objects as shown in Figure 7.

Figure 7

Select Crystal Reports objects

Step 5. Right click on any of the selected reports to open a context menu and click Database Configuration (Figure 8).

Figure 8

Choose Database Configuration from the menu

Step 6. The Database Configuration window appears (Figure 9). From this window, the administrator can set the data source configuration and data source logon settings for the chosen Crystal Reports.

Figure 9

The Database Configuration window

Step 7. The administrator must select one of following four options to determine what happens when the report is viewed or scheduled:

• Prompt the user for database logon. The system prompts users for a password when they refresh a report. This option has no effect on a scheduled instance of a Crystal Report. Also, the BI platform only prompts users when they first refresh a report; that is, if they refresh the report a second time, the user is not prompted.
• Use a single sign-on context for a database logon. The system employs the user’s logon and password to log on to the database. For this option to work, you must have your system configured for end-to-end single sign-on, or for single sign-on to the database.
• Use the same database logon as when the report is run. The system uses the same database logon information as was used when the report was run on the job server.
• Use user database credentials for database logon. The system uses the database credentials that are specified for the user account. 

The administrator must also select one of two options for database logon information:

• Use the original database logon information
• Use custom database logon information 

The first option allows an administrator to change the database, user name, and password, but not the database server that to which the chosen Crystal Report is connected. 

The second option allows an administrator to change the database server to which the chosen Crystal Report is connected. If you select the second option, you can specify a server name (or a data source name in the case of an ODBC data source), a database name, a user name, and a password from a number of predefined database drivers, or from a custom database driver that you select. If you have changed the default table prefix in your database, you can specify a new custom table prefix as well.