AnsweredAssumed Answered

Xcelsius Dashboard in custom Object

Question asked by StefaniaLorenzini1317912 on Dec 6, 2012
Latest reply on May 31, 2013 by rayd_indian
Hello, I created a query that operates regularly on a custom object of Clarity v. 13, which contains a number of fields @ param, according to which the query totals.

How can I use the query in Xcelsius? how can I reference these fields? The variables flash allow me only to reference the fields in the object ...

The below is the query I am using.

Thanks,




SELECT
@SELECT:DIM:USER_DEF:IMPLIED:BDG:t1.obs || '_' || t1.cons_nocons || '_' || t1.wbs_capex_opex || '_' || t1.tipo_wbs || '_' || t1.wbs_category:ID@
,@SELECT:DIM_PROP:USER_DEF:IMPLIED:BDG:t1.obs:OBS@
,@SELECT:DIM_PROP:USER_DEF:IMPLIED:BDG:t1.cons_nocons:cons_noncons@
,@SELECT:DIM_PROP:USER_DEF:IMPLIED:BDG:(CASE WHEN t1.cons_nocons = 'CS' then 'Consolidato'
WHEN t1.cons_nocons = 'w' then 'Consolidato'
WHEN t1.cons_nocons = 'NC' then 'Non-Consolidato' else null end):excons_noncons@
,@SELECT:DIM_PROP:USER_DEF:IMPLIED:BDG:t1.wbs_capex_opex:capex_opex@
,@SELECT:DIM_PROP:USER_DEF:IMPLIED:BDG:t1.xname:excapex_opex@
,@SELECT:DIM_PROP:USER_DEF:IMPLIED:BDG:t1.tipo_wbs:tipo_wbs@
,@SELECT:DIM_PROP:USER_DEF:IMPLIED:BDG:t1.wbs_category:categoria@
,@SELECT:DIM_PROP:USER_DEF:IMPLIED:BDG:t1.CNAME:eXcategoria@
,@SELECT:PARAM:USER_DEF:STRING:forecast_anno_precedente:fr_precedente@
,@SELECT:PARAM:USER_DEF:STRING:anno_precedente:anno_precedente@
,@SELECT:PARAM:USER_DEF:STRING:forecast_anno_corrente:fr_corrente@
,@SELECT:PARAM:USER_DEF:STRING:anno_corrente:anno_corrente@
,@SELECT:METRIC:USER_DEF:IMPLIED:sum(nvl(t1.IMPORTO_ANNO_CORRENTE,0)):importo_anno_corrente@
,@SELECT:METRIC:USER_DEF:IMPLIED:sum(nvl(t1.IMPORTO_ANNO_PRECEDENTE,0)):importo_anno_precedente@

FROM
((SELECT
ribalta.FIAT_ANNORIF || '_' || op.name || '_' || entirib.fiat_raggrupp || '_' || WBS.FIAT_WBS_CAPEX_OPEX || '_' || WBS.fiat_wbs_type || '_' || wbs.fiat_wbs_category ID
,ribalta.FIAT_ANNORIF ANNORIF
,op.name OBS
,CAT.NAME CNAME
,CAT1.NAME XNAME
,(case
when @WHERE:PARAM:USER_DEF:STRING:forecast_anno_precedente@ = 'F1' then sum(nvl(ribalta.fiat_ribf1,0))
when @WHERE:PARAM:USER_DEF:STRING:forecast_anno_precedente@ = 'F2' then sum(nvl(ribalta.fiat_ribf2,0))
when @WHERE:PARAM:USER_DEF:STRING:forecast_anno_precedente@ = 'F3' then sum(nvl(ribalta.fiat_ribf3,0))
when @WHERE:PARAM:USER_DEF:STRING:forecast_anno_precedente@ = 'B_01' then sum(nvl(ribalta.fiat_ribbdg,0))
when @WHERE:PARAM:USER_DEF:STRING:forecast_anno_precedente@ = 'F_01' then sum(nvl(ribalta.fiat_ribgen,0))
when @WHERE:PARAM:USER_DEF:STRING:forecast_anno_precedente@ = 'F_02' then sum(nvl(ribalta.fiat_ribfeb,0))
when @WHERE:PARAM:USER_DEF:STRING:forecast_anno_precedente@ = 'F_03' then sum(nvl(ribalta.fiat_ribmar,0))
when @WHERE:PARAM:USER_DEF:STRING:forecast_anno_precedente@ = 'F_04' then sum(nvl(ribalta.fiat_ribapr,0))
when @WHERE:PARAM:USER_DEF:STRING:forecast_anno_precedente@ = 'F_05' then sum(nvl(ribalta.fiat_ribmag,0))
when @WHERE:PARAM:USER_DEF:STRING:forecast_anno_precedente@ = 'F_06' then sum(nvl(ribalta.fiat_ribgiu,0))
when @WHERE:PARAM:USER_DEF:STRING:forecast_anno_precedente@ = 'F_07' then sum(nvl(ribalta.fiat_riblug,0))
when @WHERE:PARAM:USER_DEF:STRING:forecast_anno_precedente@ = 'F_08' then sum(nvl(ribalta.fiat_ribago,0))
when @WHERE:PARAM:USER_DEF:STRING:forecast_anno_precedente@ = 'F_09' then sum(nvl(ribalta.fiat_ribset,0))
when @WHERE:PARAM:USER_DEF:STRING:forecast_anno_precedente@ = 'F_10' then sum(nvl(ribalta.fiat_ribott,0))
when @WHERE:PARAM:USER_DEF:STRING:forecast_anno_precedente@ = 'F_11' then sum(nvl(ribalta.fiat_ribnov,0))
when @WHERE:PARAM:USER_DEF:STRING:forecast_anno_precedente@ = 'F_12' then sum(nvl(ribalta.fiat_ribdic,0))
else 0 end) IMPORTO_ANNO_PRECEDENTE
,substr(entirib.fiat_raggrupp,1,2) cons_nocons
,WBS.FIAT_WBS_CAPEX_OPEX WBS_capex_opex
,WBS.fiat_wbs_type tipo_wbs
,wbs.fiat_wbs_category wbs_category
,0 IMPORTO_ANNO_CORRENTE
,@WHERE:PARAM:USER_DEF:STRING:forecast_anno_precedente@
,@WHERE:PARAM:USER_DEF:STRING:anno_precedente@
,@WHERE:PARAM:USER_DEF:STRING:forecast_anno_corrente@
,@WHERE:PARAM:USER_DEF:STRING:anno_corrente@



FROM ODF_FIAT_VSIMUL_ENTI_V2 RIBALTA

LEFT OUTER JOIN ODF_fiat_department_V2 entirib ON ribalta.FIAT_ENTERIB=entirib.code
LEFT OUTER JOIN ODF_PROJECT_V2 WBS on RIBALTA.FIAT_WBS=WBS.PROJECT_CODE
INNER JOIN prj_obs_associations assoc on assoc.record_id=wbs.odf_pk
AND assoc.table_name='SRM_PROJECTS'
INNER JOIN prj_obs_units ou on ou.id=assoc.unit_id
INNER JOIN PRJ_OBS_UNITS op on op.id=ou.parent_id

left outer join cmn_lookups_v cat ON cat.lookup_code = wbs.fiat_wbs_category and cat.lookup_type = 'FIAT_WBS_CATEGORY' and cat.language_code = @WHERE:PARAM:LANGUAGE@
left outer join cmn_lookups_v cat1 ON cat1.lookup_code = wbs.FIAT_WBS_CAPEX_OPEX and cat1.lookup_type = 'FIAT_WBS_CAPEX_OPEX' and cat1.language_code = @WHERE:PARAM:LANGUAGE@

WHERE ribalta.fiat_active=1
AND ribalta.fiat_annorif=@WHERE:PARAM:USER_DEF:STRING:anno_precedente@
AND @WHERE:SECURITY:PROJECT:entirib.ODF_PK@
GROUP BY ribalta.FIAT_ANNORIF || '_' || op.name || '_' || entirib.fiat_raggrupp || '_' || WBS.FIAT_WBS_CAPEX_OPEX || '_' || WBS.fiat_wbs_type || '_' || wbs.fiat_wbs_category
,ribalta.FIAT_ANNORIF
,op.name
,substr(entirib.fiat_raggrupp,1,2)
,WBS.FIAT_WBS_CAPEX_OPEX
,WBS.fiat_wbs_type
,wbs.fiat_wbs_category
,CAT1.NAME
,cat.name
,@WHERE:PARAM:USER_DEF:STRING:anno_precedente@
,@WHERE:PARAM:USER_DEF:STRING:forecast_anno_precedente@
,@WHERE:PARAM:USER_DEF:STRING:anno_corrente@
,@WHERE:PARAM:USER_DEF:STRING:forecast_anno_corrente@)

UNION
(SELECT
ribalta.FIAT_ANNORIF || '_' || op.name || '_' || entirib.fiat_raggrupp || '_' || WBS.FIAT_WBS_CAPEX_OPEX || '_' || WBS.fiat_wbs_type || '_' || wbs.fiat_wbs_category ID
,ribalta.FIAT_ANNORIF ANNORIF
,op.name OBS
,CAT.NAME CNAME
,CAT1.NAME XNAME
,0 IMPORTO_ANNO_PRECEDENTE
,substr(entirib.fiat_raggrupp,1,2) cons_nocons
,WBS.FIAT_WBS_CAPEX_OPEX WBS_capex_opex
,WBS.fiat_wbs_type tipo_wbs
,wbs.fiat_wbs_category wbs_category
,(case
when @WHERE:PARAM:USER_DEF:STRING:forecast_anno_corrente@ = 'F1' then sum(nvl(ribalta.fiat_ribf1,0))
when @WHERE:PARAM:USER_DEF:STRING:forecast_anno_corrente@ = 'F2' then sum(nvl(ribalta.fiat_ribf2,0))
when @WHERE:PARAM:USER_DEF:STRING:forecast_anno_corrente@ = 'F3' then sum(nvl(ribalta.fiat_ribf3,0))
when @WHERE:PARAM:USER_DEF:STRING:forecast_anno_corrente@ = 'B_01' then sum(nvl(ribalta.fiat_ribbdg,0))
when @WHERE:PARAM:USER_DEF:STRING:forecast_anno_corrente@ = 'F_01' then sum(nvl(ribalta.fiat_ribgen,0))
when @WHERE:PARAM:USER_DEF:STRING:forecast_anno_corrente@ = 'F_02' then sum(nvl(ribalta.fiat_ribfeb,0))
when @WHERE:PARAM:USER_DEF:STRING:forecast_anno_corrente@ = 'F_03' then sum(nvl(ribalta.fiat_ribmar,0))
when @WHERE:PARAM:USER_DEF:STRING:forecast_anno_corrente@ = 'F_04' then sum(nvl(ribalta.fiat_ribapr,0))
when @WHERE:PARAM:USER_DEF:STRING:forecast_anno_corrente@ = 'F_05' then sum(nvl(ribalta.fiat_ribmag,0))
when @WHERE:PARAM:USER_DEF:STRING:forecast_anno_corrente@ = 'F_06' then sum(nvl(ribalta.fiat_ribgiu,0))
when @WHERE:PARAM:USER_DEF:STRING:forecast_anno_corrente@ = 'F_07' then sum(nvl(ribalta.fiat_riblug,0))
when @WHERE:PARAM:USER_DEF:STRING:forecast_anno_corrente@ = 'F_08' then sum(nvl(ribalta.fiat_ribago,0))
when @WHERE:PARAM:USER_DEF:STRING:forecast_anno_corrente@ = 'F_09' then sum(nvl(ribalta.fiat_ribset,0))
when @WHERE:PARAM:USER_DEF:STRING:forecast_anno_corrente@ = 'F_10' then sum(nvl(ribalta.fiat_ribott,0))
when @WHERE:PARAM:USER_DEF:STRING:forecast_anno_corrente@ = 'F_11' then sum(nvl(ribalta.fiat_ribnov,0))
when @WHERE:PARAM:USER_DEF:STRING:forecast_anno_corrente@ = 'F_12' then sum(nvl(ribalta.fiat_ribdic,0))
else 0 end) IMPORTO_ANNO_CORRENTE
,@WHERE:PARAM:USER_DEF:STRING:forecast_anno_precedente@
,@WHERE:PARAM:USER_DEF:STRING:anno_precedente@
,@WHERE:PARAM:USER_DEF:STRING:forecast_anno_corrente@
,@WHERE:PARAM:USER_DEF:STRING:anno_corrente@


FROM ODF_FIAT_VSIMUL_ENTI_V2 RIBALTA
LEFT OUTER JOIN ODF_fiat_department_V2 entirib ON ribalta.FIAT_ENTERIB=entirib.code
LEFT OUTER JOIN ODF_PROJECT_V2 WBS on RIBALTA.FIAT_WBS=WBS.PROJECT_CODE
INNER JOIN prj_obs_associations assoc on assoc.record_id=wbs.odf_pk
AND assoc.table_name='SRM_PROJECTS'
INNER JOIN prj_obs_units ou on ou.id=assoc.unit_id
INNER JOIN PRJ_OBS_UNITS op on op.id=ou.parent_id

left outer join cmn_lookups_v cat ON cat.lookup_code = wbs.fiat_wbs_category and cat.lookup_type = 'FIAT_WBS_CATEGORY' and cat.language_code = @WHERE:PARAM:LANGUAGE@
left outer join cmn_lookups_v cat1 ON cat1.lookup_code = wbs.FIAT_WBS_CAPEX_OPEX and cat1.lookup_type = 'FIAT_WBS_CAPEX_OPEX' and cat1.language_code = @WHERE:PARAM:LANGUAGE@

WHERE ribalta.fiat_active=1
AND ribalta.fiat_annorif=@WHERE:PARAM:USER_DEF:STRING:anno_corrente@
AND @WHERE:SECURITY:PROJECT:entirib.ODF_PK@
GROUP BY ribalta.FIAT_ANNORIF || '_' || op.name || '_' || entirib.fiat_raggrupp || '_' || WBS.FIAT_WBS_CAPEX_OPEX || '_' || WBS.fiat_wbs_type || '_' || wbs.fiat_wbs_category
,ribalta.FIAT_ANNORIF
,op.name
,substr(entirib.fiat_raggrupp,1,2)
,WBS.FIAT_WBS_CAPEX_OPEX
,WBS.fiat_wbs_type
,wbs.fiat_wbs_category
,CAT1.NAME
,cat.name
,@WHERE:PARAM:USER_DEF:STRING:anno_precedente@
,@WHERE:PARAM:USER_DEF:STRING:forecast_anno_precedente@
,@WHERE:PARAM:USER_DEF:STRING:anno_corrente@
,@WHERE:PARAM:USER_DEF:STRING:forecast_anno_corrente@)) t1

WHERE @FILTER@
GROUP BY t1.cons_nocons, t1.wbs_capex_opex, t1.tipo_wbs, t1.wbs_category, t1.cname, t1.xname, t1.obs
HAVING @HAVING_FILTER@

Outcomes