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 ?? 

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

 

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.

 

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