Tuesday 4 December 2012

Validate Date Time Parameters in SSRS


If there are 4 parameters e.g start date, end date, start time, end time , then validating date range( say 7 days) and handling invalid inputs can be done with the following code in "No row Message" property of the control

IIF(Count("MainDS") = 0,
  IIF(DateDiff(
"s",CDate(CStr(FormatDateTime(Parameters!StartDate.Value, DateFormat.ShortDate)) & " "& Parameters!StartTime.Value),CDate(CStr(FormatDateTime(Parameters!EndDate.Value, DateFormat.ShortDate)) & " "& Parameters!EndTime.Value)) > 604800,"Date Range Should Not Exceed more than 7 days",
IIF(DateDiff("D",Parameters!StartDate.Value,Parameters!EndDate.Value) < 0, "Start Date should be less than End Date",
  IIF(DateDiff(
"s",CDate(CStr(FormatDateTime(Parameters!StartDate.Value, DateFormat.ShortDate)) & " "& Parameters!StartTime.Value),CDate(CStr(FormatDateTime(Parameters!EndDate.Value, DateFormat.ShortDate)) & " "& Parameters!EndTime.Value))<0,"If Start Date and End Date are same then Start Time should be less than End Time","No Data Available"))),nothing