Project and Portfolio Management Practitioners Forum
cancel

Project in two or more programs

Highlighted
patrick-sa
Super Contributor.

Project in two or more programs

hi,got a problem with a filter on a custom portlet.the filter filters projects per program.Filter works perfectly when a project belongs to only one program but fails to return any results if a project belongs to more than one program.
validaion:PMO - Programs
filter secton :and pm_utils.get_project_programs(prj.PROJECT_ID) in ('[P.PROG]')

my alternative is to create another filter using the query below but problem is that multiple rows are returned.see below
please advise.thanks

select prj.PROJECT_NAME ,


(select ppc.PROJECTS_CONTAINER_ID from pm_program_projects ppp,pm_projects_containers ppc,pm_projects prj1
where ppp.PROJECT_CONTAINER_ID=ppc.PROJECTS_CONTAINER_ID
and ppp.PROJECT_ID=prj1.PROJECT_ID
and prj1.PROJECT_ID=prj.project_id)



from pm_projects prj
3 REPLIES
Erik Cole
Acclaimed Contributor.

Re: Project in two or more programs

Because the get_project_programs() can return more than one result, you need reverse the order to make that your IN list instead:

and [P.PROG] IN pm_utils.get_project_programs(prj.PROJECT_ID)
patrick-sa
Super Contributor.

Re: Project in two or more programs

Thanks,but it still doesnt return any results even after rearranging the in list as suggested.
patrick-sa
Super Contributor.

Re: Project in two or more programs

hi,i've solved it using a new filter as shown below.i'd want to see the function work though.

and (select ppc.PROJECTS_CONTAINER_ID from pm_projects prj1 ,pm_program_projects ppp,pm_projects_containers ppc
where ppc.PROJECTS_CONTAINER_ID=ppp.PROJECT_CONTAINER_ID
and ppp.PROJECT_ID=prj1.PROJECT_ID and prj1.PROJECT_ID =prj.PROJECT_ID and rownum =1 ) in ([P.RE])