How to incorporate the use of SSRS Dataset parameters with Timestamp escape clause?

Question

I've the following WHERE CLAUSE of a SQL Query String in the SSRS Dataset:

WHERE "Input_date" >={ts '2009-01-01'}
AND   "Input_date" < {ts '2009-12-31'}

And now, I'd like to use report parameter to wrap up the dates in the SQL statement, i.e. @indate1, and @indate2.

I've tried this, but error occurs:

WHERE "Input_date" >={ts @indate1}
AND   "Input_date" < {ts @indate2}

Please kindly advise. Thanks.

Answer 1

What have you done to add these parameters? I assume that you have altered the dataset query with the changes you have posted in your question. There are two more steps you need to perform to make this work:

1. Define a new parameter to the parameters folder. Right click the parameters folder and choose Add parameter. Specify which values you want the user to be able to select. Repeat for the second parameter.
2. Add the parameters to the dataset you are using for the report. This can be done in the parameters section when you edit the dataset. Add two parameters with the names @indate1 and @indate2, and set each parameters value to the parameters you defined in step one.
3. Alter the SQL statement as you described in your post. I would think that your SQL should look something like this (assuming Input_date is a column in your table):

WHERE Input_date >= @indate1 AND Input_date < @indate2

This is specific to the BIDS 2008 version of Visual Studio. If you are using report builder or something else, please let me know, and I can change the instructions a bit.