In this example, we are looking to find donors whose giving history match the following criteria.
- The donations sum made by the donors/contacts
sum should be atleast $10,000 for the last 3 years.
- Each individual donation to be less than
This multi-step report is built in 3 steps. The first two steps come from the Opportunity Contact Role object, with the same set of fields
but with different filter criteria. The third step is from the Contact object.
- Step 1 is from the Opportunity Contact Role
(OCR), with fields from the Contact and Opportunity objects, and filtered by fields
from the Opportunity object. Basic fields required are the Full name (Contacts) and
the Opportunity Amount (Opportunities) and the contact lookup
field (OCR), which is required for the linkage. Filter terms from the opportunity
object include the close date range for the last 3 years and the Opportunity
Amount:Sum (a metrics field from opportunities) to be atleast 10,000.
- Step 2 is similar to step 1, except for
the filter criteria. This time we want the Opportunity Amount as we are looking for
individual donations. You can clone Step 1 of the report and just change the filter
condition, where the Opportunity amount is greater than $500. No linkages required in
this step too. If you run the report, you will get 2 separate blocks of contacts,
which match the respective filter terms.
- Step 3 is created from the contact object.
Select the fields you require for the final report including the contact ID field,
which is necessary for the linkage. This step has no filters (you can add filters if
required). The linkage step is the key here. You need to link all the with the
contacts that match the filter criteria in step 1 but not the filter criteria in step
2. You also need to get all the records that match the 2 relationships. This means that only the contacts that match step 1 but not step
2 will show up in the final list.
The image belows will give you an idea of the linkages
for this report.