Nov 25, 2011

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.

No comments:

Post a Comment

Hi,

Thanks for your visit to this blog.
We would be happy with your Queries/Suggestions.