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?


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) {

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.

  • Thanks David - business needs have already ruled out Excel, so it's Apex and VF time then. – Rob Cowell Jan 23 '18 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 '18 at 15:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.