Pass parameters to Stored Proc being used as data source? 3

[CraigBest]

Is it possible to pass parameters to a Stored Proc being used as a data source for a report? I have a situation where I want to use the SP to collect my data, but the SP requires a parameter to be passed to it (date range) in order to work when called. How can I do that from within my report?

 

[vidru]

If you have a stored proc (that has parameters) as a data source, as soon as you add the proc as the datasource, Crystal automatically creates the parameter(s).

Is that what you're asking?

-dave

 

[CraigBest]

It might be, I've never done this before! :-O

Currently I'm using a view to collect the data but it has a fixed date range. We have it in mind to re-write the view as an SP so we can change the date range when we run the report. I wasn't sure how this would work -- you are saying that when you select an SP like this that Crystal automatically creates a parameter to fill this field?

If so that is very good news!

 

[vidru]

That is correct. You'll need two date parameters in the proc (you can't use a range parameter if the parameter is coming from a stored procedure) like this:

CREATE PROC MyProc
@StartDate SMALLDATETIME,
@EndDate SMALLDATETIME
AS
.......

Something to note - Crystal will pass the time to the procedure as well as the date, so you either need to have your users set the time accordingly, or deal with the time portion in the procedure (i.e. strip off the time portion).

-dave

 

[CraigBest]

Great! Thanks, I'll pass this on to our DBA.