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.

Answer 2

I used the hidden text box idea because it was better than letting the report crash. What I was hoping to accomplish was to force the user to change their parameters up front before the report executed.

BTW, the syntax didn't work. I used this instead:

= Month(Parameters!begindate.Value) = Month(Parameters!enddate.Value) and Year(Parameters!begindate.Value) = Year(Parameters!enddate.Value)

Thanks coldice for the idea.

Answer 3

Hi,

I tried to call the below mentioned function in Report parameter default value. But it is giving error as the datatype of that Parameter is Date while here code is returning Integer. I also want to stop running the report while End Date is smaller than Start Date. Please let me know the calling of this function in Report parameter default values.

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

Any help would be appreciated.

Thanks Soniya