How do you create a report showing accounts that haven't had an opportunity created during a certain period of time? - Answers - Salesforce Trailblazer Community
Trailblazer Community
Ask Search:
Brendan ConroyBrendan Conroy 

How do you create a report showing accounts that haven't had an opportunity created during a certain period of time?

We would like to see a report showing all accounts in Salesforce that shows accounts that haven't had an opportunity created for 6 months, 1 year, 2 year etc. 

Is that possible? Thanks so much
Best Answer chosen by Brendan Conroy
Jon TreskoJon Tresko
Ugh, the problem here is that Roll-ups do not allow Smart Date filters i.e. LAST 6 MONTHS or THIS YEAR.

What I was basically getting at is that you can simply do a roll-up counting all records with Create date Greater than LAST 6 MONTHS.

They also cannot use Formula fields as filters.

One way to achieve this, that would require maintenance a every month or so, is to do a Roll-up summary for each date range, (3 months, 6 months, 12 months, etc) and do a count of opportunities with create date or close date greater than a specific date. It would, of course have to be updated each month to keep moving the dates up.  It's only about 5-10 minutes each month to update it, but it would be a routine Admin task. Unfortunately, without EE, this may be the only way to achieve this! I'll put some more thought into it, but this would at least get you the reports you need now.

Your report filter is then easy... - Oppsinlast3months__c - equals - 0

See this screenshot: http://awesomescreenshot.com/0341hqnd81

This is a very simple solution in Enterprise Edition, because this is a simple filter in a report.

I know it's a big jump in price, but have you considered EE?

All Answers

Jon TreskoJon Tresko
If you're on PE, Outer-Joins or Exception reporting are not available.

There may be a way to do this with Roll-up summaries, though...

Give me a moment and I will test it out...
Brendan ConroyBrendan Conroy
HI Jon,

Yep PE is my org. Thanks for looking into rollups.
Jon TreskoJon Tresko
Ugh, the problem here is that Roll-ups do not allow Smart Date filters i.e. LAST 6 MONTHS or THIS YEAR.

What I was basically getting at is that you can simply do a roll-up counting all records with Create date Greater than LAST 6 MONTHS.

They also cannot use Formula fields as filters.

One way to achieve this, that would require maintenance a every month or so, is to do a Roll-up summary for each date range, (3 months, 6 months, 12 months, etc) and do a count of opportunities with create date or close date greater than a specific date. It would, of course have to be updated each month to keep moving the dates up.  It's only about 5-10 minutes each month to update it, but it would be a routine Admin task. Unfortunately, without EE, this may be the only way to achieve this! I'll put some more thought into it, but this would at least get you the reports you need now.

Your report filter is then easy... - Oppsinlast3months__c - equals - 0

See this screenshot: http://awesomescreenshot.com/0341hqnd81

This is a very simple solution in Enterprise Edition, because this is a simple filter in a report.

I know it's a big jump in price, but have you considered EE?
This was selected as the best answer
Marko LamotMarko Lamot
Well, if you have "Workflow & Approvals" enabled in PE you can do as follows:
- create custom field on account. e.g. "Last Opportunity Created Date"
- create workflow rule on opportunity that will update field "Last Opportunity Created Date" on account on every opportunty-create event
-> and you'll have field on account which you'll report on ;-)
Brendan ConroyBrendan Conroy
I've thought about it since a lot of the high level tasks I am given are inhibited by Professional's restrictions (even with all of the workarounds that I have found). I was quoted at 2 and a half times what we currently pay.

Is there a way to have workflow and approvals enabled in PE?
Marko LamotMarko Lamot
Yes, items like
* "workflow & approvals
* "profiles & page layouts"
* "APIs"
can be activated in PE :)  
but there is price attached to it :(  
on the other hand, especially when you are buying SF licences, you can make a good deal on those "add-ons" :)
if you are already SF buyer, it's a little bit harder to negotiate :(
Brendan ConroyBrendan Conroy
Yeah we already have 47 licenses, so that might not be applicable. I've looked into APIs and it is about 12K just for that. I can inquire into what workflow would cost (since in all probability that would be the reason that we would go to enterprise). Thanks guys!
Marko LamotMarko Lamot
All three have the same prices. Basically, if you buy two of them (considering their list price) it doesn't make sense anymore as you are already on enterprise price.  It make sense only if you are somehow in the possition to negotiate those prices...
Brendan ConroyBrendan Conroy
Good point. I sent my account exec an email to get a general idea.