Back to Top

Overriding the Data Source Connection inside The Report Viewer

You may need to connect a report to a data source that is similar in content and structure to the data source referenced in the report, but that has a different name or driver. In this case, you need to override the data source settings. Below is an example of overriding the connection.

If your report connects using ODBC, it may reference an ODBC driver with a DSN "SALES_DB" That reference will be hard-coded inside the report. If you open that report using report viewer, and you do not override the settings, it's going to look on the system for that database driver. Successfully running the report depends on the existence of that database driver.

If it cannot find that database driver, you may get a message that says "failed to load report." You could also get a message saying that permission was denied, because it may think it cannot find the driver because the Windows user does not have the right to use it.

If it can find the database driver, it will use it, but that does not mean all worry is over. Is that database driver configured correctly? Does the user referenced in that driver have rights to use the database? Does the user even exist in the database? Is the driver even pointing to the database you think it's pointing towards? If The Report Viewer gives you a message that a specific table or field does not exist in your database, it is not a problem with The Report Viewer; most likely, that specific table or field does not exist in the database referenced in the driver, or the user doesn't have permission to view it. It could even be that the driver is pointing to test data. It could be that the database is down. Don't fall into the trap of blaming the messenger; try to understand the message.

Some reports will connect to Excel spreadsheets or .xls files. If your name is Rick, and you create a report that uses a spreadsheet stored at "C:\Users\Rick\Documents\sales.xls," and you give that report to another user, you should expect to get a message that it could not load the report. That's because that sales spreadsheet won't exist on the other users PC at that path. When you are including XLS, MDB, or some other external file, you have a few options to make the report portable. You could use the ODBC driver to access those data-sources. Or, you could save those data-sources on some network attached storage, and then map those network drives the same way for each user. If they were referenced at the path "N:\SalesData\sales.xls" and every user had the network drive mapped as the letter 'N,' your reports would be much more portable.

In some cases you will want users to have access to different data sources with the same report. You could give out multiple reports with different data-source references, or you could override the database reference in the report using The Report Viewer. Important Note: The data-source reference override feature in The Report Viewer will only work on reports that only reference one data-source. If your report references both an ODBC database and an Excel spreadsheet, the override feature will override both databases, and that's not what you want.

Also note when overriding the databases, The Report Viewer can not generally connect directly to your database. You need to override it to connect to a driver. So, if your data source is an ODBC database, set the server and database fields to the DSN of the ODBC driver.

Topics: