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.
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
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
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
Create a textbox in your report and use the expression:
So, whenever the conditions mentioned in your Code breached by the user a message with show up in your report.