Replacing SQL querey with a Stored Procedure

I have a Crystal Report, which has an embedded SQL querey, (to fetch data for the report) I just want to remove that SQL querey and make my report fetch the data using a Stored Procedure instead.

Don't know how to do that! :)

If I try to remove the Tables from the report first, the report designer also start removing the fields from the report. I don't want to completely re-do the whole report.

Is there I way that I can just replace the SQL querey with the Stored Procedure name without disturbing the current layout / fields-positions etc from the report.

An urgent reply will be highly appreciated.

Thanx,
Kashif.


truedev-pkAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DRRYAN3Commented:
Which version of CR do you have?  Did you use the SQL Driver or the ODBC driver originally?
0
mlmccCommented:
I haven't found an easy way to do that.

You can try to change the location.

Click DATABASE --> SET LOCATION
Click SET LOCATION
Set the connection to your database
Select the stored procedure

I have had mixed results with that method.  Also it still refers to the data through the original query.  Usually to avoid future confusion and to make it easier in the long run I add the stored procedure to the report then replace the fields one by one. This is essentially rebuilding the report except that I have the old fields to use to help place the new ones.

Steps I use
Insert a field from the new source
Select it and the same field in the report
Make them the same size
Set fonts the same
Align top  
Align left
Click somewhere on the report to unselect the fields
Click the field (the new one s on top)
Click FORMAT --> MOVE TO BACK
Click the field again
Ensure you have to old field
Delete it

Repeat for each field then you can delete the original source.  It is a pain but I haven't found anything easier.

There are some tools that claim to be able to help but lack of $ mean I couldn't get them.  A list is at
http://www.kenhamady.com/bookmarks.html

mlmcc
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PWinterCommented:
Hi.

If you use a database other than MS SQL Server, you probably can simply re-set location pointing to the stored procedure. Depending of CR version, you might also need to allow your sp getting null parameters, and in this case have it to return a dataset quite similar to normal one.
If your report works against MS SQL Server, you have to re-add all fields manually as mlmcc describes.
I'd suggest couple of extra steps to the algorithm:
- When you done with all fields make sure that none of them still in use at any formula. Take a look on field explorer, none of the field should have a checkmark.
- If any of fields have the checkmark, find a formula(-e) that refers to the field, and replace the field with new one (from sp). If you have lots of formulae, you can export the report to a report definition text file, and do the search for the fieldname through the file.
- When no field has the checkmark remove old table(-s) from the report.
- Repeat this for subreports as well.

Good Luck.
PW
0
mlmccCommented:
If you try to remove tables, Crystal will warn you if it is still being used for something.  Sometimes it can be very difficult to find the offending field.

mlmcc
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.