I am building a query to get the resource baseline usage in a monthly base as we can do it on Actuals and ETC.
I tried to use this slice MONTHLYCURRENTBASEPROJUSAGE but the query did not return any data. below the query that I built :
II.NAME AS PROJECT_NAME,
PB.NAME AS BASELINE,
SR.FULL_NAME AS RESOURCE_NAME,
SUM(ISNULL(BASE.SLICE,0)/8 ) AS BASE_USAGE,
FROM INV_INVESTMENTS II
LEFT OUTER JOIN PRTASK PT ON II.ID=PT.PRPROJECTID
LEFT OUTER JOIN PRASSIGNMENT PA ON PT.PRID=PA.PRTASKID
LEFT OUTER JOIN PRJ_BASELINE_DETAILS PBD ON PA.PRID=PBD.OBJECT_ID
LEFT OUTER JOIN PRJ_BASELINES PB ON PBD.BASELINE_ID=PB.ID
LEFT OUTER JOIN SRM_RESOURCES SR ON PA.PRRESOURCEID=SR.ID
LEFT OUTER JOIN (SELECT PBS.PRJ_OBJECT_ID,
FROM PRJ_BLB_SLICES PBS, PRJ_BLB_SLICEREQUESTS PBSR
AND PBSR.REQUEST_NAME IN ('MONTHLYCURRENTBASEPROJUSAGE ')) BASE ON PA.PRID=BASE.PRJ_OBJECT_ID
Do you have any ideas ?
Thank you in advance,
Just a quick observation:
Your query has the line
AND PBSR.REQUEST_NAME IN ('MONTHLYCURRENTBASEPROJUSAGE ')
Try taking out the extra space at the end of 'MONTHLYCURRENTBASEPROJUSAGE '
Please also consider adding 'pb.is_current = 1'.
I think that the query may still not returning correct values, but this is just based on 'cut-paste' of the SQL and running against one of our PPM environments where the values didn't match back to some data. Sorry, don't have the capacity to fully review, but hopefully Karim will be able to confirm.