Last year had been busy, in fact very busy and it continues. during this time i have come across a problem in Project Server.
As usual our Sales team has promised customer 5 reports (part of their marketing plan where they do not check the requirements) without any requirement gathering. for which we landed in soup and it took hell lot of time for us to create those reports. in this one of the requirement was to show the earned value on monthly basis. which can be seen inside the MSP but unfortunately it is not available inside the Project Server database. and same has been confirmed by the MS during support call. however customer being customer. they were adamant on getting this values from MSP, as it is crucial part in their reports. and customer being the construction domain earned value is their highest priority.
So, we went ahead and created the custom database. which takes the snapshot in monthly basis and stores the Project/ Task’s BCWP in my custom database. Now, there was another problem since this earned value is cumulative on month on month basis. this solution also did not served purpose. then we suggested that let’s introduce 2 custom fields
- Earned Value Current Month
- Earned Value Previous Month
- Net Earned Value (Calculated Field) = Earned Value Current Month – Earned Value Previous month + Cumulative EV for Last Month
after above custom field creation, we thought we have solved the purpose/ requirement of the customer. but no we faced another challenge where when we copy the BCWP from Task Usage view to Custom field. Somehow, that value gets change automatically. Which is again an Issue and not acceptable at all. then we found an article where MS has suggested to write a Macro which copies the BCWP and stores it inside the Custom Field.
Now,Considering the pain i had to go through. and also i i don’t want others to go through it. so below is how you create the form and update the custom Field. and form will look something like this:
As you can see in above image After copy and Paste values change in Task Usage sheet. now without any further a due let’s just go with the code.
Private Sub btnCurrent_Click() On Error GoTo A Call TimePhasedData(txtCurrStart.Value, txtCurrFinish.Value, "Earned Value Current Month") MsgBox "Previous Month EV value has been pasted successfully" Exit Sub A: MsgBox Err.Description Exit Sub End Sub Sub TimePhasedData(s As Date, F As Date, CF_Name As String) Dim T As Task Dim tsv As TimeScaleValue Dim tsvs As TimeScaleValues 'Timephased for Task with a UniqueID of 1 For Each T In ActiveProject.Tasks Set tsvs = T.TimeScaleData( _ StartDate:=s, _ EndDate:=F, _ Type:=pjTaskTimescaledBCWP, _ TimeScaleUnit:=pjTimescaleMonths, Count:=1) For Each tsv In tsvs Debug.Print "Task Name: " & T.Name & "Start: " & Format(tsv.StartDate, _ "Long Date"), "BCWP: " & Val(tsv.Value) & "$" T.SetField (FieldNameToFieldConstant(CF_Name)), Val(tsv.Value) Next tsv Debug.Print 'Blank line Next T End Sub