Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Business Objects: Crystal Reports 4 Other topics FAQ

Common Formulas

Working with rolling dates by HowardHammerman
Posted: 28 Oct 01

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.


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

Back to Business Objects: Crystal Reports 4 Other topics FAQ Index
Back to Business Objects: Crystal Reports 4 Other topics Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close