Monday, July 25, 2011

Crystal Reports : Consolidating Buckets into One Formula

Let us suppose further that we are creating a crosstab object to show us where the INVOICES land in date buckets. You would want to specify each ROW of the crosstab as your summary (i.e. COUNT or SUM). You would need, then, to specify your COLUMNS within the crosstab as a single, unified function that will "kick out" the correct "bucket name" based on INVOICES.DATE.

We will call our unified crystal function DATEBUCKET and the code would look like this:

IF {INVOICES.DATE} IN CURRENTDATE TO CURRENTDATE - 30 THEN '0-30 days' ELSE
IF {INVOICES.DATE} IN CURRENTDATE - 31 TO CURRENTDATE - 60 THEN '31-60 days' ELSE
IF {INVOICES.DATE} IN CURRENTDATE - 61 TO CURRENTDATE - 90 THEN '61-90 days' ELSE
IF {INVOICES.DATE} < CURRENTDATE - 91 THEN '91+ days'

This code consolidates our date buckets into one unified crystal function that can be added as a Group or a Crosstab column. Obvoiusly, the bucket names are "kicked out" as described above. These will ultimately be the names of your groups.

Again, watch your math to guard against double bucketing!