Convert to use Stored Procedures? 1

[mmarsten]

I am using Crystal Reports 8.0.
I have inherited dozens of legacy reports that use embedded SQL for data retrieval. How do I change these reports to use a stored procedure instead of the embedded SQL?

I am only vaguely familiar with Crystal Reports. I used version 6.0 for a few months but that was a few years ago. Back then I was never able to figure out how to convert from embedded SQL to stored procedures. I ended up rewriting all of the reports to use procedures. Does version 8 offer a solution?

Thanks,

Mike

 

[balves]

Go into a report and pick Set Location from the Database menu. Follow the dialog boxes until you can pick a stored procedure (presumably that fits the report) as the data source.

Crystal will attempt to fix up the report. If you're lucky and the field names match and their aren't conflicts, or too many conflicts, it's a snap.

 

[Turchan]

I use CR8.5 and have the same problem as mmarsten. Tried to follow balves's advise but looks like report didn't pick up the stored proc - still runs off the query... Is there any tricks that I can exercise to get rid of embeded SQL?

 

[nicatt]

A couple of thoughts. First, I am not sure what you mean by 'embedded SQL'. On PowerBuilder data windows you actually write SQL on the report. In Crystal, I think, you are talking about Crystal SQL Query files?????

If a Query file or if the report is pulling directly from a database you select the drop down menu Database\SetLocation and press the 'set location' command button to open the data source explorer. This will give you a menu of data sources you 'might' be able to access. Under the folder 'More Data Sources' will be the particular servers you can access. You sign in to the server and database where the stored proc is. Scroll through your stored proc listing and highlight the one you want. (Do not be surprised if it takes a few minutes to connect)

After highlighting the proc, hit the 'Set' command button and then the 'Done' button. It will ask if you want the proc to fix up the report. Selecting 'Yes' will attach the stored proc. A 'Mapping' window might show up. If so, it is telling you that the proc has fields in it that are NOT the same name as the ones on the report. Hopefully they have the same data Unchecking the 'match type' box helps me to identify the ones to 'match' since Crystal's selection of which one it thinks fits is often wrong.

Under the Database menu selection at the top is a 'Set Alias' option. The Crystal Report will continue to think it is looking at the 'old' code. This will let you change it but I have never done it.

The book 'The Complete Reference Seagate Crystal Reports 8' by Osborne I have found helpful. This question is covered in Chapter 15 where is how I learned it after moving from Crystal 6 to 8.

Cheers

Bob