SSRS (Visual Studio): Report returns no rows when Date/Time parameters used

0
votes

If I use this in my dataset SQL:

DECLARE @StartDate DATE = '2017-01-01'
DECLARE @EndDate   DATE = '2017-03-01'

The report runs and returns the expected Data.

When I change the above code to this:

DECLARE @StartDate DATE    
DECLARE @EndDate   DATE

  1. I am used to Visual Studio detecting that I have two date variables and creating two Date Parameters. But in this case the parameters are not automatically created. So, I create the Parameters myself - as Date/Time.

  2. But ... I get no data when the report runs.

The Dates that SSRS displays from the Calendar selection are in m/d/y [3/1/2017] format.

The Database field that is being used to compare with @StartDate and @EndDate is a SQL Server DateTime field.

This is an example of how the date parameters are being used in the SQL:

WHERE 
  a.SomeDate >= @StartDate
  AND a.SomeDate < DATEADD(dd,1,@EndDate)

Other reports that I have created: 1. Automatically create the two Date Parameters once I save the Dataset. 2. Return Data as expected using the Date Parameters.

I would appreciate any suggestions. Please let me know if there is more information I can provide that will help you to help me!

1
reporting-services
Personally I would use Profiler to see exactly what code is being sent to the database when you try to run the report. This will likely show you where your issue is. – HLGEM
You might also grab the profiler information from one that is working just to see if you can see a difference in how the parameter data is being sent. – HLGEM
Thanks @HLGEM. I have never used Profiler. Is that something I can use in Visual Studio? Or is it an SSMS tool? I'd appreciate any info. Again, Thanks! – Talay
It is a standalone tool that comes with SQL Server. It should be in your program list if you included it in the install. If not then you need to go back and get it from the install disks. – HLGEM
It uses memory, so make sure you only run it on dev or QA and then turn off the trace when you are done. – HLGEM

1 Answers

0
votes

When you write you dataset's query you should NOT declare the parameters. If you declare them SSRS assumes they are local.

The parameter datatype is inferred from the usage but you can change it to whatever you need later.