CODEWITHSUNDEEP
reporting-servicesexpression
Nate S.
Nate S.

Reputation: 1157

Comparing todays date against first day of the week

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?

Upvotes: 0

Views: 669

Answers (2)

Chris Latta
Chris Latta

Reputation: 20560

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))

Upvotes: 1

Ross Bush
Ross Bush

Reputation: 15185

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()))

Upvotes: 1

Related Questions