In Totara, it’s often important to view not only the direct reports of a manager but also the entire team structure beneath them, including all indirect reports. To address this, we created a custom SQL-based report that generates a complete team hierarchy under a specific manager. This is useful for HR, compliance, and for managers to easily understand the structure of their teams. The report is built using a recursive SQL query that starts by selecting all direct reports of a manager (Level 1), and then recursively finds all indirect reports, assigning each their appropriate level in the hierarchy.
Here is the query we used:
WITH RECURSIVE team_hierarchy AS (
-- Level 1: Direct reports
SELECT
ja.userid,
1 AS level
FROM {job_assignment} ja
INNER JOIN {job_assignment} mja ON ja.managerjaid = mja.id
WHERE mja.userid = :managerid
UNION ALL
-- Recursively find indirect reports
SELECT
ja.userid,
th.level + 1
FROM {job_assignment} ja
INNER JOIN team_hierarchy th
ON ja.managerjaid = (
SELECT id FROM {job_assignment} WHERE userid = th.userid LIMIT 1
)
)
SELECT
base.id,
base.username,
base.email,
COALESCE(base.firstname, '') || ' ' || COALESCE(base.lastname, '') AS fullname,
th.id,
-- Job Title
(
SELECT string_agg(COALESCE(ja.fullname, '-'), '^|:' ORDER BY ja.sortorder)
FROM {job_assignment} ja
WHERE ja.userid = base.id
) AS job_title,
th.level ,
CASE
WHEN base.suspended = 1 THEN 'vacant'
ELSE th.level::text
END AS level,
CASE
WHEN base.suspended = 1 THEN 'Suspended'
WHEN base.confirmed = 0 THEN 'Unconfirmed'
ELSE 'Active'
END AS user_status,
FROM {user} base
JOIN team_hierarchy th ON th.userid = base.id
WHERE base.deleted = 0
ORDER BY th.level ASC, fullname ASC
This query starts with the given manager’s direct reports and recursively finds all levels of team members reporting to them. It also pulls in user details such as full name, username, email, job title, and a human-readable user status (like Active, Suspended, etc.).