Business Use Case:
Create a search that calculates for the ratio of the total Billable time in a Department and the product of the # of employees per Department on the Results and a certain number of hours. eg. 40 hours.
Example: Department A has a total of 10 employees with Billable time entry totalling to 250.
The formula should be
250 hours / (10 employees * 40 hours) = 0 .625
Solution:
1.Create an employee search by navigating to Transactions > Management > Saved Searches > New
2. Select Employee
3. On the Results tab > add the following fields:
- Department, Summary Type Group
- Time: Duration (decimal) , Summary Type Sum
- Name, Summary Type Count
- Formula (Numeric) , Summary Type Maximum
SUM(case when {time.billable}= 'T' Then {time.durationdecimal} else 0 end) / (count(distinct {entityid})*40)4. On the Criteria tab > Summary sub tab
-Summary type > Maximum
-Field > Time: Billable
-Description > is True
5. Name the search
6. Click on Save and Run
Hi. Is there a way to have a field in the formula where it says '40' instead of the hard coded value?
ReplyDeletei.e. I am trying to do a saved search which is almost the same as your one
The difference is, the employees all have varying hours and I want to create a column showing 'billability' by each employee
The numerator in the formula would be the same as what you have
The denominator would be sum('work profiled hours') (taken from the weekly timesheet)
however, I am finding that the denominator is calculating incorrctly
Instead of taking the sum of work hours for the time period specified in the criteria, it is multiplying the value by the no of time entries that make up the billability figure
Do you know how to get around this? thanks