Work Variance on the My Tasks Page

[Bryan]

Hello,

I have been tasked with adding the work variance column to the My
Tasks page. It isn't in the field selection box in the adjust views.
A custom enterprise field with trying to recreate the Work Variance
field doesn't calculate correctly at the Assignment level.

My manager wants this done today but I am at a loss for how to get it
done. I hope there's a solution; it seems like it might be fairly
common.

FYI...I am using MOPS 2007 SP2 with the February 2010 CU.

Thanks for your help!

-Bryan

 

[Barbara Henhapl]

Hi Bryan,

unfortunately "Roll down, unless manually specified " does really mean what
it says: take the value from task level and copy it to assignment level. I
also miss sometimes "Use formula" for assignment level which exists for
summary rows.

The only solution I can think about, is to use a macro, which could be event
driven for e.g. BeforeSave. So your plan had to been opened with ProjProf,
but that is also the case if you use formulas.

Here an idea for a macro:
Sub MyVariance() '(Private Sub Project_BeforeSave(ByVal pj As Project)???)
Dim T As Task
Dim A As Assignment
For Each T In ActiveProject.Tasks
If Not T Is Nothing And T.Summary = False Then
T.MyWorkVariance = T.WorkVariance
For Each A In T.Assignments
A.MyWorkVariance = A.WorkVariance
Next A
End If
Next T
End Sub

I named my field "MyWorkVariance" and it is set to "Roll down unless...". To
make it happen in that way, your field name should not contain any blanks
(http://blogs.msdn.com/project_progr...-enterprise-custom-field-values-with-vba.aspx).

Hope that helps?
Regards
Barbara

 

[Bryan]

Hi Bryan,

unfortunately "Roll down, unless manually specified " does really mean what
it says: take the value from task level and copy it to assignment level. I
also miss sometimes "Use formula" for assignment level which exists for
summary rows.

The only solution I can think about, is to use a macro, which could be event
driven for e.g. BeforeSave. So your plan had to been opened with ProjProf,
but that is also the case if you use formulas.

Here an idea for a macro:
Sub MyVariance() '(Private Sub Project_BeforeSave(ByVal pj As Project)???)
Dim T As Task
Dim A As Assignment
    For Each T In ActiveProject.Tasks
        If Not T Is Nothing And T.Summary = False Then
            T.MyWorkVariance = T.WorkVariance
            For Each A In T.Assignments
                A.MyWorkVariance = A.WorkVariance
            Next A
        End If
    Next T
End Sub

I named my field "MyWorkVariance" and it is set to "Roll down unless...".To
make it happen in that way, your field name should not contain any blanks
(http://blogs.msdn.com/project_programmability/archive/2008/01/24/read...).

Hope that helps?
Regards
Barbara

Thanks Barbara. Could you walk me through the steps of including the
macro in my file? The only thing I know about using VBA for macros is
how to open the window (Alt+F11)!

 

[Barbara Henhapl]

Hi Bryan,

you didn't write if you want to have it automatically run on save or
manually started by users. I will describe the automatic way. Your users
will need to change their security stetting in ProjProf to allow macros to
run (Tools - Macro - Security ...)

For a first test, I suggest to create one project and put the code in there.
1. Create a project with some tasks, baseline, ... whatever you do normally.
2. Save and publish
3. Start Visual Basic Editor (Alt+F11)
4. Make sure, that you are in VBAProject (YourTestProjectName) - Microsoft
Project Objects - ThisProject (YourTestProjectName) - see object pane on the
left.
5. Paste the following in:
Private Sub Project_BeforeSave(ByVal pj As Project)

Dim T As Task
Dim A As Assignment

On Error Resume Next 'There should not happen a lot when ignoring errors

For Each T In ActiveProject.Tasks
If Not T Is Nothing And T.Summary = False Then
T.MyWorkVariance = T.WorkVariance
For Each A In T.Assignments
A.MyWorkVariance = A.WorkVariance
Next A
End If
Next T
End Sub

Change "MyWorkVariance" to your field name - it must not include any blanks.
Depending on the field type you chose, you may need to add a calculation
like A.MyWorkVariance = A.WorkVariance/60 .
Make changes to your variance and see if everything works as expected.

If you are fine with the result, you can put this code into Enterprise
Global. Replace steps 1-4 with:

1. Make an administrative backup of your Enterprise Global
2. Open Enterprise Global
3. Start Visual Basic Editor (Alt+F11)
4. Make sure, that you are in VBAProject (Checked-out Enterprise Global) -
Microsoft Project Objects - ThisProject (Checked-out Enterprise Global) (see
object pane on the left)

and after pasting your working code, move on with following steps:
6. Save Enterprise Global
7. Close and checkin Enterprise Global.
8. Restart ProjProf to get that macro active.

If something happens you can restore your Enterprise Global.
If you have more questions, let me know.

Good luck!
Barbara

Hi Bryan,

unfortunately "Roll down, unless manually specified " does really mean
what
it says: take the value from task level and copy it to assignment level. I
also miss sometimes "Use formula" for assignment level which exists for
summary rows.

The only solution I can think about, is to use a macro, which could be
event
driven for e.g. BeforeSave. So your plan had to been opened with ProjProf,
but that is also the case if you use formulas.

Here an idea for a macro:
Sub MyVariance() '(Private Sub Project_BeforeSave(ByVal pj As Project)???)
Dim T As Task
Dim A As Assignment
For Each T In ActiveProject.Tasks
If Not T Is Nothing And T.Summary = False Then
T.MyWorkVariance = T.WorkVariance
For Each A In T.Assignments
A.MyWorkVariance = A.WorkVariance
Next A
End If
Next T
End Sub

I named my field "MyWorkVariance" and it is set to "Roll down unless...".
To
make it happen in that way, your field name should not contain any blanks
(http://blogs.msdn.com/project_programmability/archive/2008/01/24/read...).

Hope that helps?
Regards
Barbara

Thanks Barbara. Could you walk me through the steps of including the
macro in my file? The only thing I know about using VBA for macros is
how to open the window (Alt+F11)!

 

[Bryan]

Hi Bryan,

you didn't write if you want to have it automatically run on save or
manually started by users. I will describe the automatic way. Your users
will need to change their security stetting in ProjProf to allow macros to
run (Tools - Macro - Security ...)

For a first test, I suggest to create one project and put the code in there.
1. Create a project with some tasks, baseline, ... whatever you do normally.
2. Save and publish
3. Start Visual Basic Editor (Alt+F11)
4. Make sure, that you are in VBAProject (YourTestProjectName) - Microsoft
Project Objects - ThisProject (YourTestProjectName) - see object pane on the
left.
5. Paste the following in:
Private Sub Project_BeforeSave(ByVal pj As Project)

Dim T As Task
Dim A As Assignment

On Error Resume Next  'There should not happen a lot when ignoring errors

    For Each T In ActiveProject.Tasks
        If Not T Is Nothing And T.Summary = False Then
            T.MyWorkVariance = T.WorkVariance
            For Each A In T.Assignments
                A.MyWorkVariance = A.WorkVariance
            Next A
        End If
    Next T
End Sub

Change "MyWorkVariance" to your field name - it must not include any blanks.
Depending on the field type you chose, you may need to add a calculation
like A.MyWorkVariance = A.WorkVariance/60 .
Make changes to your variance and see if everything works as expected.

If you are fine with the result, you can put this code into Enterprise
Global. Replace steps 1-4 with:

1. Make an administrative backup of your Enterprise Global
2. Open Enterprise Global
3. Start Visual Basic Editor (Alt+F11)
4. Make sure, that you are in VBAProject (Checked-out Enterprise Global) -
Microsoft Project Objects - ThisProject (Checked-out Enterprise Global) (see
object pane on the left)

and after pasting your working code, move on with following steps:
6. Save Enterprise Global
7. Close and checkin Enterprise Global.
8. Restart ProjProf to get that macro active.

If something happens you can restore your Enterprise Global.
If you have more questions, let me know.

Good luck!
Barbara









Thanks Barbara.  Could you walk me through the steps of including the
macro in my file?  The only thing I know about using VBA for macros is
how to open the window (Alt+F11)!

Thanks Barbara! That is amazing! It works perfectly for the field I
was working on.

My boss asked for another field to be included and I am trying to add
it into your code but it isn't rolling down to the assignment level
when I save:

Private Sub Project_BeforeSave(ByVal pj As Project)

Dim T As Task
Dim A As Assignment

On Error Resume Next 'This should not happen a lot when ignoring
errors

For Each T In ActiveProject.Tasks
If Not T Is Nothing And T.Summary = False Then
T.ActualstoDate = T.ActualWork
T.Variance = T.WorkVariance
For Each A In T.Assignments
A.ActualstoDate = A.ActualWork / 60
A.Variance = A.WorkVariance / 60
Next A
End If
Next T
End Sub

As you can see I am trying to get the Actual Work field to do the same
thing as the Work Variance field but its not quite working.

Can I simply add in the lines like this or does it need to be broken
out into another piece to work?

Thank you!

 

[Barbara Henhapl]

Hi Bryan,

you should be able to do in that way. Have you set "ActualstoDate" to "Roll
down unless..." for "Calculation for Assignment Rows"? If you have, put a '
before "On Error Resume Next". Perhaps you will get helpful information. If
not, activate that option and restart ProjProf to get it applied.

By the way, why have you added "/ 60" on assignment but not on task level?
;-)

Let me know, how you move on!
Regards
Barbara

Hi Bryan,

you didn't write if you want to have it automatically run on save or
manually started by users. I will describe the automatic way. Your users
will need to change their security stetting in ProjProf to allow macros to
run (Tools - Macro - Security ...)

For a first test, I suggest to create one project and put the code in
there.
1. Create a project with some tasks, baseline, ... whatever you do
normally.
2. Save and publish
3. Start Visual Basic Editor (Alt+F11)
4. Make sure, that you are in VBAProject (YourTestProjectName) - Microsoft
Project Objects - ThisProject (YourTestProjectName) - see object pane on
the
left.
5. Paste the following in:
Private Sub Project_BeforeSave(ByVal pj As Project)

Dim T As Task
Dim A As Assignment

On Error Resume Next 'There should not happen a lot when ignoring errors

For Each T In ActiveProject.Tasks
If Not T Is Nothing And T.Summary = False Then
T.MyWorkVariance = T.WorkVariance
For Each A In T.Assignments
A.MyWorkVariance = A.WorkVariance
Next A
End If
Next T
End Sub

Change "MyWorkVariance" to your field name - it must not include any
blanks.
Depending on the field type you chose, you may need to add a calculation
like A.MyWorkVariance = A.WorkVariance/60 .
Make changes to your variance and see if everything works as expected.

If you are fine with the result, you can put this code into Enterprise
Global. Replace steps 1-4 with:

1. Make an administrative backup of your Enterprise Global
2. Open Enterprise Global
3. Start Visual Basic Editor (Alt+F11)
4. Make sure, that you are in VBAProject (Checked-out Enterprise Global) -
Microsoft Project Objects - ThisProject (Checked-out Enterprise Global)
(see
object pane on the left)

and after pasting your working code, move on with following steps:
6. Save Enterprise Global
7. Close and checkin Enterprise Global.
8. Restart ProjProf to get that macro active.

If something happens you can restore your Enterprise Global.
If you have more questions, let me know.

Good luck!
Barbara

Newsbeitrag







Thanks Barbara. Could you walk me through the steps of including the
macro in my file? The only thing I know about using VBA for macros is
how to open the window (Alt+F11)!

Thanks Barbara! That is amazing! It works perfectly for the field I
was working on.

My boss asked for another field to be included and I am trying to add
it into your code but it isn't rolling down to the assignment level
when I save:

Private Sub Project_BeforeSave(ByVal pj As Project)

Dim T As Task
Dim A As Assignment

On Error Resume Next 'This should not happen a lot when ignoring
errors

For Each T In ActiveProject.Tasks
If Not T Is Nothing And T.Summary = False Then
T.ActualstoDate = T.ActualWork
T.Variance = T.WorkVariance
For Each A In T.Assignments
A.ActualstoDate = A.ActualWork / 60
A.Variance = A.WorkVariance / 60
Next A
End If
Next T
End Sub

As you can see I am trying to get the Actual Work field to do the same
thing as the Work Variance field but its not quite working.

Can I simply add in the lines like this or does it need to be broken
out into another piece to work?

Thank you!

 

[Bryan]

Hi Bryan,

you should be able to do in that way. Have you set "ActualstoDate" to "Roll
down unless..." for "Calculation for Assignment Rows"? If you have, put a'
before "On Error Resume Next". Perhaps you will get helpful information. If
not, activate that option and restart ProjProf to get it applied.

By the way, why have you added "/ 60" on assignment but not on task level?
;-)

Let me know, how you move on!
Regards
Barbara
















Thanks Barbara!  That is amazing!  It works perfectly for the field I
was working on.

My boss asked for another field to be included and I am trying to add
it into your code but it isn't rolling down to the assignment level
when I save:

Private Sub Project_BeforeSave(ByVal pj As Project)

Dim T As Task
Dim A As Assignment

On Error Resume Next  'This should not happen a lot when ignoring
errors

    For Each T In ActiveProject.Tasks
        If Not T Is Nothing And T.Summary = False Then
            T.ActualstoDate = T.ActualWork
            T.Variance = T.WorkVariance
            For Each A In T.Assignments
                A.ActualstoDate = A.ActualWork / 60
                A.Variance = A.WorkVariance / 60
            Next A
        End If
    Next T
End Sub

As you can see I am trying to get the Actual Work field to do the same
thing as the Work Variance field but its not quite working.

Can I simply add in the lines like this or does it need to be broken
out into another piece to work?

Thank you!

Weird. I have the "Actuals to Date" set up identically to the
"Variance" field. The "Variance" field is working how I want it to
but the "Actuals to Date" field is not. It is still copying the task
data down to the assignment rows. The custom field formulas are
"Actuals to Date"=[Actual Work]/60 and "Variance"=[Work Variance]/60.
The summary rows are set to Rollup-->Sum and the Assignment Row
Calculations is set to roll-down unless manually specified (same for
both).

Is there something different with how the Actual Work and Work
Variance fields work in this situation?

 

[Barbara Henhapl]

Hi Bryan,

sorry for the late answer, I was on site with a customer.

I have tried your code, it is working in my environment. Some questions:
- You wrote about formulas. Have you defined formulas on your field? Since
you want to use a macro to fill it, you can't use formulas! In the field
definition leave "None" for custom attributes.
- Which field type are you using? I have checked with Duration, Number and
Text (for completeness).
- You mention "Actuals to Date" twice. Have you included any blanks? To
access enterprise custom fields in an easy way, you can't use field names
with blanks. Moreover, in your code there are no blanks.

Let me know how you move on!

Regards
Barbara

Hi Bryan,

you should be able to do in that way. Have you set "ActualstoDate" to
"Roll
down unless..." for "Calculation for Assignment Rows"? If you have, put a
'
before "On Error Resume Next". Perhaps you will get helpful information.
If
not, activate that option and restart ProjProf to get it applied.

By the way, why have you added "/ 60" on assignment but not on task level?
;-)

Let me know, how you move on!
Regards
Barbara

Newsbeitrag














Thanks Barbara! That is amazing! It works perfectly for the field I
was working on.

My boss asked for another field to be included and I am trying to add
it into your code but it isn't rolling down to the assignment level
when I save:

Private Sub Project_BeforeSave(ByVal pj As Project)

Dim T As Task
Dim A As Assignment

On Error Resume Next 'This should not happen a lot when ignoring
errors

For Each T In ActiveProject.Tasks
If Not T Is Nothing And T.Summary = False Then
T.ActualstoDate = T.ActualWork
T.Variance = T.WorkVariance
For Each A In T.Assignments
A.ActualstoDate = A.ActualWork / 60
A.Variance = A.WorkVariance / 60
Next A
End If
Next T
End Sub

As you can see I am trying to get the Actual Work field to do the same
thing as the Work Variance field but its not quite working.

Can I simply add in the lines like this or does it need to be broken
out into another piece to work?

Thank you!

Weird. I have the "Actuals to Date" set up identically to the
"Variance" field. The "Variance" field is working how I want it to
but the "Actuals to Date" field is not. It is still copying the task
data down to the assignment rows. The custom field formulas are
"Actuals to Date"=[Actual Work]/60 and "Variance"=[Work Variance]/60.
The summary rows are set to Rollup-->Sum and the Assignment Row
Calculations is set to roll-down unless manually specified (same for
both).

Is there something different with how the Actual Work and Work
Variance fields work in this situation?

 

[Bryan]

Hi Bryan,

sorry for the late answer, I was on site with a customer.

I have tried your code, it is working in my environment. Some questions:
- You wrote about formulas. Have you defined formulas on your field? Since
you want to use a macro to fill it, you can't use formulas! In the field
definition leave "None" for custom attributes.
- Which field type are you using? I have checked with Duration, Number and
Text (for completeness).
- You mention "Actuals to Date" twice. Have you included any blanks? To
access enterprise custom fields in an easy way, you can't use field names
with blanks. Moreover, in your code there are no blanks.

Let me know how you move on!

Regards
Barbara

Hi Bryan,

you should be able to do in that way. Have you set "ActualstoDate" to
"Roll
down unless..." for "Calculation for Assignment Rows"? If you have, puta
'
before "On Error Resume Next". Perhaps you will get helpful information..
If
not, activate that option and restart ProjProf to get it applied.

By the way, why have you added "/ 60" on assignment but not on task level?
;-)

Let me know, how you move on!
Regards
Barbara

Newsbeitrag

Thanks Barbara! That is amazing! It works perfectly for the field I
was working on.

My boss asked for another field to be included and I am trying to add
it into your code but it isn't rolling down to the assignment level
when I save:

Private Sub Project_BeforeSave(ByVal pj As Project)

Dim T As Task
Dim A As Assignment

On Error Resume Next 'This should not happen a lot when ignoring
errors

For Each T In ActiveProject.Tasks
If Not T Is Nothing And T.Summary = False Then
T.ActualstoDate = T.ActualWork
T.Variance = T.WorkVariance
For Each A In T.Assignments
A.ActualstoDate = A.ActualWork / 60
A.Variance = A.WorkVariance / 60
Next A
End If
Next T
End Sub

As you can see I am trying to get the Actual Work field to do the same
thing as the Work Variance field but its not quite working.

Can I simply add in the lines like this or does it need to be broken
out into another piece to work?

Thank you!

Weird.  I have the "Actuals to Date" set up identically to the
"Variance" field.  The "Variance" field is working how I want it to
but the "Actuals to Date" field is not.  It is still copying the task
data down to the assignment rows.  The custom field formulas are
"Actuals to Date"=[Actual Work]/60 and "Variance"=[Work Variance]/60.
The summary rows are set to Rollup-->Sum and the Assignment Row
Calculations is set to roll-down unless manually specified (same for
both).

Is there something different with how the Actual Work and Work
Variance fields work in this situation?

Thank you! It was due to the spaces in my custom field name! I
thought it was something simple.

I truly appreciate your guidance and help. Thank you thank you thank
you!