Looking through the forums, I've gotten VERY close to getting this to work, but still have something slightly off. I'm trying to get the field benefittype to display in the portlet, but am getting this error (for anyone who can't see the graphic, it's ERRORNPT-217: This query produced duplicate dimensional data. The results shown here may be invalid or incomplete):
I would really love for it to display in the portlet with a comma between the items if more than one has been selected (if that can be done).
Here's my code. I turned the applicable lines blue so you could see them easier. Any help MUCH appreciated!
Heidi
:
SELECT @SELECT:DIM:USER_DEF:IMPLIED:BENEFITS:b.code:code@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:b.id:id@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:b.approvaldate:approvaldate@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:b.costperhour:costperhour@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:b.fmcbenefitsdescript:fmcbenefitsdescripte@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:b.hoursneeded:hoursneeded@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:b.name:name@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:b.newhoursneeded:newhoursneeded@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:b.nextreviewdate:nextreviewdate@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:b.fmcbenefitnotes:fmcbenefitnotes@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:b.occurrence:occurrence@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:b.timevalue:timevalue@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:projectmanager:projectmanager@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:fmcprojectname:fmcprojectname@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:lkp_fmcbenefitvaluecomp:fmcbenefitvaluecomp@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:'$' || to_char(@NVL@(b.costperdaycalc,0),'9,999,990.00'):costperdaycalc@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:'$' || to_char(@NVL@(b.costperweekcalc,0),'9,999,990.00'):costperweekcalc@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:'$' || to_char(@NVL@(b.costpermonthcalc,0),'9,999,990.00'):costpermonthcalc@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:'$' || to_char(@NVL@(b.costperyearcalc,0),'9,999,990.00'):costperyearcalc@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:'$' || to_char(@NVL@(b.newcostperdaycalc,0),'9,999,990.00'):newnewcostperdaycalc@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:'$' || to_char(@NVL@(b.newcostperweekcalc,0),'9,999,990.00'):newnewcostperweekcalc@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:'$' || to_char(@NVL@(b.newcostpermonthcalc,0),'9,999,990.00'):newcostpermonthcalc@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:'$' || to_char(@NVL@(b.newcostperyearcalc,0),'9,999,990.00'):newcostperyearcalc@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:'$' || to_char(@NVL@((b.costperyearcalc - b.newcostperyearcalc),0),'9,999,990.00'):firstyearprodsavings@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:'$' || to_char(@NVL@((b.subsequentyrprodsavings * (b.costperyearcalc - b.newcostperyearcalc)),0),'9,999,990.00'):subsequentyrprodsavings@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:benriskdesc:benriskdesc@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:benriskimpact:benriskimpact@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:costriskocc:costriskocc@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:probofriskocc:probofriskocc@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:numberwithin1yr:numberwithin1yr@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:numberwithin5yr:numberwithin5yr@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:numberwithin10yr:numberwithin10yr@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:probofriskoccscore:probofriskoccscore@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:b.dirpatientaff:dirpatientaff@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:'$' || to_char(@NVL@((((b.costriskocc * b.probofriskoccscore)*b.dirpatientaffscore)),0),'9,999,990.00'):firstyearrisksavings@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:'$' || to_char(@NVL@((b.subsequentyrrisksavings * (((b.costriskocc * b.probofriskoccscore)*b.dirpatientaffscore))),0),'9,999,990.00'):subsequentyrrisksavings@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:dirpatientaffscore:dirpatientaffscore@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:b.costreducdesc:costreducdesc@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:'$' || to_char(@NVL@(b.plndcostdec,0),'9,999,990.00'):plndcostdec@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:b.timevaluecostred:timevaluecostred@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:'$' || to_char(@NVL@(b.costredperdaycalc,0),'9,999,990.00'):costredperdaycalc@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:'$' || to_char(@NVL@(b.costredperweekcalc,0),'9,999,990.00'):costredperweekcalc@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:'$' || to_char(@NVL@(b.costredpermonthcalc,0),'9,999,990.00'):costredpermonthcalc@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:'$' || to_char(@NVL@(b.costredperyearcalc,0),'9,999,990.00'):costredperyearcalc@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:'$' || to_char(@NVL@((b.subsequentyrcostred * costredperyearcalc),0),'9,999,990.00'):subsequentyrcostredcalc@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:b.revincdesc:revincdesc@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:'$' || to_char(@NVL@(b.revincrease,0),'9,999,990.00'):revincrease@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:b.timevalrevinc:timevalrevinc@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:'$' || to_char(@NVL@(b.revincperdaycalc,0),'9,999,990.00'):revincperdaycalc@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:'$' || to_char(@NVL@(b.revincperweekcalc,0),'9,999,990.00'):revincperweekcalc@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:'$' || to_char(@NVL@(b.revincpermonthcalc,0),'9,999,990.00'):revincpermonthcalc@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:'$' || to_char(@NVL@(b.revincperyearcalc,0),'9,999,990.00'):revincperyearcalc@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:'$' || to_char(@NVL@((b.subsequentyrrevinc * revincperyearcalc),0),'9,999,990.00'):subsequentyrrevcalc@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:(b.costriskocc * b.probofriskoccscore)+(b.costredperyearcalc)+(b.revincperyearcalc)+((b.costriskocc * b.probofriskoccscore)*b.dirpatientaffscore):allfirstyearsavings@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:((b.costriskocc * b.probofriskoccscore)+(b.costredperyearcalc)+(b.revincperyearcalc)+((b.costriskocc * b.probofriskoccscore)*b.dirpatientaffscore))*b.subsequentyrcode:allsubsequentyearsavings@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:BENEFITS:b.benefittype:benefittype@
FROM
(
select
b.code code,
b.id,
b.approvaldate approvaldate,
b.costperhour costperhour,
b.fmcbenefitsdescript fmcbenefitsdescript,
b.hoursneeded hoursneeded,
b.name name,
b.newhoursneeded newhoursneeded,
b.nextreviewdate nextreviewdate,
b.fmcbenefitnotes fmcbenefitnotes,
b.occurrence occurrence,
b.timevalue timevalue,
case lkp_timevalue.name
when 'Day' then (b.hoursneeded * b.costperhour)
when 'Week' then ((b.hoursneeded * b.costperhour)/7)
when 'Month' then ((b.hoursneeded * b.costperhour)/30)
when 'Year' then ((b.hoursneeded * b.costperhour)/365)
else 0
end costperdaycalc,
case lkp_timevalue.name
when 'Day' then ((b.hoursneeded * b.costperhour)*7)
when 'Week' then (b.hoursneeded * b.costperhour)
when 'Month' then ((b.hoursneeded * b.costperhour)/4)
when 'Year' then ((b.hoursneeded * b.costperhour)/52)
else 0
end costperweekcalc,
case lkp_timevalue.name
when 'Day' then ((b.hoursneeded * b.costperhour)*30)
when 'Week' then ((b.hoursneeded * b.costperhour)*4)
when 'Month' then (b.hoursneeded * b.costperhour)
when 'Year' then ((b.hoursneeded * b.costperhour)/12)
else 0
end costpermonthcalc,
case lkp_timevalue.name
when 'Day' then ((b.hoursneeded * b.costperhour)*365)
when 'Week' then ((b.hoursneeded * b.costperhour)*52)
when 'Month' then ((b.hoursneeded * b.costperhour)*12)
when 'Year' then (b.hoursneeded * b.costperhour)
else 0
end costperyearcalc,
lkp_fmcbenefitvaluecomp.name lkp_fmcbenefitvaluecomp,
case lkp_timevalue.name
when 'Day' then (b.newhoursneeded * b.costperhour)
when 'Week' then ((b.newhoursneeded * b.costperhour)/7)
when 'Month' then ((b.newhoursneeded * b.costperhour)/30)
when 'Year' then ((b.newhoursneeded * b.costperhour)/365)
else 0
end newcostperdaycalc,
case lkp_timevalue.name
when 'Day' then ((b.newhoursneeded * b.costperhour)*7)
when 'Week' then (b.newhoursneeded * b.costperhour)
when 'Month' then ((b.newhoursneeded * b.costperhour)/4)
when 'Year' then ((b.newhoursneeded * b.costperhour)/52)
else 0
end newcostperweekcalc,
case lkp_timevalue.name
when 'Day' then ((b.newhoursneeded * b.costperhour)*30)
when 'Week' then ((b.newhoursneeded * b.costperhour)*4)
when 'Month' then (b.newhoursneeded * b.costperhour)
when 'Year' then ((b.newhoursneeded * b.costperhour)/12)
else 0
end newcostpermonthcalc,
case lkp_timevalue.name
when 'Day' then ((b.newhoursneeded * b.costperhour)*365)
when 'Week' then ((b.newhoursneeded * b.costperhour)*52)
when 'Month' then ((b.newhoursneeded * b.costperhour)*12)
when 'Year' then (b.newhoursneeded * b.costperhour)
else 0
end newcostperyearcalc,
srm.first_name || ' ' || srm.last_name projectmanager,
inv.name fmcprojectname,
case lkp_occurence.name
when 'Sustainable' then 1
else 0
end subsequentyrprodsavings,
b.benriskdesc benriskdesc,
b.benriskimpact benriskimpact,
b.costriskocc costriskocc,
b.probofriskocc probofriskocc,
b.numberwithin1yr numberwithin1yr,
b.numberwithin5yr numberwithin5yr,
b.numberwithin10yr numberwithin10yr,
case probofriskocc
when 'Low' then .01
when 'Medium' then .25
when 'High' then .5
else 0
end probofriskoccscore,
case lkp_occurence.name
when 'Sustainable' then 1
else 0
end subsequentyrrisksavings,
case b.dirpatientaff
when 4040 then 'No'
when 4039 then 'Yes'
else 'N/A'
end dirpatientaff,
case b.dirpatientaff
when 4040 then 1
when 4039 then 1.5
else 1
end dirpatientaffscore,
b.costreducdesc costreducdesc,
b.plndcostdec plndcostdec,
b.timevaluecostred timevaluecostred,
case lkp_timevaluecostred.name
when 'Day' then (b.plndcostdec)
when 'Week' then (b.plndcostdec / 7)
when 'Month' then (b.plndcostdec / 30)
when 'Year' then (b.plndcostdec / 365)
else 0
end costredperdaycalc,
case lkp_timevaluecostred.name
when 'Day' then (b.plndcostdec * 7)
when 'Week' then (b.plndcostdec)
when 'Month' then (b.plndcostdec / 4)
when 'Year' then (b.plndcostdec / 52)
else 0
end costredperweekcalc,
case lkp_timevaluecostred.name
when 'Day' then (b.plndcostdec * 30)
when 'Week' then (b.plndcostdec * 4)
when 'Month' then (b.plndcostdec)
when 'Year' then (b.plndcostdec / 12)
else 0
end costredpermonthcalc,
case lkp_timevaluecostred.name
when 'Day' then (b.plndcostdec * 365)
when 'Week' then (b.plndcostdec * 52)
when 'Month' then (b.plndcostdec * 12)
when 'Year' then (b.plndcostdec)
else 0
end costredperyearcalc,
case lkp_occurence.name
when 'Sustainable' then 1
else 0
end subsequentyrcostred,
b.revincdesc revincdesc,
b.revincrease revincrease,
b.timevalrevinc timevalrevinc,
case lkp_timevaluerevinc.name
when 'Day' then (b.revincrease)
when 'Week' then (b.revincrease / 7)
when 'Month' then (b.revincrease / 30)
when 'Year' then (b.revincrease / 365)
else 0
end revincperdaycalc,
case lkp_timevaluerevinc.name
when 'Day' then (b.revincrease * 7)
when 'Week' then (b.revincrease)
when 'Month' then (b.revincrease / 4)
when 'Year' then (b.revincrease / 52)
else 0
end revincperweekcalc,
case lkp_timevaluerevinc.name
when 'Day' then (b.revincrease * 30)
when 'Week' then (b.revincrease * 4)
when 'Month' then (b.revincrease)
when 'Year' then (b.revincrease / 12)
else 0
end revincpermonthcalc,
case lkp_timevaluerevinc.name
when 'Day' then (b.revincrease * 365)
when 'Week' then (b.revincrease * 52)
when 'Month' then (b.revincrease * 12)
when 'Year' then (b.revincrease)
else 0
end revincperyearcalc,
case lkp_occurence.name
when 'Sustainable' then 1
else 0
end subsequentyrrevinc,
case lkp_occurence.name
when 'Sustainable' then 1
else 0
end subsequentyrcode,
clv.name benefittype
from odf_ca_fmc_benefits_mgt b
LEFT join cmn_lookups_v lkp_timevalue on lkp_timevalue.lookup_code = b.timevalue and lkp_timevalue.lookup_type = 'TIMEVALUE' and lkp_timevalue.language_code = @WHERE:PARAM:LANGUAGE@
LEFT join cmn_lookups_v lkp_fmcbenefitvaluecomp on lkp_fmcbenefitvaluecomp.lookup_code = b.fmcbenefitvaluecomp and lkp_fmcbenefitvaluecomp.lookup_type = 'VALUECOMPASSPOINT' and lkp_fmcbenefitvaluecomp.language_code = @WHERE:PARAM:LANGUAGE@
LEFT join cmn_lookups_v lkp_occurence on lkp_occurence.lookup_code = b.occurrence and lkp_occurence.lookup_type = 'OCCURENCE' and lkp_occurence.language_code = @WHERE:PARAM:LANGUAGE@
LEFT join srm_resources srm ON srm.user_id = b.projectmanager
LEFT join inv_investments inv ON inv.id = b.fmcprojectname
LEFT join cmn_lookups_v lkp_timevaluecostred on lkp_timevaluecostred.lookup_code = b.timevaluecostred and lkp_timevaluecostred.lookup_type = 'TIMEVALUE' and lkp_timevaluecostred.language_code = @WHERE:PARAM:LANGUAGE@
LEFT join cmn_lookups_v lkp_timevaluerevinc on lkp_timevaluerevinc.lookup_code = b.timevalrevinc and lkp_timevaluerevinc.lookup_type = 'TIMEVALUE' and lkp_timevaluerevinc.language_code = @WHERE:PARAM:LANGUAGE@
LEFT JOIN ODF_MULTI_VALUED_LOOKUPS MUL on b.ID=MUL.PK_ID
LEFT JOIN CMN_LOOKUPS_V clv on MUL.VALUE=clv.LOOKUP_CODE and clv.LOOKUP_TYPE = 'LKPBENEFITTYPE'
) b
WHERE 1=1
AND @FILTER@