SSRS Scheduled Report with parameters

Question

I've got an SSRS Report with a date parameter. In addition to this being an on-demand report, I also need to schedule it to run on the 1st of the month with the data parameter being the last day of the previous month. When I'm scheduling the report, there's a spot to enter a parameter value, but I don't know how to tell it to use yesterday's date.

Could you simply make the default value of the parameter yesterday's date? Then you can tell the subscription to use the default value. — Jamie F
Subscriptions can also define hidden parameters. In the past, I've defined a specific boolean parameter for toggling on a "Summary Mode" that could tweak some formatting settings (such as hiding some groups/elements) and override other parameters. You might want to consider this if there are a few tweaks that you would like to make to the subscription generated report, but not enough to create a separate version. — bitnine

Grizzly Bear Grizzly Bear · Accepted Answer · 2016-08-26T18:11:21

There are two ways to achieve this

Create a Dataset and your query is as follows:

dateadd(second, -1, dateadd(month, datediff(month, 0, getdate()), 0)) as EndDate

Then in the Report Parameter Properties dialog:

Go to Default Values;
Select Get values from a query;
Choose the Dataset and Value field.

OR

You could use an Expression in the Report Parameter Properties Dialog:

Go to Default Values;
Select Specify values, and Add the Value
Click on the function button (Fx)

Add the following expression:

DateAdd("D",-1,DateAdd("D",-(Day(Now)-1),Now))

SSRS Scheduled Report with parameters

1 Answers