H
Hernandezz
Hi project masters...I need your help...
we use EPM 2007 since we have a lot resources and projects.
One of the most important functionality for developing Projects is to
prevent the over allocation of the resources in several projects.
In the PWA it is possible to generate a report for this. However the
information is not in real time and therefore it may not be correct,
even if the report cube runs every hour.
In Project Professional, Resource Usage view exists a field with the
Information from other projects "Other projects and commitments" but
That gives me the time not real if you do not have all the projects
open. Microsoft said it is a limitation of the project.
The solution I found for this was to create a macro with VBA. Every
time the project is saved, the macro saves the data, work and actual
work by resource, day, project etc, in the BD created by me. To read
the macro I go directly to the VB and the availability is in real time
and correct .
This week I detected a problem with my macro and why? For example when
I have two projects opened such as the project professional, the
Resources Usage view gives me the work and actual work but this is the
sum of two work projects, hence, my results are not correct anymore.
It's because project, when we have two or more projects open, in view
Resource usage, project sum the hours the active projects.
Can you help me with my code? I'm searching for way to save in BD work
and Actual work to the only project I save and not active projects. I
try group by project and task, but no results...
Can you help me please?
Best Regards
My code is:
Public Sub PercorreProjecto()
Dim MyDatabase As ADODB.Connection
Dim MyRecordSet As ADODB.Recordset
Dim connStr As String
Dim idRes As Integer, idProj As Integer
Dim res As Resource
Dim pjStart As Date, pjFinish As Date, day As String
Dim TSV_AW As TimeScaleValues, TSV_WK As TimeScaleValues, HowMany
As Long
Dim aw As Variant, wk As Variant
Dim proj As Project
Dim tsk As Task
ViewApply Name:="Resource &Usage"
connStr =
"Provider=SQLNCLI;Server=XXX;Database=Test1_ProjectServer_Reporting;Trusted_Connection=yes;"
Set MyDatabase = New ADODB.Connection
MyDatabase.CursorLocation = adUseClient
MyDatabase.Open (connStr)
Set MyRecordSet = New ADODB.Recordset
MyRecordSet.Open "SELECT idProject FROM
[MSP__ResourceUsage_Projects] WHERE projectName = '" &
ActiveProject.Name & "'", MyDatabase, adOpenDynamic, adLockPessimistic
If MyRecordSet.EOF = True Then
MyDatabase.Execute "INSERT INTO [MSP__ResourceUsage_Projects]
([projectName]) VALUES('" & ActiveProject.Name & "')"
MyRecordSet.Close
MyRecordSet.Open "SELECT idProject FROM
[MSP__ResourceUsage_Projects] WHERE projectName = '" &
ActiveProject.Name & "'", MyDatabase, adOpenDynamic, adLockPessimistic
End If
idProj = MyRecordSet.Fields.Item("idProject")
'delete data for this project
MyDatabase.Execute "DELETE FROM [MSP__ResourceUsage_ResourceWork]
WHERE [idProject] =" & idProj & ""
For Each proj In Application.Projects
For Each tsk In proj.Tasks
For Each res In proj.Resources
Set MyRecordSet = New ADODB.Recordset
MyRecordSet.Open "SELECT idResource FROM
[MSP__ResourceUsage_Resources]WHERE resourceName = '" & res.Name &
"'", MyDatabase, adOpenDynamic, adLockPessimistic
If MyRecordSet.EOF = True Then
MyDatabase.Execute "INSERT INTO
[MSP__ResourceUsage_Resources]([resourceName]) VALUES('" & res.Name &
"')"
MyRecordSet.Close
MyRecordSet.Open "SELECT idResource FROM
[MSP__ResourceUsage_Resources] WHERE resourceName = '" & res.Name &
"'", MyDatabase, adOpenDynamic, adLockPessimistic
End If
idRes = MyRecordSet.Fields.Item("idResource")
MyRecordSet.Close
pjStart = ActiveProject.ProjectStart
pjFinish = ActiveProject.ProjectFinish
Set TSV_AW = res.TimeScaleData(pjStart, pjFinish,
Type:=pjResourceTimescaledActualWork, TimescaleUnit:=pjTimescaleDays)
Set TSV_WK = res.TimeScaleData(pjStart, pjFinish,
Type:=pjResourceTimescaledWork, TimescaleUnit:=pjTimescaleDays)
For HowMany = 1 To TSV_WK.Count
wk = 0
aw = 0
If TSV_WK.Item(HowMany).Value <> "" Then
wk = TSV_WK.Item(HowMany).Value / 60
End If
If TSV_AW.Item(HowMany).Value <> "" Then
aw = TSV_AW.Item(HowMany).Value / 60
End If
day = TSV_WK(HowMany).StartDate
'insert wk and Awk
If wk <> 0 Then
MyDatabase.Execute "INSERT INTO
[MSP__ResourceUsage_ResourceWork]([idResource],[idProject],
[ResActualWork],[ResWork],[Day]) VALUES(" & idRes & "," & idProj & ","
& aw & "," & wk & ",'" & day & "')"
End If
Next HowMany
Next
Next tsk
Next proj
MyDatabase.Close
MsgBox " save successful "
End Sub
we use EPM 2007 since we have a lot resources and projects.
One of the most important functionality for developing Projects is to
prevent the over allocation of the resources in several projects.
In the PWA it is possible to generate a report for this. However the
information is not in real time and therefore it may not be correct,
even if the report cube runs every hour.
In Project Professional, Resource Usage view exists a field with the
Information from other projects "Other projects and commitments" but
That gives me the time not real if you do not have all the projects
open. Microsoft said it is a limitation of the project.
The solution I found for this was to create a macro with VBA. Every
time the project is saved, the macro saves the data, work and actual
work by resource, day, project etc, in the BD created by me. To read
the macro I go directly to the VB and the availability is in real time
and correct .
This week I detected a problem with my macro and why? For example when
I have two projects opened such as the project professional, the
Resources Usage view gives me the work and actual work but this is the
sum of two work projects, hence, my results are not correct anymore.
It's because project, when we have two or more projects open, in view
Resource usage, project sum the hours the active projects.
Can you help me with my code? I'm searching for way to save in BD work
and Actual work to the only project I save and not active projects. I
try group by project and task, but no results...
Can you help me please?
Best Regards
My code is:
Public Sub PercorreProjecto()
Dim MyDatabase As ADODB.Connection
Dim MyRecordSet As ADODB.Recordset
Dim connStr As String
Dim idRes As Integer, idProj As Integer
Dim res As Resource
Dim pjStart As Date, pjFinish As Date, day As String
Dim TSV_AW As TimeScaleValues, TSV_WK As TimeScaleValues, HowMany
As Long
Dim aw As Variant, wk As Variant
Dim proj As Project
Dim tsk As Task
ViewApply Name:="Resource &Usage"
connStr =
"Provider=SQLNCLI;Server=XXX;Database=Test1_ProjectServer_Reporting;Trusted_Connection=yes;"
Set MyDatabase = New ADODB.Connection
MyDatabase.CursorLocation = adUseClient
MyDatabase.Open (connStr)
Set MyRecordSet = New ADODB.Recordset
MyRecordSet.Open "SELECT idProject FROM
[MSP__ResourceUsage_Projects] WHERE projectName = '" &
ActiveProject.Name & "'", MyDatabase, adOpenDynamic, adLockPessimistic
If MyRecordSet.EOF = True Then
MyDatabase.Execute "INSERT INTO [MSP__ResourceUsage_Projects]
([projectName]) VALUES('" & ActiveProject.Name & "')"
MyRecordSet.Close
MyRecordSet.Open "SELECT idProject FROM
[MSP__ResourceUsage_Projects] WHERE projectName = '" &
ActiveProject.Name & "'", MyDatabase, adOpenDynamic, adLockPessimistic
End If
idProj = MyRecordSet.Fields.Item("idProject")
'delete data for this project
MyDatabase.Execute "DELETE FROM [MSP__ResourceUsage_ResourceWork]
WHERE [idProject] =" & idProj & ""
For Each proj In Application.Projects
For Each tsk In proj.Tasks
For Each res In proj.Resources
Set MyRecordSet = New ADODB.Recordset
MyRecordSet.Open "SELECT idResource FROM
[MSP__ResourceUsage_Resources]WHERE resourceName = '" & res.Name &
"'", MyDatabase, adOpenDynamic, adLockPessimistic
If MyRecordSet.EOF = True Then
MyDatabase.Execute "INSERT INTO
[MSP__ResourceUsage_Resources]([resourceName]) VALUES('" & res.Name &
"')"
MyRecordSet.Close
MyRecordSet.Open "SELECT idResource FROM
[MSP__ResourceUsage_Resources] WHERE resourceName = '" & res.Name &
"'", MyDatabase, adOpenDynamic, adLockPessimistic
End If
idRes = MyRecordSet.Fields.Item("idResource")
MyRecordSet.Close
pjStart = ActiveProject.ProjectStart
pjFinish = ActiveProject.ProjectFinish
Set TSV_AW = res.TimeScaleData(pjStart, pjFinish,
Type:=pjResourceTimescaledActualWork, TimescaleUnit:=pjTimescaleDays)
Set TSV_WK = res.TimeScaleData(pjStart, pjFinish,
Type:=pjResourceTimescaledWork, TimescaleUnit:=pjTimescaleDays)
For HowMany = 1 To TSV_WK.Count
wk = 0
aw = 0
If TSV_WK.Item(HowMany).Value <> "" Then
wk = TSV_WK.Item(HowMany).Value / 60
End If
If TSV_AW.Item(HowMany).Value <> "" Then
aw = TSV_AW.Item(HowMany).Value / 60
End If
day = TSV_WK(HowMany).StartDate
'insert wk and Awk
If wk <> 0 Then
MyDatabase.Execute "INSERT INTO
[MSP__ResourceUsage_ResourceWork]([idResource],[idProject],
[ResActualWork],[ResWork],[Day]) VALUES(" & idRes & "," & idProj & ","
& aw & "," & wk & ",'" & day & "')"
End If
Next HowMany
Next
Next tsk
Next proj
MyDatabase.Close
MsgBox " save successful "
End Sub