Hello,
I have created a new n-dimensional grid portlet for capacity planning to see resources' availability and allocation to 4 projects.
I have created new allocation & availability weekly timeslices with roll over interval as semi annually.
Query development:
SELECT @SELECT:DIM:USER_DEF:IMPLIED:RESOURCE:Res_id:res_id@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:res_FULL_NAME:res_full_name@,
@SELECT:DIM:USER_DEF:IMPLIED:project:inv_id:inv_id@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:project:inv_name:inv_name@,
@SELECT:DIM:USER_DEF:IMPLIED:per:period:PERIOD@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:per:display_date:display_date@,
@SELECT:METRIC:USER_DEF:IMPLIED:total:Total@
from
(
select res.id res_id,res.FULL_NAME res_full_name,inv.id inv_id,inv.name inv_name,
periods.start_date PERIOD,TO_CHAR(periods.start_date,'MM/DD/YYYY') display_date,nvl(0+SUM(slices.slice),0) total
from PRJ_BLB_SLICES slices ,prteam,INV_INVESTMENTS inv ,srm_resources res,
(SELECT WEEK_KEY
, min(day) start_date
, max(day) end_date
FROM nbi_dim_calendar_time
GROUP BY WEEK_KEY
) periods
where slices.slice_request_id = 5002001
and slices.PRJ_OBJECT_ID(+) = PRTEAM.PRID
and slices.SLICE_DATE >= periods.start_date
AND slices.SLICE_DATE <= periods.end_date
and prteam.prresourceid = res.id
and prteam.prprojectid(+) = inv.id
and res.person_type !=0
and inv.code in ('PR83757','PR83759','PR83758')
and slices.slice_date between @where:param:user_def:date:startdate@ and @where:param:user_def:date:enddate@
GROUP BY res.id, periods.start_date,res.full_name,inv.name,inv.id
union
select res.id res_id,res.FULL_NAME res_full_name,1000 inv_id,'OFF' AS inv_name,
periods.start_date PERIOD,TO_CHAR(periods.start_date,'MM/DD/YYYY') display_date,nvl(0+(40-SUM(slices.slice)),0) total
from PRJ_BLB_SLICES slices ,PRJ_RESOURCES PRS,srm_resources res,
(SELECT WEEK_KEY
, min(day) start_date
, max(day) end_date
FROM nbi_dim_calendar_time
GROUP BY WEEK_KEY
) periods
where slices.slice_request_id IN (5002001,5002002)
and slices.SLICE_DATE >= periods.start_date
AND slices.SLICE_DATE <= periods.end_date
AND slices.PRJ_OBJECT_ID = res.ID
AND ReS.ID = PRS.PRID
AND PRS.PRISROLE = 0
and slices.slice_date between @where:param:user_def:date:startdate@ and @where:param:user_def:date:enddate@
GROUP BY periods.start_date,res.full_name,res.id
)
WHERE @FILTER@
In portlet when we filter the data we see results as (Please see cpr.jpg)
and when we filter data by providing a resource with zero allocations to those 3 projects in query we see results as (Please see cpr1.jpg)
The requirement is to show "0" value for all the projects even if that resource doesn't any allocations to those projects.
Can anyone please suggest.. Many Thanks in advance!!