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!

Jason


What's This?
  
Bookmark and Share

About Jason Buss

   Jason is a senior application developer with Customer FX.



Related Content
   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
 
   Evaluating strings in C# Code snippets
When checking for two (or more) different values for a property in a code snippet, you can't simply l
Posted on Dec 13, 2013 by Jason Buss to Jason Buss' Blog
 
   Introducing the SalesLogix Mobile Developer Toolkit
I am very pleased to announce a new & free open source tool from Customer FX named the SalesLogix Mob
Posted on Nov 13, 2012 by Ryan Farley to Ryan Farley's Blog
 
Comments

No Comments

Leave a Comment

(required)  
(optional)
(required)  
Add
All contents Copyright © 2014 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