Stored procedure with Parameters?

[ewithoud]

I have created a stored procedure which calculates the # working days between 2 dates taking into account a table with non working Holidays.

In Crystal I want the result set of a report showing details to pass in two dates and get the result from the procedure to show the correct working days.

How do I Accomplish this?

Many Thanks,

Erik

Implementation Consultant at

http://www.clickhome.com.au

 

[briangriffin]

It should be automatic - create a new report, and base it on the stored procedure (in the Database Expert you should see folders for tables, views, and stored procedures).

It will probably prompt you for the date values when it does the syntax check, but ultimately you'll see that in Crystal parameters have been created for you that match the input parameters of the stored procedure.

Note that you can then rename them to something more user friendly, and also change the prompt text, etc. as you would any other parameter.

 

[ewithoud]

Hi Brian,

yes it does import the stored procedure, but I want the parameters to be linked to a result set.


ie.
Task 1 Task 2 # workingdays
Job 1 1-1-11 3-3-11 50
Job 2 7-1-11 3-3-11 45
Job 3 9-2-11 9-3-11 35
Job 4 4-3-11 1-5-11 50

So the task 1 and task 2 date would be the input for the stored procedure and should be calculated on runtime.

How do I accomplish this?

br,

Erik

Implementation Consultant at

http://www.clickhome.com.au

 

[ewithoud]

Dear all,

I figured out how to do it.
Actually there are two ways to go about it.

1) By using a stored procedure and passing the values through to a subreport (this wasn't an option for me as I had to use the calculation in a subreport as well)

2) By using a function and creating a SQL Expression:
dbo.getworkingdays (date1,date2) you can then add the calculation to the detail section.

Erik Withoud

Implementation Consultant at

http://www.clickhome.com.au