ssrs 2008 - Reporting Services Parameter Constraint

Question

I have a reporting services (SQL 2008) report with two Date/Time parameters - begindate and enddate. I need to constrain enddate to the same month and year as begindate. This seems like it should be an easy thing to do, but I cannot figure it out.

Currently, I am checking the parameters passed to the stored procedure and raising an error if the two datetime parameters are not in the same month and year. I am looking for a more elegant way of accomplishing this.

Answer 1

You can check the EndDate value in parameter expression, and if it's incorrect, set it to StartDate + 1 Month.
Something like:

= IIF(DateDiff(DateInterval.Month, Parameters!StartDate.Value, Parameters!EndDate.Value) = 0, Parameters!EndDate.Value, AddDate(DateInterval.Month, 1, Parameters!StartDate.Value))

If you just want notify user, you can place some hidden text box with appropriate formatting (red big font) and message about date parameters incorrect range. In Hidden expression set

= (DateDiff(DateInterval.Month, Parameters!StartDate.Value, Parameters!EndDate.Value) <> 0)

Also, you can combine both actions with custom code:

Public DateMessage As String

Public Function ValidateDate(StartDate As DateTime, EndDate As DateTime) As DateTime
  Dim ResultDate As DateTime
  If (DateDiff(DateInterval.Month, StartDate, EndDate) <> 0) Then
    ResultDate = AddDate(DateInterval.Month, 1, StartDate)
    DateMessage = String.Format("End Date parameter value {0} 
      was out of range and was changed to {1}", EndDate, ResultDate)
  Else
    ResultDate = EndDate
  End If
End Function

Then, in Parameter value expression:

= Code.ValidateDate(Parameters!StartDate.Value, Parameters!EndDate.Value)

In Value property of tbDateParameterMessage textbox:

= Code.DateMessage

And in Hidden property expression:

= String.IsNullOrEmpty(Code.DateMessage)

EDIT But if you want to stop report running, use this custom code:

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

It's taken from SQLServerCentral forum.