Comparing todays date against first day of the week

Question

I am trying to set a default parameter value in SSRS report. I want to test the current date to see if it equals the first day of the week (in my case Monday). If it is the first day of week, then I want the default value to be current date minus 2 days, if it is not the first day of the week then I want the default value to be current date minus 1 day.

I seem to have a syntax problem but it doesn't tell me where. My parameters are StartDate and EndDate.

this is what I've tried:

=iif(weekday(Today(),FirstDayOfWeek.Monday)==1,DateAdd("d",-2,today(),DateAdd("d",-1,today())

this is the generic error I get:

The value expression for the report parameter 'StartDate' contains eror:[BC30201] Expression expected.

Where am I going wrong?

Answer 1

It appears that you are missing a closing parentheses after the first logical part of the if statement and another to close the statement.

=iif(weekday(Today(),FirstDayOfWeek.Monday)==1,DateAdd("d",-2,today()),DateAdd("d",-1,today()))

Answer 2

You are trying to use Sql syntax in a SSRS VBA expression. SSRS VBA allows very similar expressions for date manipulation to Sql, the main difference being the use of the DateInterval enum.

So your expression needs to use VBA syntax:

=IIF(Weekday(Today, FirstDayOfWeek.Monday) = 1, DateAdd(DateInterval.Day, -2, Today), DateAdd(DateInterval.Day, , -1, Today))