Change a datasource from SQL View to Command Obj
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2022
Forum Name: Data Connectivity
Forum Discription: How to connect to data sources and export reports
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=2081
Printed Date: 09 Apr 2025 at 9:13am
Topic: Change a datasource from SQL View to Command Obj
Posted By: tconway
Subject: Change a datasource from SQL View to Command Obj
Date Posted: 18 Jan 2008 at 4:25pm
|
I have a large number of reports that are all very involved with many Running totals, suppressions based on field values, formulas etc, etc. Everything is working fine with the SQL data currently being used.
The reports all need to have added to them 10 years of historical data. The data is from an old Unix system that has been exported and added as a table in the SQL database being used. Thus, its SQL not Unix. But the historical table, though is has the essential information and will work, is very basic.
To combine this historical data with the current data, I intended to create a command object that will use a UNION query to line up the data which will work fine.
The problem though is converting the hundreds if not thousands of formulas, groups, suppressions, subreports with links, record selections and endless running totals with formulas and group references and so on to the new command object.
Is there any way of doing this besides manually changing each field instance one at a time?
Thanks
Tim
 |
Replies:
Posted By: Lugh
Date Posted: 22 Jan 2008 at 6:16am
|
There are a few simple solutions here. First, simply name the stored procedure in your database the same as the one being used currently by your report. Since, with the union, the actual schema of the SP isn't changing, it should continue to work just fine. Second, use the Set Datasource Location option from the Database menu. With this, you can replace one datasource with another. As a note, though, the report will continue to use the old datasource name to refer to everything. So, if you replace DataSource_1 with DataSource_2, all of your formulas will still reference {DataSource_1.MyField}. This can be confusing and off-putting, but doesn't actually impact the performance of the report. Third, if you originally set up the datasource using the Add Command function to use custom SQL, you can just change it in there, and, so long as you don't change the schema, it should work perfectly well. |
Posted By: tconway
Date Posted: 24 Jan 2008 at 7:53am
|
Thanks for the response. My mistake, I should have been clearer on the issue.
The current datasource on each of the reports is not from SPs, Command Objects and so on. The datasource is tables. Typically 5 to 8 tables are linked together from which only a few fields are used. The change is from Tables as a datasource to Command Object. The Command Object will be designed to have only the fields that are needed from the multiple tables in the current data with a union that will retrieve the corresponding data from the single SalesHistory table from the previous system.
From what I have read, this is only possible in XI using the Business Objects Repository. I'll be trying this shortly, but I'm hoping that I can save the Command Object in the Repository. Then, I'll be able to change datasource to the Command Object. But here is the critical part. I have the Repository, but the client does not. I'm hoping I can use my repository to change the datasource, then disconnect the report and move it to the client site with the new datasource.
...I hope...
Thanks for your help
Tim
|