Executing/Passing parameters to a Stored Procedures based on user input

[mjmapi]

I have been trying to find a way around this with no success, I have a SP that takes a start and end time as input parameters, what I want to do is to pass variables to this SP based on the ‘Relative Date’ selected by the user e.g Today, Yesterday, Last Week

Here is what I’m trying to do on Crystal with no success, I would appreciate help on what I might be doing wrong or if the is a better alternative method

DECLARE @YStartDate datetime

DECLARE @YEndDate datetime

  SET @YStartDate = dateadd(day, datediff(day, 1, getdate()), 0) + '00:00:00'

  SET @YEndDate = dateadd(day, datediff(day, 1, getdate()), 0) + '23:59:59'

DECLARE @TStartDate datetime

DECLARE @TEndDate datetime

  SET @TStartDate = dateadd(day, datediff(day, 0, getdate()), 0) + '00:00:00'

  SET @TEndDate = dateadd(day, datediff(day, 0, getdate()), 0) + '23:59:59'

Case '{?RelativeDate}'

When ‘Today’ then         EXEC PDV_UserWorkGroupInterval  @TStartDate, @TEndDate

  When 'Yesterday' Then EXEC PDV_UserWorkGroupInterval @YStartDate, @YEndDate

  When 'DateRange' Then CONVERT(datetime, {?StartDate})

END

[DellSC · Accepted solution]

The only way you'll be able to calculate this in the report in order to send it to the stored proc will be to put the stored proc into a subreport and pass the parameters in from the main report.  There is no way to calculated this in the main report prior to calling the stored proc.

If you don't need any other subreports in your report, this should not be a problem.  You'll use a table or command that returns a single record (I like to use something like "Select GetDate()" for SQL Server or "Select Sysdate from dual" for Oracle).  Put the parameters in the main report.  Then calculate the actual start and end date in separate formulas based on the parameter.  Use these formulas to link to the store proc parameters in the subreport.

-Dell

[mjmapi · Accepted solution]

Thanks Dell,

I will try  this  and let you know, i would have preferred to avoid sub-reports, if the is no way around it I suppose i would have to go that route.

[mjmapi]

Thanks Guys, this worked beautifully

[mjmapi]

Thanks Dell and Abhi,

I'm making some progress with your help, i will let you know the outcome shortly

[abhilash_kumar]

Hi Melinyaniso,

For the 'Date Range' value, does the user need to select the Start Date and End Date from a Range prompt?

I guess you need two prompts - one that prompts for the verbiage and a Range prompt that drives the report only when the user choose 'Date Range' in the first prompt.

If you can confirm this, I can post another way to do this.

-Abhilash

[mjmapi]

Hi Abhilash,

Yes, if the user selects DateRange they will enter the start date and end date which will be passed to the SP.

As mentioned above, if other selections, i want to pass the parameters for the ranges start and end date.

[abhilash_kumar]

What Dell suggested is what I was going to suggest too!

You'll need:

- A Main Report with the two prompts

- Two formulas that return start and end dates based on the prompt selection

- The original report added as a Subreport with these two formulas linked to the Start and End prompt from the Subreport's Stored procedure.

-Abhilash