1477387 - Unable to set location from one database to another one in Crystal Reports when the databases are located on the same Server

• Reports return data from the previous database it was set to report from.
• Tables points to the original database the report was created, even after changing the data source to another database.
• In Crystal Reports, after setting the data source location from one database to another database that resides on the same server, it  only change the properties of the main connection to the new Server, but doesn’t update any tables of the report to the new database.

• SAP Crystal Reports 2013
• SAP Crystal Reports 2016
• SAP Crystal Reports 2020

1. In Microsoft ODBC Data Source Administrator, create an ODBC DSN that points to one MS SQL Server database, and name the ODBC DSN:  SQL Server – A
         
2. Create a second ODBC DSN that points to another MS SQL Server database that resides on the same server and that have the same schema, and name the ODBC DSN: SQL Server – B
         
3. In Crystal Reports, create a report off an ODBC connection and select: SQL Server – A
        
4. Add at least 2 tables on the report, and add database fields from both tables in the details section of the report.
        
5. Save and refresh the report.
        
6. Perform a set location from the ODBC DSN: SQL Server – A, to SQL Server – B
        
7. Refresh the report, and notice the report still return the data from the SQL Server database the report was first created on.
        
8. Verify the properties of the tables
   • Under the menu “Database”, select “Set Datasource Location…”
   • In the “Set Datasource Location” window,  under Current Data Source, expand the properties of the data source, and notice it points to the right ODBC DSN: SQL Server – B
   • Expand each table, and expand each table properties, and notice it is actually pointing to the database the report was first created on. The change when performing the set location was not propagated to the tables. The “Catalog” still points to the previous database.

• When setting data source location from one database to another database that resides on the same Server, it will not change the database each table are reporting from because an ODBC connection does not restrict a user to a default database. It is normal behavior.
      
• Since the user used to connect to the Database Server have access to all the databases, no change are detected for the tables, and therefore it does not change each table to point to the default database setup in the ODBC DSN connection. 

• To change the database the report points to, you can use one of the following solution:
      
• Set data source location for each table instead of setting the location at the connection level; or 
     
• Create a users on your Database Server that only have access to the database you want the report to report from, and use this user to perform the set data source location.
  
  For Example: If user A only have access to Database A, and user B, only have access to Database B.
  When performing a set data source location for a report that was pointing to Database A, use the User B,
  and it will automatically change the database for each table because User B cannot see the Database A.
       

A product enhancement has been proposed to provide an option to propagate the database information to all the tables of the report. Despite it is has been proposed as an enhancement, it does not guarantee this functionality will be added in any future version of the product, but SAP is committed to review each of the proposed suggestion.

Crystal Reports, CR, Data Source, Tables, MS SQL Server , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem

SAP Crystal Reports 2013 ; SAP Crystal Reports 2016 ; SAP Crystal Reports 2020