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?
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:
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".
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.