Show budget vs actual only at selected task rollup line items

[Randy Bristol]

I would like to be able to enter a Budget value on each task and have that
budget figure sum at each rollup level. This I have accomplished with the
Number1 field.

Next I would like to enter an Actual value on selected task rollup rows and
then have a formula column that displays a Variance (rolled up budget figure
minus entered actual figure for the row) in a third numeric column. The
third numeric column should display no value (or zeroes) for all rows that do
not have a budget value entered.

My ultimate goal is to produce a report that shows budget figures for all
tasks and rolled up but actual and variance figures only for selected roll up
tasks since we track and report actuals at a higher level than we budget.

Can this be done in Project 2007? If so, how?

 

[Andrew Lavinsky]

It sounds like in the third column, you just need to insert an IIF (If Then)
clause prior to the formula:

IIF([Number1]<>0,[Number1]-[Number2],0)

I would make it a Cost field, and not a number field though. Will be easier
to format.

-A

 

[Randy Bristol]

Thanks very much for the response, Andrew.

I tried your suggestion. The difficulty I am having is that formulas don't
seem to work on rollup rows. My first (Number1) column has the Custom Fields

Calculation for Task and Group Summary Rows option set to Rollup and Sum so

that I can roll up the budget figures to summary rows. When I apply even a
simple formula such as:

[Number1]-[Number2]

to row 3, it will not do the calculation and just returns zero for all
rollup rows. This I think is ultimately the roadblock to what I am trying to
accomplish. Does anyone have any further ideas on this?

It sounds like in the third column, you just need to insert an IIF (If Then)
clause prior to the formula:

IIF([Number1]<>0,[Number1]-[Number2],0)

I would make it a Cost field, and not a number field though. Will be easier
to format.

-A

I would like to be able to enter a Budget value on each task and have
that budget figure sum at each rollup level. This I have accomplished
with the Number1 field.

Next I would like to enter an Actual value on selected task rollup
rows and then have a formula column that displays a Variance (rolled
up budget figure minus entered actual figure for the row) in a third
numeric column. The third numeric column should display no value (or
zeroes) for all rows that do not have a budget value entered.

My ultimate goal is to produce a report that shows budget figures for
all tasks and rolled up but actual and variance figures only for
selected roll up tasks since we track and report actuals at a higher
level than we budget.

Can this be done in Project 2007? If so, how?

 

[Steve House]

In the definiton of the calculated field there is a checkbox that sets
whether or not it should roll up to summary rows.

HTH


--
Steve House
MS Project Trainer & Consultant

Thanks very much for the response, Andrew.

I tried your suggestion. The difficulty I am having is that formulas
don't
seem to work on rollup rows. My first (Number1) column has the Custom
Fields

Calculation for Task and Group Summary Rows option set to Rollup and Sum
so

that I can roll up the budget figures to summary rows. When I apply even
a
simple formula such as:

[Number1]-[Number2]

to row 3, it will not do the calculation and just returns zero for all
rollup rows. This I think is ultimately the roadblock to what I am trying
to
accomplish. Does anyone have any further ideas on this?

It sounds like in the third column, you just need to insert an IIF (If
Then)
clause prior to the formula:

IIF([Number1]<>0,[Number1]-[Number2],0)

I would make it a Cost field, and not a number field though. Will be
easier
to format.

-A

I would like to be able to enter a Budget value on each task and have
that budget figure sum at each rollup level. This I have accomplished
with the Number1 field.

Next I would like to enter an Actual value on selected task rollup
rows and then have a formula column that displays a Variance (rolled
up budget figure minus entered actual figure for the row) in a third
numeric column. The third numeric column should display no value (or
zeroes) for all rows that do not have a budget value entered.

My ultimate goal is to produce a report that shows budget figures for
all tasks and rolled up but actual and variance figures only for
selected roll up tasks since we track and report actuals at a higher
level than we budget.

Can this be done in Project 2007? If so, how?

 

[Randy Bristol]

Thank you Steve, that is exactly what I was looking for. To clarify, in the
Custom Fields dialogue box for the column, I selected the 'Use Formula' radio
button in the 'Calculation for Task and Group Summary Rows' section. This
caused my group summary rows to use the formula that I put in the 'Custom
Attributes' section.

In the definiton of the calculated field there is a checkbox that sets
whether or not it should roll up to summary rows.

HTH


--
Steve House
MS Project Trainer & Consultant

Thanks very much for the response, Andrew.

I tried your suggestion. The difficulty I am having is that formulas
don't
seem to work on rollup rows. My first (Number1) column has the Custom
Fields

Calculation for Task and Group Summary Rows option set to Rollup and Sum
so

that I can roll up the budget figures to summary rows. When I apply even
a
simple formula such as:

[Number1]-[Number2]

to row 3, it will not do the calculation and just returns zero for all
rollup rows. This I think is ultimately the roadblock to what I am trying
to
accomplish. Does anyone have any further ideas on this?

It sounds like in the third column, you just need to insert an IIF (If
Then)
clause prior to the formula:

IIF([Number1]<>0,[Number1]-[Number2],0)

I would make it a Cost field, and not a number field though. Will be
easier
to format.

-A

I would like to be able to enter a Budget value on each task and have
that budget figure sum at each rollup level. This I have accomplished
with the Number1 field.

Next I would like to enter an Actual value on selected task rollup
rows and then have a formula column that displays a Variance (rolled
up budget figure minus entered actual figure for the row) in a third
numeric column. The third numeric column should display no value (or
zeroes) for all rows that do not have a budget value entered.

My ultimate goal is to produce a report that shows budget figures for
all tasks and rolled up but actual and variance figures only for
selected roll up tasks since we track and report actuals at a higher
level than we budget.

Can this be done in Project 2007? If so, how?