Dynamically group records by date

[Former Member]

I am attempting to create a report that will dynamically group records into a set number of date buckets. This is similar to grouping records by a date field and setting the days, weeks, months, etc property but instead of grouping by a set time span I want to a specific number of date groups regardless of date span. So say i have records where the first date is today at 1am and the last record is today at 9 pm. I want the data grouped into 10 groups and the time calculated for that group based on total time span / 10. The first group would be 1AM to 3AM, the second would group 3AM to 5AM, etc.. The reason I am doing this is for a chart that displays record counts over time but the overall timespan will never be known until runtime. Setting the chart for hourly or weekly doesn't work because if the user runs the report over a year the dates will be illegible.

Thanks in advance!

[Former Member · Accepted solution]

Well this SHOULD be easy. But leave it to CR make not...

You can start by finding the minimum & maximum dates within your range:

Local DateTimeVar MinDate;
MinDate := Minimum({Table.DateField})

and

Local DateTimeVar MaxDate;
MaxDate := Maximum({Person.ModifiedDate})

Then figure out what the the interval would be if the span is broken down into 10 equal parts"

DateDiff("n", {@MinDate}, {@MaxDate}) / 10

From there just use a formula to segregate each records into the appropriate groups:

EvaluateAfter({@Interval});

IF {Table.DateField} >= {@MinDate} 
    AND {Table.DateField} <= DateAdd("n",{@Interval}, {@MinDate}) THEN 1 ELSE

IF {Table.DateField} > DateAdd("n",{@Interval}, {@MinDate})
    AND {Table.DateField} <= DateAdd("n",{@Interval} * 2, {@MinDate}) THEN 2 ELSE

IF {Table.DateField} > DateAdd("n",{@Interval} * 2, {@MinDate})
    AND {Table.DateField} <= DateAdd("n",{@Interval} * 3, {@MinDate}) THEN 3 ELSE

IF{Table.DateField} > DateAdd("n",{@Interval} * 3, {@MinDate})
    AND{Table.DateField} <= DateAdd("n",{@Interval} * 4, {@MinDate}) THEN 4 ELSE

IF {Table.DateField} > DateAdd("n",{@Interval} * 4, {@MinDate})
    AND {Table.DateField} <= DateAdd("n",{@Interval} * 5, {@MinDate}) THEN 5 ELSE

IF {Table.DateField} > DateAdd("n",{@Interval} * 5, {@MinDate})
    AND {Table.DateField} <= DateAdd("n",{@Interval} * 6, {@MinDate}) THEN 6 ELSE

IF {Table.DateField} > DateAdd("n",{@Interval} * 6, {@MinDate})
    AND {Table.DateField} <= DateAdd("n",{@Interval} * 7, {@MinDate}) THEN 7 ELSE

IF {Table.DateField} > DateAdd("n",{@Interval} * 7, {@MinDate})
    AND{Table.DateField} <= DateAdd("n",{@Interval} * 8, {@MinDate}) THEN 8 ELSE

IF {Table.DateField} > DateAdd("n",{@Interval} * 8, {@MinDate})
    AND {Table.DateField} <= DateAdd("n",{@Interval} * 9, {@MinDate}) THEN 9 ELSE

IF {Table.DateField} > DateAdd("n",{@Interval} * 9, {@MinDate})
    AND {Table.DateField}  <= {@MaxDate} THEN 10

This is where CR drops the ball... IMHO... it WON'T allow you to to group by a formula field that uses an aggregate in the formula (in this case Minimum & Maximum)... It will however allow to to graph on it, which I assume is what you are actually trying to do. If anyone knows a way to work around the grouping issue, I'd love to know it myself.

HTH,

Jason

[Former Member · Accepted solution]

Now that I think about it...

If you use separate Begin Date & End Date parameters, you could do away with the MinDate & MaxDate formulas, using the parameters in their place. This would get rid of the aggregates in the formula...

[Former Member]

Ok, I'm blind. Now I see the "n". I think the problem I'm having is that when CR populates the graph it is printing only the date of the DateAdd function instead of the full date and time value so if I have records that don't span more than one day they will all be grouped into one container when the chart is rendered. I need to figure out how to get CR to print the whole date and time value instead of just the date in order to get the grouping right.

Thanks for all of your help!

[Former Member]

OK I've got it working with the following grouping formula:

EvaluateAfter({@Interval});
 
IF {Log.NormalDateMin} >= {@MinDate} AND {Log.NormalDateMin} <= DateAdd("n",{@Interval}, {@MinDate}) THEN {@MinDate} ELSE
 
IF {Log.NormalDateMin} > DateAdd("n",{@Interval}, {@MinDate}) AND {Log.NormalDateMin} <= DateAdd("n",{@Interval} * 2, {@MinDate}) THEN DateAdd("n",{@Interval}, {@MinDate})ELSE
 
IF {Log.NormalDateMin} > DateAdd("n",{@Interval} * 2, {@MinDate}) AND {Log.NormalDateMin} <= DateAdd("n",{@Interval} * 3, {@MinDate}) THEN DateAdd("n",{@Interval} * 2, {@MinDate}) ELSE
 
IF{Log.NormalDateMin} > DateAdd("n",{@Interval} * 3, {@MinDate}) AND{Log.NormalDateMin} <= DateAdd("n",{@Interval} * 4, {@MinDate}) THEN DateAdd("n",{@Interval} * 3, {@MinDate}) ELSE
 
IF {Log.NormalDateMin} > DateAdd("n",{@Interval} * 4, {@MinDate}) AND {Log.NormalDateMin} <= DateAdd("n",{@Interval} * 5, {@MinDate}) THEN DateAdd("n",{@Interval} * 4, {@MinDate}) ELSE
 
IF {Log.NormalDateMin} > DateAdd("n",{@Interval} * 5, {@MinDate}) AND {Log.NormalDateMin} <= DateAdd("n",{@Interval} * 6, {@MinDate}) THEN DateAdd("n",{@Interval} * 5, {@MinDate}) ELSE
 
IF {Log.NormalDateMin} > DateAdd("n",{@Interval} * 6, {@MinDate}) AND {Log.NormalDateMin} <= DateAdd("n",{@Interval} * 7, {@MinDate}) THEN DateAdd("n",{@Interval} * 6, {@MinDate}) ELSE
 
IF {Log.NormalDateMin} > DateAdd("n",{@Interval} * 7, {@MinDate}) AND{Log.NormalDateMin} <= DateAdd("n",{@Interval} * 8, {@MinDate}) THEN DateAdd("n",{@Interval} * 7, {@MinDate}) ELSE
 
IF {Log.NormalDateMin} > DateAdd("n",{@Interval} * 8, {@MinDate}) AND {Log.NormalDateMin} <= DateAdd("n",{@Interval} * 9, {@MinDate}) THEN DateAdd("n",{@Interval} * 8, {@MinDate}) ELSE
 
IF {Log.NormalDateMin} > DateAdd("n",{@Interval} * 9, {@MinDate}) AND {Log.NormalDateMin}  <= {@MaxDate} THEN {@MaxDate}

The only problem now is that it appears the resolution on this is limited to days. If I have someone run the report for a single day I want it to display values at hour level grouping. I'm guessing this has something to do with using the "n" for interval type. I've done some looking and can't find any reference to "n" or it's use. I know the other interval types and have tried them but I seem to have strange results.

Thanks for your help! I'm much further along now than I was this morning!

[Former Member]

"n" is minutes.

Here is the list of interval types from CR's online help for the DateDiff function:

yyyy 	 Year 
q	 Quarter
m	 Month 
y	 Day of year 
d	 Day (both "y" and "d" find the difference in days) 
w	 Number of weeks between startDateTime and endDateTime 
ww	 Number of firstDayOfWeek's between startDateTime and endDateTime 
h	 Hour 
n	 Minute 
s	 Second

It shouldn't be limited to days... The formula I posted...

I tested it against a range of date time values that span close to 9 years.

(the ModifiedDate from the Person.Contacts table of the sample Adventureworks database)

HTH,

Jason

[Former Member]

This is getting me much closer to my goal however when I implement it as described everything gets grouped into either group 1 or group 10. I think the dateadd functions may be faltering.