CODEWITHSUNDEEP
reporting-servicessql-server-2008-r2ssrs-2008-r2
A. Gross
A. Gross

Reputation: 21

SSRS Scheduled Report with parameters

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.

Upvotes: 2

Views: 3176

Answers (1)

Grizzly Bear
Grizzly Bear

Reputation: 97

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:

  1. Go to Default Values;

  2. Select Get values from a query;

  3. Choose the Dataset and Value field.

OR

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

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

Add the following expression:

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

Upvotes: 3

Related Questions