9 answers
Renee,
Thanks for the clarification. You're right in that the suggestion I made was including all of the Opportunities that met the criteria regardless if they were on an Account that has a Pending Renewal Opportunity for this month.
That said, this is an Opportunities Report Type and I'm not aware of a way that you can filter that report type to only include/exclude specific Accounts since the data is all based on Opportunities. But, you can do it by creating a custom Report Type.
Report Type:
If you're not familiar with setting them up, navigate to Setup - Build - Create - Report Types. Click New Report Type, select the Primary Object as Accounts, ill in the required fields, and check the box for deployed. For this example, I created the Report Type called "Accounts with Opportunities". Click Next and in the Object Relationship, Account should be listed as Primary Object A. Underneath that click the box to related to another object. Here, you'll want to select Opportunities and keep the box selected for Each "A" record must have at least one related "B" record. Click Save.
Now, you can navigate back to Reports and create a new one choosing the Report Type "Accounts with Opportunities. Then, set the filters and filter logic as I had previously described:
Opportunity Record Type = Renewal
Close Date greater or equal 1/1/2016
Stage equals Closed Won, Closed Lost
Close Date equals THIS MONTH
Stage not equal to Closed Won, Closed Lost
Filter logic: 1 AND ((2 AND 3) OR (4 AND 5))
Now, we're back the same point and we need to do one last step to exclude any Accounts that do not have a Renewal Opportunity with a Close Date during this month. To do this, click the arrow on the "Add" button at the top of the filters and choose the option for Cross Filter and choose the following:
Accounts with Opportunities and Add the following Opportunity Filters:
Opportunity Record Type = Renewal
Close Date equals THIS MONTH
Stage not equal to Closed Won, Closed Lost
Here's a screenshot of your filters and logic when all said and done:
That should then do it. This will give you a list of the Opportunities that meet your criteria but only if they are on an Account that has a Renewal Opportunity for This Month that is not Closed Won or Closed Lost. If you also want the Accounts included that have a Renewal Opp This Month but it is already Closed Won or Closed Lost, just remove the very last line from that list.
Doug
