Crystal Reports Tips - Changing WeekToDateFromSun For Mon-Sun Schedule

One of the things I like most about MS CRM is that it has challenged me to learn a lot of other skills and applications that I might not have learned otherwise. My entree into Microsoft CRM was due to my experience with the web, marketing, and business process improvement. Since diving in, however, I have become the jack of all trades (though master of none) in things like ASP.NET (I'm working to learn C#), SQL Administration, Outlook and IE support, and -- the topic of today's blog -- Crystal Reports.

I must say that I have enjoyed learning to use Crystal and milking the data out of CRM in all sorts of useful ways. I have a hard time sitting and reading how-to books, though I've done some of that with Crystal. I am most comfortable learning how to use applications by experimentation, and scouring the internet for tips and tricks.

So, here's a tip I would like to pass on to someone else who is scouring the internet to try to figure this out in Crystal.

CHANGING CRYSTAL'S WeekToDateFromSun TO FIT A MONDAY - SUNDAY SCHEDULE

I had to build a series of reports for a client of mine whose reporting week starts on Monday and runs through Sunday. If they want to look at a report for an activity and select the records for Week-To-Date, Crystal 9 has a nice built in way to do this. You simply click on Report > Select Expert and add a new selection criteria. You select the field you want to check against and then an operator like "is in the period" and choose the WeekToDateFromSun option. This is great. There is also a LastWeek option.

These work very well for standard Sunday through Saturday weeks. But the dilemma I faced was that my client's week is Monday through Sunday. So here's the code you need to select records based on this type of criteria. Again, in Crystal, click on Report, but this time click Selection Formulas > Record. In the window that opens up, add this code:

if DayOfWeek(currentDate) = 1
then
{activity.date} in
CurrentDate - Dayofweek(CurrentDate) - 5 to
CurrentDate - Dayofweek(CurrentDate) + 1

Else
{activity.date} in
CurrentDate - Dayofweek(CurrentDate)+ 2 to
CurrentDate - Dayofweek(CurrentDate) + 8

and

After the word "and" you can of course add any other selection criteria you desire, or remove the word altogether. Now, here's the code to view Last Week's activities, based on a Monday through Sunday work week:

if DayOfWeek(currentDate) = 1
then
{activity.date} in
CurrentDate - Dayofweek(CurrentDate) - 12 to
CurrentDate - Dayofweek(CurrentDate) - 6

Else
{activity.date} in
CurrentDate - Dayofweek(CurrentDate)- 5 to
CurrentDate - Dayofweek(CurrentDate) + 1

and

There you go. Hope you find this helpful in your next Microsoft CRM project -- or any project using Crystal Reports.