Setting each Table to a Different Data Source

If you are changing all tables to the same server and database, then the code in Listing 17-11 works fine. But if the individual tables are being changed to different servers or databases, then the report might now work properly. The way a report is designed determines whether the individual tables can use different data sources or whether they have to all use the same server and database. You need to make sure that your report is set up to handle this situation.

If your report always uses the same server and database, then this section won’t apply.

First, lets look at an example of when a sales report with two tables needs to use different locations. The report uses two tables: Customers and Orders. The Customers table has its individual records updated regularly, but its size only grows a small amount each year. Thus, it requires no special attention. The Orders table is fairly large and every year its size gets significantly larger. To keep the Orders table optimized, all prior year data is archived on a server on the network that is separate from the current year data. You want your report to have the ability to either print from the current Orders table or pull the data from the archived Orders table. This isn’t necessary for the Customers table because it is relatively small and is only on one server.

To implement this, the application presents the user with a selection form that lets the user choose whether they want to print current data or historical data. When the user clicks the OK button, the application looks at which option the user selected and changes the server name and database name to either the current database or the archived database.

The problem with assigning tables to different locations is that your report has only allocated enough memory for one server name and database name property for all the tables. Even though your code appears to be modifying different ConnectionInfo objects for each table, behind the scenes they are all referencing the same object. In other words, if you have two tables in your report, changes made to the second table will override the changes made in the first table. When you design a report and Crystal Reports sees that the tables have the same data source, it internally creates a single ConnectionInfo object that is shared among all tables.

To fix this, the report has to be designed so that it is forced to allocate separate ConnectionInfo objects for each table. Do this by using the Set Location dialog box to set each table to use a different data source. Since the report is set to use two different data sources at design time, each table is allocated the required memory to track its own server and database properties. Now you can modify each table independently of the other during runtime and they will work as expected.

Let’s look at how this is implemented with the sales report example. The default behavior of the report is to print a sales report for current data. In this case, it will link the Customers and Orders table from the Northwind database on the local server. In Figure 17-6, The Set Location dialog box is shown and you can see that the Customers and Orders table are both coming from the local server and the Northwind database.

Figure 17-6. Both tables use the same data source.

After testing the report you determine that everything works as expected. The next step is to write the code so that the user can run the same report but and have the option to print historical data. For this example, we will say that the historical data is in the pubs database and still on the local server. The way the report is currently designed, if you write code to change the data source for the Orders table to the pubs database, then the Customers table will also change to the pubs table. To fix this problem, you have to re-map the tables so that the Orders table already points to the pubs database.

Figure 17-7. The two tables use different data sources.

In Figure 17-7, you have the same Customers and Orders tables as before, but now they are from different data sources. Both of the tables are still on the local server, but the Orders table is from the pubs database and the Customers table is still from the Northwind database. Now that both tables reference a different ConnectionInfo object, the code that changes the data source for the Orders table won’t overwrite the Customers table data source. Each table is independent of the other.

Unfortunately, the drawback is that this change makes the report’s default behavior print the historical data. Luckily, this is an easy fix. Put a condition in your code that sets the sets the DatabaseName property depending upon the user’s choice.