With George on deployment with the Minnesota National Guard, I’ll be occasionally posting on various reporting topics.
Recently, I had to develop a report which was to return data within a particular date range. Specifically, a user would need to select a date and the report would then return data from a range of Monday of the previous week to Friday of the current week. I was able to accomplish this fairly easily using Global and Local variables and a couple of different date functions.
Basically, I needed to get the day of the week for the date selected, and then add or subtract days as necessary to come up with an appropriate number to use with a DateAdd function. All this is done in the record selection formula for the report:
Global DateVar sStartDate;
Global DateVar sEndDate;
Local NumberVar nDayOfWeek := DayOfWeek({?Date}); //Determine the Day of the week selected.
Local NumberVar nAddStart;
Local NumberVar nAddEnd;
//Determining the number of days to add to the Start Date
If nDayOfWeek = 1 Then
nAddStart := -6;
If nDayOfWeek = 2 Then
nAddStart := -7;
If nDayOfWeek = 3 Then
nAddStart := -8;
If nDayOfWeek = 4 Then
nAddStart := -9;
If nDayOfWeek = 5 Then
nAddStart := -10;
If nDayOfWeek = 6 Then
nAddStart := -11;
If nDayOfWeek = 7 Then
nAddStart := -12;
//Determining the number of days to add to the End Date
//(Note, Start and End date have to be done in separate If..Then blocks, otherwise it will not work)
If nDayOfWeek = 1 Then
nAddEnd := 5;
If nDayOfWeek = 2 Then
nAddEnd := 4;
If nDayOfWeek = 3 Then
nAddEnd := 3;
If nDayOfWeek = 4 Then
nAddEnd := 2;
If nDayOfWeek = 5 Then
nAddEnd := 1;
If nDayOfWeek = 6 Then
nAddEnd := 0;
If nDayOfWeek = 7 Then
nAddEnd := -1;
//With the number of days to add/subtract to both dates determined, I use the DateAdd function to return the correct
//Start and End Dates
sStartDate := Date(Year({?Date}), Month({?Date}), Day(DateAdd('d', nAddStart, {?Date})));
sEndDate := Date(Year({?Date}), Month({?Date}), Day(DateAdd('d', nAddEnd, {?Date})));
//Finally, I use the new dates in the selection criteria.
{History.completeddate} >= sStartDate and
{History.completeddate} <= sEndDate
|
Pretty straight-forward, I think. Depending on your situation, it would just be a matter of figuring out your range and what values to add or subtract to the current date value.
I hope you find this helpful… Thanks for reading!
