Setting custom date ranges in Crystal

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!



Jason Buss

Jason is a senior application developer with Customer FX.

Submit a Comment

Your email address will not be published. Required fields are marked *

Subscribe To Our Newsletter

Join our mailing list to receive the latest Infor CRM (Saleslogix) and Creatio (bpm'online) news and product updates!

You have Successfully Subscribed!