Case Study: Adding Data Security to Show the Direct Reports of the Direct Reports

Note: This is applicable to Totara only.

When you want to only show the immediate direct reports for your Managers, adding the managers.xml Data Security file to your existing Data Source can solve the problem for you. For more information, please review this page: Zoola Data Source & Security File Mappings.

For scenarios where you want the report to go deeper into the Manager-Employee hierarchy [ VPs see Directors and their direct reports (Managers) and any direct report of the Managers (employees) ] you can add the following Derived Table to your Data Source. Once you have saved the Derived Table, you must INNER JOIN it to a table that has a user id (i.e. mdl_user). For anyone runnning the report, this will restrict the resulting report to only show your data and anyone below you in the Manager-Employee hierarchy.

SELECT boss.id AS bossid, mja.*
FROM mdl_job_assignment mja
JOIN (SELECT id
      FROM mdl_job_assignment
      WHERE userid = { attribute ('userid') }) boss  ON (    mja.managerjapath LIKE CONCAT('%/', (boss.id), '/%')
														  OR mja.managerjapath LIKE CONCAT('%/', (boss.id))
                                                        )