Tips and Tricks
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Tips and Tricks
Message Icon Topic: Group Dates on user-defined intervals Post Reply Post New Topic
Author Message
wislon32
Newbie
Newbie


Joined: 18 Apr 2008
Location: United Kingdom
Online Status: Offline
Posts: 4
Quote wislon32 Replybullet Topic: Group Dates on user-defined intervals
    Posted: 18 Apr 2008 at 8:18am
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!
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.