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,

 

Summary:

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,

select

@SELECT:DIM:USER_DEF:IMPLIED:INV:x.uid:uid@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:INV:x.name:name@,

@SELECT:DIM:USER_DEF:IMPLIED:Q:x.time_key:time_key@,

@SELECT:DIM:USER_DEF:IMPLIED:FIN:x.type:type@,

@SELECT:METRIC:USER_DEF:IMPLIED:x.pln:planned@,

@SELECT:METRIC:USER_DEF:IMPLIED:x.act:actual@,

@SELECT:METRIC:USER_DEF:IMPLIED:x.var:variance@

from (

select 'PC-'+cal.time_key+i.code uid, cal.time_key, i.code, 'Cost' type,i.name, 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'

inner join FIN_FINANCIALS f on  f.ID = map.SECONDARY_OBJECT_INSTANCE_ID  

left join odf_sl_plan_cost s on f.id = 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 i.id = actcost.investment_id and q = cal.period_Start_date

where cal.hierarchy_level = 'QUARTER'

and cal.year = '2015'

and i.odf_object_code = 'project'

and @WHERE:SECURITY:PROJECT:i.id@

) x

WHERE @FILTER@

 

 

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

 

Çağıl

Outcomes