Date groups that don’t do what you expect

Monday 15 April 2013 @ 10:03 pm

I received this question from a customer last week. Since it isn’t the first time I thought I would mention it here. He noticed that:

1) The group tree missing many dates that have data within the range.

2) The group tree including dates that are outside the range.

The answer is pretty simple. In Crystal, whenever you group on a date field, the default setting for the new date groups is “for each week”. Don’t ask me why. And you don’t even see the setting while you are adding the group, unless you go into Group Options.  Now I am sure that lots of reports need to group by week.  But most reports I do group either by day or by month. Nevertheless, CR has defaulted to weeks for a long time.  If you want to change the group to another period you have to remember to go into the group options for that group.

Another reason this causes confusion is that (unlike a month grouping) you can easily confuse weekly grouping with daily grouping that is missing dates. The reason for missing dates, of course, is that you are only going to see the Sunday’s in the group tree. So you will be missing six out of every seven days.

And because CR weekly groups use calendar weeks, the group name for the week will always be the Sunday at the beginning of that week, which explains issue number 2 above. Say you run a report for April 2013 and group by the date field. Crystal defaults to grouping by calendar week and so the first week of April will be labeled as Sunday 3/31/2013.  Even though that date doesn’t exist in the report, that week does exist, so the label will be that Sunday.

So if you get a strange group tree, check your group options.  And, on a slightly related note, if you ever want to have a weekly group that runs Monday to Sunday or something other than calendar week, you read the solution I wrote up a few years ago.

Leave a Reply

Recrystallize Pro

Crystal Reports Server