+ Start a Discussion
tbuckettbucket 

Reporting on Accounts with NO open Activities

My generic question is around reporting in SFDC on things that are NOT there.  I was trying to write a report a while back to identify Accounts without any open (or completed for that matter) Activities.  The intent was to create a Dashboard component called "Top 10 Neglected Accounts".  I had no problem creating a new report type, then writing a report with Accounts and Subjects and simply looking at it with my eyeballs.  If I saw blank rows in the Subject column, I had my answer.  I thought I was on the right track and was headed down the path of just counting records under each Account.  Well, that would not work as it (correctly) would show 1 record for Accounts that had 1 or 0 activities....and I just need the 0's.

 

I probably got going down the wrong path way upstream here.  Does anyone know that right way to do something like this?

 

Thanks in advance!

Steve MolisSteve Molis

What are you using for your Filter Criteria in your report?  

 

If you just use an Account report and select:  

Last Activity Date  [equals]   <blank>  
OR
Last Activity Date [less than]  TODAY 

that will select any Account with NO activity, or only completed activities (No OPEN Activities)

tbuckettbucket

Yes, of course, null in Last Activity!!  OK, OK, it's coming back to me now.  You know, sometimes the obvious is not so obvious when you are up late trying to do this stuff!

 

Way back when I tried the whole "Today" thing, I remembered I tried that but it will only accept a date, which means it will not maintain itself and the report what have to be touched...making it not a good candidate for a dashboard component.  The way I had worked around that was by creating a custom field called "Days Since Last Activity" and using that in my logic.

 

So, I am sure there is more than one way to solve this, but here is how I solved it using :  I used your NULL recommendation in conjuction with a custom formula field called Days Since Last Activity.  So now I get a list of Accounts that have not had an Activity in over 45 days (but I could have done 0 or 1) OR no activity at all.  By using the Days Since Last Activity, the report maintains itself and does not requiring updating....so it makes a good dashboard component. 

 

So now my report criteria looks like this:

 

Filtered By:1 AND (2 or 3)   Edit 
   1. Strategic Account equals True  
   2. Days Since Last Activity greater than 45
   3. Last Activity equals  

 

Thanks for the idea!