Hi,
this is a common challenge once you have several levels of investment hierarchy.
You're right that this can't be solved with a standard aggregated attribute or a Process. Both of those only look at direct relationships, and an investment hierarchy can be many levels deep, so neither one will walk up automatically and sum totals for you.
The good news is Clarity already tracks the full expanded hierarchy for you, so you don't need to build recursive logic yourself. Behind the scenes, when you set up parent/child relationships between investments (Program → Project, or a general Investment Hierarchy), Clarity stores the direct relationships in one table, but also maintains a second table that expands this out to every descendant at every level — not just immediate children. That second table is what makes this solvable with a single query instead of a recursive one.
How I'd approach it:
1. Calculate the true roll-up with one query. Using that expanded hierarchy table, you can get, for any investment, the total Risk/Issue count across all of its descendants — no matter how many levels down — in a single join/group-by. No recursion required.
2. Decide where that number needs to live:
- If you want it stored as a real attribute on the Investment (so it's usable in other views, filters, or NSQL portlets), run this as a scheduled job (via a GEL script) that recalculates the totals and writes them into your existing custom rollup attributes on whatever cadence makes sense for you (hourly/nightly).
- If it's only needed for this one hierarchical dashboard, you can skip storing it altogether and have your report/portlet query the hierarchy table directly at render time. That's less to maintain and it's always current.
3. For the hierarchical view itself (Client → child investments, expandable), that same expanded hierarchy table includes a "level" indicator for each descendant, which is exactly what you'd use to drive indentation/grouping in a report or portlet for the drill-down experience you're describing.
Clarity stores investment hierarchy relationships (Program→Project, or general Investment Hierarchy) in `INV_HIERARCHIES`, but that table only holds direct parent/child pairs, one level at a time. Clarity also maintains an expanded version of that table, `RPT_INV_HIERARCHY`, which includes every descendant at every level, not just immediate children — with a `THE_LEVEL` column telling you how many levels down each row is. It's kept in sync automatically as you maintain your hierarchy, so you can query it directly without rebuilding anything yourself.
Key columns on RPT_INV_HIERARCHY:
- PARENT_ID — the ancestor investment
- CHILD_ID — a descendant investment (direct or indirect)
- THE_LEVEL — how many levels down the child is from the parent
- SEQUENCE_STR — for ordering within the hierarchy display
Example query — total risk count per investment, rolled up across all descendants:
SELECT h.parent_id AS investment_id,
COUNT(r.id) AS total_risk_count
FROM rpt_inv_hierarchy h
JOIN rim_risks_and_issues r
ON r.pk_id = h.child_id
AND r.type_code = 'RISK'
WHERE h.parent_id = :investment_id
GROUP BY h.parent_id;
This only counts descendants — if you also want the investment's own direct risks included in its total, add a `UNION ALL` against `rim_risks_and_issues` filtered directly on the investment itself (`r.pk_id = :investment_id AND r.type_code = 'RISK'`)