Grouping based on gaps in time 1

[casorter]

I have a list of history records that i need to use to produce a production report. The pertinant fields are EmployeeNumber, DateTimeStamp, ActivityType. I need to look through the data generated by each employee to determine their production rate but not include gaps in time when they were on break or lunch. i have created a counter that increments each time the previous record is more than 3 minutes (this is a variable, the threshould could be 1 minute or 10 minutes.. whatever) from the current record.. this works great to produce a set of 'groups' through out the day. what i need to do is to create a group based on this calculation. just to give a scope of this there are about 13000 records a day and it breaks out into 14 or 15 groups throughout the day. i need to break out these 14 or 15 groups and do stuff with them, which i can do if i can get the groups.. anyway enough rambling.

btw i have crystal reports 7.0 pro upgrade

don't hate me i think we have a higher rev around the office somewhere if i need that .. but is there a way in 7.0?

thanks for the help
Mark

 

[synapsevampire]

Sorry, the text description isn't exactly crystalline.

Consider posting example data and expected output. You might even throw in the database type.

I see a 3 minute variable, yet only 14-15 groups for 13,000 records, and the math doesn't hold for this befuddled old mind as that would mean about 45 minutes worth of data.

-k

 

[casorter]

here are a couple of lines from the report

13 0 5.78 148 11/12/2003 3:00:53pm 2
13 0 4.67 148 11/12/2003 3:00:58pm 2
14 14 461.80 148 11/12/2003 3:07:41pm 2
14 0 1.15 148 11/12/2003 3:07:42pm 2
14 0 5.78 148 11/12/2003 3:07:47pm 2


the first column is my group number
the second is the trigger that looks for the 3rd column to be greater than 100
the 4th is the employee number
the 5th is the timedate stamp
the 6th is the activity type

if i try and add a group the only choices i get are database fields
what i am going to do is to count the number of records in each group and divide the elapsed time from the start to the end of the group

let me know if you need anything else.. can i post an 800 number for you to call??? lol just let me know and thanks for the quick response

mark

 

[casorter]

btw.. using SQL 7 odbc connection

 

[casorter]

http://support.crystaldecisions.com/library/kbase/articles/c2006972.asp

i found this WP but it just says

3) Insert your formula onto the detail section.

4) Insert a group based on your formula.

i can't find how to insert this group.. when i go to the insert group tab all i get is a list of the database fields... like i said before.

 

[lbass]

You won't be able to group on the "gap" value, since the value is dependent upon its position within the series of records. Any time you use the "previous" or "next" function this will be the case. This is why the formula is not appearing in the drop-down for grouping.

The issue then becomes whether you can do the calculations you want to do without grouping, but you haven't shared what those calculations might be.

-LB

 

[casorter]

it is a production reporting system. i need to calculate the rate at which the operator is going but not include any large (> 3 mins or so) gaps in time that would indicate that they are away from the station. most of the transactions are in the 2-8 second range. i have a list of all the transactions (13000 for a day) and basically need to get the elapsed time of production without the 'idle time' (the gaps) being included. maybe there is another way to skin this cat....

 

[lbass]

You could use a variable to sum the elapsed times, excluding the larger values:

{@reset} for the employee ID group header:
whileprintingrecords;
numbervar diff := 0;
numbervar cumuldiff := 0;

{@elapsedtime} for the details section:
whileprintingrecords;
numbervar diff := datediff("s", previous({table.datetime}),{table.datetime});
numbervar cumuldiff;

if {table.emplID} <> previous({table.emplID}) and
diff < {?seconds} then //where {?seconds} is a number parameter
cumuldiff := cumuldiff + diff else
cumuldiff := cumuldiff;

{@display} for employeeID group footer:
whileprintingrecords;
numbervar cumuldiff;

-LB