Solved

Replacing SQL querey with a Stored Procedure

Posted on 2003-11-05
6
323 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
  • 2
6 Comments
 
LVL 12

Expert Comment

by:DRRYAN3
Comment Utility
Which version of CR do you have?  Did you use the SQL Driver or the ODBC driver originally?
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 63 total points
Comment Utility
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
Comment Utility
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 100

Expert Comment

by:mlmcc
Comment Utility
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

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…
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

743 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now