How to calculate & display the % of tasks baselined for a project

F

frankod

Hi All,

I would like to be able to report via project server, at the project level,
on what percentage of tasks in the plan have a baseline saved. So far I have
sort of got it working, but its messy.

This is how I have worked so far.....

1) Created a field to count the total number of tasks using IIf([Baseline
Start]>0,"1","0") in a number field. This returns a value of 1 for every
tasks regardless of if it has a baseline or not - I know this is not very
elequant.. but it works. In addition I set the summary row roll up property
to sum. So at the 0 level task I have a count of how many tasks are in the
project.

2) Then I created a custom enterprise task field to test if each task has a
baseline using IIf([Baseline Finish]=ProjDateValue("NA"),"0","1") meaning
that if the task has a baseline the task receives a value of 1, if not 0,
again I set the summary row roll up propery to sum. So at the 0 level task I
have a count of how many baselined tasks are in the project.

3) Then I created a third custom number field to divide the result of point
2 above by the result of point 1 and then multipy by 100 - again at the 0
task level this gives me the right number for baselined tasks.

4) Finally I created a custom text field to display a "%" sign by using a
concatenate function [Number3] & " %"

My grand plan is to create a custom enterprise project field and pass up
this value, then add it to a project centre view and be able to quickly
identify projects that have less than 80% of their tasks baselined.

I have not done this yet because my method of getting the figure and adding
the % sign seems unnessarily messy, am I crazy?? There must be a cleaner
way?? Any suggestions appreciated.

Regards
Franko
 
P

Paul Conroy

I'd be inclined to run a macro (on save) which performs the calculation and
populates a custom field with the result.

If I have some time this evening I'll knock up an example.
 
P

Paul Conroy

Here's a rudimentary macro which will achieve a level of what you want.
Place it in the "on save" event in your Ent. Global Template and change the
customfield name to your own Project/Text Ent. custom field.

This macro doesn't account for summary tasks or blank lines, though it
shouldn't be too hard to accommodate for this.

Apologies to all you VBA guru's out there for my lack of coding finese.

Dim taskswithbaseline As Integer
Dim totaltasks As Integer
Dim baselinepercentage As Integer
Dim output As String
Dim tsk As Task

taskswithbaseline = 0

totaltasks = ActiveProject.Tasks.Count

For Each tsk In ActiveProject.Tasks

If tsk.BaselineStart <> "NA" Then

taskswithbaseline = taskswithbaseline + 1

End If

Next

baselinepercentage = ((taskswithbaseline / totaltasks) * 100)

output = Str(baselinepercentage) + "%"

Call
ActiveProject.ProjectSummaryTask.SetField(FieldNameToFieldConstant("customfieldname"), output)

Paul Conroy said:
I'd be inclined to run a macro (on save) which performs the calculation and
populates a custom field with the result.

If I have some time this evening I'll knock up an example.



frankod said:
Hi All,

I would like to be able to report via project server, at the project level,
on what percentage of tasks in the plan have a baseline saved. So far I have
sort of got it working, but its messy.

This is how I have worked so far.....

1) Created a field to count the total number of tasks using IIf([Baseline
Start]>0,"1","0") in a number field. This returns a value of 1 for every
tasks regardless of if it has a baseline or not - I know this is not very
elequant.. but it works. In addition I set the summary row roll up property
to sum. So at the 0 level task I have a count of how many tasks are in the
project.

2) Then I created a custom enterprise task field to test if each task has a
baseline using IIf([Baseline Finish]=ProjDateValue("NA"),"0","1") meaning
that if the task has a baseline the task receives a value of 1, if not 0,
again I set the summary row roll up propery to sum. So at the 0 level task I
have a count of how many baselined tasks are in the project.

3) Then I created a third custom number field to divide the result of point
2 above by the result of point 1 and then multipy by 100 - again at the 0
task level this gives me the right number for baselined tasks.

4) Finally I created a custom text field to display a "%" sign by using a
concatenate function [Number3] & " %"

My grand plan is to create a custom enterprise project field and pass up
this value, then add it to a project centre view and be able to quickly
identify projects that have less than 80% of their tasks baselined.

I have not done this yet because my method of getting the figure and adding
the % sign seems unnessarily messy, am I crazy?? There must be a cleaner
way?? Any suggestions appreciated.

Regards
Franko
 
P

Paul Conroy

Note, Call Activeproject at the bottom of the code should be on the same line.

HTH

P

Paul Conroy said:
Here's a rudimentary macro which will achieve a level of what you want.
Place it in the "on save" event in your Ent. Global Template and change the
customfield name to your own Project/Text Ent. custom field.

This macro doesn't account for summary tasks or blank lines, though it
shouldn't be too hard to accommodate for this.

Apologies to all you VBA guru's out there for my lack of coding finese.

Dim taskswithbaseline As Integer
Dim totaltasks As Integer
Dim baselinepercentage As Integer
Dim output As String
Dim tsk As Task

taskswithbaseline = 0

totaltasks = ActiveProject.Tasks.Count

For Each tsk In ActiveProject.Tasks

If tsk.BaselineStart <> "NA" Then

taskswithbaseline = taskswithbaseline + 1

End If

Next

baselinepercentage = ((taskswithbaseline / totaltasks) * 100)

output = Str(baselinepercentage) + "%"

Call
ActiveProject.ProjectSummaryTask.SetField(FieldNameToFieldConstant("customfieldname"), output)

Paul Conroy said:
I'd be inclined to run a macro (on save) which performs the calculation and
populates a custom field with the result.

If I have some time this evening I'll knock up an example.



frankod said:
Hi All,

I would like to be able to report via project server, at the project level,
on what percentage of tasks in the plan have a baseline saved. So far I have
sort of got it working, but its messy.

This is how I have worked so far.....

1) Created a field to count the total number of tasks using IIf([Baseline
Start]>0,"1","0") in a number field. This returns a value of 1 for every
tasks regardless of if it has a baseline or not - I know this is not very
elequant.. but it works. In addition I set the summary row roll up property
to sum. So at the 0 level task I have a count of how many tasks are in the
project.

2) Then I created a custom enterprise task field to test if each task has a
baseline using IIf([Baseline Finish]=ProjDateValue("NA"),"0","1") meaning
that if the task has a baseline the task receives a value of 1, if not 0,
again I set the summary row roll up propery to sum. So at the 0 level task I
have a count of how many baselined tasks are in the project.

3) Then I created a third custom number field to divide the result of point
2 above by the result of point 1 and then multipy by 100 - again at the 0
task level this gives me the right number for baselined tasks.

4) Finally I created a custom text field to display a "%" sign by using a
concatenate function [Number3] & " %"

My grand plan is to create a custom enterprise project field and pass up
this value, then add it to a project centre view and be able to quickly
identify projects that have less than 80% of their tasks baselined.

I have not done this yet because my method of getting the figure and adding
the % sign seems unnessarily messy, am I crazy?? There must be a cleaner
way?? Any suggestions appreciated.

Regards
Franko
 
P

Paul Conroy

Ok, so I couldn't help myself. Here's the code which filters out blank and
summary tasks.

I've got to give partial credit to Rod Gill's & Project Experts book on
programming VBA for project. Thanks Guys.

Dim taskswithbaseline As Integer
Dim totaltasks As Integer
Dim baselinepercentage As Integer
Dim output As String
Dim tsk As Task

taskswithbaseline = 0

totaltasks = 0

For Each tsk In ActiveProject.Tasks

If Not tsk Is Nothing Then

If Not tsk.Summary Then

If tsk.BaselineStart <> "NA" Then

taskswithbaseline = taskswithbaseline + 1

End If

End If

totaltasks = totaltasks + 1

End If

Next

baselinepercentage = ((taskswithbaseline / totaltasks) * 100)

output = Str(baselinepercentage) + "%"

Call
ActiveProject.ProjectSummaryTask.SetField(FieldNameToFieldConstant("customfield"), output)
 
C

Caroline Gormley, PMP, MCT

I copied your macro and inserted my custom field name. However, I’m getting
an “Invalid Number error†when I get to the next to last line of code.

Call
ActiveProject.ProjectSummaryTask.SetField(FieldNameToFieldConstant("Percent
Baselined Tasks"), output)

I’ve set up my Enterprise Custom Field as "Percent Baselined Tasks" and it
shows up in Project Information in my project file when I open it from
Server. It is a project entity type, not required, no lookup, no graphical
indicators. Any ideas? This has got to be something simple that I'm just not
seeing. I hope!
 
C

Caroline Gormley, PMP, MCT

Yes, it was something very simple! I'd set up my "Percent Baselined Tasks"
field as a Number field when it should have been a Text field. The macro is
working fine now. Thanks again.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top