0

I'm writing a sql query for an SSRS report and wanted to paramterize the @startdate and @endate fields for the user to specify. Yet when I put my query into SSRS, the user input is ignored. I was wondering if it had to do with the fact that I wasn't assigning to a parameter but comparing to it?

These are my declaration statements:

DECLARE @startdate NVARCHAR(10)
DECLARE @enddate NVARCHAR(10)

And this is my where clause:

WHERE assignees.id = customers.id
AND 

((dateadd(second,open_date,'19700101') >= @startdate
AND dateadd(second,open_date,'19700101') <= @enddate)

OR

(dateadd(second,close_date,'19700101') >= @startdate
AND dateadd(second,close_date,'19700101') <= @enddate))

AND group_name = 'SPRQ'
Matt Ball
  • 354,903
  • 100
  • 647
  • 710
jsmith
  • 565
  • 3
  • 14
  • 28

1 Answers1

1

Instead of using character types for date comparisons, I would suggest using date types for more reliable, predictable results. Also, check the parameters property of the dataset to be sure it shows the report parameter used to fill the query parameter. Sometimes the designer needs "reminded" which report parameter to use for the query's parameteer.

Rose
  • 156
  • 5
  • How specifically would I 'remind' the software to ask for the parameter? – jsmith Feb 27 '12 at 15:10
  • Check the 'parameters' tab/property of your data set. This lists the query parameter on the left side and the associated report parameter on the right side...not so much that it needs reminded to ask for it as it needs reminded where to find it :-) Often when parameter names are changed (even changing a lower-case letter to an upper-case letter), the report designer doesn't propagate the change to the parameter mapping. – Rose Feb 28 '12 at 15:07
  • Quick double-check...are you declaring your parameters at the top of your data set? If so, you will also want to remove that declaration as well...the parameter mapping will allow you to use the report parameter's declaration instead. – Rose Feb 28 '12 at 15:09