Skip to Content
avatar image
Former Member

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

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Best Answer
    May 21, 2015 at 01:26 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      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.

  • May 21, 2015 at 01:08 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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

  • avatar image
    Former Member
    May 25, 2015 at 09:09 AM

    Thanks Dell and Abhi,

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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    May 26, 2015 at 08:28 AM

    Thanks Guys, this worked beautifully

    Add comment
    10|10000 characters needed characters exceeded