AnsweredAssumed Answered

Filtering in hierarchical portlet

Question asked by Milos1 on Feb 13, 2012
Latest reply on Jan 26, 2017 by Aurora_Gaimon
Hi All

I have a hierarchical portlet and I need to filter according the child title, but I do not know how. I tried tu built in the param functionality, but it does not work. I would like to have a Title filter, that can filter according the subrequirements title.

Thanks for any help

Regards

Milos


SELECT @SELECT:DIM:USER_DEF:IMPLIED:REQUIREMENTS:h.U_ID:ID@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:REQUIREMENTS:h.CODE:CODE@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:REQUIREMENTS:h.TITLE:TITLE@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:REQUIREMENTS:h.SPRAVCE:SPRAVCE@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:REQUIREMENTS:h.STATUS:STATUS@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:REQUIREMENTS:h.RELEAS:RELEAS@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:REQUIREMENTS:h.INVESTMENTS:INVESTMENTS@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:REQUIREMENTS:h.SS_DONE:SS_DONE@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:REQUIREMENTS:h.RELEAS_LOCKED:RELEAS_LOCKED@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:REQUIREMENTS:h.***:***@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:REQUIREMENTS:h.APP_MANAGER:APP_MANAGER@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:REQUIREMENTS:h.APP_VERSION:APP_VERSION@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:REQUIREMENTS:h.hg_has_children:hg_has_children@
from
(
select (CAST(req.id as varchar) +'') U_ID,
req.code CODE,
req.title TITLE,
(select r.last_name+' '+r.first_name from niku.srm_resources r, niku.odf_ca_requirement reqs where r.id=reqs.cs_spravce and reqs.id=req.id) SPRAVCE,
(select l.NAME FROM niku.CMN_LOOKUPS_V l WHERE l.LOOKUP_TYPE= 'REQUIREMENT_STATUS' AND l.Lookup_code=req.status AND l.LANGUAGE_CODE=@WHERE:PARAM:LANGUAGE@) STATUS,
NULL AS RELEAS,
NULL AS INVESTMENTS,
NULL AS INVESTMENTS_ID,
NULL AS SS_DONE,
NULL AS RELEAS_LOCKED,
NULL AS ***,
NULL AS APP_MANAGER,
NULL AS APP_VERSION,
CASE WHEN (SELECT COUNT(*) FROM niku.RQP_REQ_HIERARCHIES WHERE niku.RQP_REQ_HIERARCHIES.parent_id = req.id ) > 0 THEN (CAST(req.id AS varchar)+'')
ELSE NULL
END hg_has_children
from niku.RQP_REQUIREMENTS req,
niku.ODF_CA_REQUIREMENT odf
where req.id=odf.id
and odf.cs_subpreq=0
and (@where:param:user_def:string:hg_row_id@) is null
union
select (CAST(reqch.id as varchar)+'')U_ID,
reqch.code CODE,
reqch.title TITLE,
(select r.last_name+' '+r.first_name from niku.srm_resources r, niku.odf_ca_requirement reqs where r.id=reqs.cs_spravce and reqs.id=reqch.id) SPRAVCE,
(select l.NAME FROM niku.CMN_LOOKUPS_V l WHERE l.LOOKUP_TYPE= 'REQUIREMENT_STATUS' AND l.Lookup_code=reqch.status AND l.LANGUAGE_CODE=@WHERE:PARAM:LANGUAGE@) STATUS,
(select rel.name from niku.RQP_RELEASES rel where rel.ID=reqch.RELEASE_ID) RELEAS,
(select inv.name from niku.INV_INVESTMENTS inv where inv.ID=reqch.INVESTMENT_ID) INVESTMENTS,
(select inv.id from niku.INV_INVESTMENTS inv where inv.ID=reqch.INVESTMENT_ID) INVESTMENTS_ID,
(select odfr.cs_ss_hotova from niku.odf_ca_release odfr where odfr.id=reqch.RELEASE_ID) SS_DONE,
(select odfr.cs_rel_zap from niku.odf_ca_release odfr where odfr.id=reqch.RELEASE_ID) RELEAS_LOCKED,
(select r.last_name+' '+r.first_name from niku.srm_resources r, niku.ODF_CA_APPLICATION app where r.id=app.cs_as and app.id=reqch.INVESTMENT_ID) ***,
(select r.last_name+' '+r.first_name from niku.srm_resources r, niku.INV_INVESTMENTS inv where r.id=inv.MANAGER_ID and inv.id=reqch.INVESTMENT_ID) APP_MANAGER,
(select app.version from niku.INV_APPLICATIONS app where app.id=reqch.INVESTMENT_ID) APP_VERSION,
NULL hg_has_children
from niku.RQP_REQUIREMENTS req,
niku.ODF_CA_REQUIREMENT odf,
niku.RQP_REQ_HIERARCHIES hie,
niku.RQP_REQUIREMENTS reqch
where req.id=hie.PARENT_ID
and hie.CHILD_ID=reqch.id
and reqch.ID=odf.id
and odf.cs_subpreq=1
and reqch.title in (
select distinct reqch.title from RQP_REQUIREMENTS reqch where (
reqch.title = (@WHERE:PARAM:USER_DEF:STRING:ch_title@)
or (@WHERE:PARAM:USER_DEF:STRING:ch_title@) is null))
and hie.parent_id=@where:param:user_def:string:hg_row_id@
)h
where @FILTER@

Outcomes