Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Replacing SQL querey with a Stored Procedure

Posted on 2003-11-05
6
Medium Priority
?
331 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 252 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 248 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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

715 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