Clarity

  • 1.  Resource Baseline usage in a monthly base

    Posted Apr 18, 2018 09:40 AM

    Hello all,

     

    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 :

    SELECT

    II.NAME AS PROJECT_NAME,

    PB.NAME AS BASELINE,

    SR.FULL_NAME AS RESOURCE_NAME,

    SUM(ISNULL(BASE.SLICE,0)/8 ) AS BASE_USAGE,

    BASE.SLICE_DATE,

    BASE.PRJ_OBJECT_ID

    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,

    PBS.SLICE SLICE,

    PBS.SLICE_DATE SLICE_DATE,

    PBSR.REQUEST_NAME

    FROM PRJ_BLB_SLICES PBS, PRJ_BLB_SLICEREQUESTS PBSR

    WHERE PBS.SLICE_REQUEST_ID=PBSR.ID

    AND PBSR.REQUEST_NAME IN ('MONTHLYCURRENTBASEPROJUSAGE ')) BASE ON PA.PRID=BASE.PRJ_OBJECT_ID

    WHERE PBD.OBJECT_TYPE='ASSIGNMENT'

    GROUP BY

    II.NAME,

    PB.NAME,

    SR.FULL_NAME,

    BASE.PRJ_OBJECT_ID,

    BASE.SLICE_DATE

     

    Do you have any ideas ?

    Thank you in advance,

    Karim



  • 2.  Re: Resource Baseline usage in a monthly base

    Broadcom Employee
    Posted May 11, 2018 04:30 PM

    Karim,

     

    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 '



  • 3.  Re: Resource Baseline usage in a monthly base

    Posted May 14, 2018 01:13 AM

    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.