SQL Server Performance

Date Parameter

Discussion in 'SQL Server 2005 Reporting Services' started by danny123, Jul 3, 2008.

  1. danny123 New Member

    Hi there,
    I am writing a simple report using SSRS 2005 where user selects the start date and end date , and data which was added between start date and end date is displayed.
    I created to date parameters and user can select dates from there. The issue is SSRS is allowing user to select the End date which is prior to selected Start Date. Is there a way i can specify or force user to select End date after Start date.
    Thanks
    Danny
  2. satya Moderator

  3. Madhivanan Moderator

    I dont think you can force it. However you can show a message on header if the end date is less than start date
  4. satya Moderator

    I believe you could do a function to validate the parameters
  5. danny123 New Member

    Hi Satya,
    Thanks for the support. I did use the custom code but i could not figure out how to assosiate that code with parameter as i could not find any place where i can set the properties for the parameter for validation.
    Please advise.
  6. danny123 New Member

    Hi there,
    I got this working. Following is the method i used to make it work in case someone else is in same situation :
    You need to add a function to validate the parameters.
    Go to the Report Properties under the Report menu and place this code to the Code Tab.
    Public Function CheckDate(SDate as Date, EDate as Date) as Integer
    Dim msg as String
    msg = ""
    If (SDate > EDate) Then
    msg="Start Date should not be later than End Date"
    End If
    If msg <> "" Then
    MsgBox(msg, 16, "Parameter Validation Error")
    Err.Raise(6,Report) 'Raise an overflow
    End If
    End Function
    Then go to the Report Parameters and Add named it XXX with the datatype is string. Checked the Hidden and Allow blank value ckeckboxes. In Available Values choose Non-Queried and from Default Values choose Non-Queried in right side of textbox then press the FX button then paste this code.
    =CODE.CheckDate(<parameterStartdate>.Value,<parameterEnddate>.Value)
    Then press OK.
    Thanks

Share This Page