Date Parameters Defaults in SSRS

0
votes

I am trying to work out to do the following.

I have two date parameters a start and end date.

What I want to default in the start date is the FIRST MONDAY of the PREVIOUS month from the day you run the report.

The End date I would want to default the day BEFORE the FIRST MONDAY of the CURRENT month the day you run the report.

So if I was running it today the first Monday of November was 03/11/14. The day before the first Monday for December is 30/11/14, so that would be the end date. Another example if I run the report sometime in Jan next year - it will default to start date - 1/12/14 (first Monday in December) and the end date will be 4/1/15 (the day before the first monday in Jan)

Hope the above makes sense?

1
datereporting-servicesparameters
This should help: stackoverflow.com/questions/22471805/… – Tab Alleman
That's showing me first Monday of the week not the month - sorry I can't get my head around what I need to do to achieve what I'm after? – ikilledbill

1 Answers

1
votes

I use a long IIF() expression to achieve your goal. Put the expressions below into your parameter default values.

FIRST MONDAY of the PREVIOUS month from today:

=IIF(Weekday(DateAdd("m", -1, DateSerial(Year(Now()), Month(Now()), 1)))=2,
DateAdd("m", -1, DateSerial(Year(Now()), Month(Now()), 1)),
DateAdd("d",9-Weekday(DateAdd("m", -1, DateSerial(Year(Now()), Month(Now()), 1))),DateAdd("m", -1, DateSerial(Year(Now()), Month(Now()), 1))))

The day BEFORE the FIRST MONDAY of the CURRENT month：

=IIF(Weekday(DateSerial(Year(Now()), Month(Now()), 1))=2,
DateAdd("d",-1,DateSerial(Year(Now()), Month(Now()), 1)),
DateAdd("d",8-Weekday(DateSerial(Year(Now()), Month(Now()), 1)),DateSerial(Year(Now()), Month(Now()), 1)))

The above expression has been tested. I can‘t share you screenshot currently due to low points.