Can you run reports with a filter that uses the current logged in user? - Answers - Salesforce Trailblazer Community
Trailblazer Community
Ask Search:
Robert MellettRobert Mellett 

Can you run reports with a filter that uses the current logged in user?

Can you run reports with a filter that uses the current logged in user?
Best Answer chosen by Moderator (salesforce.com) 
Steve MolisSteve Molis
Okay, I have a fix for you.  It involves creating a couple of custom Formula fields on the objects you want to report on, and some beer...  

Create a custom formula field on the object.  
Datatype:  Formula(Number,0)
Formula:  IF(Custom_Lookup_Field__c = $User.Id , 1 , 0)  

do this for each of the Lookup(User) fields.
 
In your Report Filter Crteria select:   
Field: Custom Fomula Field Name   Operator:  [equals]  Value:

Then send me some of these ->
User-added image

 

All Answers

Steve MolisSteve Molis
Not an individual Report, but you can do it for Dashboards.  

Setting Up Dynamic Dashboards

Available in: Enterprise, Unlimited, and Developer Editions

User Permissions Needed
To create, edit, and delete dynamic dashboards:“Run Reports” AND “Manage Dynamic Dashboards”
To enable choosing a different running user for the dashboard:“View My Team's Dashboards” OR “View All Data”

Your organization can have up to five dynamic dashboards for Enterprise Edition, 10 for Unlimited Edition, and three for Developer Edition. You can't schedule refreshes for dynamic dashboards. They must be refreshed manually.

Before you get started, create folders accessible to all dashboard viewers to store dynamic dashboards and corresponding component source reports. You can't save dynamic dashboards to personal folders.

To create dynamic dashboards:
  1. From the Dashboards tab, create a new dashboard or edit an existing one.
  2. Click the View dashboard as drop-down button button next to the View dashboard as field.
  3. Select Run as logged-in user.
  4. Optionally, select Let authorized users change running user to enable those with permission to change the running user on the dashboard view page, as follows:
    • Users with “View My Team's Dashboards” can view the dashboard as any user below them in the role hierarchy.
    • Users with “View All Data” can view the dashboard as any user in their organization.
  5. Click OK.
  6. In the View dashboard as field, enter a running user. Enter “*” to see all available users.
  7. Save your dashboard.

Tips for Source Reports for Dynamic Dashboards

  • Set the appropriate Show option on the report run page. For example, if you choose “My Team's Opportunities,” each dynamic dashboard viewer can see all opportunities for the team.
  • To avoid restricting the dashboard's view of the data:
    • Make sure advanced filters don't include specific record owners (for example, Opportunity Owner equals Frank Smith).
    • Don't click Save Hierarchy Level when saving opportunity reports.
Steve MolisSteve Molis
I may have something else that will work  work for you.  Can you explain what kind of Report you're trying to create and what you want it to display?
Robert MellettRobert Mellett
We have a group of owners for our Opportunities. So we have the opportunity owner, a Sales Engineer Owner and we have an Inside Sales Owner (all lookups to the user table). I want to create a set of reports that feed dashboards, but in order to save time I would like to create one report for each owner type. Easy for the Opp owner as you set the default report to say "My Opportunities", but for the Inside Sales Rep I was hoping to use a filter that says "ISR Owner = Current User" versus ISR Owner = Joe Smith.....clone....ISR = Mike Smith....clone....etc...

..
Robert MellettRobert Mellett
We have a group of owners for our Opportunities. So we have the opportunity owner, a Sales Engineer Owner and we have an Inside Sales Owner (all lookups to the user table). I want to create a set of reports that feed dashboards, but in order to save time I would like to create one report for each owner type. Easy for the Opp owner as you set the default report to say "My Opportunities", but for the Inside Sales Rep I was hoping to use a filter that says "ISR Owner = Current User" versus ISR Owner = Joe Smith.....clone....ISR = Mike Smith....clone....etc...

The running user is nice, but not the right solution for me.
Robert MellettRobert Mellett
Sorry Running User for Dashboards is nice, but not the right solution....if I can do what I want above it will save me from creating close to 150 reports.
Steve MolisSteve Molis
Okay, I have a fix for you.  It involves creating a couple of custom Formula fields on the objects you want to report on, and some beer...  

Create a custom formula field on the object.  
Datatype:  Formula(Number,0)
Formula:  IF(Custom_Lookup_Field__c = $User.Id , 1 , 0)  

do this for each of the Lookup(User) fields.
 
In your Report Filter Crteria select:   
Field: Custom Fomula Field Name   Operator:  [equals]  Value:

Then send me some of these ->
User-added image

 
This was selected as the best answer
Robert MellettRobert Mellett
This might work! If it does I will track you down at Dreamforce to pay my pittance.
Steve MolisSteve Molis
What do you mean might work?!?
Robert MellettRobert Mellett
Come on you know Salesforce....IT DOES WORK THOUGH...I will check with a larger data set soon!!!You gonna be at Dreamforce?
Steve MolisSteve Molis
Yeah, I'm speaking at the "Formula Ninjas" sessions with @ImpStout (Brad Gross) on Wednesday and Thursday.

Daniel KiftDaniel Kift
Hi SteveMo & rmellett11092, 
 
We've got a similar requirement to capture a second opportunity owner, for deals that two sales people have worked on.  To achieve that we added a custom field on Opportunity which is a lookup to the user object.  
 
We want to run reports & have dashboards looking at Opportunities that includes opportunites if you're the owner or secondary owner.   Using your answer as a basis we added a formula field like this: 
 
IF( OwnerId = $User.Id && Custom_Field__c = $User.Id , 4,
IF(OwnerId = $User.Id && Custom_Field__c <> $User.Id,3,
IF(OwnerId <> $User.Id && Custom_Field__c = $User.Id,2,
IF(OwnerId <> $User.Id && Custom_Field__c <> $User.Id,1,
0))))
 
We've then got an Opportunity report looking at 'all opportunities', with a filter - 'custom_field__c = 4,3,2'
 
This works well if you're logged in as the user or looking at dahsboards (with them set as the running user).  But, do you know of a way to do something similar to mimic 'my team's opportunities' for Manager dashboards/reports?
 
It seems like it would be complicated.  Salesforce Team Selling might have to be the way to go, but I'd rather not add another related list and reporting complexity unless I have to.  Any help appreciated.
Sean RileySean Riley

Dear SteveMo,

I.O.U - One Beer

Jason BatesJason Bates
Is it possible to use a formula like this to work for "My Team"?
Sandy DolanSandy Dolan
Thank you! Thank you! Thank you!  I am using this to create a dynamic activity list view to find open tasks not assigned to "running user".  I owe you a beer, if you are ever in Nebraska.
V VelloreV Vellore
Steve, you rock man!  Any time you are in the Bay area I will buy you 'Pliny the Elder'!
I am posting this 'cause you answered one of my questions before.

Cheers!
Case ManagerCase Manager
wow... Steve... you are awesome.. its working like champ... thank you.. unfortunately i am not in US to treat you with Pliny Elder :-)


Michael MoyesMichael Moyes
Amazing formula. Simple yet very effective. My team loves it!
Khoa Mach DinhKhoa Mach Dinh
Hello Steve Molis,

I tried your formula but Custom_Lookup_Field__c does not exists, check spelling.. Could you please help ?

Thank you,

Khoa.
Steve MolisSteve Molis

Khoa Mach Dinh 
That is just a "generic" field name that I used in my formula, I don't have access to anyones SFDC org or their custom fields.  You'll need to use the Insert Field button in the Formula Builder to get the API Field Name from your SFDC org 

User-added image

 

Emily DehlEmily Dehl
Oh, stevemo, that might be the most appropriate screenshot in... ever. Can I steal it?
Christina BellChristina Bell
Hi Steve,

I have a custom object, the owner field is defaulted by ssforce, its data type: lookup(user, queue); I tried to use your above formula to create dynamic report /dashboard; since the owner data type is lookup(user, queue), when i go through 'insert field' via 'advanced formula', it popped up quite a few user related field, such as: username (I found this is user email); user ID etc... I don't know which field in user object that i need pick up in order to make the report working dynamically.. Can you help this? thanks.
Emily DehlEmily Dehl
It's the user ID, Christina. :)
Emily DehlEmily Dehl
Also, in case anyone is wondering: you can take this to the next level and use the power of one reports in a dynamic dashboard. We have a separate, hard-coded AC Assigned field, and wanted to created a dashboard that would only display records for which the logged-in user was AC Assigned. So we used Steve's formula above (inserting the AC Assigned API name for his generic custom_lookup__c), created the base reports filtered by power of one = 1, then added them to a dynamic dashboard. The dynamic dashboard then displays only records for which the logged in user is AC Assigned! So slick!
Christina BellChristina Bell
Hi Emily,
this works on, after I ajusted to If(Owner:User.Id = $User.Id,1,0); thanks.
Steve MolisSteve Molis
This post is so old, now I would just use the new Formula(Checkbox) datatype and a formula like this: 

OwnerId =  $User.Id


Emily DehlEmily Dehl
Ha, good point. Just adjusted our formulas.
Steve MolisSteve Molis
Formula Checkboxes are my new F*ing JAM!
Ariane OuvryAriane Ouvry
I really like this and it saved me from creating a ton of reports.  Is it possible to take it to the next level and have this work dynamically for My team ?
Steve MolisSteve Molis
Hi Ariane,
How is "Your Team" defined?  Where are those User.ID's stored on the record in the report?
Ariane OuvryAriane Ouvry
thanks for the quick reply. 

On the opportunity object we have a lookup to User field called Supported by.  With the formula given above I can create only 1 report (instead of 1 per user) where the logged in user can see all the opportunities where his name is in the Supported by field.  

If the logged in User is the manager (is in the report to role in the role hierarchy), it will not work because he is not the person in that field.  I was hoping (crossing fingers and toes) that maybe there was a trick and that there was a formula that would work in that case.  Like the My team standard field on record owners.  

Steve MolisSteve Molis
Are you using the Manager Field on the User Object?
Ariane OuvryAriane Ouvry
The data in that field is not as clean as I would like it to be but yes we are.
thanks again
Steve MolisSteve Molis
Okay, then if you can clean that field up, you could use it in your formula.  I just built and tested this on my Dev SFDC org and it worked =>
$User.Id = Lookup_User__r.ManagerId



Ariane OuvryAriane Ouvry
Thanks Steve
Dan JeanDan Jean
Great option on the ManaqerID field @stevemo, but is there a way to take this to the next management level and higher?  We have 7-8 levels of Management who would be interested in seeing reports built this way.  Would it just be a series of 7 or 8 OR options looking at the managers managers manager and so on?
Steve MolisSteve Molis
Hi Dan,
You might be able to do that, I've never really pushed it out to see what the limits are.  But at some point you might have to take a step back and ask "What am I really doing here?  And what am I trying to do???".  

It might be easier to start at the top and attack it from an Exec Level Report using a simple "My Team..." filter or something like that, otherwise you may end up playing User.ManagerId> User.ManagerId>User.ManagerId>... Whack-a-Mole 
Venkat R BadeVenkat R Bade
@Steve 

Brilliance is the word. 
Girish AhujaGirish Ahuja
Hi Everybody,

I have similar type of requirement on Case Object to build a dynamic dashboard for logged in user who will be Manager but this dashboard needs to display the data for only his sub-bordinates.

This is the scenario on which report needs to be built:
i have 2 managers named A and B.
Under A, teammates X,Y and Z are assigned.
Under B teammates P,Q and R are assigned.
We have built a report for Manager can view teammate's cases based on the filter 'My Role-based Team's cases'.
But A  can able to see cases of X,Y,Z and P,Q,R also..
How to restrict the manager A to see only cases of X,Y and Z only.. not P,Q and R.

Criteria considered:
1. OWD of case object is "Public".
2. Also case teams are not implemented.

Please suggest a solution satisfying the above criteria.

Thanks in advance and hoping to get quick response.

This scenario has been already posted by our teammates in below link:
https://success.salesforce.com/answers?id=90630000000i1MmAAI
Zach FredinZach Fredin
Hi Steve--this solution seems like a good fit for me. I'd like to report on Opportunity Teams based on the currently logged in user, so I created a checkbox field under Opportunity Teams as you described above. However, it doesn't look like the User ID field is availble under Opportunity Teams when I hit the Insert Field button; the Opportunity Team Member ID seems to be a unique code not related to the actual user. I need something to replace your Custom_Lookup_Field__c field. I'm a bit new to this, so I'm guessing there is an easy answer I'm missing.

Any thoughts?
Steve MolisSteve Molis
Hi Zack,
Unfortunately I don't think you'll be able to access the User.Id values on the Opportunity Team object for use in that Formula.
Zach FredinZach Fredin
Thanks for the quick response, Steve. I'll still buy you a brew if you drop by Dreamforce this year, it looks like a good workaround for many folks.
Sophie CurtisSophie Curtis
Do you think this would work with a partner/community user? Is the ID field the same?
Steve MolisSteve Molis
Hi Sophie, I don't have any experience with Partner or Community users, but my gut tells me that the same rules would apply.
Al D'AmbrosioAl D'Ambrosio
Does anyone know of a way we can do this for a report of reports?  The "My Reports" drop-down option within reporting window shows the user reports in his/her My Personal Custom Reports folder. Use case: To keep the Unfiled Public Reports folder manageable, we'd like to periodically share with users a link to report that shows the user all reports created by himself/herself that sit in the Unfiled Public Reports folder that are over 90 days old, so users can purge or move them.  We can't seem to use the custom formula field with $User.Id because we can't put that as a field in reports, only in an object.  Is there another way? Thanks!
Matt MetzingerMatt Metzinger
This is great but I need one more thing. I need to schedule a report to run for multiple users and I want the report to populate the filter "
Field: Custom Fomula Field Name   Operator:  [equals]  Value: 1 " as the user that will recieve the report. Does this already happen or is this report going to run as me? 
 
Steve MolisSteve Molis
Hi Matt,
You can't do that with a Scheduled Report because you need to select a Running User, who then becomes "The 1".  For that you'd need a Dynamic Dashboard or (maybe) a Report Subscription.
Danton CrosserDanton Crosser
Hi Steve -

Is it possiblee to do this with Queues? We have roughly 30 Queues assigned as record owners in our Custom Object and we'd like to be able to dynamically filter the report based on logged in user versus records that are owned by Queues, making reports quicker and easier to navigate. Unfortunately it doesn't look like there is a Queue field I can use in the formula above to link back to the running user. Thoughts?
 
Steve MolisSteve Molis
Danton,
I'm not sure I understand what you want to do?  They would never match because one is a User.ID and the other is a Queue.ID.  

Are you trying to compare the $User.Id to the all of the Users who are members of the Queue?
Danton CrosserDanton Crosser

Steve - 

Sorry - let me clarify. We using a Custom Object called 'Budget', some of the records are owned by individual users and some are owned by Queues. When viewing the report created for Budget, the 'My Budgets' option under 'Show' returns records owned by the individual user logged in, but if the user owns multiple records across multiple Queues, nothing would return. For the users in a Queue, we are trying to figure out a way for a report to only return the records owned by the Queue they are in. Is it possible to achieve this through a similar formula you stated above (as you mentioned above, comparing the $User.ID to the members of a Queue)?
 
Steve MolisSteve Molis
Steve Molis
Hi Danton 
I don't think that's gonna be possible, you'd essentially need to compare the $User.ID against all of the User.ID's of all the individual Users, Users in Groups, Users in Roles, Users in Subordinate Roles who make up the members of that Queue...  

So, long story short: I think you're packing your bags for the Wonderful World of Apex and Visualforce. 
Chris JohnsonChris Johnson
Steve (or anyone!), any thoughts on if it is possible to create a Dynamic Dashboard and incorporate a Dashboard filter that toggles between "My" and "My Team's" ? Rationale is in creating a single-page Sales Manager/Producer dash and being able to toggle the source data vs. making two reports and two dashboards. The user could select a person, click the filter to see that user's team's data in the dash, click the filter again and only see that user's specific data.

If I am going about this in a crazy/incorrect way, let me know as well; danke!
Steve MolisSteve Molis
Hi Chris,
I think you're proabably gonna need 2 Dashboards and 2 sets of reports. The "My..." and "My Teams..." filters are a layer below the Dashboard, down in the Reports
Chris JohnsonChris Johnson
That was what I knew in the back of my mind would be the answer, but was cautiously optimistic it was possible. A guy can dream; right?

Thanks Steve;
Matthias FraschMatthias Frasch
Steve (or others)
for the formula IF (Owner = $User.Id,1,0): No matter what name I type for the (Custom_Lookup_Field__C) it errors out saying Field no available. I know "Owner" exists, but I get nothin'. Could it be that I don't have sufficient privileges?
 
Venkat R BadeVenkat R Bade
Hello Matthias,
You're formula should be IF (OwnerId  = $User.Id,1,0).
Matthias FraschMatthias Frasch
Hello Venkat-
I tried all types of spellings. Lead.OwnerId, OwnerId, Account.OwnerId,.... all of them error out. I also have a custom field named "Campaign_Response__c" which errors out as well. I tried Owner because I know its a SFDC Standard field. Seems more like I have a global issue with writing formulas. Just to make sure I copy/pasted your suggestion and it was a no go. Am I missing someting?

User-added image
Venkat R BadeVenkat R Bade
You should create the formula at object level. Report level formulae are only summary formulae.

Go to the object on which this report is based on and create a new formula custom field. Select the output as number. Decimal point to be 0. and then create the formula.

Once you save it. Come back to the report and add a filter to the report saying fieldName__c = 1. That should do the trick.
Matthias FraschMatthias Frasch
Thank you so much, Venkat. I'll do that and let you know the outcome.
Matthias FraschMatthias Frasch
Venkat. It worked but I ended up using: IF(OR(Lead.OwnerId = $User.Id, Contact.OwnerId = $User.Id), 1, 0) in order to capture leads or contacts. thanks again.
Venkat R BadeVenkat R Bade
No problem. Leave a thumbs up to my answer if it helped you.
Steve MolisSteve Molis
Hi Mattias, you could make things a little simpler by using a datatype:
Formula(Checkbox) and a Formula like this:
OR(
Lead.OwnerId = $User.Id, 
Contact.OwnerId = $User.Id
)
My original post pre-dates the Formula(Checkbox) datatype
 
Matthias FraschMatthias Frasch
Steve- I'll take a look into this. Still learning all the tricks. Much appreciate you all your help. Now, lets talk beer. Have you ever had "Weihenstephan Hefe Weisse" ? Good German beer. 
Tim ChingosTim Chingos
For anyone looking to solve this for Created By, I used a modified version of Steve's recommendation:

Name: Created by Me
Formula field: TEXT
IF( CreatedBy.Id = $User.Id , "Yes" , "No")

Then I have a report on dashboard where "Created by Me" = Yes.  I use this so my SDRs can track the Opportunities they generate for AEs.

Thanks Steve!
Brett StonierBrett Stonier
Steve, this worked really well for me.  Thanks for the tip.
Leo SantillanLeo Santillan

Steve, or anyone who knows a lot about reports/dashboards:

I have a dashboard that runs great when you choose the "View as" and then you choose the User that you want. 
We have 15 managers and I would like to create a Dashboard filter so you can pick the manager name from there and the values will show as if the picked Manager  was the running user of the Dashboard. (just so all of the Executives don't have to remember the names of all of the managers when comparing numbers and reports).
Any help will be appreciated!

Christopher RobinsonChristopher Robinson

Can this be used on queue memberships?
I.e. I want to run a report, that returns both:

Leads owned by current user (I think you've already provided that formula field on lead to handle this)
AND
Leads owned by Queue current user is a member of.

Steve MolisSteve Molis
Hi Guys,
Sorry the origoinal question is so old that I don't get notified when new follow-up questions are posted.  You're better off posting these as new Questions in the Answers Community so everyone can see them
Steve MolisSteve Molis
No problem @CGrez glad it worked for you
Elizabeth BauerElizabeth Bauer
Hi there, I'm trying to follow along the lengthy thread. We have a requirement to have reports & dashboards only show the data relevant to the logged in user. I've created the checkbox on my custom object as mentioned above... 
OwnerId = $User.Id
To allow for our consultants to only see their data on the reports & DBs. 
Is it supposed to be default checked? On Page layout? How would you make it default checked if thats what it needs to be? 
I've added the filter to the report but I feel as though I'm missing something. 
Any help or guidance would be appreciated. Thank you! 

Elizabeth 
Steve MolisSteve Molis
Hi Elizabeth, did you create just a regular Checkbox field?  Or did you create a Formula field and select "Checkbox" as the Result Type?  You need to create a Formula(Checkbox) field and then use this as your Formula
OwnerId = $User.Id
and then use Checkbox [equals] TRUE in your Report Filter
 
Elizabeth BauerElizabeth Bauer
Thanks for getting back so quickly Steve! Yes, its a formula box exactly as you've done above. But it is default unchecked.. which is where my gap is. And given that it's a formula field, it's a locked field. User-added image
Steve MolisSteve Molis
Hi Elizabeth, I'm a bit cunfused.  There is no "Default Value" on a Formula Field.  The Value will always be whatever the Formula Result is when it evaluates the record.  

Can you elaborate on what you mean by "But it is default unchecked."? 
Can you create a Report or a List View that displays all of the Fields in your Formula and the Formula results side-by-side and post a screenshot? If you're getting a bad or unexpected result instead of a Syntax Error, it's gonna make troubleshooting the Formula a LOT easier if we can see the Input and the Output side-by-side.  

Tips & Tricks: Measure twice and cut once with Formula QC List Views and Reports
https://success.salesforce.com/answers?id=9063000000046BfAAI
 
Elizabeth BauerElizabeth Bauer
User-added imageUser-added image
Here's my thinking: 
Because it is a formula field, unchecked, it won't return the results that I'm looking for.. which is for Steve to look at the Report & DB and only see his records. 
I'm getting zero results returned on ym report. 
Does that make more sense? 
Steve MolisSteve Molis
There are basically 3 reasons why something doesn't show up in a Report or Dashboard: 

1. It does not meet the scope or filter criteria of the Report
2. The User does not have at least Read access to it
3. It does not exist  

How are you testing this?  Are you logging in as the other User?  
I can't see a lot of the fields that are being used in your Report filter in your screenshot of the Project detail record, so I can't even tell if the record would have shown up in the report with or without the checkbox.  

What happens if you remove the last Filter: 
AND Running User [equals] TRUE and replace it with  
AND Project Owner [equals] Steve 
Then re-run the Report and see if the results change
Elizabeth BauerElizabeth Bauer
I’m logging in as a user that this would apply to. Basically, the structure we have is the OWNER of the record is the Project Manager, and the consultant is a User look-up. The dashboard is meant to show “My Assigned Projects” The director isn’t interesting in every Consultant doing their own report, so I was hoping this would be an appropriate solution. Security on the field is visible only to me and the consultant profile. Without the formula field checkbox, the report is currently grouped by Consultant, to show a summary or records for that particular person.
Steve MolisSteve Molis
Wait...  whut the... who the...whut?!? 

"consultant is a User look-up"
Steve MolisSteve Molis
Are you trying to filter this report so that it return only records that the User = the Consultant?
Elizabeth BauerElizabeth Bauer
Yes correct. Only trying to return results for the User = the consultant. (Current field to capture WHO the consultant is is called Primary Consultant (user Look up) And so when the Consultant looks at the DB from their perspective, it is only pulling in records associated to them. If I did a filter for each consultant on each report we’d be talking like… 60 variations of the same report “Primary Consultant = Steve” rinse repeat. I owe you many beers. Where are you located?
Steve MolisSteve Molis
That's not the Record Owner then...

You'll need to use a Formula like this:
$User.Id =  Consultant__r.Id

 
Elizabeth BauerElizabeth Bauer
You are right. Now, if I’m using the report, then the checkbox does need to be checked, correct? And my checkbox is currently unchecked. And I’m not sure, if I need to add in an “If field not blank, check box” ?
Steve MolisSteve Molis
It depends on what records you are trying to include and exclude from the Report or Dashboard Results results
Dipika KothariDipika Kothari
Hey Steve,

I have role hierarchy setup. My client wants to have a dashboard view by rep and manager. How can i manage this?
Will filter "my accounts" and "my teams account" work?
Steve MolisSteve Molis
Dipika Kothari 
Can you provide more detailed information?  Are you trying to use the Formula that I posted to do that, or something else?  
Can you provide an example of the Report, Dashboard, the Filters and Settings that you are using?
João MachadoJoão Machado
Steve Molis,
Based on your idea, I was able to develop a somewhat different formula/filter for my report... I wanted to see only certain records based on the User Role Name, so I came out with this: 

(Boolean)
CONTAINS( $UserRole.Name, Custom_Lookup_Field__r.Name )

Then I filtered my report by this new field equals "True" - worked accordingly!!! Thanks a lot!!!
Steve MolisSteve Molis
No problem @João Machado glad it was able to help you
Kelley HarrisKelley Harris
Hi Steve Molis,
Hoping you can help with a reporting question. We have OWD setting for Opportunity that is Private, but opens up view/visibility to users through sharing rules (I think in doing this we open up visibility to Opportunity data in reports). In our Opportunity reports, we want to limit data visibility to only allow users to see 'My Teams Opportunities' (only seeing things they own or things below them in the role hierarchy own). Is there a way to lock down the standard report filter 'Show Me' ? Is there another workaround we could use to ensure that in backend reports a user would not be able to open up the data to see all opportunities?

Thank you!
Steve MolisSteve Molis
@Kelley Harris  

I think you might need to do this in your User Role Settings or maybe Sharing Rules.  The Formula that I built here was just for a dynamic Report Filter using a custom Lookup(User) field, nothing more, it does not have anything to do with data access or visibility.
Natasha MukherjeeNatasha Mukherjee
Thanks @Steve Molis, the solution worked for my use-case.
Steve MolisSteve Molis
No problem, @Natasha Mukherjee you owe me a beer!  ;-D
Ruth SmithRuth Smith
SteveMo you helped me yet again - thank you. My use case was slighty different but the ah ha moment of making a fileds value dynamic based on the current user- phenomonal. 
Reminds me of that moment in the new Star Trek move where Spock shows Scotty the formula he will someday write to make warp speed travel possible and he said "It never occurred to me to think of space as the thing that was moving."
Steve MolisSteve Molis
Awww thanks @RuthSmith, glad that it helped you
Waciuma MainaWaciuma Maina
Thanks a lot, this is an elegant solution (especially the formula checkbox)!
Kyle ArmsKyle Arms
Steve, 

Very new to Salesforce and I am having trouble implmenting the solutions you have provided. I am attempting to create a report that shows revenue per sales rep based on an invoices object, and share that report with the sales team so that they only see their own revenue. The issue is that some invoices are owned by one user, but the actual sales rep behind the invoice is another user. This makes running the report with "my invoices" innacurate. I have attempted using this in the formula field:
 User-added image
Where salesperson__c is a user look up field already on the invoice object. 

I am currenlty returning no records when running a report where this field is set to true. 

Thanks

 
Steve MolisSteve Molis
Can you create a Report or a List View that displays all of the Fields in your Formula and the Formula results side-by-side and post a screenshot?  If you're getting a bad or unexpected result instead of a Syntax Error, it's gonna make troubleshooting the Formula much easier if we can see both the Input values and the Output results side-by-side.  

Tips & Tricks: Measure twice and cut once with Formula QC List Views and Reports
https://success.salesforce.com/answers?id=9063000000046BfAAI 
Tommy StolzTommy Stolz
Hi Steve, 
I'm a new Admin and have been reading through these threads but for the life of me, I cannot get it to work. 

I need to have dynamic dashboards for our sales team, I have the info they need in reports but it is showing everyone's data, not the user logged in's data. I have entered the formula OwnerId = $User.Id and then use Checkbox [equals] TRUE in the Report Filter. This usually pulls up nothing..... I have been trying to fix this for weeks and I know its user error but as I said I'm new. Any step by step would be much appreciated.

Thanks  
Ruth SmithRuth Smith
Tommy I used the formula Steve had suggested with the number (I wanted to be able to sum if I needed to later) so mine looks like this - IF(OwnerId = $User.Id ,1, 0)
When I log in as users this works just fine.
Not sure if will help your troubleshooting to try it this way and compare in  report? 
Due to it being dynamic you will have to keep logging in at different users to troubleshoot. :-(
Steve MolisSteve Molis
If you're getting a Bad Result instead of a Syntax Error, can you create a Report or a List View that displays all of the Fields in your Formula and the Formula results side-by-side and post a screenshot?  It's gonna make troubleshooting the Formula a lot easier if we can see the Input and the Output side-by-side.  

Tips & Tricks: Measure twice and cut once with Formula QC List Views and Reports
https://success.salesforce.com/answers?id=9063000000046BfAAI
Tommy StolzTommy Stolz
User-added imageUser-added image

So it is "working" but this is the user who created the account and not the user logged in's number...... What am I missing here? 
Steve MolisSteve Molis
Sorry you lost me, are you trying to filter this Dashboard using the Formula Checkbox field?
Tommy StolzTommy Stolz
I want the report to filter by the user logged in. This way the dashboard will only show the logged in sales reps numbers. Right now it's showing the numbers of the person who created the filter or report. 
Marcel HobizalMarcel Hobizal
This is definitely an oldy but great resource!  Thanks @Steve for all of your expertise here.  I just wish we could scheduled reports had even more logic and would only send to the owner's of records on the report. 
Hayley CliffHayley Cliff
Thank you for this, this has proved very very useful, you are a hero. 

This is the best I can do for beer .... 

User-added image
Steve MolisSteve Molis
No problem Hayley, glad you found it useful 

Cheers,

SteveMo
Bruno BoutantinBruno Boutantin
Hi Steve,
I use external objects created from a SAP sync, In Account Lightning page I added a Report section with a chart showing orders, the data are filtered by AccountID. So the user sees what is related to the account. Fine, but... when the same user goes to Reports menu and run the reports supporting the chart he gets all data from all Accounts exposed. How I could I fix the issue. Thanks.
Steve MolisSteve Molis
@Bruno Boutantin

Sorry baby, I had to cash that Honda... 
https://www.youtube.com/watch?v=5lL1ypndnWA&feature=youtu.be&t=51 (https://www.youtube.com/watch?v=5lL1ypndnWA&feature=youtu.be&t=51)

I posted that 8+ years ago, and the original question had nothing to do with External Objects or anything like that.

You're probably better off posting this as a new question.  Also, post a screenshot of the Report, Settings, and Filters that you're using, the results you're getting, and a mock-up of what you want.  ​​​​​​​For questions related to Reports and Dashboards those are really helpful (otherwise it's like playing "Pin the Tail on the Donkey")

 
Bruno BoutantinBruno Boutantin
Thanks
 
Elyse KingElyse King
This has to be one of the best solutions of all time.
Nicole PerryNicole Perry
This is amazing! It even works to filter out list views by custom owner field. Thanks so much Steve for sharing.
Steve MolisSteve Molis
No problem Nicole, glad it helped you out (you owe me a beer!)  ;-P
Wallace OliveiraWallace Oliveira
Steve, hi!

Dont fix for me!

My field dont show up on my reports :(

You had only idea why?
Steve MolisSteve Molis
Wallace, Can you provide more details and screenshots? Are you using a custom report type?
Cash ShurleyCash Shurley

Before I came to the page, I found a similar answer in KB article https://help.salesforce.com/articleView?siteLang=en_us&id=000338834&type=1&mode=1, but I was confused by this:

Note: Do this for each of the Lookup(User) fields present on the Object.

Then I found this excellent page and a comment above helped clarify it for me.  I needed to create a report that shows all open cases by createdBy, so a formula checkbox does the trick, as advertised.

CreatedBy.Id = $User.Id
And now I understand that you would create another checkboox formula field if you wanted to report on a different user lookup field (like say "Last Modified by").

So it would be clearer to state "do this for each lookup(user) field that you want to filter a report on."

Good stuff regardless!
Steve MolisSteve Molis
Yeah, it will work on any field that holds a User.Id in it
Alimali StephenAlimali Stephen
Just followed up the lenthy responds and I got kinda lost. I have similar issue, I happen to create a dynamic dashboard for sales reps. On each user perspective I would love them to see e.g opportunities that they own. I have configured a report and filtered view "My Opportunities" logged in as one of the Sales Reps but unfortunatley I don't see any opportunity records that they own. Just asking am I supposed to create the formula field with a check box data type on the opportunity object?

I would appreciate any help to solve this puzzle.
Steve MolisSteve Molis
@Alimali Stephen 

What setting did you select for the Dashboard Running User?  That will determins which Opportunity records appear in the Dashboard if the Source Reports are set to "My Opportunities"
Alimali StephenAlimali Stephen
User-added image
@Steve That's the view as dashboard settings...
Alimali StephenAlimali Stephen
@Steve tried the checkbox formula field it works with opportunities but not with a custom object that was created previously. I want to find a solution where sales reps can view their sales by using "Sales Rep ID" as a unique factor
whether as a formula field on the custom object or a formula on the report. The end results is to enable each Sales Rep to view his/her own Sales on the report. Any idea how I can work this around?
Ashley BettilyonAshley Bettilyon
Hi there  - I was trying to use this formula to help when viewing a case history report so the user can  see only the cases they "edited." 
Do you have any suggestions on if this is possible? I was attempting to use the formula like the below but there are times where someone edits a case and they werent the last modifer which means those cases would be included in the report. 
IF(LastModifiedById = $User.Id , 1 , 0)

 
Thanks!
Steve MolisSteve Molis
@Alimali Stephen 

What is the Custom Object and what is the  "Sales Rep ID"? 

Are you now saying that the Dashboard contains a mix of source reports that includes Opportunity Reports and Custom Object Reports?
Steve MolisSteve Molis
@Ashley Bettilyon 

Unfortunately I don't know if you'll be able to do that using the LastModifiedBy from the Case History, the Case History Object does not support custom fields, and that where you would need to create the custom Formula Field 
Alimali StephenAlimali Stephen
@Steve Molis yes the Dashboard contains a mix of source reports. Custom object is called Orders and each Sales Rep has Order Records, the Sales Rep Id is assigned to Sales Rep on User level.
 
Steve MolisSteve Molis
That's the problem, they are not the same "common" field