Hi,

I'm trying to create a work performance report which is going well so far. The only issue I have is the grouping. I have grouped performances by weeks which is great until I come to a job that last over a week or runs into the next week. Is there away to split the data over the 2 weeks, for example we have a job that starts on the Wednesday and ends on the following Tuesday, 30 hours completed in that time. It would be great to calculate the average, 18 hours week 1 and 12 hours week 2 and then place them into there relative groups. At the moment the groups are sorted by the start date not the finish so data is shown only in week 1 at present.

many thanks

Ads

If above isn't the case, I might know something - probably not the prettiest solution, the best, or god knows what else, but in my example it worked for me :)

I created 8(!) formula's (am sure you would be able to bring this down with a bit more time)

In my formula's, I am using a table called TEMP, with 2 dates, DATES as start date and DATES2 as end date, and NUMBERS as my total number of hours worked.

//Day Diff
datediff('d',{TEMP.DATES},{TEMP.DATES2})+1 -
(DateDiff ("ww",{TEMP.DATES}, {TEMP.DATES2}, CrSaturday ) +
DateDiff ("ww",{TEMP.DATES}, {TEMP.DATES2}, CrSunday ))

//Day Number
dayofweek({TEMP.DATES},crMonday)

//Week 1 Days -
if {@Day Number} = 1 then {@Day Diff} - 4 else
if {@Day Number} = 2 then {@Day Diff} - 3 else
if {@Day Number} = 3 then {@Day Diff} - 2 else
if {@Day Number} = 4 then {@Day Diff} - 1 else
if {@Day Number} = 5 then 1

//Week 2 Days
{@Day Diff} - {@Week 1 Days}

//Week Number
if datepart('ww',{TEMP.DATES},crMonday) = datepart('ww',{TEMP.DATES2},crMonday) then {TEMP.NUMBERS} else

{TEMP.NUMBERS}/{@Day Diff}*{@Week 1 Days}

//Week Number 2
if datepart('ww',{TEMP.DATES},crMonday) <> datepart('ww',{TEMP.DATES2},crMonday) then {TEMP.NUMBERS}/{@Day Diff}*{@Week 2 Days}

//Previous Week
previous({@Week Number 2})

//Previous Week 2
if not isnull({@previous week}) then previous({@Week Number 2}) + {@Week Number}
else {@Week Number}

Use the last formula (Previous Week 2) in your group header.

So I now have the hours the job has taken. 41.02 of which 13.09 is in week 1 and the rest being in week 2, how can I tell the report to move the week 2 data into the next group or is it possible to duplicate the line and place it in the second group then perform the calculation then?

Thanks

Ads

It would certianly be easier to copy the job over, I can take the days that are in that week of the year which it is grouped by and work it out that way. The current week I'm looking at is 16, the start time is in week 16 but the finish time is in week 17.

Thanks again.