Search code examples
sql-serverdatereporting-servicesexpressioniif

SSRS conditional date selection expression

I have a sql ssrs report which takes data based on selected date range.

There's 2 date parameters: date from date till

I need the date from and date till to fill in automatically based on which date of the week it is when the report is run.

  1. If it's monday, then the date from should be the monday of previous week and date till should be the sunday of previous week.
  2. If it's tuesday, then the date from should be current week Monday and date till should be Tuesday and so on - Wednesday (Monday to Wednesday), Thursday (Monday to Thursday)...

Or perhaps for point 2 it would make sense to just have an expression that if it's tuesday, then select the whole current week monday to sunday as future dates simply won't return any data anyway and SSRS allows to select future dates?

Thank you!

Solution

  • You can these expression on the default value on your parameters: date from:

    =IIF(DatePart("w", Today) = 2,DateAdd("d", -7, Today), DATEADD("d", 1 - DATEPART(DateInterval.WeekDay, Today,FirstDayOfWeek.Monday), Today))

    date till:

    =IIF(DatePart("w", Today) = 2, DATEADD("d", 1 - DATEPART(DateInterval.WeekDay, Today,FirstDayOfWeek.Monday), Today)),Today)

    DatePart("w", Today) begins on sunday that's why monday would be 2