Date Parameters : Default to most recent Monday to Sunday

2 Answers 33 Views
Report Parameters
Tommy
Top achievements
Rank 1
Iron
Iron
Iron
Tommy asked on 26 Jul 2021, 04:38 AM

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

2 Answers, 1 is accepted

Sort by
0
Accepted
Tommy
Top achievements
Rank 1
Iron
Iron
Iron
answered on 29 Jul 2021, 05:08 AM

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)

0
Dimitar
Telerik team
answered on 28 Jul 2021, 01:02 PM

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:

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

Regards,
Dimitar
Progress Telerik

Brand new Telerik Reporting course in Virtual Classroom - the free self-paced technical training that gets you up to speed with Telerik and Kendo UI products. Check it out at https://learn.telerik.com/.
Tags
Report Parameters
Asked by
Tommy
Top achievements
Rank 1
Iron
Iron
Iron
Answers by
Tommy
Top achievements
Rank 1
Iron
Iron
Iron
Dimitar
Telerik team
Share this question
or