Project Costs report based on User Allocations and personal rates

Edit online

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.

Important:

All users in the system can view personal rates of each other. There is no robust solution that could hide rates set to personal custom fields, per role. Alternate option is to introduce flexible hidden role-based or skills-based rates instead of personal rates. The formulas are described below.

Targetprocess does not track weekends, holidays, and tracking of calendar weeks is limited.

Improperly configured calculations may be not accurate enough. Verify the reports before using them for billing and invoicing purposes. Use non-verified reports on your own risk.

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.

Note:

Metrics for project costs based on user allocation data cannot be configured. Neither UserProjectAllocation entities nor fields of UserAllocations collection of Project entity are supported by custom formula metrics at the moment.

Prerequisites

The report produces results only when People Allocations are set.

The report does not produce any results if users are assigned to projects and no user allocations are set. Select Project Members entities as data source in this basic case.

Note:

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.

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.

New to Visual Reports? Learn how Visual Reports editor works.

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

Predefined fields:

Field Name	Description
Percentage	Percent participation of a user. Set as a number in [0..100] range. Divide the value by 100 in custom calculations.
Project	Name of the project the allocation is related to.

Custom calculations:

Formula Name	Custom Calculation Formula
Project Member	ProjectMember.User.FirstName + " " + ProjectMember.User.LastName
Duration (Weeks)	WEEKS(EffectiveEndDate - EffectiveStartDate)

Note:

How effective start and end dates are calculated for an allocation: Effective Start Date, Effective End Date

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.

Formula Name	Custom Calculation Formula
Personal Hourly Rate	ProjectMember.User.CustomValues.Number("Hourly Rate")
Personal Weekly Rate	ProjectMember.User.WeeklyAvailableHours * ProjectMember.User.CustomValues.Number("Hourly Rate")

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

Formula Name	Custom Calculation Formula
Personal Weekly Rate	ProjectMember.User.CustomValues.Number("Weekly Rate")

Complex result formula multiplies all source values together.

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

Here's how to add custom calculations to charts in the Visual Reports Editor: Add Fields or Custom Formulas to Data Fields.

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