Active TopicsMemberlistSearchHelpRegisterLogin

Data Connectivity
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Data Connectivity
Message Icon Topic: Changing datasource from sproc to command Post Reply Post New Topic
Author Message
SvenC
Newbie
Newbie


Joined: 23 Jan 2008
Location: Denmark
Online Status: Offline
Posts: 2
Quote SvenC Replybullet Topic: Changing datasource from sproc to command
    Posted: 23 Jan 2008 at 4:16am
I have a weird problem
 
I have a number of complicated reports that control the progress of a large number of administrative procedures and ony display the cases where one or more of the control parameters are missing or wrong.
 
The reports are bases on stored procedures. I would like to change the datasource from the sprocs to commands in the individual reports, but for some reason it is not possible to change the datasource from a sproc to a commandscript in crystal reports. I have usually no problems with changing the datasource between servers and databases in CR-reports. 
 
The sql-scripts in the sprocs and the command are identical and return exactly the same fields in exactly the same order, and runs fine in sql. I can insert the sql-script in the promted commandbox in the CR\Database\set database location\ and when I press the Update button Crystal reports obviously runs the commandscript and returns no errors. But the data-source remain the sproc. I have identical databases with different data, so it is easy to observe, that there is no change in the datasource.  
 
The reports are all originally written in CR v9 and I am working in CR v11. Theres is probably a very simple solution but ?? 
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 23 Jan 2008 at 2:23pm
Is there a specific reason why you must do this?  I don't see that you gain anything by making this change.
 
Are you using the Database Expert or the Set Datasource Location wizard set the command text?  If Set Datasource, do you select the stored proc in the top and select the command text in the bottom?
If that's not working, the only way to do this may be to add the command text under the Database Expert, replace each of the fields on the report with the corresponding field from the command text (don't worry about the formulas for now...) and then delete the stored proc from the report.  When you run it, each of the formulas that contain fields from the proc will come up as an error and you can replace them with the fields from the command text.  I know this can be time-consuming, but it may be the only option you have (other than not changing the reports in the first place.)
 
-Dell
Proviti, Data & Analytics Practice
www.protiviti.com/US-en/data-management-advanced-analytics
IP IP Logged
SvenC
Newbie
Newbie


Joined: 23 Jan 2008
Location: Denmark
Online Status: Offline
Posts: 2
Quote SvenC Replybullet Posted: 24 Jan 2008 at 7:36am
Thanks for the answer.
 
The reason for changing the datasource is that I want to make the reports available for other users of the same application and similar database in other companys. The installation of the reports is very simple, when the dataselection is fully defined in the report command.
 
I have tried using the Set database location and everything works fine, including the validation of the commandscript, except for the fact that the database location remain unchanged.
 
I have tried various combinations of Database expert and set database location, still with no result. Adding the commandscript as a new datasource and then deleting the sproc deletes all my report fields including the formatting formulas behind these fields.
 
I am avare that i can manually change each datafield one at a time in report fields and formulas when I have added the command as an extra datasource. But I have 8 different reports, with 25 to 30 reportfields in each depending on 50 to 60 fields in the dataset, and some rather tricky formulas, so it is worth a bit of trouble to solve the problem.
 
I do not understand why CR11 cant simply replace the sproc datasource with the command.
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 24 Jan 2008 at 10:14am
I'm not sure why Crystal isn't doing this for you - and I don't work with stored procs for data, so it's not something I can test myself.  If you can write a program, there may be a way to do this.  You would add the command to the report and save it.  Then walk through the report and change each field to get its data from the command instead of the stored proc.  After that, it would update the formulas too.  This would not be too difficult to do if all of the field names are exactly the same.
 
If you don't have access to do something like that, let me know as I may be able to work something out for you.
-Dell
Proviti, Data & Analytics Practice
www.protiviti.com/US-en/data-management-advanced-analytics
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.047 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.