I am not able to locate the correct syntax when using a data parameter where the Range = Yes for the Report Editor. I created a date parameter for my report and set it to Range = Yes but I am receiving errors when executing it because the syntax is not correct. I was able to create 2 separate parameters with @startdate and @enddate and then input this into my data source query in the report so the code is as follows: AND startdate between @startdate and @enddate.
Question: What is the correct syntax in my SQL when I user a date Parameter where the Range = Yes? I would like to use this functionality in my report to set a date range or should I just use the two separate date parameters with an @startdate and @enddate?
Thank you in advance.
If your parameter is specified as range you will implicitly get 2 parameters defining the start and the end that you can use in your queries.
So instead of @parametername, you can use @parameternameStart and @parmeternameEnd in your SQL query or where-clause.
AND startdate between @parameternameStart and @parameternameEnd
These 2 parameters also appear in the dictionary of the report definition.
One sample for this would be the report User account operations (VI_UNS_Account_Operations) that does not demonstrate the SQL usage but the auto-created parameters in the report definition.
Thank you again so much for the details and screen shots. I was successful with using the following syntax in the SQL: AND startdate between @parameternameStart and @parameternameEnd. This is a fantastic option for our reports since we were hardcoding the "previous week" or "previous month" in the SQL where-clause. We can now replace the hard coded values in the where-clause with this date range parameter as it provides us with the necessary date ranges required for our reports such as:
Last 180 days, Last 30 days, Last 365 days, Last 60 days, Last 90 days, last month, last week, Last year, None, This month, This week, This year, Today, Yesterday.
Thanks again for the help and for sharing the knowledge!