## Tuesday, November 13, 2018

### Create a search Calculating 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

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

#### 1 comment:

1. Hi. Is there a way to have a field in the formula where it says '40' instead of the hard coded value?

i.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