I am wondering if anyone has ideas on where to start with a customization to accurately capture field staff utilization in Acumatica. Our work is all project based and we have hundreds of projects running at a given time. Teams are tracking Travel time in the projects under the tasks IBT and OBT (inbound and outbound travel) The rest of tasks are onsite time.
I am hoping to shape the data in a way I can use the stock Dashboards to report Onsite vs Travel time and %of 40 hours a week but in a GI I can’t seem to create all the calculations I need so am thinking I need to have a custom field or two to store the data.
My GI is based on PX.Objects.CR.PMTimeActivity and left joins in the Projects and Tasks to use the common name between projects for the tasks.
Here is the way I am calculating the hours “Onsite” in the GI =((CDec([PMTimeActivity.TimeBillable]) - iif( [PMProject.ContractCD] = '1',CDec([PMTimeActivity.TimeBillable]),0) - iif( left(CStr([PMTask.TaskCD]),3) = 'IBT',CDec([PMTimeActivity.TimeBillable]),0) -iif( left(CStr([PMTask.TaskCD]),3) = 'OBT',CDec([PMTimeActivity.TimeBillable]),0)))/60
That part works fine when grouping by Employee but I can not get a ratio in a GI because if I then create the below field it will not weight the calculation to the total hours for the Day or Week I’m trying to populate.
=IIf( [PMTimeActivity.TimeSpent] = 0 , 1, (CDec([PMTimeActivity.TimeBillable]) - iif( [PMProject.ContractCD] = '1',CDec([PMTimeActivity.TimeBillable]),0) - iif( left(CStr([PMTask.TaskCD]),3) = 'IBT',CDec([PMTimeActivity.TimeBillable]),0) -iif( left(CStr([PMTask.TaskCD]),3) = 'OBT',CDec([PMTimeActivity.TimeBillable]),0)) / CDec([PMTimeActivity.TimeSpent]) )
So my next thought is that if I were able to store a calculation like this for each Employee in the table for the day and week with an event handler the necessary information would be available. I’m sure this is not the preferred method of doing something like this since its all hard coded but maybe creating a boolean field on the task to simplify the logic would be enough to make it a good idea. In any case I”m just looking for some ideas on the best route to solve this problem without getting PowerBI or Tableau involved.
Thanks in advance for anyone who takes the time to offer any help, Greg