Crystal Reports: Leap years in Crystal Reports

	Resources
	Best sellers: cView Report Analyzer cViewSERVER ReCrystallize

	Articles: Administration Advanced Basic Crystal eNL Database Financial Problems Solved Books: CR Books Database Books Developer Books
	Tools: Analyzers Bestsellers CR Schedulers CR UFLs CR Viewers DataBase Tools Graphics International Mail UFLs ReCrystallizePro
	Add'l: About us Contact Us cViewSUITE Ppt Support
	CrystalReports on Steroids

Crystal Reports: Leap Years

The sales report had been working for several years. But this month it failed with an error. The initial investigation revealed a problem and solution that we would like to share with you.

The report asked for the end date of the sales period. This was usually the last day of the month, so in March we entered 29-Feb-2004. This happens to be leap year day.

There was a formula in the report to calculate the matching day of the same period last year:

Date(Year({?EndDate})-1,Month({?End Date}), Day({?End Date}))

So this formula was failing because it was trying to calculate 29-Feb-2003. It would quite easy to modify it to check for Feb-29 and use Feb 28 for the prior year.

But what about next year? Next year we will be running the report for 28-Feb-2005 and would like a matching date for the prior year of 29-Feb-2004. The modification we planned would not work next year.

The solution is slightly more complicated, but is based on the fact that we are always selecting an end date of the last day of the month. So advance one day to the 1^st of the next month, move back a year, and then back one day.

Date(Year({?EndDate}+1)-1, Month({?EndDate}+1), Day({?EndDate}+1)) -1

Problem with a formula because 2008 is a leap year

In March, our client phoned us in a panic. Their end of month report was failing with an error. The report had been happily running month after month for several years now with no changes. So what was the problem with the February 2008 report? The report had a start date and end date for the month as parameters. It worked fine last year and in January this year, but not for February.

You might immediately identify the cause. That month, February, this year, has 29 days, and this only happens once every 4 years. Yes, we need to watch for leap year problems.

The formula causing the problem in this report was to calculate the month end date of the prior year. The formula said:

Date(Year({?End Date}) -1 ,month({?End Date}), Day({?End Date})

So if February finishes on the 29^th this year, then the formula for last year calculates 29-Feb-2007, which is invalid and raises an error. We could modify the report for this month, but we get the opposite problem next year when we want to do the calculation for this year.

The new formula is now

Date(Year({?End Date}+1)-1 ,month({?End Date}+1), Day({?End Date}+1) -1

This will work every year because it advances forward one day to the first of the next month, back one year and then back one day. The first of the month does have any leap year problems.

Followup

Of course, we know that with computerized systems things sometimes go inexplicably wrong. The concept of "set it and forget it" is rarely something we should use as a work strategy. Various traps lie in waiting.

This means you need to follow up. Make an Outlook appointment to check on the leap year settings a few weeks before they matter. Most likely, everything will be OK. But if it's not, then you have ample time to fix whatever went wrong.

This article is copyrighted by Crystalkeen, Mindconnection, and Chelsea Technologies Ltd. It may be freely copied and distributed as long as the original copyright is displayed and no modifications are made to this material. Extracts are permitted. The names Crystal Reports and Seagate Info are trademarks owned by Business Objects.