Salesforce expert Aaron Crearer reveals the top advanced reporting tips and tricks for new and existing admins.
This Xforce Data Summit session features Leonard Linde and speaker Aaron Creaer, Founder of Hat-Trick Consulting and Salesforce Expert. Aaron dives deep, offering everything you need to know about reporting. Providing advanced tips and tricks, the goal of this session is to help Salesforce Admins become much more efficient.
Topics include cross filters, field-to-field filters, power of one and unique values, custom summary and row-level formulas, buckets, joined reports, and custom report types. Each feature is described, followed by a detailed use case or two. Aaron then completes a demo, showcasing exactly how that feature is used.
Based on the case uses and demos provided, this talk is ideal for new Salesforce Admins. However, you may also benefit as someone who has built on the platform for years — after all, features are always evolving. Whether you’re looking to learn new reporting features or strengthen your current skills, “Advance Reporting Tips and Tricks for New Admins” will certainly come in handy!
[00:00:00] Hello and welcome to another Xforce Data Summit presentation. Today we have Aaron Crear, who has a company called Hat-Trick Consulting in the Boston area. He's a Salesforce expert and he's going to give us advanced reporting tips and tricks today — and I am eager to hear him. Without any further ado, here's Aaron.
[00:00:36] Thanks, Leonard. Thanks everybody for joining today. Again, my name is Aaron. I'm going to teach you a bit about reporting here. And just to give you a little background on myself, I'm a recovering sales manager. I'm proud to say I haven't made a cold call or begged somebody for a purchase order in almost three years — never felt better in my life.
[00:00:55] Been using Salesforce since about 2009. I started as an inside sales rep using it to create opportunities, log activities, and became kind of an accidental admin just by learning more to make my job easier. And about four years ago, I decided I wanted a career change and that I was kind of tired of having that quota hanging over my head and made a career pivot.
[00:01:16] Cause I really saw the value in how companies could use Salesforce to make them more efficient and help them grow. So it began with Trailhead and I started studying for certs. I speak at a lot of these events cause I feel very fortunate that I was able to make that career pivot. And it was kind of my way to give back.
[00:01:31] I run the Lower Massachusetts Admin User Group, I’m a co-organizer of Northeast Dreamin’, and I'm co-founder of a blog called Report Force. So, all the tips and tricks we're going to show you today, they're really simple things once you know they exist. But there was certainly a time in my career where I did not know they existed.
[00:01:51] So, they're going to be really useful for you. They're going to make you much, much more efficient. So our topics today are cross filters, field-to-field filters, power of one and unique values, custom summary and row-level formulas, buckets, joined reports, and custom report types.
[00:02:10] We'll have a brief overview of the topic, we’ll have a use case or two, and then we'll have a demo of how you actually use these features. So, first is cross filters. They allow you to filter reports by Objects instead of just Fields. These Objects filters are dependent on the Parent Object of the report. type.
[00:02:32] We have three separate use cases here. So one is, “a marketing manager wants to identify all accounts without contacts so they can put some more in their marketing funnel.” Second, we have “a sales director that wants to review all accounts with opportunities to look at industry trends, but only relying on new customers.” And third is “a sales rep, all contacts part of opportunities should have activities associated with them and you want to make sure that you've done this.”
[00:03:00] Alright. Let's take a look at our first use case for cross filters. That is a marketing manager wants to identify all accounts without contacts to add contacts into their marketing funnel. So we will take a look at our first report. Which is accounts without contacts, and we can see here to stay tabular report with the few fields based on the accounts.
[00:03:23] And we're going to edit this to see how we were able to use the cross filter. And we'll just click on our filters tab here — if we were adding a brand new one. As we can see, we have one here already. To add a cross filter, we simply click the little dropdown there and “add cross filter,” but we'll open up the existing one and we've selected “accounts.”
[00:03:44] That's the only thing available because that's based on the report type, which we can see up here, is just accounts and then it's just a with or without statement. So one object with or without another. We selected without, and then there's our secondary object. We've selected contacts out of all these related objects.
[00:04:06] So very quickly, very simply, we get a quick synopsis of which accounts have no contacts related to them. And the first time that I — somebody asked me for this report, I didn't know cross filters existed. I exported the entire database, created a couple of formulas in Excel to basically return a zero or one if there were contacts related to that account.
[00:04:29] It was not fun, it took quite a long time, it was a complete pain in the butt. And then a few hours later I read a blog post about cross filters and it really saved a ton of time. I wish I had known about it a lot sooner. We will take a peek at our next use case, which is, a sales director is interested in reviewing all accounts with opportunities in order to identify industry trends, focusing only on new customers.
[00:05:00] So we're going to look at accounts with opportunities by industry. So we can see here we have a chart group by industry. We have some ratings, we have our account names, and we're basically looking at a record count. And so how many opportunities within each of these industries? Let's take a peek inside.
[00:05:24] We'll look at our cross filter. Again, we have accounts. This time, we've selected “with opportunities” — made our filter so that we're not returning any accounts without opportunities, but we only want to focus on new customers. So if we use this filter within the cross filter, say on the type field we're allowed to select “new customer” here.
[00:05:57] So when we apply this, now, we're reducing that list to only show opportunities of the type new customer and the really cool part about this is we're using that field from the opportunity as a filter within our report, even though we're only using an accounts report type up here.
[00:06:18] There's not a — this isn't accounts with opportunities. There's no fields over here from the opportunities. If we look, these are all just account fields. So it's a really good way, if you just need a quick numeric value or quick list of records and you don't need to create that custom report type, we can use, fields from the other, other objects within our cross filter as filters.
[00:06:42] So, a really good use case here.
[00:06:47] Our third one is as a sales rep — all “contacts part of opportunities” should have activities associated with them. And you want to make sure you've done this to create a report and begin outreach. So, my second job out of college, I was a sales rep. And every Friday afternoon at 4:30 my manager wanted to have a review call.
[00:07:07] I don't know about you, but 4:30 on a Friday, I want to log out, crack a beer, and relax. I don't want to rehash what I did all week or more importantly, what I didn't do all week. So after a couple of weeks of these meetings, I said, this is for the birds, I gotta figure out a better way to do this so I can get him the answers that he needs and I can get these calls over with quicker — get on with my life.
[00:07:29] And to do that, I started creating some reports for myself just to make sure I didn't miss anything over the course of the week. So we'll take a quick peek here, at our report — so we're looking at “contacts without activities” and we can see our cross filter here.
[00:07:47] Real simple — um, we want to make sure that we're looking at “contacts with opportunities.” So to do that, we can add a second cross filter, so we can layer these — or nest them. So we're going to say “without activities” but “with opportunities” and we're going to apply, and now I can see my list.
[00:08:09] This would be a real standard SLA for most organizations. Obviously if you have an opportunity, you should be working that opportunity. There should be contacts related to it. Those contacts should have that activity. So now I can see that I need to reach out to Jane and Avi, before that Friday call.
[00:08:25] That way I can make sure I've covered all my bases and done my outreach. So there's a quick demo of how to use cross filters, and we'll move on to the next section.
[00:08:36] Alright. Our next session is going to be field-to-field filters. These let you compare values of two different report fields.
[00:08:44] This is only available in Lightning Experience, and this is one of the newer reporting features from the last few releases. In our use cases, a marketing team lead wants to evaluate all campaigns that have an actual cost more than the budgeted costs to help with planning for next year. Alright, we'll take a look at our use case for field-to-field filters.
[00:09:06] Before we do that, we want to make sure that it's enabled in our org, so we'll go to setup and then over here in our quick find, we'll look for report and dashboard settings. We'll click on that, and we want to make sure that this box is checked. As this is one of the newer features, so just check that box and you'll be good to go.
[00:09:29] And we'll take a look at our report for our use case, which is “a marketing team lead wants to evaluate all campaigns and having an actual costs more than the budget costs to help with budget planning for next year.” So we take a peek at our report here. We can see our budgeted costs and campaign and our actual costs and campaign.
[00:09:49] We can see we've gone over in several of these campaigns and what was budgeted versus what was actually spent. So how did we filter our report to only show these? Well, to do so, we use this newer feature, which is field-to-field filters.
[00:10:08] And when we look at our filter here — we'll click into it. In this little type section, we have the option, which now is selected on “field” versus “value.” So you're probably familiar with filtering based on values of amount greater than a hundred K, under a hundred K, things like that. But now we're filtering on a field.
[00:10:28] So we're saying, look at actual cost, compare that to the “budgeted cost” field and return any that are greater than. And when we have applied that, now we're only seeing records that have an actual cost, greater than budgeted costs. Really good new feature — but it is limited to only certain types of fields.
[00:10:53] As an example, you cannot use lookup fields in this. So if you're trying to compare, “contact or opportunity owner” versus “account owner,” things like that are not available in this. So, just be clear when you're trying to use this. If for some reason you don't have this option available when you try to filter, that's the reason why, it's based on the field you're selecting.
[00:11:20] Alright, our next section is power of one in unique counts — and allows me to summarize and count objects and reports. Allows a new custom formula field. I recommended you do it on every object. I think it's a best practice if you name the field the same as the object, and I'll explain why in a few minutes.
[00:11:39] They're useful in rollups and additional formulas. A unique count, which is a newer feature, gives you a quick visibility and reports to these numbers. So for the use case, “sales director wants a report that shows the pipeline summary by stage, the breaks on the number of opportunities, accounts, and products within that pipeline.”
[00:12:02] Now we'll take a look at our use case for power one and unique counts. And this will be a “sales director wants a report that shows the pipeline summary by stage that breaks down the number of opportunities, accounts, and products.” So we'll look at our opportunity pipeline composition. Report — to just a funnel chart here, grouped by stage.
[00:12:25] And then if we scroll down here, we can see the number of opportunities, accounts, products. And this is a really useful report for sales and marketing departments because we can take a look and see, is too much of our pipeline weighted towards one or two customers, where if that relationship goes south or you don't get that initial win, are you really going to take a hit there?
[00:12:51] Or on the product side — is it too heavily weighted towards one product, which you just found out is going to be delayed and released for a month or two? That can really crush a quarter for companies. So it's really used to judge the health of your pipeline. So to create these, we've created, basically formula fields on the objects themselves that allow them to count themselves.
[00:13:14] And that allows us to look at this report in this funnel and say, how many different opportunities are covered here? How many different accounts, how many different products or opportunity products? We can see over here to the right, we have a row for, a column rather, for record count. I never ever used that unless it's a single object report, because I think it's really confusing for users to understand what it's actually counting.
[00:13:40] So using the power of one to show these is definitely a much better way to do it. So how do we create that formula pop over to set up.
[00:13:54] We'll just take a look at one of our objects. And we’ll look at an account and we'll look at field called accounts. And we'll edit this just to take a peek at it, and it's a number for the return type, zero decimal places, and now the really, really complicated part is the formula itself.
[00:14:23] And that's just the number one in that big white box. That's all you have to type in there. That's the entire formula, and now it's allowing it to count itself. So if we go back to our report here, I told you that it's a best practice to name it the same as the object. So the first time I read Steve Mo's blog post about this, I said, oh, this is a great feature that solves one of my problems.
[00:14:47] I went into one of my orgs and I put the field on every single object in the org and on every single object. I named it the power of one. And then when I looked at these four columns here, they all said the power of one. And I had no idea which one went to which object. So I had to go back and re-label them all.
[00:15:04] So, good learning moment there for me — but a good way to use this. Now there's a newer feature that just came out in the last release that kind of helps with this. I'm going to show that to you here — but the power of one is definitely useful. There's other use cases that you're going to see throughout this presentation — we'll tie it back, here from some of the other sections, but I would really want you to understand the concept of it.
[00:15:33] So if we edit this — and we'll take a look, we'll show our detail rows and, let's say we just want to find out how many accounts are in here. We're just going to click this little dropdown next to account name, and we're going to say “show unique count”. We get our total down the bottom or run this, and we'll just compare it to our power of one field to see if they're exact or not.
[00:16:02] And if we look over here, our unique count on the bottom here is seven. If you look over at the accounts, we have a seven. So this new feature, a hundred percent came from the usage of the power of one by lots and lots of people. If you’re just looking for that quick total, I would definitely use this feature here, as opposed to going to create all those formula fields in the accounts.
[00:16:25] But there's definitely reasons to create that formula field on your objects because there are a lot of other ways to use it and we'll see some of those as we move forward.
[00:16:34] Right, our next section is custom summary and row-level formulas. Custom summary formulas are used to calculate additional totals and summary matrix and joined reports.
[00:16:46] They're based only on numeric fields, which can be summarized. They are unique to the report and cannot be shared to other reports. Row-level formulas, which is a newer feature, allows you to do the calculations on every row. You have flexibility outside of numeric fields with standard formula operations.
[00:17:05] You can definitely reduce the number of formula fields you have in your org by using this. And these are also unique to the report and cannot be shared to other reports. So we have a few use cases here. The marketing department needs to know what percentage of leads created were converted.
[00:17:23] You want to give the sales team a visual representation of where their sales are compared to goal. An executive wants to see the number of days between when an opportunity was created and when it closed. And the territory manager wants to evaluate their current structure by looking at all opportunities to have a different owner than the related account.
[00:17:45] Now we'll take a peek at our use cases for custom summary and row-level formulas. So the first is, “the marketing department needs to know what percentage of leads created were converted.” Real simple metric that most marketing departments are going to use. We'll take a look at two ways to look at these. And the first is just using a simple “is converted statement” or field, standard out-of-the-box availability, true or false.
[00:18:09] And when we hover over here, we can see the record count or number of leads, as well as the percentage. So again, out-of-the-box functionality works fine. When we look at this, we can see our grouping.
[00:18:24] Right here, by that converted field or statement works fine, but if you want to do something a little more advanced, we can use the custom summary formula to add to that. So we can see here we have our vertical bar, which is showing the number of leads for the month. As well as the upward sloping of our horizontal bar — line rather, that is showing the conversion rate.
[00:18:51] And we can see here we have our conversion rate column with our percentage, and we can see the FX here, which is telling us it's a formula. So if we edit this, let's take a peek at our summary formula, here — we've named a conversion rate, we've selected percent.
[00:19:10] We could choose number or currency. We've said two decimal points, and then we've written our formula in here, converted some. So the number of leads converted divided by the sum of leads. So the total number of leads. We can see this looks a little odd, right? Lead “dot” leads “sum.” This piece right here highlighted is actually a complete power of one.
[00:19:35] On the leads object. So we're taking what we learned in the previous section and we're adding it within our custom summary formula.
[00:19:49] Our next use case is, “we want to give the sales team a visual representation of where their sales are compared to goal.”
[00:20:00] So we can see here we have our vertical bars, which are showing basically “closed one” for the month, which was how much was sold. And then we have our horizontal bar, which is our monthly goal. And we can scroll down and we can see here we have four custom summary formulas that are showing us these calculations.
[00:20:21] So the first time I wrote this report, I was visiting one of my customers, and the sales director, her name is Elizabeth, sitting at her desk, she's got both screens going, she's got a piece of paper and a calculator and Excel spreadsheet. She's scratching her head. She's swearing. And I said, “Elizabeth, what's the matter?”
[00:20:28] And she says, “we have a new way we need to report things, and I'm trying to figure out if we're above or below our goals.” They don't use standard Salesforce forecasting. They don't have any custom fields or custom objects to capture their goals. They don't live anywhere inside of Salesforce. So we get to talking for a bit.
[00:20:56] And once I understood that it's a really simple case, they have a flat goal per month, that's all they need to capture. I said, okay, we can do that right in a report — we don't have to build anything crazy, we can get you the numbers that you need.
[00:21:08] So if we open this up, we'll take a look at how we built our four formula fields.
[00:21:15] So first is monthly goals, and again, we select a currency to decimal points. And we've written our formula here is ABS 500 K. All ABS means is absolute value. Whatever you put inside of these parentheses, that's what it's going to return or display. In this case, we've selected a specific group to display it as, based on close date. And what that means is basically in this report, since we grouped it by that close date, every row is going to return 500,000.
[00:21:47] Which if we close this, we can see right here. When we look at our percentage to monthly goal, again, we've said the amount, sum, in this case, what's closed one for the month, dividing it by that 500K and again, we've displayed that on the specific group for closed date. Again, returning that calculation on every row.
[00:22:13] We look at annual same way we built the monthly goal, except we've replaced the 500K with 6 million. The difference is we've chosen to display this on the grand total only, which is why it only shows up on the bottom row. So when we look at this, we can see our 6 million only shows up down here. It doesn't return on every row, which is what we want.
[00:22:39] Percentage annual goal. We've divided that amount, or closed one divided by the 6 million. Okay. And we've displayed on the grand total only — which displays right here.
[00:22:55] So a really quick way to show people kind of where they stand in a report without having to build custom fields, custom objects, or adding forecasting, anything like that.
[00:23:10] Next use case, “an exec wants to see the number of days between when an op was created and when it closed without creating a new custom field.” So a very common use case prior to the last release. If we look at column here, we have one for created date, we have one for close date, and then we have days to close — which again, we can see as a formula with a calculation here, but we've chosen to use a row-level formula instead of a custom summary formula.
[00:23:43] Basically, because we have to, we can't use a custom summary formula for this. And again, custom summary formula is basically — they’re giving you totals. They have to be based on numeric values and in this case, we're doing a calculation based on two date fields.
[00:23:56] So if we click into our formula, we've chosen not to summarize because it's not relevant for this. We're going to edit it. And we selected days to close, number as our output — obviously we're not going to be able to select currency because it's not a currency field, and we've selected our field — so close date.
[00:24:20] And then create a date. And we've added a formula function of date value because created date is technically a date time field. And we only want to calculate the date part of that. So we've added date value in here, and that's allowed us to create our calculation. So really good use case — this is a common formula field that a lot of companies have put on their opportunities.
[00:24:46] Now, we don't need to unnecessarily use up that field. We can just do it in the report here as long as you don't need to actually see it on the record itself. So if you're only consuming this for reporting, just to gauge how long, after taking the close, you don't need to see it on that individual record or in a list view, this is a great way to do it.
[00:25:07] And we'll look at our last use case which is, “a territory manager wants to evaluate the current structure by looking at all ops that have a different owner other than the account owner.” So we will look at our report here. We can see we have our opportunity owner, we have our account owner, and then a column called same owner, which is a formula.
[00:25:31] And these three — uh, four, have returned, “no” — which, if you look at a quick compare here — and none of these first two columns match. So these all should say no.
[00:25:41] How did we create that? Look at our formula — title it “same owner,” it's a text formula. We've used an “if” statement. So we've said “if account owner,” is our first field, equals full name, and in this case, full name is the opportunity owner. You said returning? Yes. If not, return a no. So we're basically using a text formula here and we've applied it and it's given us our calculations.
[00:26:14] Then I've also used it as a filter so we can take the formulas that we've created, and we've used it as a filter to say, “only show me records that do not have the same owner.” So a really, really good use case. A row-level formula is definitely my favorite feature out of the new release. Right now, you're limited to using one in a report.
[00:26:36] I can't wait until that changes. But this is a real game-changer for our reports.
[00:26:44] Alright, our next section is buckets — and buckets allow you to categorize a group of values. You can use custom names for each bucket, they do not require formulas or custom fields, and I kind of think of it as opportunity stage forecast categories.
[00:26:59] And you have a couple of use cases here. So you're tasked with creating a sales by region report. Opportunities from accounts in the U.S. and Canada should be combined into “NA” and all countries in Europe should be combined into “Europe.”
[00:27:15] Alright. We'll take a look at our use case for buckets. So we're tasked with creating a sales by region report. Opportunities from accounts in the U.S. and Canada should be combined into “NA” and all countries in Europe should be combined into “Europe.” So we look at our sales by region report. We can see here we have things grouped by region.
[00:27:35] We can see Europe, NA — we can look at the record count as well as the percentage. And then we can see over here we have a column for region. We can see a little icon here, which is a bucket. And that tells us we’ve bucketed fields based on values here. So if we edit this. We'll take a look at our bucket and we've named it region.
[00:27:59] You can name whatever you like. We're selecting a specific field. That's what we're bucketing. And in this case, we select billing country. We can see we have a bucket for NA with blue — bucket for Europe with purple. And if you look at our values, we can see France is in that purple bucket. USA is in that blue bucket.
[00:28:17] China, although, is not bucketed. So let's create a new bucket for Asia.
[00:28:25] We have our new bucket. Now we need to put a value into it. So if we click on all values, we can see China hasn't been put in there. We'll check the box next to it and simply say, move to Asia. We'll hit apply. Now we have our column updated with Asia. We have our chart updated with Asia. We still have this group called other.
[00:28:50] And why is that? After we run — we don't actually don't need to run it. We can look back over here into our bucket and we can see we have this box checked. Basically putting any remaining values into the quote-unquote other buckets. That's why that's showing there. So if you didn't want that, you can simply uncheck the box.
[00:29:15] So this is a good use case for grouping things in a bucket for quick reporting purposes. If it's something you're going to use a lot, you may want to do this on the objects themselves. So much like a custom summary and row-level formulas, buckets live in the reports themselves. They don't actually sit on the objects.
[00:29:30] So if you need to see this on or you record as an example, you're not going to be able to do that. You might be better off in that case of creating an actual region field on, you know, leads, accounts, contacts, opportunities. You could automate that with some — a process builder that looks at, let’s say, a shipping or billing country, and then updates that region field, something like that.
[00:29:51] But if you're only using it for reporting purposes, this is a really good way to use it. Again, you would have to recreate this bucket depending on the report you're creating, unless you kind of clone this. So if you'd just do a quick ‘save as’ and do this as a new version and then update your filters, fields, etc., the bucket would get passed through.
[00:30:12] Next we'll talk about joined reports. They allow you to view different types of information within a single report. You can create up to five blocks and the blocks can be cross object. And our use case here is, “a sales director would like one report that shows sales for the month. So year to date in the last 12 months, all within one report, one view.”
[00:30:36] Next, we'll take a look at our use case for joined reports. So “a sales director would like one report that shows sales from month, year to date in the last 12 months.” So we'll take a look at our report here and we can see we have basically three blocks here, all looking at opportunity information for different time periods.
[00:30:58] So we have month, this year, last 12 months. They all have closed date amount and opportunity name. So we could, instead of creating this report, we could create a dashboard with three charts based on these time periods. So it would be very simple to do. This is really a personal preference use case where this manager just wants one report.
[00:31:20] Then maybe it gets emailed to them daily or weekly that has these metrics in it, and no need to create a whole dashboard in that case. But if we edit this — how did we create this? So the first way we do it is by selecting a joined report. So if we click up here, basically you're going to select report, that'll be selected by default when you create a report.
[00:31:42] And this would cover tabular summary matrix. You'd have to select this to switch over to a joint. You can have up to five blocks, as I said earlier. And when we look at our columns here, we can see the blue “dot” is showing us the separation between each block. That's also telling us the object. So these are all blue because they're opportunities.
[00:32:06] So if we wanted to change the columns, just like you would in a normal report, you can select your columns here for each block. So each block does not have to have the same columns. So we can add “created by” to that column as an example to that block, rather, not put it in the other. We also have our filter, so just like a normal filters for each block, we can set our filters.
[00:32:30] And we can see these are identical with the exception of the date periods, based on close date. Those are all different. So if we want to add a new block, we select our report type. When you do that, it's automatically going to put it over to the far right. So don't be your alarmed if you think it's going to show up on the left, it always shows up here.
[00:32:51] And if we scroll down and look at our outline, we have a ton of fields here. Let's, let's get rid of these and match it up to the rest. So we're going to remove all columns and then we'll add in the same as the others. So we'll put in our close date, our amounts, and our opp name.
[00:33:19] Now we want to give this a unique name, so let's say the last two years, as an example.
[00:33:42] So now we have our new block, but we still need to update our filters for it. So based on that time period, so instead of current fiscal quarter, we could use something like current and previous fiscal year. We'll say that what we're using for our last two years and now we have our new block. So a really good way to get a bunch of different data points into one view on a report.
[00:34:07] But another great use case for joint reports is that you don't have to have the same object in all five blocks. So let's add another block, and assume you want to capture some lead info. So we're going to add another block here. And again, we'll scroll over to the right and we can see we have lead info.
[00:34:28] We'll look at our outline. Okay. And we can see that screen. So that's telling us it's a different object compared to the blue, which is opportunities. And we have our columns here. Now we would just need to update our filter. Let's say we want to look at all the leads, let's say all times, as an example.
[00:34:58] Now we run a report.
[00:35:05] Joint reports do take a minute or so to run here cause they're doing the calculations across objects, especially when we're doing all time. But it's a really good use case for getting, like I said, different objects, different data points into one view, without having to create a dashboard — that you're gonna have to maintain, especially if this is kind of a one off use case for a specific person sometimes, or even a couple of specific people.
[00:35:35] And now we can see over here we have our lead info as well.
[00:35:41] Alright, next we're going to talk about custom report types, and they allow you to create more complex reports not provided by standard ones. You can include up to four objects — primary, secondary objects, which must be related to the primary. You can choose whether a secondary object may or may not have an associated record, and you can select which fields from the objects are available on the report type itself.
[00:36:06] So use cases, “a marketing manager would like to see what activity volume campaigns are driving with the visibility to the contact level.” So we can look at a campaign and see campaign members, we can look at a contact and see activities — but having that clean line and visibility from the campaign to the campaign member, to the contact, to the activities, it's not easy out-of-the-box.
[00:36:29] Alright, our final section, we'll take a look at their use case for custom report types, and our marketing manager would like to see what activity volume campaigns are driving with the visibility to the contact levels. So it's pretty easy to do some reports on campaign and the associated contacts to them.
[00:36:48] It's pretty easy to look at contacts and the activities related to them, but having a clear line of sight from. A campaign to say contact to an activity is not that simple, with standard reporting. So to do that we'll create a custom report type to help us with that. So the first thing we'll do is we'll take a peek at our report for our October newsletter, and we're just looking at activity volume. We can see we just have our name, we have our campaign, our subject and activities.
[00:37:20] This activities column is just the power of one on that object. So again, we're going back to something we learned earlier in the session, looping that into another way to use it in a report. And I use these reports a lot for call back lists. So for reps, you know, if you walk through the expo this week, obviously there's hundreds of vendors here.
[00:37:37] They all have people manning their booths. When they kind of scan your name, there's people that are going to be reaching out to us all next week, trying to sell us product services, all of that. So how do each of their reps know who they're following up with?
[00:37:50] I would create a report like this and give that to them, and then we can actually filter it so that when they make contact with somebody, they fall off this report. So the simple way to do that. Would just be to filter on that activities field. Add this in here, scroll down the bottom, and we'll just say greater than zero and apply.
[00:38:23] Actually, we want to make that less than one, is actually the better way to do that. So now when we run that, once you make contact with somebody they'll fall off this list. So now we’re only seeing the people I do need to still follow up with. So in order to do that, we created a custom report type.
[00:38:53] So we’ll go to setup and we'll go to report types. And we can see here we have our activity volume for contacts. And if we edit this, campaigns was our primary object, contacts is our secondary, and they have to be related. And we've said, each A record must have at least one B record. So when we use this report type to create a report, it's only gonna return values of campaigns that have at least one contact on them.
[00:39:31] So if you have content — campaigns, rather, that only have leads on them, they're not going to show up when you run a report based on this report type. But we've said each B record may or may not have a related C record. So we're saying only show campaigns that have at least one contact, but show contacts regardless of whether they do or they do not have activities.
[00:39:55] We just filtered that report to show us people that didn't have any, but the report type starting basis, we're saying, show us whether they do or they do not. This is really important to make sure when you select these, get them right and really label your report type something that's clear.
[00:40:12] So if I'm going to choose something like this, I want to say campaigns with contacts. If I'm going to change it to that value here, saying they may or may not, I'm going to say campaigns with or without contacts because it can be very confusing to use as to what they're returning — because based on the reports type, you will filter things out of their report.
[00:40:36] Another thing to note about report types is you can choose what fields are available or not available in the reports themselves. So we have our layout here where we can see our three objects and all the fields here. When you create your report type, unless you come in and edit this all fields from those three objects that are going to show up in your report type.
[00:41:00] When you go to add a new custom field to any of these objects. Let's say we created a, today, all the fields are in here. On Monday, we go in and we create a new custom field on one of these records — one of these objects. It doesn't necessarily show up here. It actually won't. You will physically have to come in here and look for the new field that you added on the contacts object called individual, and you have to drag it in here and then you have to save it.
[00:41:29] It's a very frustrating thing, but when you're creating a report, and you're looking for that new field that you created and you can't find it to add as a column or a field in your report or you can't find it to use as a filter.
[00:41:41] This is the reason why. You need to come back in here and update your layout and hit save. Another really cool use is to add related fields via lookup. And what this means is we can add fields available in our report type without linking additional objects in there. So we had campaign contact inactivity, in this report type.
[00:42:08] Let's assume we want to look at something based on, say, the contact owner. So we don't have the user object as part of this report site — but if I look at contact owner, we have all of these fields available from the actual user record. So let's say we want to select role, or locale or language,
[00:42:40] We can add those in without going back and adding that user object in here. And now we've added those fields via the lookup. So really good use case. If you can start by using the bottom level of your report, adding fields in via lookup, you're going to save yourself a ton of work. It's a lot better than trying to jam in a bunch of objects if you don't have to.
[00:43:07] Alright, so some other resources for you, Trailhead, I'm sure everybody's heard this by now. Great way to learn, especially with hands-on modules. Certifications are great — I have six of them. Every single one of them. I've had to learn things about the platform I didn't know about. I had been a sales director for a decade, and an admin for I think two or three years.
[00:43:28] I thought I was going to breeze through the sales scout certification, not so much just because there were areas that I hadn't been exposed to. So you're always going to have to learn new areas. Trailblazer’s community is awesome. Anytime I don't know how to do something, it's the first place I look. You have MVPs, CTAs, user group leaders — sometimes to answer your questions in real time. Invaluable resource.
[00:43:49] Community groups are great. They're outstanding for networking, but you can also learn a ton from the presenters there. Community conferences — so whether they're virtual like this or they're in person, there's 30 plus streaming events around the world.
[00:44:03] They're great ways to learn and find a mentor. A mentor could be somebody more senior at your organization. It could be somebody you meet, a speaker at a conference like this. It could be somebody you follow on Twitter. But the best part of this community is people are willing to share their knowledge and their time.
[00:44:19] So don't be shy about asking for that, cause I know people are willing to do it. And then reportforce.blog, there's a lot of articles up there that will help you with your reporting journeys. So thank you very much for your time.
[00:44:36] Thanks, Aaron. Well, I sure learned something about Salesforce reporting.
[00:44:38] I guess one of the things, as more of an old school Salesforce user, used it a while back, it all seemed like Salesforce had sort of stopped developing reporting features, but it looks like with Lightning, they've kind of let it go. Are they — do you see some things on the horizon coming or have you heard of more things and in reporting that you think are interesting?
[00:45:00] So the last couple of releases, there's been a lot about reporting. So from the Lightning report builder to row-level formulas and unique counts — so those have been really good. Some of the newer things that I've heard are going to be coming out in the somewhat near future are the example that I showed on how new custom fields don't automatically show up on custom report types.
[00:45:23] That's supposed to be a feature that will be coming out, so that when you add those new fields, they will automatically go to those report types. And I think they have more recently made some, some larger investments in the reporting. Even speaking to the product manager for reports at Dreamforce over the last year.
[00:45:44] Just talking about row-level formulas. Right now it's, it's really only limited to one. And that, that was a huge hurdle for them looking at adding more because it is such a performance degradation by doing that. So I think it is something they're going to continue to invest in. I mean, obviously we have Analytics and now Tableau they've acquired.
[00:46:04] So there's other, other products for this, but core reporting is certainly not going away.
[00:46:09] Right — and I think the, in addition to the, you know, whatever the changes Salesforce is making, this power of one thing is super clever, you know, like that report you showed that had the cutoff line for that, just using regular reporting, that was, that kind of stuff is really — I mean, if you can do it in a platform that all your users can use and that they're comfortable with, that's, that's huge.
[00:46:33] Well, thanks, Aaron. We really appreciate your time and your expertise.
[00:46:36] Thank you for having me.