Time Subtotals??

[DanaHallenbeck]

I emailed this question to Ken Hamady yesterday and he asked that I post it here. I still have a question about this that I will add at the bottom.

****original question****
I am new to the Crystal Reports world, and am trying to run a report on an IIS log file. I have the basics of the report down, but i need to calculate how many hours individuals are working on the internet. We decided that we could be fairly accurate by checking to see if the time stamp from one transtaction is within 5 minutes of the next (for a givin person) and then adding the difference between the 2 into a counter. This counter would then show a sum when the difference was greater than 5 minutes, and the process would begin again and end with a total sum when the user name changes. Please let me know if this makes any sense and if you have any idea where I can go from here. Any help is appreciated.

****Response****
You need to create a running total using the Next or Previous functions.
There is a FAQ that describes the three types of running totals.
You could use the automatic running totals if you have V7 or later.
Otherwise you will probably need the "3 formula" technique

Ken

****New Question****
Where can I find this FAQ, and will these running totals let me add the difference between two time values?

Thanks for your help Ken. And thanks to anyone else in advance for any help you can offer.

Dana
[sig][/sig]

 

[kenhamady]

FAQs are in a section of the forum, there should be a button at the top of the threads list that takes you to the FAQ section.

If you use the &quot;Previous&quot; function you should be able to bring the prior value into the current record. As long as this is a true time value you should be able to net them. [sig]<p>Ken Hamady<br><a href=mailto:ken@kenhamady.com>ken@kenhamady.com</a><br><a href=

http://www.kenhamady.com/>Crystal

Reports Training by Ken Hamady</a><br>[/sig]

 

[DanaHallenbeck]

X-)Ok, I guess if I open my eyes and look on the page I will find tha FAQ. The other question still stands though. [sig][/sig]

 

[DanaHallenbeck]

I will try it out and let you know [sig][/sig]

 

[DanaHallenbeck]

Ok...I guess I am having trouble with the syntax. Should this work:
datevar curdate;
datevar lastdate;
curdate := cdate({WSM200009-WSM200009 <WINSOCK-PROXY, Monthly>.Log Date});
lastdate := cdate(previous({WSM200009-WSM200009 <WINSOCK-PROXY, Monthly>.Log Date}));
if curdate = lastdate then
formula := &quot;same as &quot; + lastdate
else
formula := &quot;not same as &quot; + lastdate [sig][/sig]

 

[DanaHallenbeck]

I got it to work!!! Thanks.
How do you sum a formula field though? [sig][/sig]

 

[kenhamady]

As long as it is a numeric, you sum it like you sum any database field. (Select the column and use Insert Grand-Total, Subtotal,etc.

However, if you have used certain operations or variables in the formula, you may not be allowed to sum that formula using this simple technique. The simple technique always sums at the detail level, meaning every record in the result table.

In these cases you have to sum it using another running total. [sig]<p>Ken Hamady<br><a href=mailto:ken@kenhamady.com>ken@kenhamady.com</a><br><a href=

http://www.kenhamady.com/>Crystal

Reports Training by Ken Hamady</a><br>[/sig]

 

[DanaHallenbeck]

Here is the code for my formula:

whileprintingrecords;
datevar curdate;
datevar lastdate;
timevar curtime;
timevar lasttime;
stringvar newdate;
stringvar names;
numbervar total;

names := next({WSM200009-WSM200009 <WINSOCK-PROXY, Monthly>.Client User Name});
curdate := cdate({WSM200009-WSM200009 <WINSOCK-PROXY, Monthly>.Log Date});
lastdate := cdate(previous({WSM200009-WSM200009 <WINSOCK-PROXY, Monthly>.Log Date}));
curtime := ctime({WSM200009-WSM200009 <WINSOCK-PROXY, Monthly>.Log Time});
lasttime := ctime(previous({WSM200009-WSM200009 <WINSOCK-PROXY, Monthly>.Log Time}));
total:= curtime - lasttime;

if {WSM200009-WSM200009 <WINSOCK-PROXY, Monthly>.Client User Name} = names then
if curdate = lastdate then
if curtime - lasttime > 300.00 then
0
else
total
else
0
else
0

At the bottom here where it says,
&quot;if curtime - lasttime > 300.00&quot;
I want that zero to actually be a sum of everything printed above it on the report. I have been hacking at it for a few hours now and can't figure it out. Any ideas? [sig][/sig]

 

[kenhamady]

Don't try to do the summing in this formula.

This formula should just give you a column of individual values in your report (total). End this formula before the &quot;If&quot; series, so that it will display the appropriate numbers on the report.

You have to use another variable, in 3 other formulas. These will accumulate, display and reset this variable value. That is where you will need your if-then logic.

See the FAQ for what to put in each of the 3 formulas. [sig]<p>Ken Hamady<br><a href=mailto:ken@kenhamady.com>ken@kenhamady.com</a><br><a href=

http://www.kenhamady.com/>Crystal

Reports Training by Ken Hamady</a><br>[/sig]