5

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?

Improve this question

2 Answers 2

Reset to default
4

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.

Custom Report Type definition

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

Unfiltered: Unfiltered report

Filtered: Filtered report

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. enter image description here

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

enter image description here

Improve this answer
1
  • first off ty. How would I report on all contacts that are deacons and that are NOT retired?
    – fourq
    Oct 17, 2012 at 19:01
3

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.

Improve this answer
3
  • @gringriforce I like this answer. I was also thinking about creating a trigger that would update a text field with a semi delimited string of all milestones. If I did it with a text field would I be able to report against it?
    – fourq
    Oct 17, 2012 at 3:59
  • @fourq Yes, if you had a text (or multi-select picklist) field like Deacon;Retired;... you could definitely report against that but I would avoid creating a trigger if you don't have to.
    – Greg Grinberg
    Oct 17, 2012 at 4:14
  • You can create the report with cross-filters.
    – Mike Chale
    Oct 18, 2012 at 23:21

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .