07 Mei 2010 17:27
I'm trying to get a quick look at the values for an application that I've developed, and I'm not using PSI. I'm just trying to get ACWP, BCWP, etc values for each project summary task.
I'm trying to use the following query in the published database of the RC version:
A.PROJ_UID, A.PROJ_NAME, A.MOD_DATE, A.PROJ_INFO_STATUS_DATE, B.TASK_UID, B.TASK_NAME, B.TASK_ACWP, B.TASK_BCWP, B.TASK_BCWS,B.TASK_SV,B.TASK_CV,TASK_SPI, TASK_CPI, TASK_TCPI FROM MSP_PROJECTS A, MSP_TASKS B, MSP_TASK_BASELINES
WHERE A.PROJ_INFO_STATUS_DATE IS NOT NULL AND A.PROJ_UID = B.PROJ_UID AND B.TASK_UID = MSP_TASK_BASELINES.TASK_UID AND MSP_TASK_BASELINES.TB_BASE_NUM = 0 AND (task_outline_level LIKE '0') order by task_acwp
When i execute the query, I manage to get a list of project summary tasks with the columns that i need.
The problem here is that ACWP, BCWP, BCWS, etc has 0 in these columns and when open the project and see the values of these columns, these values are allways greater than 10000.
The view in PWA has the correct values but the result query doesnt.
So my question here is:
Where does the published database store the values for the columns:
The test i made to validate these results was:
Opened project 2010, created a new project, new tasks, new resources, saved base line and changed the phisical % complete in the tasks with different status dates. Saved it, published it and closed the project.
When i open this project in Project 2010, i can see the bcws, bcwp, acwp, etc values. Did the same validation in PWA site, checked the values and there they were.
When i run the query above, all of these values in the published and archived database were 0.
This happend because I've used a project server 2007 database and did an installation of 2010 RC with that database (BCM).
Using a project server 2010 database with no BCM, did the same test. Created a project, etc, etc and voila: if i run the above query, all of the columns have non zero values.
So I think that if we have BCM active, there maybe other columns that are beeing used to store these values.
I really need to know where these columns are (table name).
12 Mei 2010 4:24Moderator
The Published DB is not documented, and queries of the Published DB are not supported. All of the fields you are interested in are in the MSP_EpmTask_UserView view in the Reporting DB.
- Ditandai sebagai Jawaban oleh Alexander Burton [MVP]MVP, Moderator 12 Mei 2010 11:22