AnsweredAssumed Answered

2 Dimension Query. Need help with portlet

Question asked by Atul.K on Feb 1, 2010
Latest reply on Feb 10, 2010 by Atul.K
I have created two dimension query. But the porlet displays every record in a seperate line instead of one line.Please advise on what is wrong with the query and/or portlet  Here is the NSQL.....   SELECT     @SELECT:DIM:USER_DEF:IMPLIED:COST:ID:ID@,
                @SELECT:DIM_PROP:USER_DEF:IMPLIED:COST:jdi_project_id:project_id@,
                @SELECT:DIM_PROP:USER_DEF:IMPLIED:COST:jdi_company:jdi_company@,
                @SELECT:DIM_PROP:USER_DEF:IMPLIED:COST:jdi_type:jdi_type@,
                @SELECT:DIM_PROP:USER_DEF:IMPLIED:COST:prj_name:prj_name@,
                @SELECT:DIM_PROP:USER_DEF:IMPLIED:COST:comp_name:comp_name@,
                @SELECT:DIM_PROP:USER_DEF:IMPLIED:COST:jdi_division:jdi_division@,
                @SELECT:DIM_PROP:USER_DEF:IMPLIED:COST:div_name:div_name@,
                @SELECT:DIM_PROP:USER_DEF:IMPLIED:COST:cost_type:cost_type@,
                @SELECT:DIM:USER_DEF:IMPLIED:COST2:jdi_period:period_id@,
                @SELECT:DIM_PROP:USER_DEF:IMPLIED:COST2:period_name:period_name@,
                @SELECT:METRIC:USER_DEF:IMPLIED:jdi_cost:jdi_cost@
FROM
(select data.*
from

(select (jdi_period || jdi_company ||jdi_project_id||
jdi_type|| cost_type|| jdi_division) id,
 jdi_period, jdi_company,jdi_project_id,
jdi_type, cost_type, sum(jdi_cost) jdi_cost,
prj_name, comp_name, jdi_division,
div_name, period_name
from

(SELECT cost.jdi_period, cost.jdi_company,cost.jdi_project_id,
cost.jdi_type, 'Steady State' cost_type, sum(jdi_ss_cost) jdi_cost,
inv.name prj_name, co.name comp_name, co.jdi_division,
jdiv.name div_name, prd.period_name
FROM sst094p.odf_ca_jdi_ss_growth_cost cost, sst094p.inv_investments inv,
sst094p.odf_ca_jdi_company co, sst094p.biz_com_periods prd,
(SELECT lookup_code, name
            FROM sst094p.cmn_lookups_v
            WHERE language_code = 'en'
                        AND lookup_type = 'JDI_DIVISION') jdiv

WHERE   cost.jdi_project_id = inv.id
AND cost.jdi_company = co.code
AND cost.jdi_period = prd.id
AND jdiv.lookup_code = co.jdi_division

GROUP BY
cost.jdi_period, cost.jdi_company,cost.jdi_project_id,
cost.jdi_type,inv.name, co.name, co.jdi_division,
jdiv.name, prd.period_name

union

SELECT cost.jdi_period, cost.jdi_company,cost.jdi_project_id,
cost.jdi_type, 'Growth' cost_type, sum(jdi_growth_cost) jdi_cost,
inv.name prj_name, co.name comp_name, co.jdi_division,
jdiv.name div_name, prd.period_name
FROM sst094p.odf_ca_jdi_ss_growth_cost cost, sst094p.inv_investments inv,
sst094p.odf_ca_jdi_company co, sst094p.biz_com_periods prd,
(SELECT lookup_code, name
            FROM sst094p.cmn_lookups_v
            WHERE language_code = 'en'
                        AND lookup_type = 'JDI_DIVISION') jdiv

WHERE   cost.jdi_project_id = inv.id
AND cost.jdi_company = co.code
AND cost.jdi_period = prd.id
AND jdiv.lookup_code = co.jdi_division

GROUP BY
cost.jdi_period, cost.jdi_company,cost.jdi_project_id,
cost.jdi_type,inv.name, co.name, co.jdi_division,
jdiv.name, prd.period_name


union

SELECT cost.jdi_period, cost.jdi_company,cost.jdi_project_id,
cost.jdi_type, 'Innovation' cost_type, sum(jdi_innovation_cost) jdi_cost,
inv.name prj_name, co.name comp_name, co.jdi_division,
jdiv.name div_name, prd.period_name
FROM sst094p.odf_ca_jdi_ss_growth_cost cost, sst094p.inv_investments inv,
sst094p.odf_ca_jdi_company co, sst094p.biz_com_periods prd,
(SELECT lookup_code, name
            FROM sst094p.cmn_lookups_v
            WHERE language_code = 'en'
                        AND lookup_type = 'JDI_DIVISION') jdiv

WHERE   cost.jdi_project_id = inv.id
AND cost.jdi_company = co.code
AND cost.jdi_period = prd.id
AND jdiv.lookup_code = co.jdi_division

GROUP BY
cost.jdi_period, cost.jdi_company,cost.jdi_project_id,
cost.jdi_type,inv.name, co.name, co.jdi_division,
jdiv.name, prd.period_name

)

GROUP BY
jdi_period, jdi_company,jdi_project_id,
jdi_type, cost_type,
prj_name, comp_name, jdi_division,
div_name, period_name

having Sum(jdi_cost) > 0

)data)

WHERE           @FILTER@
HAVING     @HAVING_FILTER@

Attachments

Outcomes