SSRS Calendar and Date Restriction

Interactive reports are always in demand, the ease it gives our users for usage. One of the important component to any report will be the Date Parameter/Filter, to get insight of historical data as well as current data.

Adding a calendar will solve the purpose.

SSRS provides a data type known as Date/Time for this purpose.

Now, that's simple we just use the Date/Time and got the Calendar. So, life's simple :) not for developers you know.

One of the requirements we usually get is to restrict the calendar, what we mean by this - user should be allowed to select only selected dates for which we got the data.

How to achieve that? Till now I haven't came across perfect solution for that.

Note: Though you can assign a default value to a Date parameter, you can't do that for available values.

Available Values section should be assigned - "No Default value", otherwise you will lose the Calendar.

Default values can be anything like

= Today    = Now     =DateAdd("d",-1,Today)

So, how to achieve the restriction of Dates?

We can use Custom code for that. Custom code will be a function that will validate the dates selected by the user.

Code

Function CheckDateParameters(d1 as Date, d2 as Date)

Dim msg as String

msg = ""

If (d1 <DateAdd("d",-200,Today ) ) Then                                         'If From Date is less than 200 days

msg="Please select 'From Date' again, it should not be before than " 

& DateAdd("d", -200, Today ) & "." & vbCrLf & msg

End If

If (d2 >DateAdd("d",0,Today ) ) Then   

'If To Date is greater than Current date

msg="Please select 'To Date' again, it should not be later than " 

& DateAdd("d", 0, Today ) & "." & vbCrLf & msg

End If

If (d1 > d2 ) Then                                                                        'If From Date selected is greater than To Date

msg="From Date should not be greater than To Date" & vbCrLf & msg

End If

Return msg

End Function

Create a textbox in your report and use the expression:

=Code.CheckDateParameters(Parameters!FromDate.Value,Parameters!ToDate.Value)

So, whenever the conditions mentioned in your Code breached by the user a message with show up in your report.