Forum Discussion
Solved
Is it possible to customize the Task cost calculation?
I have a question on how to customize the calculation of Task costs. A bit of context: in my research organization, most of our research projects are funded by institutions (European Commission, ...
Christophe,
First, I suggest you read this Wiki article on data types in Project:
https://web.archive.org/web/20200720083203/https://social.technet.microsoft.com/wiki/contents/articles/31991.ms-project-data-types-task-resource-assignment.aspx
Then you might also want to read the article referenced in the above concerning extra fields:
https://web.archive.org/web/20200722225529/https://social.technet.microsoft.com/wiki/contents/articles/32051.ms-project-extra-fields-in-views.aspx
Those articles will explain why your formulas don't seem to do what you want. However, based on your description, I don't see a need for custom cost fields at all. Instead of creating separate custom fields for funding types and resource types, why not combine the two. For example, you could have an EC work resource, an EC material resource, an EC cost resource, a Regional work resource, Regional material resource, a Regional cost resource, and so forth for each funding entity. Set up each resource type with the appropriate rate based on how they fund. You can even set up a time based cost escalation and/or up to 5 separate cost rates for each resource via resource cost rate tables A through E. This is accessible through the Cost tab on the Resource Information window. Varying cost rate tables for each resource are applied via the Cost Rate Table field at assignment level on either the Task Usage view or the Resource Usage view.
Hope this helps.
John
Hello Christophe.
I am attaching a sample calculation with formulas in case it suggests any useful ideas.
The scenario takes place in the Resource Sheet view.
A field is included VKI(Text1)=resource class (vki1, vki2, vki3) and another Proj_type(Text2)=Project type (ECHE, REGIONAL)
The Work, Cost, Remaining Cost and Rate of each resource in the project are informatively shown.
The Cost1 and Cost2 formula is based on the Switch function, similar to Iif, and multiplies the Remaining Cost by the value that follows the condition that is met for Resource Class and Project Type, and that in this example, for Cost1 is as follows:
[Remaining Cost]*switch(Text1="vki1" And Text2="ECHE";1;Text1="vki2" And [Text2]="ECHE";2;Text1="vki3" And [Text2]="ECHE";3;Text1="vki1" And [Text2]="REGIONAL";1;Text1="vki2" And [Text2]="REGIONAL";3;Text1="vki3" And [Text2]="REGIONAL";3) ...
In the case of Cost2, the only thing that varies are the coefficients:
[[Remaining Cost]*switch(Text1="vki1" And Text2="ECHE";4;Text1="vki2" And [Text2]="ECHE";0;Text1="vki3" And [Text2]="ECHE";0;Text1="vki1" And [Text2]="REGIONAL";4;Text1="vki2" And [Text2]="REGIONAL";0;Text1="vki3" And [Text2]="REGIONAL";0) ...
To obtain the totals you must have the Rollup Sum option selected in the Cost1 and Cost2 formula form.
Additionally, you must group by the Proj_type(Texto2) field (Right click on the column title).
I hope it helps you or gives you some new clue.
Ignacio