WARNING - the below procedure ONLY works if you grant PTO balances on the first of the year and do not grant additional throughout the rest of the year. The reasons for this are complex. Unfortunately for users that do not fit this model we don't have a way of reporting on PTO that will tie out to areas like Time Off. You'll need to run Projector reports to get actual time off and then balance things outside of our software.
You'll likely want to add the following data fields:
- Hours Fields -> Person Hours - number of hours reported against both projects and time off
- Time Off Fields -> Available Time Off - available time off at the end of a reporting bucket.
- Time Off Fields -> Granted Time Off - amount of time off granted in a reporting bucket
You'll likely find the following row fields useful:
- Resource Fields -> Resource Display Name - the name of the resource the hours are associated with
- Time Off Fields -> Time Off Reason - returns values listed in your Time Off Reason Editor
Granted Time Off
You may find that your "Granted Time Off" field is negative, even for resources that have never been granted time off. How could that happen? The reason this occurs is because of how the pivot table works. In order to calculate the field Available Time Off, you need to start out with a balance. For resources that were granted time off, this balance is straight forward to figure out. For resources that never had time granted, we have to start somewhere, and that somewhere is with a negative balance based on all previously approved time off.
This tab may be particularly important for you. If you are reporting on both actual and projected hours, then you must untick a checkbox. Otherwise, running totals get zeroed out when you cross the actual/projected gap.
- Include actual vs projected indicator - untick this!
The screenshot below shows the problem I'm talking about. Note the orange box that indicates the boundary between actual and projected hours. Now look down and see that in the first projected field the available time off has suddenly gone to negative 24 hours. This happens because pivot tables start subtracting from zero again once you cross the line.
By unticking the checkbox, you remove the actual/projected field from the output and the numbers suddenly start to make sense again.
You can filter for specific time off reasons from here. For example,
If you want all time off reasons EXCEPT for some, you can use negative filters. Make sure the filter is defined on one line. For example, this query finds all time off reasons except these ones. Note the negative operator, !.
!Jury_Duty !Bereavement !Paternity_Leave
Here, it is important that you select the following options:
- Include Resources in - as time off is resource based, you need to report on resources, not engagements
- Include timeoff and holidays - tick the checkbox or your report won't contain any time off data
If you are trying to create a report that matches what resources would see in the Time Off area of Projector Web, then also tick the checkboxes for:
- Include requested hours in projections
- Include unapproved hours in actuals