Passing Parameter Values to a Stored Procedure

[oj75]

Using, CR 9.0 Developer and SQL Server 2000. Is it possible to use an SP as the source for my report, and then add another parameter that does not get passed to the SP? Instead, the parameter values are used in a selection formula against the result set retrieved via the SP. Trying to prevent from having to write dynamic sql. When using the SP with dynamic sql the resulting fields are not shown in Crystal to build the report.


OJ

OJ
DB/.Net/CR Developer

 

[aftertaf]

with CR.Net i have damn nearly lost my mind.....
if you hard link the report to a SP, you are in for a hefy time cos the object model is complicated...

create a dataset.xsd and link that to your report: that way the records are available to your report.

then run the SP, get a dataset object and whang it to your report as its datasource

much easier in the long run, i assure you!

and go here:

http://www.crystalreportsbook.com/

damn fine FREE book

david

 

[vidru]

Yes, you can do that, but you'll take a performance hit because the records will be re-filtered in Crystal. If you're dealing with a small # of records, it's not really an issue, but otherwise, it could really slow down your report.

-dave

 

[aftertaf]

you find???

since i changed to the push model i find i have a lot more control over the report, and it runs faster too...

 

[oj75]

So, this is not possible unless using the push model?? This can't be accomplished when designing the report CR 9 Developer?

OJ
DB/.Net/CR Developer

 

[vidru]

Did you read my previous post? Just add your proc as the datasource for the report. Crystal will automatically add your parameters. Then, create your other parameter in Crystal, and put it in your record selection formula. The down side of doing it this way, is that you're losing the efficiency of the stored procedure, because the records will be filtered on the Crystal side after the dataset has come back from the proc.

-dave

 

[oj75]

I did read your previous post. However, I've done what you have written. Everytime I add my other parameter, after crystal has automatically added the SP parameters, and add the other parameter to the selection formula I get a Failed to Open Rowset error. When I delete the selection formula with the other parameter the error goes away.

Any ideas? Have you done this before?

OJ
DB/.Net/CR Developer

 

[vidru]

Yes, I've done it before, and never had a problem. What kind of connectivity are you using to SQL Server from Crystal? If using OLEDB, try switching to ODBC, or vice versa, and report back.

-dave

 

[oj75]

Getting worse, switched to ODBC connection and now stored procedure is not pulling any data. Not sure what is going on.



OJ
DB/.Net/CR Developer