I've sometimes needed to group dates on intervals which are not those defined by Crystal. I could define the interval as a formula, but I couldn't work out how to use it in grouping by date. Here's a workaround based on an example:
You need to group Bed and Breakfast Occupations (Boardings.date) on 'Last Month', ' Previous Month' and ''Others' in the same year. That is, you don't want the current partial month.
Formulae
1) Create the interval formulae:
a) lastMonth
{Boardings.Date} < date(year(currentdate), month(currentdate),1)
and
{Boardings.Date} > dateadd("d",-1,dateadd("m",-1,date(year(currentdate), month(currentdate),1)))
b) PreviousMonth
{Boardings.Date} < dateadd("m", -1, date(year(currentdate), month(currentdate),1))
and
{Boardings.Date} >= dateadd("d",-1,dateadd("m", -2, date(year(currentdate), month(currentdate),1)))
c) YearToLastMonth
{Boardings.Date} < date(year(currentdate), month(currentdate),1)
and
{Boardings.Date} in YearToDate
Note that this covers the WHOLE period we are looking at. The grouping formula (see below) filters the individual records into the right slot, so when grouped this will show all the others.
Record Selection
Open the Selection Expert and enter the following RECORD selector formula:
{@YearToLastMonth}
This ensures we are only dealing with the records up to the end of last month.
Grouping
We then need to group our data. Create another formula which defines how the data will be displayed in the groups:
d) GroupOnPeriod
if {@PreviousMonth} then
'Previous Month ' + totext({Boardings.Date}, 'MMMM') + totext({Boardings.Date}, 'yyyy')
else
if {@lastMonth} then
'Last Month ' + totext({Boardings.Date}, 'MMMM') + totext({Boardings.Date}, 'yyyy')
else
'Others ' + totext({Boardings.Date}, 'yyyy')
I've used Boarding.date as when I change groups this will be the new value to use for formatting the date. You may need to adjust this in your own intervals.
Physical Grouping
1 Open the group Expert dialog and
2 choose {@GroupByPeriod} as the group.
3 Select 'In Original Order'
4 Close the group dialog
5 Refresh the data. And you should see the data grouped (since it is April 2008:
Last Month March 2008
Previous Month February 2008
Others 2008
The principal holds for as many intervals as you need. You can make the interval(s) as wide and as irregular as you like.
Have Fun!
|