SSRS Expression - Nested IIF, default date

Question

I have an SSRS report with a date parameter which defines the start of the week that we wish to report on. I want the default date for this parameter to display as follows based on a nested IIF statement that uses the current day of the week to determine which date the parameter should display as default (I have included example dates for January in the brackets):

Monday (18th) = Monday Last Week (11th)
Tuesday (19th) = Monday Last Week (11th)
Wednesday (20th) = Monday Last Week (11th)
Thursday (21st) = Monday Last Week (11th)
Friday (22nd) = Monday This Week (18th)
Saturday (23rd) = Monday This Week (18th)
Sunday (24th) = Monday This Week (18th)

I have attempted to write an expression to accomplish this but so far I have been unable to get the parameter to display as I require. The expression I have written is:

=IIF(DateInterval.Weekday = 1, 'Monday = Monday Last Week
DateAdd(DateInterval.Day, 2-WeekDay(Today), DateAdd(DateInterval.Day, -7, Today)),
    IIF(DateInterval.Weekday = 2, 'Tuesday = Monday Last Week
        DateAdd(DateInterval.Day, 2-WeekDay(Today), DateAdd(DateInterval.Day, -7, Today)),
            IIF(DateInterval.Weekday = 3, 'Wednesday = Monday Last Week
                DateAdd(DateInterval.Day, 2-WeekDay(Today), DateAdd(DateInterval.Day, -7, Today)),
                    IIF(DateInterval.Weekday = 4, 'Thursday = Monday Last Week
                        DateAdd(DateInterval.Day, 2-WeekDay(Today), DateAdd(DateInterval.Day, -7, Today)),
                            IIF(DateInterval.Weekday = 5, 'Friday = Monday This Week
                                DATEADD("d", 1 - DATEPART(DateInterval.WeekDay, Today,FirstDayOfWeek.Monday), Today),
                                    IIF(DateInterval.Weekday = 6, 'Saturday = Monday This Week
                                        DATEADD("d", 1 - DATEPART(DateInterval.WeekDay, Today,FirstDayOfWeek.Monday), Today),
                                            IIF(DateInterval.Weekday = 7, 'Sunday = Monday This Week
                                                DATEADD("d", 1 - DATEPART(DateInterval.WeekDay, Today,FirstDayOfWeek.Monday), Today),
                                                Today
                                            )
                                    )
                            )
                    )
            )
    )
)

Is what I am attempting possible? And if so where am I going wrong? Also, I am aware that comments will cause the expression to error, I have added comments to the example code to make it more readable.

So is your parameter actually a "Text" datatype? Because you can't put strings like this as the default value of a true "Date/Time" datatype parameter. — Tab Alleman
No, it's set as a Date/Time parameter. When I preview the report it returns a default value but it's incorrect. I get 18/01/2016 when I should get 11/01/2016 — Curtis Elliott
Ok, but do you understand that you can't set the default value of a Date/Time parameter to something like "Monday Last Week (11th)"? — Tab Alleman

GarethD GarethD · Accepted Answer · 2016-01-18T16:31:19

The following will work for you:

=DateAdd("ww", Datediff("ww", CDate("1900-01-01"), DateAdd("d", -5, Today)), CDate("1900-01-01"))

The premise is that it finds the number of weeks between a fixed point in time (1st Jan 1900) and 5 days ago (to get the default week to change on Friday), then adds that number of weeks to the same fixed point giving the start of the current week, or the previous week if it is Monday to Thursday.

SSRS Expression - Nested IIF, default date

1 Answers