Custom parameter in Report Builder

Question

Can anyone help me write the code for a custom parameter for a report? I'd like to add an option to the dropdown list that is the following date:

Specific date in the past (2021-01-01, for example) until a date 7 days prior to the date the report is being run.

Example:
If I run the report on 2021-07-22, it should run for the following dates 2021-01-01 through 2021-07-15.

Is this even possible? I'm not an experienced user but attempting to edit some reports that were previously created. Any help is greatly appreciated!

Answer 1

Hi @Patricia Y ，
You need to set two parameters, the Startdate and the Enddate.
Regarding the start date, you can set and use Calendar, or you can choose to manually enter the date.

For the date seven days ago, you can use the expression:

=DateAdd("d",-7,CDate(Format(Today(), "MM/dd/yyyy")))  

For how to add parameters, please refer to: Tutorial: Add a Parameter to Your Report (Report Builder).

Best Regards,
Joy

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Thank you for your response, Joy! I appreciate your help. We actually already have the Start Date / End Date parameters setup, so we have the option to select them manually. However, I was hoping to add an option to the "Available Parameters" list so that it appears in my dropdown. This option would always give the date range of: xxx through 7 days prior to the date the report is being run.

The reason I need it to be an option in a dropdown is because I'm planning to schedule this report to run weekly so that specific date range would change every time the report is run.

So I'm hoping I can get some help on writing the syntax for that date range in the expression field for this new parameter. Does that make sense? I'm new to all of this but I'm attaching some screenshots to hopefully clarify a little better.