Monday, July 25, 2011

Crystal Reports : The Perils of Double Bucketing Continued

Double bucketing; it is a scourge upon the hapless Crystal Reporter!

Another technique to use when bucketing is to force Crystal to use mutually exclusive "automagical" functions. A good example of this is to use the YEAR() function to group any date by it's corresponding year.

So, if we break out INVOICES.DATE by grouping by YEAR(INVOICES.DATE), we can be assured that any one invoice can only belong to one specific year. Therefore we avoid double bucketing.

But the needs of the client are paramount; some businesses like "strange logic" to be applied to their buckets, depending on usage requirements.

An example: a client needs all invoices cut in December 2009 to belong to year 2010, yet anything previous should belong to year 2009.

So:

2010 Bucket:
YEAR(INVOICES.DATE) = 2010 OR (YEAR(INVOICES.DATE) = 2009 AND MONTH(INVOICES.DATE) = 12)

This code above includes all invoices with a year of 2010 AND also the ones from December 2009.

So we can see that sometimes the "automagical" crystal date function do not always meet the expectations of the client.

Know your manual date math! And beware the perils of double bucketing!

No comments: