Parameter Startdate, Enddate validation

  • Hi all,

    I'm working on a ssrs report with parameters startdate and enddate

    @startdate : default to 1st of previous month

    @enddate : default to last day of previous month

    User can change the startdate,enddate

    Now when user select

    case 1) @startdate < @enddate ( ex: startdate: 10/1/2010, enddate: 10/11/2010 this is fine )

    case 2) @startdate > @enddate (ex: startdate: 10/31/2010, enddate: 10/1/2010 this should not happened)

    The user should not be able to select values as in case2

    or

    If he select as in case2, then we should prompt/warn the user (msg -"startdate should not later than enddate")

    I tried the following solution:

    report->report properties->code

    step1)

    Function CheckDateParameters(@StartDate as Datetime, @EndDate as Datetime) as Integer

    Dim msg as String

    msg = ""

    If (@StartDate > @EndDate) Then

    msg="Start Date should not be later than End Date"

    End If

    If msg <> "" Then

    MsgBox(msg, 16, "Report Validation")

    Err.Raise(6,Report) 'Raise an overflow

    End If

    End Function

    step2)

    created a hidden parameter "@validate" and set its default to

    =CODE.CheckDateParameters(@StartDate.Value,@EndDate.Value)

    It worked in BIDS (My system) but failed on QA

    Please help me in solving this issue

    Thanks in advance:)

    -pravin

  • Make the available values for your @EndDate based on a query that constrains the dates to > @StartDate

  • Hi that is an alternative.

    I have tried using the code itself , it is working but still Im not satisfied... 🙂 the code is below. As you told I have used a hidden parameter then from there I called the function.

    But problem is because of the Err.Raise(6,Report) the return value is not a string type so that the parameter is not getting the value, ie the validation of the hidden paramter is not satisfied and so the report wont run this is what we need.

    But the problem is it will show a message as the validation failed for the parameter... 🙁

    Can any experts help on this...?

    Public Function MyFunction(S as Datetime,E as Datetime) as String

    Dim msg as string

    msg=""

    if(S>E) then

    msg = "Start Date Should not be greater than End Date"

    end if

    If msg <> "" Then

    MsgBox(msg, 16, "Report Validation")

    Err.Raise(6,Report) 'Raise an overflow

    end if

    Return msg

    End Function

    You can see this if you call the function from a text box rather than the hidden parameter, but there the report will run even though it is checking the Start date and End Date...

    Thanks & Regards,
    MC

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply