Crystal Reports Grouping by sets of consecutive time

Technical Questions

Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions

Topic: Grouping by sets of consecutive time

Author	Message
citizencia Newbie Joined: 28 Dec 2007 Location: United States Online Status: Offline Posts: 2	Topic: Grouping by sets of consecutive time Posted: 28 Dec 2007 at 7:37am
	How do I group by a set of consecutive times. For instance, I have data that has a duration of time that I am reporting on. The data is grouped by date of entry and is sorted by time of day that the event happened in ascending order. Example: A series of events happened from 8:00-8:15, 8:15-8:30, 8:30-8:45. A second set of events happened at 10:00-10:20, 10:20-10:25, and the last set of events happened at 3:00-3:15, 3:15-3:20. Is it possible to group those events together without presetting a time range for which the groups will fall into.
	IP Logged


Lugh Senior Member Joined: 14 Nov 2007 Online Status: Offline Posts: 377	Posted: 28 Dec 2007 at 10:44am
	Is the breakpoint between the groups when the times stop being continguous? I think that's what you're saying, but I'm not positive. This is going to be tricky. The Next() and Previous() functions are going to come into play. Unfortunately, I don't think you can group on a formula that references those functions. So, I could make the report look like it had groups like that. I could use the formula to toggle the appearance of a second details section, that could be formatted to look like a group footer and/or group header. I could even, possibly, use a running total (reset by the same toggling formula) to simulate some group summaries. But, I think that's about as close as I could get. Depending on your data source, you might look into a way to add a label there. It would take some tricky SQL (and probably T-SQL/PL-SQL/whatever). But if you could make the query, say, return 1 until there is a break in the time, then return 2 until the next break, and so on, that would work very nicely.
	IP Logged

citizencia Newbie Joined: 28 Dec 2007 Location: United States Online Status: Offline Posts: 2	Posted: 28 Dec 2007 at 2:53pm
	I'm trying to use a DateDiff function to do this, could you provide me with an example of a possible If function that would work to accomplish this?
	IP Logged

tconway Newbie Joined: 25 Dec 2007 Location: United States Online Status: Offline Posts: 20	Posted: 28 Dec 2007 at 8:14pm
	Possibly something like this... In a formula field... NumberVar Evnt; If Previous({Table.EndTime}) <> {Table.StartTime} then Evnt := Evnt + 1; Evnt If you have your StartTime and EndTime in the Details Section, place this new formula field in the same Details section. The number will increment when the Previous End Time does not equal the current Start Time. This formula isn't really necessary for your report but will help you visualize how it increments on each change of Event Group. To use it though, you could for example do Running Totals based on this number changing method. Say you have a Numeric field to sum based on the event grouping. Put the Numeric field in details, right click the field and choose Insert Running total. - Type of Summary is Sum - Evaluate on each Record - Reset value based on Formula, open the formula X2 and enter Previous({Table.EndTime}) <> {Table.StartTime} //essentially the same formula or principle as above... Save and close. It will total the by the Group Event. Also, be sure in the Options (or Report Options) to set NULL values to default to get the first value of the formula field to return 0. The problem is, what if an Event that is not part of the Group happens to have the correct comparision that will NOT increment the next value, but they are really two different events. Is there no other field with an Identifier for each group of events? Seems like there would be. Not sure how you need to use it, but hope this is helpful. Edited by tconway - 28 Dec 2007 at 8:53pm
	IP Logged

Lugh Senior Member Joined: 14 Nov 2007 Online Status: Offline Posts: 377	Posted: 02 Jan 2008 at 7:55am
	*Originally posted by citizencia* I'm trying to use a DateDiff function to do this, could you provide me with an example of a possible If function that would work to accomplish this? When you say "this," which "this" are you referring to? I offered two very different solutions. The first solution (creating fake grouping) depends on a formula very like what tconway posted. The second solution (using SQL tricks to create grouping) depends on what database you are using, though I could probably throw out a mostly generic solution that you could tweak. I would very much like to know which solution you want, though, as posting either solution is going to take some time and effort. If you want the SQL solution, could you post the relevant parts of your schema (i.e., primary key field, and how the times are actually stored)? That will help me create a solution that is closer to what you actually need.
	IP Logged

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