0

I have a request for reporting on all accounts that don't have at least 1 contact that is a Campaign Member on a specified campaign, but I'm struggling to build this report.

It feels like cross filter reports will be involved somewhere and I also have "power of one" fields on all my objects (https://www.adminhero.com/power-of-one-the-most-brilliant-analytics-trick-of-all-time/), but I can't seem to get them combined in the right way to provide the results I need.

What is the right way to build this?

1 Answer 1

1

I've had to implement this solution before, and it's tricky. You can't use a cross filter to do this in a report, and the obvious workaround (a Rollup Summary Field from Campaign Member to Contact) isn't supported.

There's no way to do it in a fully generalizable way (i.e., you can run it on any Campaign) with vanilla Salesforce functionality without either writing some Apex or using Excel and an INDEX/MATCH construct. If you're focusing on a single Campaign that will never change, there's probably a third solution using Declarative Lookup Rollup Summaries.

Option 1: Apex

You can build this as a Visualforce report by running two separate Apex queries like the below. I'm assuming campaignId is defined, and your Campaigns use the Status value 'Registered'.

List<Contact> members;
members = [SELECT Id, AccountId
           FROM Contact 
           WHERE Id IN (SELECT ContactId 
                        FROM CampaignMember
                        WHERE CampaignId = :campaignId
                        AND Status = 'Registered')]; // Your status here...

Set<Id> accountIds = new Set<Id>();
for (Contact c : members) {
    accountIds.add(c.AccountId);
}

List<Account> accounts;
accounts = [SELECT Id, ... [additional fields]
            FROM Account
            WHERE Id NOT IN :accountIds];

// Do something with list of Accounts and display in Visualforce. 

Option 2: Excel

The easier, but less automated, way to approach this challenge is to create two reports and correlate them in Excel. I would probably try this with a Campaigns with Campaign Members report, on the one hand, and an Accounts and Contacts report on the other. Then an INDEX/MATCH construct in your Accounts and Contacts report can determine whether each Contact is a campaign member, and you can create a pivot table to summarize and filter the data by Account.

2
  • Thanks David - business needs have already ruled out Excel, so it's Apex and VF time then.
    – Rob Cowell
    Jan 23, 2018 at 15:08
  • 1
    @RobCowell The other way I've finished off this solution is to programmatically insert parameters into a standard Salesforce report using the Analytics API - the user clicks a button to fire a Visualforce page that runs these queries and then dumps the Account Ids into an existing Salesforce report the user can run and use other tools on. Good luck!
    – David Reed
    Jan 23, 2018 at 15:10

You must log in to answer this question.

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