Date Parameters : Default to most recent Monday to Sunday

[Tommy]

When running a report I want the start and end date parameters to default to the most recent Monday as the start and the next Sunday as the end date?

This is what I have working for something similar which is the first day of last month and the last day of last month...

First Day of Last Month

= Today().AddDays(CDbl(1 - Today().Day)).AddMonths(-1)

 

Last Day of Last Month

= Today().AddDays(CDbl(- Today().Day))

And this is how I did the weekly defaults in SSRS, but I cannot figure out if the same is possible with Telerik...

 

Set First Day of previous week (Monday)

=DateAdd(DateInterval.Day, -6,DateAdd(DateInterval.Day, 1-Weekday(today),Today))

 

Set Last Day of previous week (Sunday)

=DateAdd(DateInterval.Day, -0,DateAdd(DateInterval.Day, 1-Weekday(today),Today))

[Tommy]

Thanks @Dimitar for your suggestions... A user function is out of the question for me because I need to use it in various reports and also in the web designer.

But I did not know about the DayOfWeek option and so using your example I have assumed that 1 will be Monday and the following seems to work well in my environment!

Monday of current week

=AddDays(Today(),((Now().DayOfWeek - 1)*-1))

 

Sunday of current week

=AddDays(Today(),(7 - Now().DayOfWeek))

 

Monday of last week

=AddDays(AddDays(Today(),((Now().DayOfWeek - 1)*-1)),-7)

 

Sunday of last week

=AddDays(AddDays(Today(),(7 - Now().DayOfWeek)),-7)

[Dimitar]

Hello Tommy,

Thank you for the provided information.

There are 2 approaches that can be used for achieving this task.

Approach 1: Using the built-in date and time functions.

I wasn't able to come up with an elegant solution with this approach. Basically, the DayOfWeek property of the dates can be used to determine which day of the week it is today, and then by using the AddDays() function, we can subtract as many days as needed based on the current day of the week. In order to achieve this, however, you need to nest the logical IIf() function for each day of the week. For example, to get the last Monday when the current date is Tuesday or Wednesday:

= IIf(Now().DayOfWeek = 3,AddDays(Today(),-2),
IIf(Now().DayOfWeek = 2,AddDays(Today(),-1), Now()))

The same approach can be used for getting the Sunday date.

Approach 2: Using User Functions.

This is the preferred way to do this, as it provides the full framework capabilities and you can achieve a lot more things in a cleaner and more robust code. This approach involves creating a new .NET Class library project. Because the Standalone Report Designer is a WPF application built against .NET Framework 4.0, you can use only .NET Framework or .NET Standard 2.0 Class Library. More information is available in the How To Use User Defined Functions in the Stand-Alone Telerik Report Designer tool article.

For the function itself, you may one of the approaches suggested in the following links:

• C# get the current time last Monday and last Sunday
• How can I get the DateTime for the start of the week?

Please let us know if you have any other questions or need further assistance. Thank you for using Telerik Reporting!

Regards,
Dimitar
Progress Telerik