Separate fields for material and personnel costs (totals)

[Riko Wichmann]

Dear all,

in our project plans we plan Capital Investment als Material Resource
and Work Resource (big surprise). Of course, it is of interest who much
capital investment is planned for individual projects and (summary
tasks). For that purpose, I'd like to define a custom cost field, which
such sums the costs for material costs.

What partially works is the following. All capital investment resources
belong to the Resource Group "Cap". Then I define a filed "Invest" with
the following formula:

IIf([Resource Group]="Cap";[Cost];0)

However, that only seems to work as long as I don't mix work and
material resource assignment to a task. If I have task which have only
material resource or work resources (but not both) assigned, bost costs
are separated correctly. If I have a task which uses both resource
types, the total cost is used (which I guess makes sense from the above
definition, if you assume the If statement actually checks, if the term
"Cap" in contained in the resource group field).
But it is almost impossible to prevent all people in the project from
using mixed resource assignments.

Is there a more clever way on separating material and personnel costs?

Thanks,

Riko

 

[Frank L. Feng [MVP]]

Hi, Riko
It's very complex to archive your aim in 2003.

1st, if you include a task with mix work and material resource ,the
[Resource Group] is not equal to "Cap", you can test it with create a custom
fileld and define it as '= [Resource Group]',you can find that the 'Resource
Group' is a character string only. so you can change you formula to
IIf(InStr([Resource Group],'Cap');[Cost];0).

2nd, you cannot separate the cost with mix work and material resource and
get the cost of material resource only.
my solution is:
set a custom filed, the formula is : if you material resource name be
included in the Resource filed, the custom filed equal to the cost. and set
the group summary rows to rollup-sum.
eg. IIf(InStr([Resource Names],'MyMaterialName');500;0).

I'm a Chinese and my English is not good, wish the information above is
helpful to you.
Thanks

 

[Riko Wichmann]

Hi Frank,

it was useful! Thank you!

I realized, that if we want to also record Actuals separately for
Material Costs (Invest) and Personnel Cost (Work) we HAVE to avoid mixed
resource assignment. So for now, my proposal is to include in the PM
handbook for our projects an appropriate rule. Then, I can calculate the
material costs similarly to what you suggested as:

IIf(InStr([Resource Group];"Cap");IIf(InStr([Resource
Group];"Personnel");1/0;[Cost]);0)

which in addition gives an #Error (via 1/0), if material resources
(resource group "Cap") and Personnel Resources ("Personnel") are mixed
for a task.

Of course, if someone has a more elegant solution, I'm happy to hear it.

Cheers,

Riko