Calculate Hold Time

[aporras]

The table (which is a part of a workflow database and captures the history for items going through workflow) has the following structure (I am only listing relevant fields): UserID, ItemID, Reason (can be "Held", "Viewed", "Cancelled", "Routed In", "Routed Out&quot, InstanceTime (Time Stamp).

Data Example:

UserID ItemID Reason InstanceTime

UserA 1234 Routed In 10:00:30
UserA 1234 Held 10:01:00
UserB 1234 Viewed 10:02:00
UserB 1234 Held 10:05:00
UserA 1234 Viewed 10:10:00
UserC 1234 Held 10:10:05
UserA 1234 Cancelled 10:10:15
UserA 1234 Held 10:11:10
UserA 1234 Cancelled 10:11:25
UserA 1234 Routed Out 10:12:00

The report needs to calculate time on hold (difference between reason "Held" and whatever Reason follows "Held", ordered by ItemID and InstanceTime by item and charge it to the user who put the item on hold (but hold time cannot be calculated until the item has been taken off hold). For the data example, total hold time for UserA should be 1 minutes 15 seconds (1 minute + 15 seconds), for UserB 5 minutes, and for UserC 10 seconds. The report cannot be grouped by UserID as you would lose the actions UserB and UserC performed.

I have received a suggestion to create a multi-dimensional array but I am not sure whether that is really the way to go or how to create one (the report has to be created in Crystal Reports 8.5, Crystal Analysis is not an option).

Thank you very much in advance for your help.

 

[storyboi]

create an appropriate number of hidden feild on the report (isheldA, isheldB, isheldC etc) set this value to 1 if the reason field is "held" and if userX=X then you will be able to group by these hidden fields if in the summary section of the report.


While this is an easy solution it is really not the correct one because you may have 500 users, but if you only have a couple this kludge will work.

 

[aporras]

Unfortunalety, it is a lot more likely to have a high number of users than just a couple. But thank you for your input.

 

[Naith]

To accomplish this, you'd want to think about grouping by Item, by User. This would mess up your chronological order to some extent, (although if this was an issue, you could replicate the data with the Item by User grouping in a subreport, thus maintaining the chronological order in the main report).

Once you have the two groups, you can use a running total to sum up the periods where Reason = 'Held' and return the subtotal for each user into the User group footer.

Naith

 

[synapsevampire]

I don't think that CR supports multidimensional arrays, but I think 2 arrays will handle this.

Here's some theory (will need tweaking):

whileprintingrecords;
global string UsersArray Array;
global string HoldTimeArray Array;
Boolean CurrentUser:=false;
numbervar x;

If {MyTable.Reason} = held and next({MyTable.ItemID} = {MyTable.ItemID} then

For x := 1 to ubound(UsersArray) do(
if UsersArray[x] = {MyTable.UserID} then
HoldTimeArray[x] := HoldTimeArray[x]+next({MyTable.InstanceTime})-{MyTable.InstanceTime};
CurrentUser = true
)

if currentuser <> true then
UsersArray[(ubound(UsersArray)+1)] = {MyTable.UserID};
HoldTimeArray[(ubound(UsersArray)+1)] := next({MyTable.InstanceTime})-{MyTable.InstanceTime};

This would build 2 arrays, one with the users, and one with the times, the relationship being that they're in the same indexed location within the array.

Now you'll need to determine when and how to display and reset the arrays, but this is the basics, hope it helps.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[aporras]

I have tweaked the suggested code as follows:

whileprintingrecords;
global StringVar Array UsersArray;
global NumberVar Array HoldTimeArray;
BooleanVar CurrentUser:=false;
numbervar x;

If {q_hist.REASON} = &quot;Held&quot; and next({q_hist.ITEM_ID}) = {q_hist.ITEM_ID} then

For x := 1 to ubound(UsersArray) do (
if UsersArray[x] = {q_hist.USER_ID} then
HoldTimeArray[x] := HoldTimeArray[x]+ next({q_hist.INSTANCE_TIME})-{q_hist.INSTANCE_TIME};
CurrentUser = true
)
else
if CurrentUser <> true then
UsersArray[(ubound(UsersArray)+1)] = {q_hist.USER_ID};
HoldTimeArray[(ubound(UsersArray)+1)] := next({q_hist.INSTANCE_TIME})-{q_hist.INSTANCE_TIME};

When I check it for errors, I get &quot;No errors found&quot;. However, when I place the formula in the report, get the following error message: &quot;A subscript must be between 1 and the size of the array.&quot;

Any ideas?

Again, thanks so much for your help.

 

[synapsevampire]

Right, sorry, the array needs to be redimensioned prior to appending to it.

Redim Preserve MyArray[ubound(MyArray)+1]

Here's some info on using arrays:

http://support.crystaldecisions.com/communityCS/TechnicalPapers/cr_arrays.pdf

In a hurry right now, hope that this gets you there.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com