this one is a real challenge to me. I have a Time Entry table with the following fields:
- Date of entry
- Duration of entry
- Customer's project
Where it comes difficult is that there is a special customer which is in fact the employer, for which the projects are for example vacations, unpaid hours off, etc.
What I need to get for each week and each employee is:
- a total for all the projects where the customer is not the employer - one total
- a total for each project of the employer - many totals
- how may hours each employee should work in a week
- a calculation result on the above fields
An image could better show what I want to get:
Period SundayDate to SaturdayDate
Normal total week hours
Total worked (for all customers and all projects)
Paid hours off |
Unpaid hours off > Employer total per project
Vacation hours |
Calculation on the above
If it were a report only for real customers, or only for the employer, that would easy to realize. But mixing both, I don't see how I can do it with Summary fields - it doesn't look a condition can be attached to this type of field. To me, the only way to implement that would be to use ExecuteSQL script step to compute all of these numbers and create records in a temporary table that would the source of the layout. I understand summary fields are kind of place holders that get computed in the context of the report. Something just came to my mind: there could be a field that differentiate the Employer from the customers.
Any advise on how to achieve this ?