Active TopicsMemberlistSearchHelpRegisterLogin

Data Connectivity
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Data Connectivity
Message Icon Topic: Change a datasource from SQL View to Command Obj Post Reply Post New Topic
Author Message
tconway
Newbie
Newbie
Avatar

Joined: 25 Dec 2007
Location: United States
Online Status: Offline
Posts: 20
Quote tconway Replybullet Topic: Change a datasource from SQL View to Command Obj
    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
 
Dead
 
IP IP Logged
Lugh
Senior Member
Senior Member
Avatar

Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
Quote Lugh Replybullet 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.
IP IP Logged
tconway
Newbie
Newbie
Avatar

Joined: 25 Dec 2007
Location: United States
Online Status: Offline
Posts: 20
Quote tconway Replybullet 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 
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.