How do I get total elapsed time from time differences?

[tweek]

With CR 8.5, I have a report that returns a Batch, Process, and the Time it takes the process to run. The 'Time' field is a string.

What I want to do is to calculate the total time for each batch and place it in the footer. For example, a batch can have 2 lines of data in the report displaying times of 02:00:07, and 00:10:23. I want to display 02:10:30 as the total time for this batch.

I have no clue how I can do this. Any ideas?

Thanks in advance.

 

[lbass]

First create a formula {@duration} for the details section:

DateDiff ("s", DateTime(0,0,0,0,0,0), DateTime(Date(0,0,0),Time({table.stringtime})))

Create three other formulas:

//{@reset} to be placed in the batch group header:
whileprintingrecords;
numbervar dur := 0;

//{@accum} to be placed in the details section:
whileprintingrecords;
numbervar dur := dur + {@duration};

//{@convertdisplay} to be placed in the group (batch) footer
//(this formula is taken from SynapseVampire's FAQ767-3543):
whileprintingrecords;
numberVar dur;
numberVar hrs;
numberVar min;
numberVar sec;
stringVar hhmmss;

hrs := Truncate(Truncate(dur/60)/60);
min := Remainder(Truncate(dur/60),60);
sec := Remainder(dur,60);

hhmmss := totext(hrs, "00") + ":" +
totext(min, "00") + ":" + totext(sec, "00");

hhmmss;

-LB

 

[tweek]

lbass:

Thanks for your response. I haven't tried your suggestion yet, but would this work for multiple batches? Looking at my initial posting it looks like I would only have 1 batch within a group when, in fact, there may be several batches with different processes in the detail line. Could your suggestion still work or would you accomplish getting the elapsed time of each batch a different way?

Thanks again.

 

[lbass]

I think you need to describe your group structure and the final display you want to see. My suggestion assumes that you have a group on batch. You could have only the batch group or you could have an outer group with batch as the inner group. Let's say you have an outer group on {table.date} (daily), and an inner group on batch. Then if you suppress the batch group header (and drag the group name into the batch group footer) and also suppress the details section, your display would look something like:

11/01/04
Batch 1 04:23:15
Batch 2 02:45:30
Batch 3 03:10:01
11/02/04
Batch 1 02:24:33
Batch 2 04:22:14
Batch 3 01:02:23
etc.

-LB

 

[tweek]

The report groups by date and user. It looks like this:

Date: 11/6/04 User: Tweek
Batch 1 Process 1 02:45:53
Batch 1 Process 2 00:18:12
Batch 2 Process 1 05:07:30
Batch 2 Process 2 00:47:15
Batch 2 Process 3 06:23:02

I want to display the detail information (like above) and the total time for each batch in the report per user. I just don't know the best way to do this.

 

[lbass]

Have you tried my suggestion? I can't tell what problems you are having. If your report is grouped by date and then by user, you need to insert a third group on batch. Then follow my suggestion. If you want to display the details section, the report would then look like:

11/01/04
Tweek
Batch 1 Process 1 02:45:53
Batch 1 Process 2 00:18:12
Total 03:04:05
Batch 2 Process 1 05:07:30
Batch 2 Process 2 00:47:15
Batch 2 Process 3 06:23:02
Total 12:17:47
LBass
Batch 1 Process 1 02:45:00
Batch 1 Process 2 00:15:12
Total 03:00:12
Batch 2 Process 1 02:07:30
Batch 2 Process 2 00:07:15
Batch 2 Process 3 03:23:00
Total 05:37:45
11/02/04
etc.

If you need further help you need to show what results you are getting and then also show what you would like the results to look like.

-LB

 

[tweek]

IT WORKED!!, after adding the 3rd group.

Thank you SO very much for everything, LBass.

 

[tweek]

LBass:

LBass, I overlooked one thing. Because I have the {@duration} and {@accum} formulas in the details section, the total time will always add the last process elapsed time again.

Using your example:

LBass
Batch 1 Process 1 02:45:00
Batch 1 Process 2 00:15:12
Total 03:15:24 <== 15 min 12 secs is added again to the total time rather than displaying the correct time of 03:00:12.

How can I get around this?

I sincerely appreciate all the help you have given me so far.

 

[lbass]

I think you must have accidentally put a copy of the formula {@accum} in the batch group footer. This would cause the last value to be repeated. The only formula that should be in the batch group footer is {@duration}.

-LB

 

[tweek]

I thought the only formula in the batch group footer is the {@convertdisplay}.

I am using your formulas you had shown above. I have {@reset} in the batch group header. {@duration} and {@accum} are in the details area. And the {@convertdisplay} is in the batch group footer. I don't have any other formulas in the batch group footer.

I tried your last suggestion of placing {@duration} in the batch group footer and I get the same results.

Also, the last value doesn't get repeated in the details section, it only gets added into the equation again.

Using this example:
Batch 2 Process 1 02:07:30 <== Detail
Batch 2 Process 2 00:07:15 <== Detail
Batch 2 Process 3 03:23:00 <== Detail
Total 05:37:45 <== Batch Group Header

The value does not get repeated like this:
Batch 2 Process 1 02:07:30 <== Detail
Batch 2 Process 2 00:07:15 <== Detail
Batch 2 Process 3 03:23:00 <== Detail
Batch 2 Process 3 03:23:00 <== Detail
Total 09:00:45 <== Batch Group Header

The value is just added twice to display:
Batch 2 Process 1 02:07:30 <== Detail
Batch 2 Process 2 00:07:15 <== Detail
Batch 2 Process 3 03:23:00 <== Detail
Total 09:00:45 <== Batch Group Header

In any case, you've been a big help. I would have even gotten this far without your help. Thanks.

 

[lbass]

Sorry, I didn't look at the formula names when I last responded. You have the formulas in the right positions. Did you by any chance change {@convertdisplay} so that the first line was:

whileprintingrecords;
numberVar dur := dur + {@duration};

That would cause the repetition. The formula should be exactly as I showed it above, with those first two lines reading:

whileprintingrecords;
numberVar dur;

If you used the formula exactly, then I would wonder whether you might have a duplicated detail line--you say you don't, but are you sure you do not have any suppression set either on the detail section or on detail fields, e.g., "suppress if duplicated"?

-LB

 

[tweek]

I did recheck the formulas. The only time I am accumulating the time is in {@accum}. I don't have 'suppress if duplicated' checked also.

It's always just the last detail in the group that gets added twice.

I will figure something out. Thanks again, LBass, for all your help.

 

[lbass]

I cannot think of any reason this would happen if the formulas were in the right position. I noticed that you are showing the "Total" in the batch group header, which should be in the batch group footer, although this alone would not cause repetition--{@accum} would have to be in the group footer. A couple of thoughts: Are you using any of these formulas for any special formatting?

Try removing the formulas. Make sure that when you remove them, they no longer show as checked on the field explorer. Then add them back in the right position.

If you want to, you could copy the formulas into the thread and I'll take a look at them, although it seems you feel confident you have created them exactly as I suggested. It's frustrating to see you give up when this should easily work.

-LB

 

[tweek]

LBass:

I'm an IDIOT!

I looked at the formulas one last time and I found the problem. Here was my mistake.

This is your formula:
//{@accum} to be placed in the details section:
whileprintingrecords;
numbervar dur := dur + {@duration};

//{@convertdisplay} to be placed in the group (batch) footer
whileprintingrecords;
numberVar dur;
numberVar hrs;
numberVar min;
numberVar sec;
stringVar hhmmss;

hrs := Truncate(Truncate(dur/60)/60);
min := Remainder(Truncate(dur/60),60);
sec := Remainder(dur,60);

hhmmss := totext(hrs, "00") + ":" +
totext(min, "00") + ":" + totext(sec, "00");

hhmmss;

And here was mine:
//{@accum} to be placed in the details section:
whileprintingrecords;
numbervar dur := dur + {@duration};

//{@convertdisplay} to be placed in the group (batch) footer
whileprintingrecords;
numberVar dur;
numberVar hrs;
numberVar min;
numberVar sec;
stringVar hhmmss;

hrs := Truncate(Truncate({@accum}/60)/60);
min := Remainder(Truncate({@accum}/60),60);
sec := Remainder({@accum},60);

hhmmss := totext(hrs, "00") + ":" +
totext(min, "00") + ":" + totext(sec, "00");

hhmmss;

Did I already express that I was an idiot?? After I noticed my mistake and changed it, it worked. Thank you so much for all your help!!!