ExploitedDevOrgs

Changing the way Salesforce Admins and Developers think about client solutions.

Rant #1 – Cross Filters

Please read this, if you never read anything else I write, and you don’t know about Cross-Filters.  I guarantee you’ll make the time back the first time you use them.

Horse

Summary:

Ever wanted to quickly pull Accounts that don’t have Contacts, or Accounts that don’t have Opportunities, or Accounts that don’t have Activities?  Maybe you wanted to pull all three of these at once?  Or in the case above, people with male torsos who are not centaurs?  Something like this:

xComplete2

 

After all, what good is an Account in Salesforce if it has no Contacts and No Opportunities?  Especially if it also doesn’t have any Activities.  Something that fits all of these criteria seems to me to be an orphan and should be removed as part of a routine cleaning exercise.  With this simple report I can take a quick look and then delete these as I see fit

So what’s the best way to do this?  Well let’s talk about some of the wrong ways to do it.

First we need to step back and talk about Standard Report Types

By default, standard report types have two main characteristics

  1. They are always built using WITH, not WITH OR WITHOUT secondary records.  So you can look at Accounts WITH Opportunities, but it won’t also give you Accounts WITHOUT Opportunities
  2. The primary object in a standard report type depends on the relationship.
    • Master-Detail relationships have the Master as the Primary
    • Lookup relationships have the child as the Primary.

The second piece there about lookup relationships will seem odd to people but think about it:

  • There is a report type called Contacts and Accounts, there are report types for Activities with Accounts, Activities with Contacts, etc.  The only exception to this that I know of off the top of my head is Accounts with Opportunities but that isn’t even a true lookup because Salesforce allows you to do Roll-Up Summaries.
  • This is always something to keep in mind when you are building a relationship – how are you expecting to report on it?  Security is important as well – Master-Detail is always controlled by Parent, but in some cases the business may end up caring even more about reporting than they do about security.
  • Here is the full guide on relationship considerations

Rant Time!

And here’s where the rant starts.  Too often on the success forums I’ve seen people tell users to create a custom report type just to satisfy this simple request.  These are not just any users either, I’ve seen people with a lot of knowledge and posts, and even MVPs give the wrong solution to this problem.

  • Want Accounts without Opportunities? – custom report type
  • Want Accounts without Contacts? – that’ll be another custom report type
  • Want Accounts without Activities? – 3rd custom report type
  • Want to report on all three to get a combined view?  – that’ll be a join report
  • Want to use that join report to do some data cleanup?  Yeah, they are not exportable, only in printable view, at which point you’ll spend even more time putting them back into a tabular report.
  • Follow up: What if I want Accounts without Open Opportunities?
    • You’ll typically get some dead air on the other side of that question.  Why?  Because when you try to do this in the custom report type all the filter will do is remove open opportunities, you still have to sift through the accounts.

Summary: Awesome info, data cleanup sounds like it sucks.  You are looking to run different cases, and people are telling you to always start with the same Primary object and join them all together.  Think of how many custom reports you would be creating to get to this:

VennBig

Custom Report Types – How they work

SF Help Article

For those of you that don’t know about custom report types, they are great for the following items:

  • Joining objects together using with or without clauses
  • Removing and renaming fields to be more intuitive for end users.  I call these reports for dummies.  Take away all the extra fields they don’t need, and leave them with 20 odd fields that they can pretty much not screw up.  Note: New fields you add are not automatically added to custom report types
  • Looking up to other objects that are in lookup relationships in order to get additional fields. For example, you could pull into a custom report type the Role or Profile of the CreatedBy User and group on that field (a common request)
    • Adding fields via lookup is another item not used nearly enough, but not as rant worthy so I won’t spend too much time on it.  Here’s a picture, use this feature, especially if you are building a report on Contacts for example.  Leave it as the Primary object and get Account information this way, don’t do Accounts with Contacts2014-12-05_13-43-53
    • As a rule of thumb, always start by figuring out what object you are really reporting on, and make sure that it is your primary object.  Play around with things until you figure out that piece.  Reporting on Contact with a report type that has Accounts as the primary object will cause nothing but headaches

Back to the rant

Too often I see people start to go down the custom report type path, and this is what happens:

They start with Accounts, everyone starts with Accounts (or Users), and add Contacts as the second object in a with or without relationship

2014-12-05_13-42-39

Now they can pull all Accounts, whether or not they have contacts in a summary report, and just filter out the ones with contacts.  This of course has to be done manually in Excel

Next people think, this is great, I’ll keep going and add Opportunities and Activities

2014-12-05_13-43-22Finally they are told they can’t add anymore and think great, I’m done!

So here’s what we have, and bear with me while I try to explain it.

  1. Accounts with or without Contacts
    1. If those Contacts have an Opportunity associated with them, grab it as well
      1. If those Opportunities have Activities, grab them

So look at the picture, the Activities we are trying to get to have almost nothing to do with the Account.  We are only getting Activities that belong to the Opportunity, that belongs to the Contact, that belongs to the Account

At this point the user will go one of two ways

  1. They’ll use this report and think it’s correct because they don’t do enough unit testing.  Eventually their boss will ask why the data is wrong and they are confused.  This obviously looks bad on them.  I hate to see this happen
  2. They’ll realize it’s wrong, and build 3 different custom report types, still trying to satisfy the request

By now they’ll probably also have been at this for a few hours

What’s the Solution –  Cross Filters!!!

Even if you stopped reading this fully long ago, read this on Cross Filters.

Cross Filters is Salesforce’s way of saving you days of work getting to what you really want, which in this case is just orphaned Accounts.

First, just use the Accounts standard report type.  Nothing else.  No need for additional objects in the report, no nothing, just accounts.

Accts

Next, add a Cross Filter

Xfilter

Pick whatever related object you want make sure either exists, or does not exist.  You can choose up to three.

It took me more time to paste the below screenshot into this post than it did to ask for Salesforce to give me all Accounts that do not have any Contacts, Opportunities, or Activities.

Xcomplete

Key note: This is also a tabular report, which means I can export it, and use dataloader with the IDs to delete them as well.  I could even sort this by Last Modified (or Filter) and only delete those that also haven’t been modified in the last 6 months.

This is literally all you have to do, that’s it.  The report is done.  As opposed to that ridiculous 4 object custom report type I showed you above, these Activities are actually related to the Account, as are the Opportunities.

In a follow up post I’ll discuss how to take them even further with a use case I got asked for recently that someone doubted I could do through Cross Filters.

Share Button

3 Comments

  1. I don’t think I was an MVP was I said create that custom report type!

    Nice post though

  2. This is remarkably well-written and extremely valuable. Thank you, Geoff!

    • geoffreyflynn

      January 13, 2015 at 6:47 pm

      Thanks Brooks, much appreciated! I’ve always liked writing so I’m enjoying trying a different avenue here

Leave a Reply

Your email address will not be published.

*

four × 1 =

© 2018 ExploitedDevOrgs

Theme by Anders NorenUp ↑