Project Management

Project 98 Cost and Number rollups

While Microsoft Project 2002 and 2000 both allow users to have cost and number rollups, 98 does not. Since many organizations still use Project 98, consultants could be asked to find a way to make this feature work. Here's a macro that does it.

Project 2000 added a feature that allows you to have custom fields, such as Cost and Number, roll up the summary task structure: If you have a summary task that has sub tasks, and if each of those sub tasks has a number in a field that “rolls up,” the value for the summary task will be the sum of the values in the fields of the sub tasks.

This is a powerful tool for companies that need to track custom field values and see them represented at the summary task level.

Project 2002 is now out, but many companies still use Project 98. With the tightening of IT budgets, you may be asked to consult and assist in efforts to lengthen the life span of existing software investments in the older version. The set of Visual Basic functions shown in Listing A will allow Project 98 users to roll up Cost and Number custom fields.

How it works
First, choose which fields you want to have rolled up. The constants that Project uses have the fields named in a certain format. They all have the prefix pj, followed by an identifier that determines whether the field is for a task, a resource, or an assignment.

For example, the constant for the task custom field Number1 is pjTaskNumber1, and for the Cost10 field is pjTaskCost10. To make the solution work, you put lines of code into the following Start_Rollup subroutine:
Sub Start_Rollup()
Roll_Up FieldID:=pjTaskCost1
Roll_Up FieldID:=pjTaskNumber1
End Sub

The Start_Rollup subroutine, shown above, will rollup the Task Cost1 field and the Task Number1 field. You can add new fields by inserting another line with the constant for the new field inserted in place of the constants shown above. For example, to add the Task Cost5 field, you would add this line:
Roll_Up FieldID:=pjTaskCost5

The lines of code in the Start_Rollup subroutine each call the Roll_Up subroutine for a given field. The Roll_Up subroutine in turn calls the Field_Roller function for every task that is a first-level summary task. It then sets the value of the given custom field for the summary task to be the output of the Field_Roller function, which is the value of all its sub tasks.

The Field_Roller function is a recursive one, meaning that it can call itself, if needed, to get the right value. It must sometimes do this because it’s dealing with summary tasks, which often can have subtasks that are themselves also summary tasks.

Field_Roller will call itself anytime a field it’s acting on is a summary task. It does so to make sure it gets to the “bottom” of every “string” of tasks in an outline structure. To make this more clear, let’s look at a sample summary task structure (see Figure A) and review how the macros will go through the tasks to get the rollup values.

Figure A

We can see Summary Task 1 and 2 are both first-level summary tasks. The Roll_Up subroutine will call the Field_Roller function for each of these tasks. When it calls the function for Summary Task 1, it will be quick. It will just add up the values of the custom field for each of its sub tasks, in this case Task 1 and Task 2.

For the Cost1 field, this sum would be $2. For the Number1 field, it would be 4. The Field_Roller call for Summary Task 2 will be a bit more complex. It starts out the same for the first two tasks. It sums these fields and gets a total of $2 for the Cost1 field and 4 for the Number1 field.

Then it hits the Summary Task 3 field and it has a problem. It has sub tasks itself (obviously, or it would not be a summary) so it cannot just add its field value to the total, because it does not yet have a field total for these custom fields. It therefore has to call itself for this summary task in order for Summary Task 3 to have a value that can be added.

So Field_Roller calls itself for Summary Task 3 and it finds a total of $2 for Cost1 and 4 for Number1. It adds this to the $2 and 4 it found for the Cost1 and Number1 fields for tasks 3 and 4 to get a total of $4 for Cost1 and 8 for Number1 for Summary Task 3. Then control is passed back to the Roll_Up subroutine and it takes the total of all the first-level summary tasks for the fields and puts that into the Project Summary task fields. The output of these macros on the sample in Figure A is shown in Figure B.

Figure B

So now each time the Start_Rollup subroutine is run, the rollups will be updated. To ensure that they’re kept up to date, a call to the Start_Rollup subroutine should be placed in one or more of the Events in Project. These events offer a spot into which you can place code you want when certain actions happen in Project, such as the printing, saving, or closing of the project.

To access these events, double-click on the ThisProject object in the project explorer tree control in the Project VBA editor, and then select Project from the drop-down list in the top-right of the code window, as shown in Figure C.

Figure C

Then, from the top-right drop-down list, pick the event that you want to fire your Start_Rollup subroutine. Figure D shows the list.

Figure D

Here is how to add a call to your Start_Rollup subroutine:
Private Sub Project_BeforeSave(ByVal pj As Project)
Start Rollup
End Sub

Now, just prior to saving the project, the BeforeSave event will call the Start_Rollup subroutine so that the custom rollup values will be updated before the project is saved. Another good event to use is BeforePrint. With the BeforeSave and BeforePrint events covered, you can ensure that the rollups will be current.

Editor's Picks