AnsweredAssumed Answered

Query that includes projects and sub-projects of a Program

Question asked by pjromano on Jun 10, 2010
Latest reply on Jul 12, 2010 by Owen_R
I have a custom object that is a subobject of the Project object that is called "weekly project report" and I was able to write a Query in SQL (and then NSQL) that allows the user to filter by PROGRAM and it displays all of the "weekly project report" instances for that program as well as any projects one level below in that program ...all the while I'm passing along all of the higher level information too (program name, code, project name, code) then the subobject data.

The problem I am encountering is if any of those projects have a subproject. When I run the query for the Program, that subproject does not display because it is one one level below the program… I do have the query setup so that I can run it by Project too, and if I do, the Program data outputs as blank in the UI. (it's NULL in the actual SQL).

I've been able to identify how I find a subproject, which is where the PREXTERNALID in the PRTASK table has a matching CODE in INV_INVESTMENTS (or I can use INV_HIERARCHIES table, too) ...However, I can't find a way to marry all of that data together properly. I am ok if the Program information is still NULL, since that subproject is not actually directly beneath the program, but if I run the query by program, I would like to return the data for ALL of the projects that fall under that Program, including those that are subprojects of projects, and so on.

Any advice?

Just in case this helps, here is the SQL I'm using…

SELECT
INV_INVESTMENTS.CODE Program_Code,
INV_INVESTMENTS.NAME Program_Name,
INV_INVESTMENTS_1.NAME Project_Name,
INV_INVESTMENTS_1.CODE Project_Code,
SRM_RESOURCES.FULL_NAME Proj_Mgr,
ODF_CA_WEEKPROJREP.code Report_ID,
ODF_CA_WEEKPROJREP.rep_period rep_period,
ODF_CA_WEEKPROJREP.overall_rag_statusov Overall_RAG,
ODF_CA_WEEKPROJREP.pm_opinion PM_Statement,
ODF_CA_WEEKPROJREP.specific_esc_req Escalations,
ODF_CA_WEEKPROJREP.this_period_summary Progress_This_Week,
ODF_CA_WEEKPROJREP.next_period_headline Plan_For_Next_Week,
ODF_CA_WEEKPROJREP.deliv_to_sched Time_RAG,
ODF_CA_WEEKPROJREP.proj_time Time,
ODF_CA_WEEKPROJREP.funds_available Effort_RAG,
ODF_CA_WEEKPROJREP.proj_cost Effort,
ODF_CA_WEEKPROJREP.deliv_to_qual_std Quality_RAG,
ODF_CA_WEEKPROJREP.proj_quality Quality,
ODF_CA_WEEKPROJREP.agree_apprv_in_place Scope_RAG,
ODF_CA_WEEKPROJREP.proj_commercial Scope,
ODF_CA_WEEKPROJREP.defects_in High_In,
ODF_CA_WEEKPROJREP.defects_in_2 Low_In,
ODF_CA_WEEKPROJREP.defects_out High_Out,
ODF_CA_WEEKPROJREP.defects_out_2 Low_Out,
ODF_CA_WEEKPROJREP.ID Report_Internal_ID,
INV_INVESTMENTS_1.ID Project_Internal_ID,
INV_INVESTMENTS.ID Program_Internal_ID
FROM ODF_CA_WEEKPROJREP LEFT OUTER JOIN
SRM_RESOURCES RIGHT OUTER JOIN
INV_INVESTMENTS AS INV_INVESTMENTS_1 ON SRM_RESOURCES.USER_ID = INV_INVESTMENTS_1.MANAGER_ID ON
ODF_CA_WEEKPROJREP.odf_parent_id = INV_INVESTMENTS_1.ID LEFT OUTER JOIN
INV_INVESTMENTS RIGHT OUTER JOIN
cop_program_v ON INV_INVESTMENTS.ID = cop_program_v.program_id ON INV_INVESTMENTS_1.ID = cop_program_v.project_id
WHERE INV_INVESTMENTS_1.Is_Active = 1

Outcomes