Cumulative Earned Value

[Kris]

QUESTION:
What is the SQL statement to extract cumulative earned value data from
the Project Server database?

GOAL:
Display an earned value line chart/graph depicting ACWP, BCWP, and
BCWS as cumulative values over time. Extracting the data using SQL
queries against the Project Server database and then graphing the data
using Access or Excel is desired. Looking for as much automated data
manipulation as possible.

EXAMPLE:
Jan. Feb. Mar.
ACWP 1 1 1
BCWP 2 2 2
BCWS 3 3 3

Jan. Feb. Mar.
Cumulative ACWP 1 2 3
Cumulative BCWP 2 4 6
Cumulative BCWS 3 6 9
Would like to graphically display these Cumulative values for each
month.

REFERENCE POINT:
MS Project is able to display cumulative earned value data by going to
View-Reports, selecting the Cost Report, editing the Cash Flow report
(select Month and BCWP), and then viewing the Cash Flow report.

ATTEMPTED SQL:
This SQL statement gets close to what we are looking for; however, the
data is a monthly sum of earned value and not the month by month
accumulation of earned value. Also, this "consumes" the earned value
in the month the task started and does not distribute the tasks earned
value over the course of the task.

SELECT
dbo_MSP_PROJECTS.PROJ_NAME,
CStr(Month(dbo_MSP_TASKS.TASK_START_DATE))+"-"+
CStr(Year dbo_MSP_TASKS.TASK_START_DATE)) AS TheMonth,
Sum(dbo_MSP_TASKS.TASK_ACWP)/100 AS ACWP,
Sum(dbo_MSP_TASKS.TASK_BCWP)/100 AS BCWP,
Sum(dbo_MSP_TASKS.TASK_BCWS)/100 AS BCWS
FROM
dbo_MSP_TASKS,
dbo_MSP_PROJECTS,
dbo_MSP_WEB_PROJECTS
WHERE
(((Year(dbo_MSP_TASKS.TASK_START_DATE)) Is Not Null)
And ((dbo_MSP_TASKS.TASK_IS_SUMMARY)=0)
And ((dbo_MSP_TASKS.PROJ_ID)=dbo_MSP_PROJECTS.PROJ_ID)
And ((dbo_MSP_PROJECTS.PROJ_ID)=dbo_MSP_WEB_PROJECTS.PROJ_ID))
And dbo_MSP_WEB_PROJECTS.WSTS_SERVER_ID=1
GROUP BY
dbo_MSP_PROJECTS.PROJ_NAME,
Month(dbo_MSP_TASKS.TASK_START_DATE),
Year(dbo_MSP_TASKS.TASK_START_DATE);

 

[James Garoutsos]

I am also looking for this type of solution without having to automate
Project, inserting different status dates and reading the database. Any
help would be appricated.

Thanks

James