Joined: 04 Nov 2009
Online Status: Offline
Posts: 123
Topic: Help using Stored procedures for the first time Posted: 29 Dec 2009 at 2:32pm
I have a report that I need to include a Stored Procedure from a MS SQL database. The SQL I was giving to review is the following:
SELECT driveid, shiftid, ProjectedDaSHRate, ActualDaSHRate, (SELECT dbo.DAL_DaSH_DriveProjected(dbo.DriveShiftDaSHRates.driveid) AS Expr1) AS Dash FROM dbo.DriveShiftDaSHRates
The line (SELECT dbo.DAL_DaSH_DriveProjected(dbo.DriveShiftDaSHRates.driveid) AS Expr1) AS Dash
is the line referencing the stored procedure and work fine in a SQL viewer.
When I am in CR DB expert and try to add the Dal_Dash_DriveProjected Stored procedure I get a prompt screen titled "Enter Values" with @DriveID in both corrners. there is a white text box and then a check box next to "Set to Null" then the OK and Cancel button. No matter what I put in the text box. I keep getting an error message: The Parameter is of type "Number" ..... What am I supposed to put in the box. to work in CR.
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Posted: 29 Dec 2009 at 2:54pm
Sounds like the SP has a paramter set into it. If a SP has a parameter it will create a a paramter in the crystal report and ask the user to enter it (the same as a crystal param). The difference is the param from a SP runs at the source of the SP and not after the data is pulled like a crystal param.
You have to enter some number value that the SP is expecting.
Joined: 04 Nov 2009
Online Status: Offline
Posts: 123
Posted: 29 Dec 2009 at 4:19pm
I know the number it is looking for is from the driveid field. As I have it working in a sql statement posted above where the driveID is passed on as the paramert, how can I maybe do this with Crystal reports? If it is possible at all.
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Posted: 30 Dec 2009 at 6:39am
I am not sure what you are asking. What do you mean by "how can I maybe do this with Crystal reports"?
One of the great things about using SPs as a data source is that you can create a param in it and that param is also created in crystal when the source is added to the report. This allows you a lot more flexibility in data selection at run time.
Joined: 04 Nov 2009
Online Status: Offline
Posts: 123
Posted: 30 Dec 2009 at 7:17am
I guess what I am saying is the parameter is being passed on automatically in the sql statement, so how or can I do the same thing in crystal. Or is it possible to use SQL expression fields to include this stored procedures?
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Posted: 30 Dec 2009 at 7:22am
You mean because once you add the the SP it is asking for the param?
Just put in a dummy (but acceptable) value which it needs while you build the report. Once you refresh the data or rnu it in the viewer it will ask for the param again just like if it was a crystal param.
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Posted: 30 Dec 2009 at 7:30am
As I reread your original post, I guess you are wanting to know what "dummy" value is acceptable since nothing you are entering is working, correct? You will have to get into the SP and decipher it to determine that.
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
