SSRS with three Parameters with @StartDate and @EndDate, but allows the date parameters to blank

Question

I have the following SQL:

Select *
FROM tblAccessLog

WHERE
--For SSRS by AIS Number
(AISNo = @AISNoParam)

AND 

([ViewedDT]  >= @StartDate AND [ViewedDT]  <= @EndDate) 


ORDER BY AISNo asc, [ViewedDT] ASC

Here is a screenshot of the SSRS:

I would like to set it up where if the "StartDate" and "EndDate" are blank that it will return data. Currently, due to the "And" in the SQL, I get no data. I have tried to allow nulls in the parameters, but that doesn't return data.

Answer 1

You need to allow for the dates to be NULL on the parameter setting (Allow null vale) and change your code to allow for the same in sql.

Select *
FROM tblAccessLog

WHERE
--For SSRS by AIS Number
(AISNo = @AISNoParam)

AND 
(
   ([ViewedDT]  >= @StartDate AND [ViewedDT]  <= @EndDate) 
  or 
    (@StartDate is null and @EndDate is null)
)
ORDER BY AISNo asc, [ViewedDT] ASC