Project Level SPI, SV, CPI & CV in the datamodel?

[Robert_Ensinger]

What's the best way to get project level SPI, SV, CPI & CV in a query?

I haven't found a column or a view that just presents this yet - although I could be missing it.
If not readily available, calculate it dynamically out of the project level data? Or roll it up out of the task level data?

I'm attempting to reproduce a view such as this (from the project object). My goal is to use this on portfolio dashboards.

Thanks!

.

[Anon Anon]

Pure guesswork : There are a CPI_NUMBER and SPI_NUMBER attributes in the NBI_ tables for projects whihc appear to be calculated in the NBI_PROJECT_CURRENT_FACTS_SP procedure... no sign of SV or CV though. :sad:

[Deactivated User]

Looking at the project (and task attributes) they appear to be virtual, which to me means that they do not exist in the database but are calculated in the fly.
Therefore you cannot have them in a query based portlet as db fields, instead they have to be calculated using their formulas.
The option is to use the project object as the data provider for the portlet, but that ways you would be limited to the project attributes (and cusotom attributes) only.

Martti K.

[Robert_Ensinger]

Thanks Dave & Martti - I wanted to make sure I wasn't missing something. If neither of you two see 'em in the standard tables (we don't run the datamart), I'm going to assume it's not there as a column and it's time for calculating in the query.

According to the ERD, the PRJ_EV_HISTORY is where you should go for the pieces - BCWS, BCWP, ACWS & ACWP. As an example, the following query reproduces the data in the "object based portlet" view above.

SELECT 
PM.FULL_NAME
, I.NAME
, I.STATUS_INDICATOR
, I.STATUS_COMMENT
, (EV.ITD_BCWP/EV.ITD_BCWS) AS SPI
, (EV.ITD_BCWP - EV.ITD_BCWS) AS SV
, (EV.ITD_BCWP/EV.ITD_ACWP) AS CPI
, (EV.ITD_BCWP - EV.ITD_ACWP) AS CV
, I.SCHEDULE_START
, I.SCHEDULE_FINISH
FROM INV_INVESTMENTS I
JOIN SRM_RESOURCES PM ON I.MANAGER_ID = PM.USER_ID
JOIN PRJ_EV_HISTORY EV ON I.ID = EV.PROJECT_ID
WHERE EV.OBJECT_TYPE = 'Project'
AND EV.PERIOD_INTERVAL IS NULL
AND I.ID = 5089718

Thanks!
.