+ Start a Discussion
Sunny GSunny G 

How can i show all the users with zero opportunities



Please help me to create a report that display all the users who have not created any opportunity in last 7 days.

prathap raoprathap rao

Look at this link:



You can get the different formats salesforce offers you.


When you are creating a new Report in the last step when it asks your for Select criteria make sure you have your criteria as below


Created Date Less than LAST_N_DAYS:7


Hope this works:)



You cannot create "exclusion" reports in standard Salesforce reporting.  i.e., you can create a report that shows everyone that HAS created an opportunity in the last x days, but you cannot create one that shows who has NOT done so.  It's just not something that the Salesforce reporting engine supports.  You will have to use third-party reporting tools, download to Excel, SOQL, VF, etc. to do this.


Thanks Sir...


The work around would be similiar to what I had to do and was suggested to me for opportunities without a related partner record.


1. For you situation I believe you would create a checkbox field on the Account object.  Call it 'Opportunity_Exist__C' for this example.

2. Write Apex code that loops through the follwing query:


                     Select a.Id, a.Opportunity_Exists__c, (Select Id From Opportunities) from Account a


3. if the sub query 'Opportunities' is empty set the Opportunity_Exists__c to false; otherwise make it true.


4. Schedule the apex code to run every night.


5. Create report that utilizes the Opportunity_Exists__c equal to false.  That will give a report of all accounts that do not have opportuntiies.


Cons to this work around:


1. It gives a snapshot in time based on the frequency that the code is schedule to run.  For example, if during the day a user creates an opporutunity for a given account that account will still show up as missing an opportunity until the next time the code executes.


2. Running code that makes updates will update the last mod date and modified by fields to the time the code executes and whoever schedule the job.  There are work arounds for this so that you do not lose the capture of who update the record last from the screen but is in to much depth for me to type it out.



Where is a snapshot portion of code that I wrote for my situation described in my first statement above.  It can give you a general idea of how you would need to create the loop for your situation.  The code in red would be what detects that red portion in the query select above does not have an associated opportunity.  Again, this may be somewhat confusing because you are trying to do this for account and my example show how to do it on for missing opportunity partner records on the opportunity.  The parent child relationship is the same though.  I hope this helps move you to a solution.


 for(Opportunity o : scope){ 
            if ( o.OpportunityPartnersFrom.size() > 0 ) {
                o.MissingPartner__c = false;    //Opportunity has partner record(s)                               
            } else {                                        
                o.MissingPartner__c = true;     //Opportunity is missing partner record(s)                 
            // Update the Custom field so we can know who really last modified the record
            // This is necessary because the audit trail from user updates would be lost, otherwise            
            if ( o.LastModifiedby.Name <> UserInfo.getName() ) {                
                o.Last_Modified_By_User__c = o.LastModifiedBy.Name + ', ' + o.LastModifiedDate.formatLong(); 


Todd KadasTodd Kadas
I know this is old but I was able to acheive this by creating a custom report type users with or without opportunities created by.