when that opportunity record has a contract that specifies a contact that specifies an account If any one of those lookup fields are blank, that row is filtered out That’s the power of the primary object, all of the records in your report need to be able to access that primary object, that account object in order to show up on the report Now, this is a very limiting scenario, we can only handle certain requests, if we were looking for all opportunity records, probably not gonna get those results in this report type And furthermore, if you wanted to include fields from some other objects, maybe a campaign field or a field from the price book related to the contract, or any of the user fields, the contact creator, the account owner, it’s not immediately accessible to you, when you are relating objects in your report, hierarchy working your way down So I’ve learned all this and it dawned on me there’s a better way to structure this, there’s a better way to organize our custom report types to give us more flexibility and remove all these built in filters And the answer has been right in front of us the whole time Start at the bottom If we start at the bottom of our data architecture hierarchy, specifying the object that we want to have its records show up as rows in our report, we can easily add fields from any object above it in our data architecture hierarchy So those same four objects from before, we can easily reference those fields in our custom report type, for an opportunities report We can easily pull in fields from the campaign, that price book, the contact creator, the account owner, literally any field, any look up, or master detail field that you can access above the data, in the data architecture hierarchy, above your primary object, you can pull in fields from those objects into your report And look at just all the different things that you can access now And the best part is none of these objects are enforced Everything is optional They’re just reference fields for a report that’s showing you a guaranteed list of all the opportunity records in your organization Now, ladies and gentlemen, I present to you for your reporting pleasure, the Swiss Army Knife of reporting, Deluxe report types The best part is these are so easy to create You only need one per object in your org Three easy steps, step one, you’re gonna choose a primary object This is the object that you want to have its records represented by rows Step two, this is gonna be the hardest part When you get to this page where you normally enforce object relationships, do nothing Just click Save You don’t wanna enforce any relationships in this report type, because all the magic happens on step three We’re going to edit the custom report type layout, and we’re gonna add those reference fields from other objects So let me show you just how easy that is I’m gonna hop into my Lightning enabled org Everything I’m showing you today works in Lightning, or Classics, so you have no excuse I’ve searched for report in setup menu, I’ve clicked on Report Types I’m gonna create a new one This is going to be an opportunities report So I’ve chosen opportunities as the primary object Again, that’s the object that will have its records represented by rows Now for the label, I like to call all of my Deluxe report types the name of the object followed by Deluxe in parentheses, so that everybody knows that it’s special In the description, I list every row represents an opportunity record, followed by all of the objects I’m planning to reference in the layout, and you’ll see why a little bit later We’re gonna put that in a category And then we’re gonna make sure our report type is deployed so that we can build reports using it Again, on the next page, this is the hardest part, you’re gonna be very tempted to start relating other objects, just click Save And if we scroll down on this page, at the very bottom, you’ll see this Edit Layout button Now, this is showing you all the fields that are available to you as columns in your report So by default, all the fields from opportunity are included And if you ever make a new opportunity field, it will show up in the panel over here on the right by scrolling through these pages Now, just above that, there’s this Add Fields Related via Lookup If we click on that, we’re going to see a list of all the lookup or master detail fields on our opportunity object
So simply by traversing one of these relationships, maybe up to the contract object, I can now pull in any contract field into my report type layout So let’s say I wanted to get the name of the contract, it’s created date, and the description Great Those fields show up with a magnifying glass next to them, letting you know their reference fields from some other object Now pro tip number one that I will give ’cause this will get crazy real quick, create a new section for every object that you include in your custom report type layout So I’m gonna call this contracts, you can get as fancy as you want with your organization, but at a minimum, I would recommend at least a new section per object We can drag all those fields down to that section And we can go grab fields from some other object This is not just limited to the direct parents of opportunities If we go up to our contract object and watch this path here, this navigation, I’ve started from opportunities, I’m looking at a contract now, I can go find the customer who signed that contract and click View Related Fields Now I’m looking at a contact record that’s related to that opportunity, I can pull in any field from that object I can keep going So maybe I wanna reverse that account relationship on the contact object Now I’m looking at an account record And I can go one step further all the way up to the account owner I have just included fields from five different objects in my report type, something you can’t do if you’re relating them in your object relationships Now, once I get to this object, you might be tempted to click Select All and say, “Yeah, give me everything.” But what you’ll find is there’s actually a 1000 field limit in custom report types So you will host that real quick if you click Select All on a few different objects Instead, take this opportunity to tailor the experience This is a great way to only include fields that your organization is using, that makes sense for your users, so that they’re not frustrated digging through all the fields that exist on our objects So maybe we only need a few things from this account owner, like the division, the email, and maybe their full name When’s the last time you were reporting on SU org admin expiration date? That doesn’t need to be taken up a seat in your custom report type layout So we’ll add those fields, and we’ll make a new section for it And if we hold Shift, we can select multiple fields and drag them down Now this can get very elaborate You can start starting from the opportunity object, I can go pull in some fields from the campaign I can traverse a campaign hierarchy if I needed to, by going to the parent campaign, and you have to watch the path here just to see how many levels you’re going, but I can pull in fields from two levels up, three levels up, four levels up, as much as I want If I go back to opportunity, I can traverse to custom objects, maybe I have this program object who has the hostile region on it, and I can traverse that relationship, pulling in fields anywhere above that object in our data architecture hierarchy Now, once you’ve got all those fields in our layout, there’s a couple other options on this page I wanna show you So if you double click on any one of these fields, you get two options The first one is this Display As field And by default, it has this really long navigation breadcrumb trail name to it And that doesn’t look great if you’re grouping by this field on your report So you can actually shorten that up You can just name this division You can name this, maybe you store the name of your team in the division field on the user object and your team is always complaining that, “Why is it grouped by division? “That doesn’t mean anything to me.” We can easily change that so that it displays something that makes sense for our users, that makes sense for our organization This is a great way to tailor that experience and get some user adoption because things will become more familiar for people And this isn’t changing the label of your field, it’s not changing the API name It’s just changing how this field displays on this custom report type So it has a huge potential for letting you have that very fine grain tweaking ability The other option you see here is Checked by Default And this simple little checkbox lets you create a very powerful template for your users All the fields that you have checked by default will show up automatically as columns in your report anytime you create a new one using this report type So that can be a great way to jumpstart people who aren’t familiar with reports They just need some of the common fields, set those up for them You can set them in the order they display here is the order they will display as columns in your report So you can fancy with reorganizing these sections,
or even create a new section at the top for default fields or something The other thing I mentioned is whatever date or date time field you have listed first on this page will automatically be used as the timeframe filter on your report Just another way that you can delight your users to have everything all set up for them, all the columns they need, and a date field filter that makes sense for that kind of report So this is great We’ve got all the fields we need We’ve got ’em organized, we have some defaults, we’ve changed some labels Let’s see what this report type can do And for that, I’m gonna cover seven different query scenarios Now these seven scenarios are the basis of any report you’re gonna run in Salesforce Either you’re working with records from the single object, or you’re working with some combination of parent records and child records And it can get more elaborate than just two levels, you can go three or four levels Before these examples to keep things a little bit simple, I’m gonna use accounts as my parent object, I’m gonna use opportunities as my child object And in true start at the bottom mentality, we’re gonna start with number seven So the report request sounds like this, can you show me a list of opportunity records that don’t have accounts? And we’re gonna use our Deluxe report type to do that Because we want a list of opportunities, we’re gonna use the opportunities Deluxe report type, because every row represents an opportunity record Now, by default, we see all opportunities, but if we wanna just find the opportunities that don’t have an account, we can simply add a field filter here And I can make sure that the account name is blank When I do that, I only see that opportunity record that doesn’t have an account specified The next report request is, can you show me a list of opportunities? And I’d like to reference the account fields if there’s an account related We can do this with a Deluxe report type In fact, we’ve done too much work already So with this opportunities, Deluxe report type, and no other filters applied, we see a list of opportunity records And we can reference the account name, we can reference the account type, any other field that we’ve included in our layout, we can easily add as column So I add some of those in, getting them the order that I want And now I have a list of opportunity records, fields from the opportunity as columns, and I’m referencing account fields, and it doesn’t matter if this patties deal opportunity doesn’t have an account It’s still included in our report Next up, we say, okay, well, can you show me a list of opportunities that do have accounts specified? Maybe we wanna enforce that relationship We can do that with our Deluxe report type All we’re gonna do here is add a filter again, which is easily changeable at any time in our report And we’re gonna make sure that account name is not blank Now, we have a list of opportunities, all of them specify accounts, and we can reference account information Pretty great We’ve done three totally different scenarios, we’re using a single custom report type Now we’re gonna turn things around, and we’re gonna focus on looking at a list of the parent object and how it relates to the child So parent records without child records is, hey, can you build me a report of accounts that don’t have any opportunities? Again, we’ll use Deluxe report types to do this, but this time since we want a list of account records, we’re going to use an accounts Deluxe report type Every row shows us an account record, and to filter on the existence or not of a child object, we’re going to use a cross filter It’s under the Filters pane, there’s a filters drop down menu, and you can add a cross filter And this is literally so easy Show me accounts without opportunities And just like that, we filter down our list of account records to the ones that don’t have any opportunities Now you can add additional criteria to your cross filter for even more flexibility in power Let’s say the request is can you show me a list of accounts that don’t have big opportunities? Sure thing With our cross filter, we can choose Add an Opportunity Filter here and filter on an opportunity field So if we wanted to filter on amount, say show me accounts without opportunities to have an amount greater than a million dollars, we can do that But this means some of the account records in our report have opportunities,
but none of them have an opportunity that’s greater than a million dollars Very powerful stuff Now, since we’re talking about cross filters, we can also turn this around and say, “Hey, can you show me a list of accounts “that do have opportunities?” Easy enough, in our cross filter, we’ll just change this criteria to accounts with opportunities Now, we’re just looking at those account records that have child opportunities And again, we can add additional criteria to that So let’s say okay, now just show me a list of accounts that have big opportunities Easy enough, we’ll filter again on the Amount field, making sure it’s greater or equal to a million dollars And now we have those results Just a few simple clicks and using the power of understanding your report type and some cross filters, we have just answered a tonne of different report requests without any need to export to Excel, any need to deduplicate records We know exactly what we’re gonna get We understand our report type and use the tools at our disposal The next up, I am here to promote these Deluxe report types, but this is the ultimate guide to report types So there are some case scenarios where you might need some other tools in your toolbox So I wanna talk about a couple of those situations Next up, we have, can you show me a list of my accounts, and then summarize the amount of opportunities that closed two years ago? Sounds a little complicated, right? Some of those accounts could not have any opportunities or not have any opportunities that meet that two year ago criteria But the request is, I want a list of my accounts and don’t you dare filter out any of my accounts, so I wanna see all of them If there are no opportunities, just show me a zero so I know they’re there For this we’re gonna use a with or without report type Is probably the most misunderstood report type that exists So I wanna cover exactly what you’re getting in those results So we go to the report type screen, there’s a picture here that gives you all the answers and shows you exactly what this with or without report type is doing I’m gonna start with you notice the middle section is shaded in either case here So that middle section means we’re seeing a row for every opportunity that specifies an account Everybody’s with me so far? When we switch over to this other option for the with or without report type, we get this object A is all shaded in But the middle section is also still shaded in So we’re still getting that inner join every time there’s an opportunity that specifies an account But in addition to that, we’re gonna tack on all of the account records that don’t have any child opportunities And it gets a little tricky because the filters work almost independently on this report type So if we get started here, I’m using this accounts with or without opportunities report type I see a row for every time an opportunity specifies an account, if there are two opportunities on the same account, you’ll see the same account listed twice And then in addition to that we’re tacking on those account records that don’t have any opportunity information So that’s why this Dream Big Incorporated is included here So to get to our report request, we wanna add a filter here for the close date And if we add a filter from the child object in this scenario, it has no impact on the account object So even though I’m gonna filter this down to just show me close dates from two years ago, and apply that, I have filtered out the opportunity records that don’t meet that criteria, but all of the accounts are left behind If I wanted to filter out an account record, I would have to filter from a field on the account object And we’re actually gonna do that in this case, ’cause the request is show me my account So whoever runs this report, they will see records relevant to them with this my accounts filter So we’ll apply that And then to clean this up a little bit to show a summary, we’re just gonna group by the account name We’re gonna hide some of these details And now we have a list of accounts, all the accounts owned by the current running user And we’ve summarized the amount of opportunities that closed two years ago And you’ll notice Dream Big Incorporated, they don’t have any opportunities, they’re still included in the results here And any opportunities from the other two accounts that didn’t close two years ago, those got filtered out So it’s a really great way to address this specific scenario where you need to show the zeros on your report Next up, we have a request, can you show me the opportunities that are related to accounts and I wanna reference fields from both objects? Now we’ve actually already done this
This is an inner join, we covered it in scenario five It’s the exact same results no matter which way you go, the only difference is which object is the primary object So when we look at what we did before, opportunities Deluxe, and we put that filter to make sure the account name is not blank, we saw four opportunity records that totaled up to $4,100,000 If I switch over and use an accounts with opportunities report type that enforces that relationship, I’m gonna get the same for opportunity records that specify an account and they total up to the same $4,100,000 So knowing that we’ve already covered how the deluxe report type is far more flexible, so I would encourage you to use this anytime you can But there are a couple asterisks, I put a little note here We might want to use this accounts with opportunities report type in a few particular situations, and they involve the filters that are available to you So remember, we were using this dynamic ownership filter that’s only available to you for the primary object in your report So if the request is show me a list of opportunities that are related to accounts that are owned by my team, this report type will be able to do that for you We already covered how it’s the object at the bottom of the hierarchy that’s showing up as rows So we know this is a list of opportunities They’re all related to accounts, and then we just add this dynamic ownership filter to filter down to accounts owned by the current users team Easy The other thing we can do with this report type is some cross filter magic So this is probably the most complex scenario I’ll talk about today But the request sounds like this, show me a list of opportunities that are related to accounts where those accounts have cases With this report type, we have access to start with the account object in our cross filter So we can only include accounts that have cases If I can type right Now we’re seeing a list of those opportunity records related to accounts that have cases And again, with our cross filter, we can add additional criteria So maybe the request is show me opportunities that are related to accounts that have medium or high priority cases That’s easy It’s just a few easy clicks, using our report types and some cross filters and we have those results, no exporting needed That might have got a little complicated, so I save the easy one for last The last scenario is just show me records from one object referencing the fields on that object And we can easily do that with our Deluxe report type All we’re gonna do here is make sure we don’t include any fields as columns from other objects So we already know that in my opportunities Deluxe report type, I see a row for every opportunity record and I can access every field on the opportunity object So we’ve seen how to build this Deluxe report type, we’ve seen the power that it has, the flexibility that comes with it Now let’s put it front and center for ourselves to use in our organization If we look at that report type selection screen, it’s still pretty abysmal at this point We type in the word opportunities and our Deluxe report type is buried somewhere away at the bottom Now, I posted an idea for this but I learned yesterday in the report roadmap that it’s completely unnecessary because they already have it included Future looking statement applies here But at the top of the report type selection screen, right now it’s only in Classic, but it’s coming to Lightning soon, you can check off this select report types to hide, and you can hide any report type in your organization Those standard report types, the ones that are part of a managed package, or any of the report types that were built in your organization prior to you becoming the admin And what’s great about this is if you ever change your mind, you can come back in and hide something And any existing reports that are using our hidden report type will still run as normal The report type just needs to be deployed in order for that to work What you’re trying to do here is whittle down this list to report types that you trust, to report types that are tailored for your organization Personally, I widdle this all the way down to the standard report type foreign object, and the custom report type for an object Now the reason I keep the standard one around is part of our development process So anytime you create a new field, it’s automatically included in the standard report type for that object So that’s a great way to double check yourself to make sure your new field is working
For your Deluxe report types, you are going to have to add new fields to it So I’ve set up a list view in my report type, my custom report type setup menu for all my Deluxe report types And if I come to this page, and I know I added some fields to the account, I can easily search for account on this page, because we listed all of the objects that we are referencing in our layout in the description of our Deluxe report type So as long as you keep up with that, and as you’re adding new fields, you keep adding them to the report types that makes sense for your tailored experience, you can easily implement a brand new field added to these five report types and be confident you can access that new field in every report in your organization Imagine that kind of power Now once we have our list down to where we want it, to the report types we trust, know and love, the only thing left is knowing which report type do I use for any given scenario? And for that, I have a handy little riddle, which we’ve been saying all along Every row represents a blank record Now you can use this to help yourself, you can use this to train other users who are running reports in your organization Just fill in the blank with the name of the object that you want to be represented by rows If you’re looking for, hey, I need a list of opportunities that closed last year Every row is gonna represent an opportunity record You type in opportunities on the report type selection screen, and you have just two choices Astro loves how easy it is to make a choice when there’s only two choices Do I want just fields from the opportunity? Or do I want fields from the opportunity and anything above it in the data architecture hierarchy? Do I wanna reference other stuff? Now I named it a deluxe report type on purpose because if given the choice between standard and Deluxe, for the same price, which one are you gonna choose? Deluxe, exactly That report type doesn’t have any filters buried within it You’re just gonna be able to run the report you need, answer that report request using the most powerful, flexible report type available to you, with no need to ever restart on a report again, because the report type is wrong How does that sound? (audience clapping) Who learned something here today? Excellent If you’d like to keep learning, we’re at Dreamforce There’s a couple other sessions on this report topic In fact, coming up next, my good friend, Aaron, is doing advanced reporting tips and tricks right here in this room So be sure to stick around for that Tomorrow, there’s another set of reporting session starting at 12:30 in the Admin Theater, top five reasons your report might be wrong, covers all those filters scenarios, and then leverage custom report types to empower your users is right after that Then at 3:00 p.m., I’ll actually be at the manage and report on data booth in the Admin Meadow So you can stop by, we can walk through any of your scenarios I’m happy to answer questions I’d love to meet all of you to talk about this some more And if you want some homework, I have Trailhead module listed here, the reports and dashboards module There’s two blog posts The first one I’ve actually written, it’s the “Ultimate Guide To Report Types,” on admin.salesforce.com It covers all those seven reporting scenarios and how to address them accurately And the second one there is by my good, one of my heroes, Brent Downey, wrote the “Hidden Custom Report Type Functionality” article So check that out And then I got two videos, one is on cross filters and the other one is about how to hide those pesky report types that don’t make sense for your organization Thank you so much It’s been a pleasure to be here (audience clapping) And it looks like you have a few minutes for questions So there’s a microphone right here if anybody has a burning question
who need to use other fields So, our development, people use very different fields than our volunteer people So what’s the best practice for when you’re running out of fields and you can’t add everything you want?