Working with rolling dates

Often we encounter reporting requirements where we need to prompt the user for a starting or ending date and then display columns of information for a number of previous periods. I encountered this when working with a health care organization in Pennsylvania. They wanted to show revenue by month for the previous 12 months based on a date provided by a parameter field. I could not use Crystal's cross tabulation expert because many of the other calculations and accumulations were just too complex.

I used Crystal array capability to solve the problem. The following formula simply provides the correct column heading for the second column in the report. The second column could be any of the twelve months depending on the value of the parameter field.

//labmon2

stringVar array Months := ["Jan","Feb","Mar","Apr","May","Jun", "Jul","Aug","Sep","Oct","Nov","Dec"];
numberVar mon := Month({?High Date})-1;
numberVar monF := IF mon <1 then 12+ mon;
numberVar yearF := IF mon <1 then Year({?High Date}) -1
else Year({?High Date});
Months[monF]+" "+ToText (yearF,0,"")


In the first line, I create a 12-position array of the month abbreviations.

In the second line I create a memory variable, mon, which is equal to the month number of the parameter field's date minus 1. I am subtracting 1 because it is in the second column. For column 3 I would subtract 2, etc.

In the third line I test to see if I have crossed the boundary between years. If High Date were equal to January, mon would be equal to 0. In that case monF would be equal to 12 + 0 = 12 or December.

In the fourth line I do the same to find the correct year.

The fifth line puts it all together as a text string.

I used the same logic to calculate the proper amounts for each group footer line.

Note that this will work for any date.

Howard Hammerman, Ph.D.
Hammerman Associates, Inc.
http://www.hammerman.com
800-783-2269
Hammerman Associates, Inc. provide Crystal Reports training,
consulting, course material, utilities and software. Consultants are available throughout North America for short or long-term assignments.