Clarity

Expand all | Collapse all

PRJ_BLB_SLICES_M_BASE question

Jump to Best Answer
  • 1.  PRJ_BLB_SLICES_M_BASE question

    Posted 05-28-2014 07:35 AM

    Hi!

    Could someone share some light about the table: PRJ_BLB_SLICES_M_BASE. As I understood, this is resources monthly baseline allocations (time slices) and that what i want (i want to get some specific project baselined team & monthly allocation). Looking clarity UI - i know the data is somewhere stored but i could not build a query by myself. Could you suggest, what table/field should be joined with PRJ_BLB_SLICES_M_BASE.PRJ_OBJECT_ID (whatever I'll try - don't get no slices).

    SELECT
    pb.NAME,
    r.FULL_NAME,
    s.SLICE,
    s.SLICE_DATE
    FROM
    PRJ_BASELINES pb
    INNER JOIN PRJ_BASELINE_DETAILS pd ON pd.BASELINE_ID = pb.ID AND pd.OBJECT_TYPE = 'TEAM'
        INNER JOIN niku.PRTEAM tm ON pd.OBJECT_ID = tm.PRID
        INNER JOIN niku.SRM_RESOURCES r ON tm.PRROLEID = r.ID
    LEFT JOIN PRJ_BLB_SLICES_M_BASE s ON tm.PRID = s.PRJ_OBJECT_ID
    WHERE
    pb.ID = xxxx AND
    pb.IS_CURRENT = 1


  • 2.  RE: PRJ_BLB_SLICES_M_BASE question

    Posted 05-28-2014 09:28 AM
      |   view attached

    Hi Janurva,

    How you reached on PRJ_BLB_SLICES_M_BASE table?

    If you are looking for resources monthly baseline allocations, don't you think you need to use Timeslice ID 312 (MONTHLYRESOURCEBASECURVE)?

    Thanks,

    Georgy



  • 3.  RE: PRJ_BLB_SLICES_M_BASE question

    Posted 05-28-2014 09:38 AM

    Hi!

    I tried several options and nothing worked for me -> then i reached for this M_BASE table :)

    Anyway, now i tried your suggestion for using slice ID 312 (then the table is PRJ_BLB_SLICES). Did the following query and still did not got any results:

    SELECT
    pb.NAME,
    r.FULL_NAME,
    s.SLICE,
    s.SLICE_DATE
    FROM 
    PRJ_BASELINES pb
    INNER JOIN PRJ_BASELINE_DETAILS pd ON pd.BASELINE_ID = pb.ID AND pd.OBJECT_TYPE = 'TEAM'
        INNER JOIN niku.PRTEAM tm ON pd.OBJECT_ID = tm.PRID 
        INNER JOIN niku.SRM_RESOURCES r ON tm.PRROLEID = r.ID 
    LEFT JOIN PRJ_BLB_SLICES s ON tm.PRID = s.PRJ_OBJECT_ID AND s.SLICE_REQUEST_ID = 312
    WHERE
    pb.ID = xxxx AND
    pb.IS_CURRENT = 1


  • 4.  RE: PRJ_BLB_SLICES_M_BASE question
    Best Answer

    Posted 05-29-2014 06:16 AM

    Hi!

    Ok, i think i finally figure out the data logic regarding baseline. It seems, that when you save the baseline the resources time slices data is not saved (because the baseline = allocation). But when you change the allocation after the baseline is saved, then the baseline (previous) allocation is alos saved.

    Most probably there is a possibility to write better SQL, but it seems to work for me.

    -- if there is saved baseline
    SELECT
    full_name,
    res_id,
    role_id,
    role_name,
    SUM(ISNULL(avail_hrs,0)) avail_hrs,
    SUM(ISNULL(alloc_hrs,0)) alloc_hrs,
    SUM(ISNULL(actual_hrs,0)) actual_hrs,
    CASE 
          WHEN SUM(ISNULL(baseline_hrs/8,0)) = SUM(ISNULL(baseline1_hrs/8,0)) THEN SUM(ISNULL(baseline1_hrs/8,0))
          ELSE SUM(ISNULL(baseline_hrs/8,0))
    END AS baseline_hrs,
    slice_date
    FROM (
    -- if there is changed allocation in the project (meaning there are values in PRJ_BLB_SLICES_M_BASE) then use this
    SELECT
    r1.ID res_id,
    r1.full_name,
    tm1.ROLE_ID role_id,
    r1.full_name role_name,
    0 avail_hrs,
    0 alloc_hrs,
    0 actual_hrs,
    SUM(ISNULL(s1.slice,0)) baseline_hrs,
    0 baseline1_hrs,
    s1.slice_date
    FROM PRJ_BASELINES pb
    INNER JOIN PRJ_BASELINE_DETAILS pd1 ON pd1.BASELINE_ID = pb.ID AND pd1.OBJECT_TYPE = 'ASSIGNMENT'
    INNER JOIN PRASSIGNMENT tm1 ON pd1.OBJECT_ID = tm1.PRID
    INNER JOIN niku.SRM_RESOURCES r1 ON tm1.ROLE_ID = r1.ID
    LEFT JOIN PRJ_BLB_SLICES_M_BASE s1 ON pd1.ID = s1.PRJ_OBJECT_ID
    WHERE
    pb.IS_CURRENT = 1
    AND pb.PROJECT_ID = ***
    GROUP BY r1.ID, r1.full_name, tm1.ROLE_ID, s1.slice_date, s1.SLICE
    UNION 
    --  if there is no changed allocation compared to baseline in the project use allocation values PRJ_BLB_SLICES_M_ALC
    SELECT
    r.ID res_id,
    r.full_name,
    tm.PRROLEID role_id,
    r.full_name role_name,
    0 avail_hrs,
    0 alloc_hrs,
    0 actual_hrs,
    0 baseline_hrs,
    SUM(ISNULL(s.slice,0)) baseline1_hrs,
    s.slice_date
    FROM PRJ_BASELINES pb
    INNER JOIN PRJ_BASELINE_DETAILS pd ON pd.BASELINE_ID = pb.ID AND pd.OBJECT_TYPE = 'TEAM'
    INNER JOIN niku.PRTEAM tm ON pd.OBJECT_ID = tm.PRID 
    INNER JOIN niku.SRM_RESOURCES r ON tm.PRROLEID = r.ID
    LEFT JOIN PRJ_BLB_SLICES_M_ALC s ON tm.PRID = s.PRJ_OBJECT_ID
    WHERE
    pb.IS_CURRENT = 1
    AND pb.PROJECT_ID = ***
    GROUP BY r.ID, r.full_name, tm.PRROLEID, s.slice_date, s.SLICE
    ) A
    WHERE
    slice_date BETWEEN DATEADD(yy, DATEDIFF(yy,0,GETDATE()), 0) AND GETDATE()
    GROUP BY full_name, res_id, role_id, role_name, slice_date
    order by SLICE_DATE


  • 5.  Re: PRJ_BLB_SLICES_M_BASE question

    Broadcom Employee
    Posted 06-16-2014 02:05 PM

    The 'PRJ_BLB_SLICES_M_BASE' is an INTERNAL, INSTA-SLICE that is limited in date range scope; this may cause you to not show all relevant data as you might expect if you have long date ranges.  Therefore as 'georgy' mentioned, you should use Time Slice ID = 312 (MONTHLYRESOURCEBASECURVE) or create your own user-defined time slice definition.