If you didn’t read my first post about Cross Filters, read my first post about Cross Filters.
If it still isn’t clear after you’ve read it, I think these are really important to understand, and I think there are too many people out there giving bad advice on solving problems when something like this could be used instead.
For example, what if you need to know with of the F-14s on the USS Enterprise weren’t involved in that dogfight with Maverick and Iceman shooting down those 4 MiGs? You could do a custom report, but a Cross-Filter is easier. Don’t forget to exclude Hollywood’s plane, it was shot down.
Cross filters allow you to pull tabular reports that filter based on related objects such as Accounts without Opportunities, without having to have the related object as part of the report.
Here is a quick video I stole from Salesforce
I want to show a few use cases I’ve explained to people in the past regarding cross filters, and hopefully by showing a few more advanced cases you’ll have a better appreciation for why I like them so much, and why I think that they are so under-utilized
Case #1 – Filtering a Cross-Filter
Use Case: Give me all Accounts without Open Opportunities. The key here is the Open part, I’ve already shown you Accounts without Opportunities here
This one can be hard to wrap your head around because it isn’t always intuitive.
Here are my 3 example accounts. One has no opportunities, one has an open opportunity, one has a closed opportunity.
Here is a basic Account report with my three accounts ready for export
I can also filter that Cross Filter to say: Give me all Accounts without Opportunities, but only in cases where the Opportunities are Open
Maybe it’s just me who is slow with this, but I hate the wording on these. I always end up creating test data to wrap my head around it. This one is easy because you can change it in your head to say “All Accounts without Open Opportunities”, but when you get into other fields that adjustment doesn’t always form a sentence as well.
The important thing to remember for filtering is that it reviews all the Opportunities. So if A3 had both an open and a closed, it would not show up anymore because it has an open Opportunity, and we are only looking for Accounts that have zero open Opportunities.
Summary: Filters are great, but use test data and go back to the original records to make sure you have it right. Or somehow understand the wording SF uses better than I do. I honestly think that this is what scares people off of Cross-Filters sometimes
Case #2 – More than 3 cross-filters
I had this one recently and everyone but me shall remain nameless where the person asking the question needed to filter on 4 different cross-filters. He was doing exactly what I think the biggest benefit to cross filters is, and that’s analyzing potentially bad data.
The original request was to pull a list of Accounts that have at least one of: Contacts, Opportunities, Cases, and Activities. The key was that the person said it was because he wanted to do cleanup. What the person actually meant was that he wanted this report to compare to a full account list, and therefore get his garbage data.
The recommendation provided was one big join report. I guess it could work but the issue again becomes that it isn’t exportable as a tabular report and is quite frankly a pain to deal with. You have to watch out for duplicate 15-character IDs if you don’t use the CASEIFID formula to get an 18-character ID…there are so many things that can go wrong here.
My solution was to run two different reports with Cross Filters, and then stitch them together.
If you get Accounts that have no Contacts and no Opportunities, and put it with a report that has Accounts that have no Cases or Activities – any Account that doesn’t exist in both lists obviously has nothing. Problem solved, you’ve just filtered by 4 different related objects at the same time.
Picture exporting the results of two of these, just with different related object filters:
Now picture having to accomplish the same thing using a join report…..once you get the hang of these there is no turning back
Case #3 – Using ORs with Cross-Filters
A user on the Success community wanted a report on Opportunities with the following criteria:
- Are at a certain stage
- DO NOT have an open task OR Have an open task that is older than Today
This was posted on the forum and of course the first response to come back was to create a custom report type….which if you’ve been reading is probably wrong. By the time I posted about Cross-Filters the person had already started building but I managed to get him to back away in time to avoid wasting a day banging his head against the wall.
Similar to Case #1 above I like to start with my test data. 3 Opps, each with a task – one closed, one open in future, one open in past. I created this with the Activities with Opportunities standard report type, but that’s just to make sure it’s there. I’ll be moving to just a regular Opportunities report
Here is our regular Opportunity report with a basic stage filter
Next up is to get just those without open tasks, which is to say O1 only.
We’ve covered this already, but the Cross Filter above is saying give me all Opportunities without Open Activities. The part I want to cover is that you get the same results by saying give me all Opportunities with Closed Activities in this case.
You’ll note that you get the same result here. Now this doesn’t always hold true, but it’s important in this case and the reason is that his last request specifies that for Open Activities to be considered valid, they must be in the future, which is essentially an OR statement. He specifically calls it an OR, I had to think about it differently. An OR statement is just catching more data than an AND statement really, but there is still potentially data on the other side of that when you think about a full bucket of data. Bear with me again, I’ll try a real’ish example.
Say you are going grocery shopping, you have your meat section, your veggie section, and the aisles where all the crappy pre-packaged garbage food resides.
Saying give me all ingredients that are Veggie AND Meat returns nothing.
Saying give me all ingredients that are Veggie or Meat brings back pretty much 90% of my shopping cart.
But there is still the last 10% that forces me to go down the aisles – pasta, rice, canned tomatoes, etc. Honestly, it’s only 10% of my grocery cart, so much of that stuff is crap.
So instead of thinking of a cross-filter that has extra filters as an OR statement, think of it as excluding everything else. For this I go back to my test data:
What I need to do is get a report that Excludes O2. So it’s not “include this OR this”, it’s “Exclude this” which looks like:
This filter is saying:
Give me all Opportunities without an Activity that is Open, and due in the future.
As a result, we get all the other opportunities that don’t fit this criteria, which is getting around the OR statement.
I’d be lying if I said that this didn’t take me awhile the first time but once I got it, everything made perfect sense.
Always remember with Cross-Filters that they are evaluating all the child records.
Accounts without Open Opportunities is only pulling back Accounts that have zero Open Opportunities, not all accounts that have a closed Opportunity.
Hopefully be reading these you understand them a little better. I can always spot when they would be useful, but I still sometimes have trouble writing them. I think that this is why so many people don’t use them. They knew enough to pass the certification, and then promptly forgot about them
- Take your time understanding the requirements
- Create test data in a sandbox
- Create your filters
- Add more test data to ensure your report is still running as expected
- Push your new shiny report to production
- Sit back and pretend like it took you days to come up with this