Login / Register  search  syndication  about

          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. 

I hope you find this helpful... Thanks for reading!


What's This?
Bookmark and Share

About Jason Buss

   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

No Comments

Leave a Comment

All contents Copyright © 2015 Customer FX Corporation
Customer FX Corporation
2324 University Avenue West, Suite 115
Saint Paul, Minnesota 55114
Tel: 800.728.5783

  Follow @CustomerFX on twitter
Follow the best news, tips, and articles
  Subscribe to Customer FX on youtube
Watch SalesLogix tutorial videos from Customer FX
Login / Register