Baseline vs Actual report

[Tom Bartolo]

I am trying to get a report that will compare my baseline to actual
hours (not cost - hours only)for each employee. How do I do this?

Thanks.

Tom

 

[Rod Gill]

Hi,

In Project select View, Resources then:
View, Table, Work

To see assignments:
Select View, Resource Usage
Format, Details, Baseline work

--
Rod Gill
Project MVP
For Microsoft Project companion projects, best practices and Project VBA
development services
visit www.projectlearning.com/

 

[Tom Bartolo]

I am trying to get a report that will compare my baseline to actual
hours (not cost - hours only)for each employee. How do I do this?

Thanks.

Tom

Sorry if I wasn't clear, but my "work" column is not correct. I need
to get just the "Actual Hours" vs the "Baseline Hours" without
involving the work column. Is it easier to export it to Excel? If so
how do I do this.

Thanks.

Tom

 

[Tom Bartolo]

Dale,

First of all, Thank You. It was exactly what I wanted. Now of course I
want more. I just want to add a variance column to show the difference
between the two. I tried importing into Excel but the hours come in
with the tag "hrs" right in the cell so I can't use a formula on them.
How should I proceed?

Many Thanks.

Tom

 

[Dale Howard]

Tom --

There is a Variance column in the resource Work table (of which we made a
copy and then modified the copy). However, that column is calculated as
follows:

Work Variance = Work - Baseline Work

If I read you correctly, you want to calculate the difference between the
Actual Work column and the Baseline Work column. To do this, you would need
to use a custom field and create the desired formula in that field. Here's
how to do it:

1. Open the project in question
2. Click Tools - Customize - Fields...
3. Select the Custom Fields tab, then select the "Resource" option at the
top
4. In the Type drop-down list on the right, select the "Text" option
5. Select any unused Text field, then click the Rename... button
6. Name the field something like "Actual Work Variance" and click OK
7. Click the Formula button on the right
8. In the Formula dialog box, copy and past the formula listed below

cStr(([Actual Work]-[Baseline Work])/60) & " h"

(You might want to write this formula the other way, depending on whether
you want to see positive or negative numbers when the task has not finished,
and the Actual Work is less than the Baseline Work.)

9. Click OK and then click OK again when warned about deleting existing
data in this field
10. Click OK to close the Customize fields dialog box

Now, you will need to insert this custom field into the table we created
last night. To do this, complete the following steps:

1. Click View - Table - More Tables...
2. Click the "Resource" option at the top of the dialog box
3. Select the "_Actual vs. Baseline Work" table we created and click the
Edit... button
4. Select the location where you would like to add the new field
5. Click the Field Name drop-down list and select the "Actual Work
Variance" field from this list
6. Set the Title for this field to something like "Variance" and set the
Column Width to 11 or 12
7. Click OK and then click the Organizer... button
8. In the Organizer dialog box, select the Fields tab
9. Click the "Resource" option at the top, and then copy the new "Actual
Work Variance" field to your Global.mpt file
10. Click the Table tab, select the "Resource" option, and then copy the
newly-edited Table to your Global.mpt file
11. Click Yes to replace the old Table with the new Table, then click Close
12. Click Close to close the More Tables dialog box

Now, at this point you should be able to run the Report we created last
night, and see the new column of data in the Report. Prepare yourself not
to see a total at the bottom of the new Variance column as this is NOT a
number field, in spite of what the data looks like (we used a Text field to
show the "h" formatting at the end of each Work Variance value). Let me
know if this helps.

 

[Tom Bartolo]

Dale,

Please send this to your boss....

GIVE DALE A RAISE!!!!

It worked perfectly!

If you are not busy could you explain the formula? If you are no worries.

Many Thanks

Tom

Tom --

There is a Variance column in the resource Work table (of which we made a
copy and then modified the copy). However, that column is calculated as
follows:

Work Variance = Work - Baseline Work

If I read you correctly, you want to calculate the difference between the
Actual Work column and the Baseline Work column. To do this, you would need
to use a custom field and create the desired formula in that field. Here's
how to do it:

1. Open the project in question
2. Click Tools - Customize - Fields...
3. Select the Custom Fields tab, then select the "Resource" option at the
top
4. In the Type drop-down list on the right, select the "Text" option
5. Select any unused Text field, then click the Rename... button
6. Name the field something like "Actual Work Variance" and click OK
7. Click the Formula button on the right
8. In the Formula dialog box, copy and past the formula listed below

cStr(([Actual Work]-[Baseline Work])/60) & " h"

(You might want to write this formula the other way, depending on whether
you want to see positive or negative numbers when the task has not finished,
and the Actual Work is less than the Baseline Work.)

9. Click OK and then click OK again when warned about deleting existing
data in this field
10. Click OK to close the Customize fields dialog box

Now, you will need to insert this custom field into the table we created
last night. To do this, complete the following steps:

1. Click View - Table - More Tables...
2. Click the "Resource" option at the top of the dialog box
3. Select the "_Actual vs. Baseline Work" table we created and click the
Edit... button
4. Select the location where you would like to add the new field
5. Click the Field Name drop-down list and select the "Actual Work
Variance" field from this list
6. Set the Title for this field to something like "Variance" and set the
Column Width to 11 or 12
7. Click OK and then click the Organizer... button
8. In the Organizer dialog box, select the Fields tab
9. Click the "Resource" option at the top, and then copy the new "Actual
Work Variance" field to your Global.mpt file
10. Click the Table tab, select the "Resource" option, and then copy the
newly-edited Table to your Global.mpt file
11. Click Yes to replace the old Table with the new Table, then click Close
12. Click Close to close the More Tables dialog box

Now, at this point you should be able to run the Report we created last
night, and see the new column of data in the Report. Prepare yourself not
to see a total at the bottom of the new Variance column as this is NOT a
number field, in spite of what the data looks like (we used a Text field to
show the "h" formatting at the end of each Work Variance value). Let me
know if this helps.

--
Dale A. Howard
Project Management Trainer/Consultant
Denver, CO

Dale,

First of all, Thank You. It was exactly what I wanted. Now of course I
want more. I just want to add a variance column to show the difference
between the two. I tried importing into Excel but the hours come in
with the tag "hrs" right in the cell so I can't use a formula on them.
How should I proceed?

Many Thanks.

Tom



"Dale Howard" <[email protected]> wrote in message

 

[Jack D.]

Tom,

Dale already showed you how to solve your problem, but there is an
alternative way which will allow you to work with the values in excel.
Simply select the row then go to edit menu and select replace. Enter h in
the top box and nothing in the bottom and click replace all. It will strip
all the h's from the numbers and you can then work with them.

-Jack

Dale,

First of all, Thank You. It was exactly what I wanted. Now of course I
want more. I just want to add a variance column to show the difference
between the two. I tried importing into Excel but the hours come in
with the tag "hrs" right in the cell so I can't use a formula on them.
How should I proceed?

Many Thanks.

Tom

--
Please try to keep replies in this group. I do check e-mail, but only
infrequently.
For Macros and other things check http://masamiki.com/project

-Jack Dahlgren, Project MVP


+++++++++++++++++++

 

[Dale Howard]

Tom --

Thanks for the nice compliment. I work for myself, at least for the moment,
so I don't see a raise anytime soon. Thanks anyway!

The formula does the following:

1. First, the formula subtracts the Baseline Work from the Actual Work.
2. Because Work values are stored in the database in minutes, the formula
divides the answer by 60 to determine the number of Hours.
3. The formula then converts the number of hours into a text string using
the cStr function.
4. Finally, the formula appends the text string with a space and an "h"
character to show the final answer is Hours.

Rather ingenious, wouldn't you say? Ha! Thanks for asking.

--
Dale A. Howard
Project Management Trainer/Consultant
Denver, CO

Dale,

Please send this to your boss....

GIVE DALE A RAISE!!!!

It worked perfectly!

If you are not busy could you explain the formula? If you are no worries.

Many Thanks

Tom



"Dale Howard" <[email protected]> wrote in message

Tom --

There is a Variance column in the resource Work table (of which we made a
copy and then modified the copy). However, that column is calculated as
follows:

Work Variance = Work - Baseline Work

If I read you correctly, you want to calculate the difference between the
Actual Work column and the Baseline Work column. To do this, you would need
to use a custom field and create the desired formula in that field. Here's
how to do it:

1. Open the project in question
2. Click Tools - Customize - Fields...
3. Select the Custom Fields tab, then select the "Resource" option at the
top
4. In the Type drop-down list on the right, select the "Text" option
5. Select any unused Text field, then click the Rename... button
6. Name the field something like "Actual Work Variance" and click OK
7. Click the Formula button on the right
8. In the Formula dialog box, copy and past the formula listed below

cStr(([Actual Work]-[Baseline Work])/60) & " h"

(You might want to write this formula the other way, depending on whether
you want to see positive or negative numbers when the task has not finished,
and the Actual Work is less than the Baseline Work.)

9. Click OK and then click OK again when warned about deleting existing
data in this field
10. Click OK to close the Customize fields dialog box

Now, you will need to insert this custom field into the table we created
last night. To do this, complete the following steps:

1. Click View - Table - More Tables...
2. Click the "Resource" option at the top of the dialog box
3. Select the "_Actual vs. Baseline Work" table we created and click the
Edit... button
4. Select the location where you would like to add the new field
5. Click the Field Name drop-down list and select the "Actual Work
Variance" field from this list
6. Set the Title for this field to something like "Variance" and set the
Column Width to 11 or 12
7. Click OK and then click the Organizer... button
8. In the Organizer dialog box, select the Fields tab
9. Click the "Resource" option at the top, and then copy the new "Actual
Work Variance" field to your Global.mpt file
10. Click the Table tab, select the "Resource" option, and then copy the
newly-edited Table to your Global.mpt file
11. Click Yes to replace the old Table with the new Table, then click Close
12. Click Close to close the More Tables dialog box

Now, at this point you should be able to run the Report we created last
night, and see the new column of data in the Report. Prepare yourself not
to see a total at the bottom of the new Variance column as this is NOT a
number field, in spite of what the data looks like (we used a Text field to
show the "h" formatting at the end of each Work Variance value). Let me
know if this helps.

--
Dale A. Howard
Project Management Trainer/Consultant
Denver, CO

Dale,

First of all, Thank You. It was exactly what I wanted. Now of course I
want more. I just want to add a variance column to show the difference
between the two. I tried importing into Excel but the hours come in
with the tag "hrs" right in the cell so I can't use a formula on them.
How should I proceed?

Many Thanks.

Tom



"Dale Howard" <[email protected]> wrote in message

Tom --

If I am understanding you, you want to see and print a Report (by clicking
View - Reports) that will show the following information for each resource:

Resource Name Baseline Work Actual Work

Am I right? If this is what you want, here's how I would handle it. First,
I would create a custom resource Table to show only the columns you

want
in

this Report. Do this by completing the following steps:

1. Open the desired project
2. Click View - Table - More Tables...
3. Select the "Resource" option at the top of the dialog box
4. Select the Work table and then click Copy...
5. Name this table something like "_Actual vs. Baseline Work"
6. Delete any of the Work fields you do not want, such as the % Work
Complete field and others, so that the only fields remaining are ID, Name,
Baseline Work, and Actual Work
7. Click OK when finished, and then click the Close button

Then I would create the custom Report using my new Table, by

completing
the

following steps:

1. Click View - Reports...
2. Double-click the Custom... icon
3. In the Custom Reports dialog box, click the New... button
4. Select "Resource" as the Report Type and then click the OK button
4. Name the new Report something like "_Resource Actual vs.

Baseline
Work"

5. For the Table in this report, select the new Table we created (the
"_Actual vs. Baseline Work" table)
6. If you desire, click the Details tab and select the "Show

totals"
option

7. Click the OK button when finished
8. Click the Organizer button
9. Select the Tables tab, select the "Resource" option at the top,

and
then

copy your new Table from the current project to your Global.mpt file
10. Select the Reports tab and copy your new Report to your

Global.mpt
file

11. Click the Close button
12. Click the Preview button to see your new Report

I'm not sure this is what you wanted, so please let me know if this helps.

--
Dale A. Howard
Project Management Trainer/Consultant
Denver, CO


I am trying to get a report that will compare my baseline to actual
hours (not cost - hours only)for each employee. How do I do this?

Thanks.

Tom

 

[Tom Bartolo]

When it rains it pours. Now my boss wants it in a different format.
She wants it by resource by task, for example:

Dale Baseline Actual Variance
Think of everything 25 20 5
Program everything 30 29 1
Distribute everything 10 10 0

Total 65 59 6

Tom
don't think 10 8 2
don't do anything 8 5 3

Total 18 13 5

etc...

I have been trying to alter the Task Usage Report because that gives
all the info. In addition how do you get the baseline to come out ONLY
through June not the whole project?

Thanks

Tom

 

[Jack D.]

I only have time for a quick answer, but instead of task usage take a look
at the resource usage view.
To only capture part of the baseline you will need to do some tricks (VBA
programming). It is possible, but not so easy.

-Jack

When it rains it pours. Now my boss wants it in a different format.
She wants it by resource by task, for example:

Dale Baseline Actual Variance
Think of everything 25 20 5
Program everything 30 29 1
Distribute everything 10 10 0

Total 65 59 6

Tom
don't think 10 8 2
don't do anything 8 5 3

Total 18 13 5

etc...

I have been trying to alter the Task Usage Report because that gives
all the info. In addition how do you get the baseline to come out ONLY
through June not the whole project?

Thanks

Tom

--
Please try to keep replies in this group. I do check e-mail, but only
infrequently.
For Macros and other things check http://masamiki.com/project

-Jack Dahlgren, Project MVP


+++++++++++++++++++