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

This case study demonstrates how to add the Data Security file to existing data sources in Lambda Analytics.

When you want to only show the immediate direct reports for your managers, adding the managers.xml Data Security file for your existing data source can solve the problem for you. 

For scenarios where you want the report to go deeper into the Manager-Employee hierarchy (for example, allowing VPs to 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 running the report, this will restrict the resulting report to only show your data and anyone below you in the Manager-Employee hierarchy.

NOTE  NOTE

This is applicable to Totara only.

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))
)