Solved

Replacing SQL querey with a Stored Procedure

Posted on 2003-11-05
6
329 Views
Last Modified: 2008-01-09
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.


0
Comment
Question by:truedev-pk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
6 Comments
 
LVL 12

Expert Comment

by:DRRYAN3
ID: 9686662
Which version of CR do you have?  Did you use the SQL Driver or the ODBC driver originally?
0
 
LVL 101

Accepted Solution

by:
mlmcc earned 63 total points
ID: 9686812
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
 
LVL 6

Assisted Solution

by:PWinter
PWinter earned 62 total points
ID: 9706224
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 9707403
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question