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.

7 comments:

  1. Do we need to add any reference? I have copied the above code and created one text box as you have suggested, but I am getting an error on line 4. I have changed it to '2014-01-01' because i do not want that user select the date before this date. Could you please tell me what mistake Am I doing here? Many thanks.

    ReplyDelete
  2. Hi Mustafa,

    We won't need to add any reference.
    It seems that while copying the above code, there is one thing that we need to ensure for the below lines:

    msg="Please select 'From Date' again, it should not be before than "
    & DateAdd("d", -200, Today ) & "." & vbCrLf & msg

    and

    msg="Please select 'To Date' again, it should not be later than "
    & DateAdd("d", 0, Today ) & "." & vbCrLf & msg

    There shouldn't be any line break in between.

    Please let me know in case you face any problem.

    ReplyDelete
  3. Validation is working perfectly but it not able restrict report.(after firing validation still it shows report)

    ReplyDelete
  4. Hi,
    Nice post, i have one query.
    I have one requirement like have two parameters FromDate, ToDate. I want to restrict to FromDate before 1st Aug 2018, means i don't want to see before 1st Aug date in date picker in ssrs reports.

    ReplyDelete
  5. HI,
    While am using the same code in the report paramter-code section then in the output am getting this error
    there is error on line6 of customcode: [bc30035] syntax error

    ReplyDelete