I have a custom object named Milestones (with a field named MilestoneType) that has a Master-Detail relationship to Contact. I want to build a report where I can pull all contacts that have a milestone with a milestone_type of Deacon, but exclude those that also have a milestone_type of Retired. How do I build the report so that this conditions are met?

Attribution to: fourq

Possible Suggestion/Solution #1

Create two roll-up summaries on Contact. One that counts all milestones where type equals 'Deacon' and one that counts all milestones where type = 'Retired'. Then report on all contacts where deacons > 0 and retired = 0.

I don't believe there is a way to directly build this in a report without adding these roll-ups on the contact.

Attribution to: Greg Grinberg

Possible Suggestion/Solution #2

You can do this with Custom Report types (CRT). First, create a custom report type for Contacts with Milestones by going to My Name -> Setup -> Create -> Report Types. Choose Contacts as the base object and then add a relationship for Milestones.

Create a new report based on the new CRT and you can filter on MilestoneType:

Unfiltered:

Filtered:

Edit To show only Contacts who are Deacons and NOT retired we need to use cross-filters.

Unfiltered, showing updated Contacts. Note that the Contact "Second Test" has milestone types of both Deacon and Retired, while the other is just Deacon.

By setting a cross-filter of Contacts without Milestones and Milestone Type equals Retired we filter "Second Test".

Attribution to: Mike Chale
This content is remixed from stackoverflow or stackexchange. Please visit https://salesforce.stackexchange.com/questions/3405