AnsweredAssumed Answered

Three Dimensional Grid Portlet Problem

Question asked by demca02 Employee on Jun 10, 2015
Latest reply on Jun 10, 2015 by nick_darlington

Hello All,



My three dimensional portlet doesnt group different dimensions data and its listed with multiple rows. 

Untitled picture.png


I'm trying to make a three dimensional portlet that shows quarterly cost and benefit's planned,actual and variance values for each particular project.


I planned a three dimensionala portlet to show the data. My dimensions will be;

1. Projects

2. Quarter

3. Financial Metric (Cost, benefit etc.)


Than I planned a query that will produce a dataset as follows;

NameQuarterType Planned ActualVariace
TFF-1 Q1Cost123
TFF-1 Q2Cost456
TFF-1 Q3Cost789
TFF-1 Q4Cost123
TFF-1 Q1Benefit456
TFF-1 Q2Benefit789
TFF-1 Q3Benefit123
TFF-1 Q4Benefit456



I ve just made a sample query that would provide cost side data only,








from (

select 'PC-'+cal.time_key+i.code uid, cal.time_key, i.code, 'Cost' type,, i.schedule_start, i.schedule_finish, cal.period_start_date, cal.period_end_date, isnull(s.slice, 0) pln, isnull(actcost.act,0) act, isnull(s.slice, 0)-isnull(actcost.act,0) var from

nbi_dim_calendar_time cal

inner join inv_investments i

on (i.schedule_start <   DATEADD(yy, DATEDIFF(yy,0,cal.period_end_date) + 1, -1) and i.schedule_finish > DATEADD(yy, DATEDIFF(yy,0,cal.period_start_date), 0)  )

left join ODF_OBJECT_INSTANCE_MAPPING as map on map.PRIMARY_OBJECT_INSTANCE_ID = i.ID and primary_object_instance_code = 'project'


left join odf_sl_plan_cost s on = s.prj_object_id  and s.slice_request_id = '5003001' and s.slice_date = cal.period_start_date

left join (

select investment_id, DATEADD(qq, DATEDIFF(qq, 0, transdate), 0) q,  SUM(wv.totalcost) act

FROM   ppa_wip w

JOIN   ppa_wip_values wv

       ON  w.transno = wv.transno

       AND UPPER(wv.currency_type) = 'HOME'

where status = 0

group by investment_id, DATEADD(qq, DATEDIFF(qq, 0, transdate), 0)

) actcost on = actcost.investment_id and q = cal.period_Start_date

where cal.hierarchy_level = 'QUARTER'

and cal.year = '2015'

and i.odf_object_code = 'project'


) x




Output should be one row for each project but its multiple rows;

Untitled picture.png



Do you have any suggestion for my problem?


Do you have any multi-dimensional portlets that has more than 2 dimensions?


Best Regards