Role Hierarchy and User hierarchy design for reporting purpose

Question

I am looking for best possible design for reporting purpose.

In our Salesforce Org there are Role hierarchies. OWD of Case is Public Read/Only

Contact Center Rep Role --> Contact Center Lead Role --> Contact Center Manager Role.

In a single role there could be multiple users.

Requirement is, Lead wants to see only his Reps' case related information in Reports and Dashboards.

Now, for each individual Rep's user record, I have mentioned, Lead as Manager. This Manager attribute is used for sending notification for escalation purpose.

If I want to create a Case Reports I can see following filters

None of the filters are serving my purpose. If I select My Role-based team Cases then if Contact Center Lead1 access this report he will see the data of other Reps (by virtue of Role Hierarchy) who doesn't report to him.

How can I configure a report like when a person access the report he will see only his subordinates data.

Solution should be flexible, no such hardcoded users to be added as filter criteria for showing report data.

Consider that, there are more than 40 managers, 100 Leads and 400 Reps.

Answer 1

Create a formula field in Case object that will check if loggedin user is part of user's hierarchy.

Owner:User.Manager.Id = $User.Id || Owner:User.Id = $User.ManagerId || $User.Id = Owner:User.Id

Finally, formula field to be used in report filter.

Answer 2

Create a formula field in Case object that will check if loggedin user is part of user's hierarchy. The logic-

Owner:User.Manager.Id = $User.Id  ||
$User.Id = Owner:User.Id ||
$User.Id = Owner:User.Manager.Manager.Id

Add this checkbox field as filter in report! Logic of formula-

1. The first one checks - If current user matches Record Owner's Manager Id (level 2)
2. Second - If Current User's is itself the record owner(level 3)
3. Third - Current user is Center Manager (level 1)