Need tip on formula to use for Project Count at Project Level with a"twist"

[anovak]

Right now I have a calculated field called "Project Count" at the
project level with the value of '1', which works great as long as each
project object represents 1 project.

However...

For tiny activities that are < 40 hours, we are going to create an
umbrella project and enter each "mini-project" in a task line (no
summaries). For example:

Does someone know of a formula I could use that would basically say if
custom field 'Operations Type' = 'Enhancements', then Project
Count=Count of all task lines; else Project Count=1?

Exampe:

Finance Minor Enhancements "Umbrella" Project (Project Count=3)
Task 1: New Report
Task 2: New field on screen
Task 3: Run ad hoc query for user

Finance Major Upgrade "Real" Project (Project Count=1)

The reason we're using Project objects is so we can report ALL actual
activity by service area, group, performer, by month, etc., both
operational activities AND "real" projects. Probably won't use
timesheets at all.

Thanks!
Andy Novak
UNT

 

[Rod Gill]

A formula can only access data for the current task, so a count of all tasks
is not possible. You can however, use VBA to do this.

--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:
http://www.projectvbabook.com

 

[Jonathan Sofer]

Here is a way to do it. I am assuming your current Project Count formula is
the following: =1

All you have to do is change your formula to read the following:
=IIf([Operations Type]="Enhancements",[Task Count],1)

Task Count is a field that is available to you out of the box.

Let us know if that works for you.

Jonathan

Create a custom number field as the task level and called it "Task Count"

 

[anovak]

Here is a way to do it. I am assuming your current Project Count formula is
the following: =1

All you have to do is change your formula to read the following:
=IIf([Operations Type]="Enhancements",[Task Count],1)

Task Count is a field that is available to you out of the box.

Let us know if that works for you.

Jonathan

Right now I have a calculated field called "Project Count" at the
project level with the value of '1', which works great as long as each
project object represents 1 project.

For tiny activities that are < 40 hours, we are going to create an
umbrella project and enter each "mini-project" in a task line (no
summaries). For example:

Does someone know of a formula I could use that would basically say if
custom field 'Operations Type' = 'Enhancements', then Project
Count=Count of all task lines; else Project Count=1?

Finance Minor Enhancements "Umbrella" Project (Project Count=3)
Task 1: New Report
Task 2: New field on screen
Task 3: Run ad hoc query for user

Finance Major Upgrade "Real" Project (Project Count=1)

The reason we're using Project objects is so we can report ALL actual
activity by service area, group, performer, by month, etc., both
operational activities AND "real" projects. Probably won't use
timesheets at all.

Thanks!
Andy Novak
UNT

Jonathan,
I understood you recommendation but then was thrown off by this
statement:

"Create a custom number field as the task level and called it "Task
Count""

Why would I need to create a number field at the task level if [Task
Count] is available out of the box at the project level?

Please let me know if I'm misunderstanding.

Thanks,
Andy

 

[anovak]

Here is a way to do it. I am assuming your current Project Count formula is
the following: =1

All you have to do is change your formula to read the following:
=IIf([Operations Type]="Enhancements",[Task Count],1)

Task Count is a field that is available to you out of the box.

Let us know if that works for you.

Jonathan

Right now I have a calculated field called "Project Count" at the
project level with the value of '1', which works great as long as each
project object represents 1 project.

For tiny activities that are < 40 hours, we are going to create an
umbrella project and enter each "mini-project" in a task line (no
summaries). For example:

Does someone know of a formula I could use that would basically say if
custom field 'Operations Type' = 'Enhancements', then Project
Count=Count of all task lines; else Project Count=1?

Finance Minor Enhancements "Umbrella" Project (Project Count=3)
Task 1: New Report
Task 2: New field on screen
Task 3: Run ad hoc query for user

Finance Major Upgrade "Real" Project (Project Count=1)

The reason we're using Project objects is so we can report ALL actual
activity by service area, group, performer, by month, etc., both
operational activities AND "real" projects. Probably won't use
timesheets at all.

Thanks!
Andy Novak
UNT

Jonathan, when I enter your formula at the Project Level, I get syntax
error as if the code is wrong or there is an unrecognized field name
(I assume it doesn't recognize [Task Count])
When I attempt to create a field called [Task Count] at the Task
Level, the error implies that the field already exists although I
can't seem to find it.
When I create a task level field called [Enhancement Count] with a
value of '1', the formula at the Project Level doesn't recognize the
field [Enhancement Count either. Perhaps this isn't as simple as I
thought it was or I'm really green when it comes to formulas and
rollups.

 

[Paul Conroy]

Here's a macro which counts tasks and populates another custom field with the
result. It's not hard to add the fucntionality to count based on the value of
another custom field

Dim TskCounter As Integer
Dim Tsk As Task

TskCounter = 0

For Each Tsk In ActiveProject.Tasks

If Not Tsk Is Nothing Then

If Not Tsk.Summary Then

TskCounter = TskCounter + 1

End If

End If

Next

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

Here is a way to do it. I am assuming your current Project Count formula is
the following: =1

All you have to do is change your formula to read the following:
=IIf([Operations Type]="Enhancements",[Task Count],1)

Task Count is a field that is available to you out of the box.

Let us know if that works for you.

Jonathan

Right now I have a calculated field called "Project Count" at the
project level with the value of '1', which works great as long as each
project object represents 1 project.

For tiny activities that are < 40 hours, we are going to create an
umbrella project and enter each "mini-project" in a task line (no
summaries). For example:

Does someone know of a formula I could use that would basically say if
custom field 'Operations Type' = 'Enhancements', then Project
Count=Count of all task lines; else Project Count=1?

Finance Minor Enhancements "Umbrella" Project (Project Count=3)
Task 1: New Report
Task 2: New field on screen
Task 3: Run ad hoc query for user

Finance Major Upgrade "Real" Project (Project Count=1)

The reason we're using Project objects is so we can report ALL actual
activity by service area, group, performer, by month, etc., both
operational activities AND "real" projects. Probably won't use
timesheets at all.

Thanks!
Andy Novak
UNT

Jonathan, when I enter your formula at the Project Level, I get syntax
error as if the code is wrong or there is an unrecognized field name
(I assume it doesn't recognize [Task Count])
When I attempt to create a field called [Task Count] at the Task
Level, the error implies that the field already exists although I
can't seem to find it.
When I create a task level field called [Enhancement Count] with a
value of '1', the formula at the Project Level doesn't recognize the
field [Enhancement Count either. Perhaps this isn't as simple as I
thought it was or I'm really green when it comes to formulas and
rollups.

 

[anovak]

Here's a macro which counts tasks and populates another custom field with the
result. It's not hard to add the fucntionality to count based on the value of
another custom field

Dim TskCounter As Integer
Dim Tsk As Task

TskCounter = 0

For Each Tsk In ActiveProject.Tasks

If Not Tsk Is Nothing Then

If Not Tsk.Summary Then

TskCounter = TskCounter + 1

End If

End If

Next

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

Here is a way to do it. I am assuming your current Project Count formula is
the following: =1
All you have to do is change your formula to read the following:
=IIf([Operations Type]="Enhancements",[Task Count],1)
Task Count is a field that is available to you out of the box.
Let us know if that works for you.
Jonathan

Right now I have a calculated field called "Project Count" at the
project level with the value of '1', which works great as long as each
project object represents 1 project.
However...
For tiny activities that are < 40 hours, we are going to create an
umbrella project and enter each "mini-project" in a task line (no
summaries). For example:
Does someone know of a formula I could use that would basically say if
custom field 'Operations Type' = 'Enhancements', then Project
Count=Count of all task lines; else Project Count=1?
Exampe:
Finance Minor Enhancements "Umbrella" Project (Project Count=3)
Task 1: New Report
Task 2: New field on screen
Task 3: Run ad hoc query for user
Finance Major Upgrade "Real" Project (Project Count=1)
The reason we're using Project objects is so we can report ALL actual
activity by service area, group, performer, by month, etc., both
operational activities AND "real" projects. Probably won't use
timesheets at all.
Thanks!
Andy Novak
UNT

Jonathan, when I enter your formula at the Project Level, I get syntax
error as if the code is wrong or there is an unrecognized field name
(I assume it doesn't recognize [Task Count])
When I attempt to create a field called [Task Count] at the Task
Level, the error implies that the field already exists although I
can't seem to find it.
When I create a task level field called [Enhancement Count] with a
value of '1', the formula at the Project Level doesn't recognize the
field [Enhancement Count either. Perhaps this isn't as simple as I
thought it was or I'm really green when it comes to formulas and
rollups.

I actually got Jonathan's example to work. At first I thought EPM
wasn't recognizing the [Task Count] field, but there must have some
other syntax error.