Red Argyle Logo

Patterns
The Salesforce Blog with Tailored Goodness

Salesforce Cross Queries = Awesome

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”

Salesforce Cross Queries

Next, add a filter, and choose “Constituency Equals Parent”.

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.

Comments

  • This capability has been around for a while though — perhaps a more interesting post would be "show me every household that doesn't have at least one member that is a parent."