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

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.

CodePudding user response：

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