Project Costs report based on User Allocations and personal rates

The Targetprocess reports described below calculate operational costs of projects. The reports are based on project member allocations data.

The reports use User Project Allocation entities as data source. Durations of the allocations are multiplied by personal weekly rates, and totals are calculated. For a Project the data is available on Allocations tab in a detailed view:

Personal weekly rates are set per individual user. Rates for a user are same for all the projects he or she is a member of. The values are set to numeric custom fields on User detailed views.

The solution is based on Visual Reports and custom calculations for the reports. The totals are displayed as charts and can be exported to tabular or graphical files. It is possible to see values distribured per project, per user, per role etc.

Prerequisites

The report produces results only when People Allocations are set. How to set them: People Allocations Management.

Every allocation record should have start and end dates set. A record won't be included to a report when its start or end dates are linked to project dates, and corresponding project dates are blank. Make sure Planned Start Date, Planned End Date fields are filled in properly for planned and running projects, and Start Date, End Date fields are filled in properly for completed projects.

Configure Rates

To keep personal rates, the report requires properly configured and filled in custom fields.

To track personal rates per user, add a custom field of Number or Money type to a User entity.

To track personal rates per role per project, add a custom field of Number or Money type to a Project entity. Add as many fields as number of billable roles you want to track separately.

To track personal rates per skill level per project, add a custom field of Number or Money type to a Project entity. Add as many fields as number of skills you want to track separately. Add a custom field of Drop Down List type to a User entity to represent skill level.

Fill in custom field values.

More information: How to set up Custom Fields?

Visual Report for Project Costs

Create the report as a blank Entity Report based on User Project Allocations data source.

On this step, apply the filter by projects if needed.

Here is the list of fields and custom calculations we'll use further:

Predefined fields:

Custom calculations:

There are two ways to define personal weekly rates.

Option [1]: Set hourly rates and weekly available hours per user. Weekly hours is a predefined field in Targetprocess. Default value for weekly hours is 40 h. Hourly rates can be set as a custom field.

Option [2]: Set weekly rates per user as a custom field.

Complex result formula multiplies all source values together.

To set up the report, add custom formulas to Data fields list.

Add Allocation Cost custom formula:

Allocation Cost [1]:

WEEKS(EffectiveEndDate - EffectiveStartDate) * (Percentage/100) * ProjectMember.User.WeeklyAvailableHours * ProjectMember.User.CustomValues.Number("Hourly Rate")

Allocation Cost [2]:

WEEKS(EffectiveEndDate - EffectiveStartDate) * (Percentage/100) * ProjectMember.User.CustomValues.Number("Weekly Rate")

To see the values per project member, add Project Member custom formula as described above.

How the setup screen of the final report looks like:

Add the name for your report. Press Finish setup button to save the settings.

Flexible Hidden Rates

Hidden role-based or skills-based rates are useful as with them it is not needed to specify personal rates in user profiles.

All the custom formulas below are valid for reports based on User Project Allocations data source.

To use the calculations, replace origin rate references in custom formulas such as:

ProjectMember.User.CustomValues.Number("Hourly Rate")

with advanced custom calculations, for example:

(CASE WHEN ProjectMember.Role.Name = 'Developer' THEN 130 WHEN ProjectMember.Role.Name = 'QA Engineer' THEN 110 ELSE 80 END)

Rates based on Project Role

The rates are calculated with Project Role Rate custom formula.

Project membership roles are selected per user in People tab of project views or in Projects & Teams tab of user views. To retrieve names of Project Roles of users use the following custom formula:

ProjectMember.Role.Name

Fixed rates per Role

Rates per role are common for all projects. Rates are specified as constant values in calculation formulas.

CASE
WHEN ProjectMember.Role.Name = 'Developer' THEN 130
WHEN ProjectMember.Role.Name = 'QA Engineer' THEN 110
ELSE 80
END

Rates vary per Project

Rates per role are specified per project. Numeric custom fields for each supported role are added to project detailed views in advance.

CASE
WHEN ProjectMember.Role.Name = 'Developer' THEN Project.CustomValues.Number("Developer Rate")
WHEN ProjectMember.Role.Name = 'QA Engineer' THEN Project.CustomValues.Number("QA Rate")
ELSE Project.CustomValues.Number("Other Rate")
END

Rates based on User Function

The rates are calculated with User Function Rate custom formula.

Personal skills level is specified per user. Drop down list custom field named Skills Level are added to user detailed views and filled in in advance. To retrieve values of user skill levels use the following custom formula:

ProjectMember.User.CustomValues.Text("Skills Level")

Fixed rates per Role

Rates per skill are common for all projects. Rates are specified as constant values in calculation formulas.

CASE
WHEN ProjectMember.User.CustomValues.Text("Skills Level") = 'Senior' THEN 130
WHEN ProjectMember.User.CustomValues.Text("Skills Level") = 'Middle' THEN 110
ELSE 80
END

Rates vary per Project

Rates per skill are specified per project. Numeric custom fields for each supported skill are added to project detailed views and filled in in advance.

CASE
WHEN ProjectMember.User.CustomValues.Text("Skills Level") = 'Senior' THEN Project.CustomValues.Number("Senior Rate")
WHEN ProjectMember.User.CustomValues.Text("Skills Level") = 'Middle' THEN Project.CustomValues.Number("Middle Rate")
ELSE Project.CustomValues.Number("Other Rate")
END