Topic: SQL Server Stored Procedure Posted: 20 May 2015 at 8:27am
I have a report that is built off of a SQL view. I pass parameters in my application which is written in c#/.NET and produce the report using the passed parameters.
I would like to steam line the report and use a stored procedure directly to pull data into the report.
I searched the forum and can't find anything. I am familiar with the "command" option, but I need to pass parameters.
How can I call a stored procedure which has parameters from Crystal Reports Version 14.0.4.738 RTM.
I have found a few things on the web, but not an example/video of a CR that calls a Stored Procedure and passes the stored procedure parameters. I do not want the user to be prompted for parameters. The report will get the parameters from the application.
Joined: 05 Dec 2013
Online Status: Offline
Posts: 50
Posted: 27 May 2015 at 9:39am
I'm not having luck using the Parameter in the Command, but what you could do is have your Command provide a dump of the entire Stored Procedure, and then whittle down the result set with your Select Criteria.
Thanks for the reply, I am not sure how to do what you are saying, but I think in general, I would need to be able to use my parameters in the query. If I don't use the parameters, I have to build and keep like 12 reports up to date. If I can use the parameters, I can pass it in code to a generic report.
You would think that in all of the world, someone has done this. It should be fairly easy I would think to call a stored procedure from CR and use parameters in that query. Fundamentally, CR knows where the database is, it has credentials to connect, it can pull from a table, it can pull from a view..... hmmmm...
Joined: 05 Dec 2013
Online Status: Offline
Posts: 50
Posted: 28 May 2015 at 9:05am
You can still use parameters in the report, just not in the Command (as far as I know).
If you go into your Database Expert, create a connection to the database where your Stored Procedure lives, expand the database, and choose the "Add Command" option.
In this Command, enter your SQL select criteria to pull everything from the Stored Procedure. Then click OK and go back to the main report.
Now under Database Fields you should have your Command with the option to drag in whichever columns you want from the Stored Procedure.
From there create a new Parameter by right clicking "Parameter Fields". I'm not great with Parameters, but once setup properly you can use that Parameter in your Select Criteria to control which data from the Stored Procedure is included in your report.
I tried something similar to what you are saying, but in the main report under the database fields nothing populates. I thought it was related to the parameters not being available when the report was written.
I will give it another shot when I have some downtime with a basic stored procedure. But I am not too hopeful. But thanks for your willingness to have a dialog on this issue.
Joined: 05 Dec 2013
Online Status: Offline
Posts: 50
Posted: 28 May 2015 at 11:12am
I can tell you it is possible. I have basically taken an example I did to perform what I've described and tried to change the code to be more generic. The first step I would do is create the Command and see if you can drag some fields in and view some data.
Just to be clear, in my scenario I was querying the data from a Stored Procedure just like I would from a table. I was not executing the Stored Procedure from Crystal which I don't think is possible.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum
IP Logged
