question

PatriciaY-7986 avatar image
0 Votes"
PatriciaY-7986 asked Joyzhao-MSFT edited

Custom parameter in Report Builder

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!

sql-server-reporting-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Joyzhao-MSFT avatar image
0 Votes"
Joyzhao-MSFT answered Joyzhao-MSFT edited

Hi @PatriciaY-7986
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.
117268-01.jpg

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

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

117180-02.jpg

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.



01.jpg (42.7 KiB)
02.jpg (31.7 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Preview:

117248-03.jpg



0 Votes 0 ·
03.jpg (115.8 KiB)
PatriciaY-7986 avatar image
0 Votes"
PatriciaY-7986 answered Joyzhao-MSFT edited

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.117504-report-builder-add-custom-parameter-to-dropdown.png



· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

If I understand correctly, you only need to enter in the expression specifying the value: =DateAdd("d",-7,CDate(Format(Today(), "MM/dd/yyyy"))), the value Is the seventh day before the run date.
If you need other dates, you could change the expression to get the date you want, you could refer to :DATEADD Function.
If you need interval values, I suggest you refer to what I said above to set different dates.
If I misunderstand what you mean, please feel free to correct me.

0 Votes 0 ·