AnsweredAssumed Answered

Capacity Planning portlet, we want to see 0 as value when empty spaces in n-dimensional grid portlet

Question asked by Nithesh on Apr 29, 2016
Latest reply on Apr 29, 2016 by sridhar_bandaru

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!!

Attachments

Outcomes