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

Very often, there are deployment issues with using the Msgbox function in an SSRS report. They work fine on our development machines, but can be difficult to get working when actually deployed. Here are a couple of links explaining the issues:

From MSDN

From SQLDev

My solution was a VERY simplistic implementation where the report was stopped, but it simply shows a report error with the message displayed to the user from the report viewer.

1. Create a new text/string parameter (I called it CheckDateRange)
2. Allow Blank Values and make it Hidden
3. Available Values = none
4. Advanced = Use Default values
5. Default values = specify values - in Value, use the function button and use the following: =CODE.CheckDateParameters(Parameters!BeginDate.Value,Parameters!EndDate.Value) - note that you can see my paramter names for the checked dates are BeginDate and EndDate

6. Finally, for the report code enter the following code snippet:

   Function CheckDateParameters(StartDate as Date, EndDate as Date) as Integer
   Dim msg as String
   msg = ""
   If (StartDate > EndDate) Then
     msg="Start Date should not be later than End Date"
     Err.Raise(22000, "VBCore.Utility", msg)
   End If
   End Function
   

Nothing fancy, but accomplishes what you need.

Good luck!

Answer 3

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 4

>     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

Unless they have made changes the MsgBox() function won't work on SSRS. It doesn't work on SSRS 2010. I believe it's a windows function so can't be used on a rendered web page.