Crystal Reports Summary of intervals within group

Report Design

Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design

Topic: Summary of intervals within group

Author	Message
ssiao Newbie Joined: 09 Nov 2008 Location: Australia Online Status: Offline Posts: 3	Topic: Summary of intervals within group Posted: 09 Nov 2008 at 9:52pm
	Hi i'm new to CR i'm using CR 9. I have a dataset with the following example data: 7/11/2008 4:04:18PM "" 14 "Feeding" 7/11/2008 4:25:34PM "" 12 "Running" 21mins 7/11/2008 4:31:36PM "" 14 "Feeding" 7/11/2008 4:45:06PM "" 12 "Running" 14mins 7/11/2008 4:51:09PM "" 14 "Feeding" 7/11/2008 5:04:27PM "" 12 "Running" 13mins 7/11/2008 5:10:28PM "" 14 "Feeding" 7/11/2008 5:21:57PM "" 12 "Running" 11mins Total Feeding Time: 59mins This data is part of a group and i want to sum the duration between the 1st "Feeding" and 1st "Running", then add the duration between the 2nd "Feeding and the 2nd "Running". How do i achieve this? Is this possible? Thanks in advance. Edited by ssiao - 10 Nov 2008 at 2:33pm
	IP Logged


rahulwalawalkar Senior Member Joined: 08 Jun 2007 Location: United Kingdom Online Status: Offline Posts: 731	Posted: 10 Nov 2008 at 3:00am
	Hi Can you please explain in context with the data what will be 1st "Feeding" and 2nd "Running", 2nd "Feeding and the 2nd "Running". Also how are you grouping ... Cheers Rahul Edited by rahulwalawalkar - 10 Nov 2008 at 3:08am
	IP Logged

lockwelle Moderator Joined: 21 Dec 2007 Online Status: Offline Posts: 4374	Posted: 10 Nov 2008 at 6:15am
	I have to agree with Rahul, how are you grouping as first question. If both lines of "Running" and "Feeding" were on one data row line you could write a formula to find the time difference and assign it to a variable that you could display later in the report and use to create your totals. Just looking at your sample data, i/we can't see how the lines are related to each other to determine a solution.
	IP Logged

ssiao Newbie Joined: 09 Nov 2008 Location: Australia Online Status: Offline Posts: 3	Posted: 10 Nov 2008 at 2:06pm
	Sorry that should be 1st Feeding and 1st Running. They are grouped by activity ID (which i have not included) and they are all separate lines of data within the group. If you have a look at my example, the paired data has the interval i expect underneath. The data is from a production line activity where a storage is feeding product onto a conveyor belt. The status "running" means the belt is running, but the storage is not feeding. Hope this makes it clearer.
	IP Logged

lockwelle Moderator Joined: 21 Dec 2007 Online Status: Offline Posts: 4374	Posted: 11 Nov 2008 at 6:10am
	OK, the grouping by ID makes sense. What I would do is still basically the same. Have a shared variable that is reset at the start of every group (it could increment a subtotalling shared variable as the same time as well). The first record would set an initial time and the subsequent time could be subtracted from it using something like datediff. In the group's subtotal you would then display this difference. I haven't tried this exact scenario, but I have done this style of keeping totals. You might have to massage the idea, but hopefully it is along the path of the solution. Hope it helps.
	IP Logged

ssiao Newbie Joined: 09 Nov 2008 Location: Australia Online Status: Offline Posts: 3	Posted: 11 Nov 2008 at 9:26pm
	how do i go through the data and get the required pairs so i can datediff them? i.e. getting the 1st feeding and the 1st running and datediff them.
	IP Logged

rahulwalawalkar Senior Member Joined: 08 Jun 2007 Location: United Kingdom Online Status: Offline Posts: 731	Posted: 12 Nov 2008 at 1:42am
	Hi Please post the sample data you have in report and how you want the report to be............. cheers Rahul
	IP Logged

lockwelle Moderator Joined: 21 Dec 2007 Online Status: Offline Posts: 4374	Posted: 12 Nov 2008 at 6:11am
	I was thinking that they are already grouped as ssiao stated that they are group by ActivityID, so they are already paired. So everytime the group changed a formula in the group header would be activated to reset the flag. Then in the details section a formula would activate that would set the flag and initialize a variable if the flag wasn't set and here is where it up to the developer, if it is always 2 paired entries, he could set a different variable if the flag is set. Then in the group footer line there would be a third formula that would datediff the 2 dates from the formula in the details section, this formula might add to the overall total time to be displayed in the report footer. Yeah lots of formulae, but it should accomplish the goal. As a note if the pairs are out of order, you could add another group that would sort by the time but doesn't 'do' anything else. Hope this helps.
	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