Today I was working with a Salesforce customer on the Nonprofit Starter Pack and a formerly impossible requirement came up. It went like this:
Show me every household that has one member that has their constituency equal to “Parent” (For reference, the constituency field was a custom field on the Contact object)
Back in the “Olden Days”, being, the days before Cross Queries were available to us, that would have have been quite difficult. With the advent of Salesforce Analytics Edition however, that is now done quite simply.
Step 1 – pick a report type
In this case, I picked simply “Households” since I just wanted a household driven mail list.
Step 2 – Define criteria.
Here’s the magic. First, add a cross query. Choose “Households With Household Members”
Next, add a filter, and choose “Constituency Equals Parent”.
The logic for this is fairly basic, I want only households with Contacts in them and out of those Contacts, I only want ones that are parents. These requirements could be changed to anything though. Another example “Only show households that have kids with blue eyes”. It could be anything based on a contact field now.
Step 3 – Run the report and enjoy the results of your hard labor.
The resultset from the report now shows household data, and only households that have contacts who are parents in them. It’s a great way to report on a higher level object, and filter out lower level data that should not be included in your final result set.
Your executive director will probably give you a ticker tape parade, and hopefully even the rest of the day off.
