• State Verified Answer
  • +1 person also asked this people also asked this
  • Locked Locked
  • Replies 2 replies
  • Subscribers 94 subscribers
  • Views 4020 views
  • Users 0 members are here
Options
Related
This discussion has been locked.
You can no longer post new replies to this discussion. If you have a question you can start a new discussion

Syntax for Date Range Parameter in Report Editor

arlie ramirez
over 5 years ago

Greetings,

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.

Parents
  • +2 over 5 years ago

    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.

Reply
  • +2 over 5 years ago

    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.

Children
  • 0 over 5 years ago in reply to Markus Weiss-Ehlers

    Hi Markus,

    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!