Many admins would like to create reports and list views that filter automatically for the current user or a manager’s direct reports. Without knowing another method, I have seen users create one report per user, per manager, etc. to satisfy this business requirement.
This seems like it really should be a standard feature in Salesforce, but so far the ability to filter reports based on running user is still an idea under product team review. (Vote Here! https://success.salesforce.com/ideaview?id=08730000000BpLkAAK)
The good news is a fix is pretty simple using formula fields.
The basic setup is to create a formula checkbox field that evaluates to true when the current user ($User.ID) matches a user lookup field on a record.
UserLookupField = $User.ID
This can be applied in a few ways.
Returns true for records belonging to the current user:
Owner.ID = $User.ID
Returns true for records belonging to the direct reports of the current user:
Owner.ManagerID = $User.ID
Returns true when a custom lookup field equals the current user:
Ex: An account has a Channel Manager along with Account Owner, Channel Manager wants to see all opportunities for accounts where they are the channel manager
Account.ChannelManager__c = $User.ID
Returns true when a value on user lookup field matches that of the current user:
Ex: You want to create a report that returns all opportunities under one department, not necessarily just direct reports.
Owner.Department = $User.Department
We could even show all results for the Channel Manager’s department:
Account.ChannelManager__r.Department = $User.Department
With these approaches, you should be able to create all the fields, on any object, that you need to filter list views and reports to the current user.
Example: ‘Opportunities belonging to Direct Reports’ List View & Report
Create a field on the Opportunity object using this formula: Owner.ManagerID = $User.ID
In the example below, I called mine ‘User Manager Check’
Create a list view that filters for this field being true and no matter which user clicks the list view, they will only see the results belonging to their direct reports (if any).
Do the same when creating a report, adding a filter for User Manager Check = True. When a user runs the report, they will only see the results belonging to their direct reports (if any) – no need to create one report per user/manager/team ever again!
Hi Tom! This is a brilliant tip, but I ran into trouble with getting it to work for non-owners of the records. I’m using it to run reports/make dashboards so that our salespeople can’t see each other’s data while using the same dashboard (I realize that using a dynamic dash is supposed to fix this, but that would require data on the OWD to be set to private for the Opps & Lead objects, which my co-admin doesn’t want to do). So, can’t figure out what’s wrong with my formula:
OR(
(Owner:User.Id = $User.Id),
(‘XXXXXXXXXXXXXBEAS’ = $User.UserRoleId)
)
The first part seems to work, because record owners can only see their records. The second part is supposed to give access to all records to the manager role. I’ve also tried putting in the manual ID’s of all the managers individually in an OR, but that doesn’t work either. Any thoughts?
Hi SIenna
Did you solve this problem. I am having the same trouble. If yes please contact me!
Thankyou
This is so helpful! I am trying to actually go 4 levels up and struggling on the syntax past the case owners manager….Any ideas?
Is the running user: the case owner OR the case owner’s manager OR the case owner’s manager’s manager OR the case owner’s manager’s manager’s manager.
Is it possible to use this as a workaround when you run out of dynamic dashboards? I’ve tested this for list views and reports and it works perfectly, but for dashboards is doesn’t .
Or Is it an extra step that I’m missing?
Thank you
Christina,
This is impossible to do for dashboards. Dashboards do not have access to the logged in user’s session, so the formula is always true.
Can anyone help with this use case?
I want to build a report of Cases owned by Queue and the report should fetch data only to the queue members (my queue member is a public group where man users are available in the Group). If any user is removed from the public group then that user should not be able to see any cases owned the queue to which is public group is associated to when they fetch the data.