The purpose of The Report Viewer is to allow end users to refresh database connections. For the user to be able to refresh the reports, the database connections need to exist in the location refreshed by the report.
This creates a problem for reports that use data sources reference by a file path.
When creating a report that uses an Excel spreadsheet as a database, report authors will often use the option in SAP Crystal Reports to Create New Connection using Access/Excel (DAO).
The path to the database file may be valid and accurate on the report author's computer, but it may not be the correct path on the report users computer. And, if the report viewer cannot find the path at that location on the report user's computer, it will not be able to refresh the reports data.
When creating a report that uses an Excel workbook as a database, add it to the report as an ODBC connection.
The first step is to create an ODBC driver that connects to the XLS database.
Step 1: Open the 32-Bit Database Administrator
As of version 4.6 of The Report Viewer, you must use 32 bit database drivers. If you are running Windows 7 x64, or another 64 bit operating system, make sure to use the 32 bit database driver administrator. This can be found on Windows 7 by searching for it, as shown in the image below:
Step 2: Add a New DSN
Once the 32-bit ODBC Database Administrator is open, use the Add button to add a new DSN.
The "Create New Data Source" wizard will display. Select to create the appropriate type of data source. This method works great for Excel, Access, Comma/Character Separated Values, Text files, and more.
Next you will need to enter information specific to your data source.
The Data Source Name must be the same on every computer.
The problem was the Excel file being referenced by the file path. This solution is to reference the Excel file by the Data Source Name. And, this Data Source Name will be stored inside the report. This means the Data Source Name must be the same on every computer that uses this report.
The path to the Excel Workbook, Access Database, CSV file, TXT file, or other database can be different on each computer.
Now we see that the DSN is set up on the computer for use.
Step 3: Create Report with New Database Driver
You are now ready to create your report with your new database driver.
Step 4: Setup Other Computers with the same Data Source Name
Now, repeat the steps 1 and 2 for all the computers that will use this report. The database file can be in different spots. As long as the ODBC drivers DSN are the same, the report will work fine.
Read this detailed example on setting on the client systems. Also, consider the server configurations.