Calculating advanced time logs

[putts]

What I have is a table with user's work time logs.

This table has the following fields:
UserID
Date
Hours
Minutes
Work Type

What I need to do is break the time log into MTWTh, Friday, Weekend and Holiday days per user.

I also cant use subreports because of comparisons I have to make between how much time the user was supposed to work with much they did work.

Any ideas?

TIA

 

[Madawc]

Crystal 8.5 has DayOfWeek({your.date}), with Sunday as Day 1. You could test for this and split by days of week.

Holidays are rather harder, it might be a good idea to hard-code them, or set up a table.

However you get the results, you can set them as a group for each user. The structure would be

Group 1 (user)
Group 2 (day type)
Record.

Madawc Williams
East Anglia
Great Britain

 

[putts]

Sorry, left off one thing - I need the time logs to be computed into sums of the time the person logged and all show on one record of the report.

Also, with the holidays, one of the work types is 10-Vacation/Holiday, so I can use that to decipher holidays and time off from days worked.

So what I need is a way to compute a whole chunk of time into one field based on what two fields:
Day of the Week and
Work Type (<> 10 for Normal days, = 10 for Holidays)

Dont know how to sum based on two fields like that though.

 

[synapsevampire]

As a general rule I suggest that you create a periods table which contains the relevent attributes for this, such as dayofweeknumber, isholidayflag.

Now you can join your table to this table and easily get this information. This is a standard table in Data Warehouses exactly for this reason, and many more like it...

Part of the trick is that not all companies have the same holidays.

To do all of this from within Crystal, check out Ken Hamadys solution:

http://www.kenhamady.com/form01.html

-k

 

[synapsevampire]

You can use 4 Running Totals, 2 for hours and 2 for minutes with Evaluate use a formula to define your criteria (one each with <> 10 for Normal days and with = 10 for Holidays).

Once you have the hours and minutes summed, add them together using the standard /60 routines.

If you post what you want this to look like someone will work out the formula.

-k

 

[putts]

I toyed with breaking up my one time log table into multiple views that broke it down to Normal, Holiday and Weekend days, however what I found is that I would have to make multiple sub-reports to use these views. That didn't work because I need to be able to run comparisons for each user all on one line of the report (hence the inability to use Shared variables because they'd always be one behind)

What I could do would be to create a view in SQL to output a field called dayNumber that would be filled with 1-7 depending on the date value in the table.

Then I'd just have to sum the hours/minutes on two number fields (again, work type and dayNumber) - I just dont know how to do this.

 

[putts]

Figured it out (thanks partially to a document on Ken Hamady's site - thanks synapse)

I create a subreport for each user in my report.(this way I can easily use the record selector to narrow the data)
In this subreport, I create fields for each category that are like this:

if dayofweek({Prj_TimeLog.Date}) < 6 and dayofweek({Prj_TimeLog.Date}) > 1 and {Prj_TimeLog.Work Type} <> 10 then
{Prj_TimeLog.Hours}
else
0

I then create a Sum field in the report footer that sums this formula field.
I then supress everything in the subreport except the footer so that just the totals show.

And, viola, we have a report broken into Normal Days (MTWTh and Fridays), Weekends and Holidays.

Thanks for everyone's help in this.

 

[synapsevampire]

You also have the dayofweek function in CR which does the same.

Try a Running Total on the field to summarize (you'll use 2, one for hours, and one for minutes)with something akin to the following:

For an example of the regular days, in the evaluate use a formula place:

dayofweek({table.date}) in [2 to 6]
and
{table.work type} <> 10

For Holidays and weekends you'd use:

dayofweek({table.date}) in [1,7]
and
{table.work type} = 10

Hope this helps.

-k

 

[putts]

slight change to my code:
switch the &quot; < 6&quot; in the if to a &quot; <= 6&quot;.

I only needed the separation between MTWTh and Fridays when calculating how many hours each user was supposed to work in the date range because our company keeps different hours on Fridays.