#### George Jensen's Blog

George Jensen on Reporting, Business Intelligence, Crystal Reports, Visual Analyzer, and more.

#### 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.

 What's This?

 Jason is a senior application developer with Customer FX.

Related Content
 Better Code Organization for Infor CRM (Saleslogix) Mobile Customizations When customizing the Infor CRM (Saleslogix) Mobile client, it's common for the ApplicationModule to g Posted on May 05, 2015 by Ryan Farley to Ryan Farley's Blog Index of Posts for Starting with Infor CRM (Saleslogix) Development The blogs on customerfx.com go all the way back to 2003. There's years and years worth of information Posted on Dec 30, 2014 by Ryan Farley to Ryan Farley's Blog Confirming Actions With an "Are you sure?" Prompt in Infor CRM (Saleslogix) I'm not a huge fan of bugging end users. I figure, if an end user clicked something, he or she meant Posted on Nov 18, 2014 by Ryan Farley to Ryan Farley's Blog Using a Test Plan So you have approved the Statement of Work (SOW) for some modifications and are awaiting the delivery of Posted on Mar 13, 2014 by George Jensen to George Jensen's Blog Creating a Saleslogix Ticket Pretty Prefix and Suffix in External Applications Some entities in SalesLogix, such as Tickets, use a "pretty key" as an easier to use value to r Posted on Feb 28, 2014 by Ryan Farley to Ryan Farley's Blog

(required)
(optional)
(required)