Executive report

[ricardo.marques01]

We are trying to develop an executive report with EPM. This report not
only shows the current status of the projects but also the historical
information from the past like a progress bar that shows the "%
complete" for all projects against the time. This need is getting very
difficult to get because EPM report is very "today status oriented"!!!

We also tried to achieve this with cubes, but when the total work of
the project changes, also changes the "% complete" from previous
report periods, because they are calculated based on the total work of
the project.


So we worked on a workaround that saves the project status each time
the project manager saves the project into the database and with that
we can have historical information to fill our executive report.
Now, the question is: are we working with the right approach? Is there
a better solution? Are we missing something?

Regards,

Ricardo Marques

 

[Dale Howard [MVP]]

Ricardo --

It's only my opinion, but I think you are going to way too much work on this
reporting need. The critical information about any project is its CURRENT
state, not it's previous state. Why does the previous state even matter at
this point? If the project has been on schedule for the past three months,
and is now seriously behind schedule, which value is the most important to
your executives? The current schedule, right? Hope this helps.

 

[ricardo.marques01]

Our experience in project management tell us that the current status
project is so important as the trend of last periods. The trend
analysis is important because tell us how the project reaches the
current status. For example, a risk that has a probability of 10% and
in the next period 20% and then 30%. Probability of 30% is not a big
concern, but the trend shows us that the probability is growing fast
and we must do something. This kind of behaviour is the difference
between proactive project management and reactive one.


Regards,

 

[Dale Howard [MVP]]

Ricardo --

Although your reasoning is sound, Project Server 2007 lacks the capability
you seek. To show the data you need, you could always export a Project
Center view to Excel on a regular basis, and then save the workbook. You
would need to copy and paste the data into a larger workbook showing trends.
Hope this helps.

 

[Chak]

Our experience in project management tell us that the current status
project is so important as the trend of last periods. The trend
analysis is important because tell us how the project reaches the
current status. For example, a risk that has a probability of 10% and
in the next period 20% and then 30%. Probability of 30% is not a big
concern, but the trend shows us that the probability is growing fast
and we must do something. This kind of behaviour is the difference
between proactive project management and reactive one.

Regards,

Hello Ricardo,

As Dale mentioned, there is no option on out of box functionality to
fulfill your requirements. MS EPM meant to provide the current status
of the project. If you want to have the ability to plot the project
trend, you have to develop your own process and system to do meet your
needs.

I am suggesting the following options, automation vs. manual.

Based on your comfort level, you can use either of the approach to
meet your requirements.

1) Automated Approach - SQL Server method:

Create new table EPM_Project_Status on Reporting database

OR

Create new database called "EPM Trend"

Create new table called EPM_Project_Status (Proj_Name,
Percent_complete,
Archival_Date)


Setup SQL Server view called "vw_project_status" as follows (Please
note, I excluded the master projects here, if you need master projects
also, please remove where condition on query)

SELECT MSP_EpmProject.ProjectName,MSP_EpmTask.TaskPercentCompleted,
GETDATE() AS StatusDate
FROM MSP_EpmTask INNER JOIN
MSP_EpmProject ON MSP_EpmTask.ProjectUID = MSP_EpmProject.ProjectUID
WHERE MSP_EpmTask.TaskOutlineLevel = 0 AND
MSP_EpmTask.TaskOutlineNumber = 0
AND MSP_EpmProject.ProjectType <> 6
ORDER BY MSP_EpmProject.ProjectName


Setup SQL Stored procedure "Archive Project Status" with following
query "Insert into EPM_Project_Status select * from vw_project_status"

Schedule stored procedure to run on weekly basis (may be on every
Friday at 5 PM)

Set up SRS Matrix report (Proj Name, Archival Date as Column,
Percent_Complete
as value)

************************************************************************

2) MS Access Solution:

Create MS Access database called "EPM Trend"

Create table called EPM_Project_Status (Proj_Name, Percent_complete,
Archival_Date)

As Dale mentioned above, you have to export excel sheet from PWA on
weekly basis and
upload excel file in to Access database, choose EPM_Project_Status
table and
choose append data option on every upload; also specify Archival Date
(current date) for each row

Create and run cross tab query (Proj Name, Archival Date as Column,
Percent_Complete
as value)

****************************************************


Example:

EPM_Project_Status table Data as of First Week:

PROJA 20% 1/1/2008

PROJB 15% 1/1/2008


EPM_Project_Status table Data as of Second Week:

PROJA 25% 1/8/2008

PROJB 18% 1/8/2008




Trend Report Layout:


1/1/08 1/8/08

ProjA 20% 25%

ProjB 15% 18%


************************************************

Thanks
Chak
http://www.epmcentral.com

 

[ricardo.marques01]

Thank you Chak for your reply, it's very usefull, i'm going to study
it.