Here’s a conundrum that you may have encountered (and maybe solved!). If you are using Work Breakdown Structures (WBS) on the project ledger and you want to report at the parent project level only how do you ‘roll-up’ the WBS elements into their parents?

Well some sites get around this problem by following a naming convention for their parent and child projects. However if you don’t then you could find yourself with the difficulty of unravelling everything!

Ok so I got hit with this little problem so I needed a solution for my client! Well the first cut was a quick and dirty solution. If you are unaware of how the data is stored what Line 500 does is store the relationship but only 1 level at a time. That means a 1st level child knows the top level project, a 2nd level child knows the 1st level etc.

The problem of course is that you might want to group financials by top level projects only and have children across many levels that are not directly joined. The quick and dirty approach is simply;

SELECT
t0.project_code AS TopLevel, t1.project_code AS C1,
t2.project_code AS C2, t3.project_code AS C3,
t4.project_code AS C4, t5.project_code AS C5,
t6.project_code AS C6, t7.project_code AS C7
FROM
scheme.prpjm as t0
LEFT OUTER JOIN scheme.prpjm AS t1 ON LEFT(t1.parent_project, 20) = t0.project_code
LEFT OUTER JOIN scheme.prpjm AS t2 ON LEFT(t2.parent_project, 20) = t1.project_code
LEFT OUTER JOIN scheme.prpjm AS t3 ON LEFT(t3.parent_project, 20) = t2.project_code
LEFT OUTER JOIN scheme.prpjm AS t4 ON LEFT(t4.parent_project, 20) = t3.project_code
LEFT OUTER JOIN scheme.prpjm AS t5 ON LEFT(t5.parent_project, 20) = t4.project_code
LEFT OUTER JOIN scheme.prpjm AS t6 ON LEFT(t6.parent_project, 20) = t5.project_code
LEFT OUTER JOIN scheme.prpjm AS t7 ON LEFT(t7.parent_project, 20) = t6.project_code