Explore Advanced Reporting Techniques with Evan Ponter

Today on the Salesforce Admins Podcast, we talk to Evan Ponter, Salesforce Consultant and Certified Application Architect.

Join us as we chat about all things reporting from his breakout session at TDX.

You should subscribe for the full episode, but here are a few takeaways from our conversation with Evan Ponter.

A deep dive into Salesforce reporting

We last spoke to Evan back in 2019. Since then, he’s struck out on his own as a Salesforce Consultant, where he helps businesses get everything they need out of Salesforce and their reporting. He recently did a 75-minute breakout session at TDX about everything reporting, so I wanted to bring him on the pod to tell us all about it.

The 75-minute deep dive breakout session is a new thing we tried this year at TDX, and it was everything we hoped for and more. Evan was able to not only cover the basic concepts around reporting but also get into some very advanced concepts. Or, as he puts it, how to crawl, walk, run, and fly with Salesforce reporting.

Crawling, walking, and running with Salesforce reporting

When he talks about learning to crawl, Evan means that you need to understand that every report you build is meant to answer a question. As he puts it, “Start with the end in mind.” If you know what question you’re trying to answer, you can make decisions about what information you need to see and how you might want to display it.

Next, Evan gets into how to walk and run with reports. To do that, you need to understand what’s happening in a custom report type as far as which records are being visualized and what other opportunities that opens up for you. Several out-of-the-box Salesforce features can help here, like cross filters, with or without filtering, and pulling in fields from other objects. 

Flying towards the future of Salesforce reporting

You probably have the same question I did for Evan: if that’s walking and running with reports, then what does it mean to fly? The answer is Cartesian product data sets, which let you bring together sibling records from two different objects that are both related to a common parent without changing your org’s architecture.

Finally, we get into what the future looks like for reporting. AI is only getting smarter but, as Evan points out, while we might be able to automate some aspects of reporting we’ll still need to understand how everything works if we want to get the results we’re after.

As you can probably tell, this is a very in-depth episode, so be sure to take a listen (or check the transcript) for more on cross filters, with or without filters, Cartesian data sets, and everything about reporting. 

 

Podcast swag

• Salesforce Admins on the Trailhead Store

Learn more

• Podcast: The Ultimate Guide to Report Types With Evan Ponter

 

Admin Trailblazers Group

•  Admin Trailblazers Community Group

Social

• Evan: @RealEvanPorter

• Salesforce Admins: @SalesforceAdmns 

• Mike on Threads: https://www.threads.net/@mikegerholdt/

• Mike on Tiktok: https://www.tiktok.com/@salesforce.mike`

• Mike on X: @MikeGerholdt

 

Full show transcript

Mike: So it's all things reporting this week on the Salesforce Admins podcast. That's right, we're fresh off of TrailblazerDX. I think I've got through my jet lag and the time change, and I'm ready to start reporting. Evan Ponter last week, gave an amazing breakout session at TrailblazerDX, and I had to have him on the pod to talk about what he was talking to all of our wonderful trailblazers about, crawl, walk, run, fly, with reporting. That's right. And speaking of TDX, I want to give a shout-out to Scott, Katie, and Bill, you three are amazing. Thank you for coming up saying hello, sharing your stories with me. You're an inspiration to me, you're why I do the podcast, and thank you to all the listeners that listen in. I got to meet with many of you last week at TrailblazerDX, it's why I love coming to events. And thank you for sharing your stories, I hope to continue inspire you, I hope to continue to make a podcast that is exactly the length of your dog walks and your commutes, because those are what I listen to podcasts for. Of course, we're getting into summer, so I got to start mowing the yard, but let's not talk about grass, this is a Salesforce podcast, and we're going to talk about reporting with Evan Ponter. But of course, before we get into that, just a quick reminder, it is super easy to follow the Salesforce Admins podcast on whatever platform you're listening to me on. So on iTunes, all you got to do is click follow, and then, iTunes takes care of all the hard work for you. Downloads the newest episode, then Thursday morning, you wake up, you're ready to go. Hey, let's knock an episode out on our drive to work, or maybe we're walking the dog that morning, and iTunes will have already downloaded it for you. It's just that easy. Almost all of the podcast apps will do that. So just a reminder, makes it easy, makes it one less thing to worry about. So wanted to get that out of the way, but let's get to our amazing conversation with Evan Ponter. Welcome back to the podcast.

Evan: Thanks for having me.

Mike: I happened to look, it was 2019, which feels like a decade ago.

Evan: Yeah, absolutely.

Mike: I'll definitely link to that episode, because we talked about the ultimate guide to report types, but you're still on the reporting train ...

Evan: I am.

Mike: ... and you gave an amazing session at TDX, and since a lot of people probably weren't there, let's talk about that. But what have you been up to since 2019?

Evan: Oh, well, in the midst of the pandemic, I decided it would be a great time to go out on my own and become an independent Salesforce consultant, through a lot of turmoil, but it ended up being a really incredible journey for me, and the next step in my career, to start to facilitate working for a handful of clients, supporting them as an external Salesforce consultant, being the expert they can lean on. And I've been able to take a lot of this reporting content and apply it to any project that I'm working on.

Mike: Yeah. To be honest with you, it's actually kind of brilliant, if you think about it, with all of the shifts in employment and labor that are going on post-pandemic, being an outsourced admin, developer, architect, however you view yourself, would benefit the company and you.

Evan: Yeah, no, it's been great.

Mike: But you still report, you still literally are my one of two go-to sources for reporting. I go to Jennifer for flows, I go to Evan for reporting. Yes, I go to Trailhead, but I feel like the way you teach me is so much better. So let's talk a little bit about ... Because you gave, at least in admin [inaudible 00:03:57], one of two 75-minute deep dive sessions, which was a little bit of an experiment for us. I think it went well, there's probably some stuff we could do better. But 75-minute session, because we heard from a lot of the feedback, like, 40 minutes is great, but I've sat through a ton of dry runs, and it feels like the start of a really good book, when they're getting to something, and they're like, oh, and if you have any questions, you're like, no, I want another 30 minutes. [inaudible 00:04:31] we did, so you did one on reporting, and I'll let you explain kind of the theory from there.

Evan: Sure, yeah, so over the years, I've given some variation of a report session, and I've had, actually, a couple of different topics. So I can do a 40-minute session, I've done a 20-minute session, and then, I've done a couple more advanced topics as separate sessions. But getting the chance to do a 75-minute hands-on deep dive allowed me to pull a majority from a few of the main topics, but really kind of package it all up together as a full hour of everything you would ever need to know about reporting if you only had an hour to learn it. So it was really cool to tackle some of those basic concepts, but also, get into some of the more advanced stuff. And to have everybody following along and doing the hands-on exercises, you really get to see, feel, and live that experience in the hour of time that we have together.

Mike: Yeah, and I think what was nice ... I mean, it can be intimidating, because a little bit of what we did with this is, it's not really a hands-on workshop, there's not really tables and that traditional learning experience, but people had the option to be what I call, fingers on keyboards, and doing something, or just following along, and I think that's a fun balance. But what I loved about how you walked into it is, there was no real worry about, oh, I better know all of my stuff or I'm going to not know where things are at, because your concept of, was it crawl, walk, run, fly?

Evan: Yeah.

Mike: Really helped walk people through the complexities of understanding reporting.

Evan: Absolutely, yeah. We start with some basic crawl concepts that you can apply to anything. So we built a deluxe report type together, and we learned how using customer report types really opens up a ton of flexibility. But then, as we get into the walking and running portions, we start to look at some more advanced stuff, so really taking that base concept and expanding it so you can tackle different requirements and get a little more complex. And then, the fly piece was really just truly on the frontier of what you can do in a customer port type, so it was really cool to share that with everybody.

Mike: Now, what part of ... So let's go back to crawling. Crawling, to me, feels like, okay, we're just going to use maybe some standard objects stuff that's there, existing architecture. What part of moving from crawling to walking is really ... How much do I need to consider when I'm building an application about the reports? Because I feel like, too often, we build tons of these cool applications, and we forget about what the report is going to look like.

Evan: Yeah, so in any project I've been on, you kind of have to start with the end in mind, and really think about what questions are you going to need to answer with this data, this process, this automation, whatever it is you're doing, so that, you at least have a concept of what a report might look like, so that you can help answer that question. And a lot of what I talk about in the crawl portion is just being able to really make it concrete, what data you're visualizing in a report. And as we build our one deluxe custom report type for that object, you are tying together records from an object to results that you see in a report. And once that concept is tangible, and you can feel that and understand how that's working, then we can look at the walking and running concepts that really build on what's happening in a customer port type, as far as which records are being visualized, and what other opportunities that opens to use some of the other out-of-the-box features, like cross filters, and doing some more advanced filtering capabilities, and pulling in other fields from other objects.

Mike: So you mentioned exactly the thing I was going to talk to you about, which is cross filters, because when I start building reports, or even when I get back into reports, if I haven't done it for a while, it's like riding a bicycle. I feel like I start off and I need training wheels, and then it takes me two, three weeks to get to Tour de France-style bike skill. You just get on a bike, and you're down the road. Cross filters to me always trip me up. What is the most common thing you see when you talk to people, or when you work with companies, that trip people up with cross filters?

Evan: Yeah, so I mean, first of all, I do a whole 20-minute session on cross filters, so being able to incorporate that into this deep dive was really cool. It's one of those topics that I'm super passionate about because there are so many problems that can be solved with a cross filter, people just aren't quite aware of it, or exactly how to set them up. So the basic thing that it solves for is, you want a list of records, but you want to filter them based on the presence or absence of some child records. And what it allows you to do is, anytime you've had a situation where you're like, I have this report results, but I need to get rid of all the duplicates. I really just want to see a clean list of accounts that had opportunities closed last year. And you started with an accounts and opportunities report type, and you have all this opportunity data, but you really just care about seeing the account records. What a cross filter allows you to do is run a report based on your report type that will show you account records, and then filter them based on those opportunities. So you get to filter based on objects and fields from that object that do not appear in your report results as columns. And it opens up a lot of possibilities, so for that example, you would say, show me all the accounts that have opportunities where the close date equals last year, and the stage is closed one, and you get a nice, clean, de-duplicated list of account records that meet that filter criteria. Then, as far as what trips ... A lot of people can get that down once you sort of explain what's happening, what trips people up is when you start using the cross filter using the without operator. So it works 100% opposite of using the with operator. So when we said, show me accounts with opportunities that have a close date in the last year, that means each one of those account records in your results has at least one opportunity that meets that filter criteria. But when we switch that operator to without, it means every one of those accounts does not have any opportunities that meet that filter criteria. And that's where things get super interesting, as you add cross filter criteria that has the without operator, you're actually opening up your results. So you could say something along the lines of, show me accounts that don't have any open opportunities, and what you'll end up seeing is, there are accounts that have opportunities, but none of them are open, they're all closed one or closed loss status.

Mike: And that exactly is where it usually happens that I get tripped up, because ... Well, and I bet it's other admins, too. I think the frustration, you probably dealt with this, too, is, especially when you're helping build a report for somebody, there's an expectation in their head. They know there's usually one or two data points they're looking for that are kind of like a check. Like, is that report really working? And if they don't show up, then they question, well, what's wrong with Salesforce? And well, it's not wrong, it's just, I think to your point, as you add without filter criteria, you're opening more things up. And the thing that always bugged me was, somebody would always pull up on the screen, well, I'm on this opportunity here, and it's got an opportunity, or I'm on this account here, and it's got this opportunity, and it fits all the criteria. And you're like, right, except you don't own that account, and we were doing my account. And you don't say that to be the IT guy, like, move, you say that to be like, no, we have to evaluate all of the criteria and figure out why something is or is not being omitted.

Evan: Exactly, and I think that's a key thing that I show in the presentation, is, if you are on the filters tab of a report, and you understand what the report type results are going to show you, you can read from top to bottom and understand exactly, every filter being applied to your data, which helps immensely when you're doing that troubleshooting of, oh, it doesn't show up because I have a my accounts filter right here. It's going to show you that. So I kind of walk people through that troubleshooting to make sure ... You have to be critical of every filter being applied to your data.

Mike: And not to mention permissions, whether or not they also have view permissions, because they could be looking at someone else's account, or ... Who knows, right?

Evan: Yeah. Yeah, security model definitely comes into play.

Mike: I feel like those are all the diagrams you kind of need to have handy when you're thinking about like, okay, I need to build this report. First, what's the architecture of my organization? Not necessarily the whole org, but pertaining to what I'm creating a report for. And then, two, what security is in place for the individual or individuals that I'm running this report for? Because I don't want to always be the run report as person.

Evan: Right, yeah, that would always show organization-wide data. So yeah, if you have a private model, thinking about that as you're building reports is really going to help you build one report that can support multiple people across different teams, and allow them to only see their own data, but it's also going to help to have an understanding of that as you're troubleshooting things. Because it's going to drive yourself crazy if you're helping two people that are on two different role hierarchy points, and they're running the same report, but they get different results, well, you must have some kind of private model in place, or some kind of my ownership filter on the records in that report.

Mike: Right. So I feel like that's walk, what are some of the things you cover in run?

Evan: Yeah, in the run portion, we're really looking at ... We do a little bit with cross filters, but we also take a look at those with or without style report types, and those can be tricky, as well. I kind of walked through, when you have the normal kind of [inaudible 00:16:13] report type words, every A record must have related B records. You're getting that inner join of the diagram that shows you all the child records that specified parents. And that is still true for that with or without style report type, where it's, each A record may or may not have related B records. It's really two different data sets that get put together and dumped onto your screen. And they can be really useful for solving certain types of problems, but the key thing to think about with those is, those two data sets that are being put together for you, can be filtered independently, so it allows you to do things like, I want to summarize the total amount of opportunities for all the accounts that I own, and I want to just get a summary of the close dates in the last year. So you'll have your opportunity records, and you'll have your account records, and because you can filter them independently, you could say, all right, show me all of my accounts, and then, only show me the opportunities that had a close date in the last year, and I want to summarize the amount from those records. What you'll end up getting is, all of your accounts are going to show up, and if they don't have opportunities that meet that criteria, the account still displays as a row, it just has a zero for that amount summary. And it really helps people keep tabs on things, like, they care about seeing all 50 their accounts, or whatever it is, but they just want a summary from a specific timeframe. And those with or without style custom report types are really the only reporting tool that allow to do something like that.

Mike: Those always trip me up, because it would or would not exclude the entire object.

Evan: Yeah, right, so if you filter an account out of your data, it's also going to take out all of its related records, so all of its related opportunities would also be excluded. But if you just filter based on an opportunity field, it doesn't exclude the account from showing up.

Mike: So the other thing that I've really wanted to always get into, I've never had the use case for, is report formulas, where you're building the formula to also be inside the report. Do you include that in your crawl, walk, run, fly? And if so, where do you stick that?

Evan: No, I mean, we don't get into that. I mean, there's so many different possibilities with setting up row level formulas-

Mike: Yeah, like, it's not going to the same with formulas for just everything, right?

Evan: ... Yeah, right. But yeah, I mean, there's a whole bunch of interesting things you can do. One place where I really like using the row level formulas is when I'm reporting on objects that I cannot customize. So you weren't able to put a formula field on the object, but now that you can do a row level formula in a report, you kind of can at this point. So that's helpful for ... I mean, there's a ton of standard objects where you can't set up a formula field, so having that ability really opens up some possibilities for data points that were never possible before.

Mike: Yeah. [inaudible 00:19:42], let's tackle a little bit ... I know everybody's [inaudible 00:19:45], what is fly? What's fly?

Evan: Fly? Yes.

Mike: So the fun backstory behind this, when I saw your submission, I read it, I was like, okay, cool, Evan's man with the report stuff. Got it, he's going to be great. And then, you showed up to the dry run, and you're like, hey, I kind of added a whole other section, and I'm calling it fly. And I was like, oh, well, tell me more.

Evan: So for as long as I've been reporting in Salesforce, somebody always comes up and asks me a question. I have records in two sibling objects, can I bring them together in a single report without using a joint report? And I've always had to tell them, no, you can't do that. There's no way for me to relate these sibling records together because there's no direct relationship. They have a common parent, but there's no way to say that record one from this object matches up with record two from this other object. So this fly portion of the presentation is what I call Cartesian product data sets. It's on the bleeding edge of what's possible in a custom report type. I've done some experiments, and I think people have been using this style of custom report type and not even realizing what's happening, but there's a handful of scenarios involving Salesforce standard objects that allow you to essentially cross multiply records from one object against records from another object. And it allows you to bring together those sibling records from two different objects that are both related to a common parent. And it's really cool to create one of those data sets. So the example I always give is, if you have three records in one object, and three records in another object, your report results, without any other filters supplied, are going to show you nine results. It would be like, record 1A, record 1B, record 1C, record 2A, 2B, 2C, 3A, 3B, 3C. And it just puts together every possible combination of matching up those records from those two different objects. And if you take that, and then filter it for your needs, you can do things like, the example I give is, you have a bunch of customers, and they have contact roles on opportunities, and you're selling them a bunch of products, and you want to answer the question, which products are associated with which contacts? Those are sibling records, they're both related to a single opportunity, but there's no direct relationship. And I walked through how you can build one of these Cartesian product data sets to cross multiply those records, then, you can put a visualization on a contact record page that shows you the products and the quantities that are associated with that contact, without having to change your architecture, without having to do any custom automation. You can just take advantage of this Cartesian product data set.

Mike: I think the key thing I heard there is, without changing your architecture. We're not building anything new that has to be constantly dealt with forever in time, because it's part of the report. So I didn't prompt you for this question, but I'm going to look ahead, because there's a lot of AI stuff out there, and I'm sure you hear about it, you see it. There's all kinds of plugins and stuff where you can throw spreadsheets at AI, and it'll analyze things, and it'll create pretty charts that, to be honest with you, aren't that hard in Google Docs or whatever, Microsoft, or whatever tool you use. Devil's advocate, five years from now, I don't know, 10 years from now, maybe, at some point, you're just going to be able to say, hey, Salesforce, run this report and make this output. How important is it now to learn this stuff, knowing that AI is going to take care of it?

Evan: I still think people are going to be troubleshooting their reports and their report results, the same, if not more than they do now, once AI is generating this stuff. Because especially at first, we're going to want to verify that we're getting the results we think we're getting, so that we don't cause ourselves to go into hallucinations with [inaudible 00:24:24] business. If the AI is not-

Mike: That's a good point.

Evan: ... Yeah, if the AI is not giving us what we need, then we're not able to make a good business decision. So a lot of the skills I'm teaching are about troubleshooting, and they're about taking the actual root question that needs to be answered, and making sure we're answering that question. And it's less about order taking and just throwing together a bunch of columns on a report and creating a chart out of it. Sure, it might look nice, but what is it we're trying to get at there? And I think that level of scrutiny, if you can start to have those skills now, while you're building the reports manually, those will carry over into the AI world, but we'll be able to scrutinize and make sure that those AI-generated results are giving us the answers that we truly need.

Mike: Yeah. I think about it sometimes, when I have it do whatever kind of calculations, I'll double check it on a different spreadsheet. I always think back to, was it like, fourth or fifth grade, I think, we finally got to use calculators in math class. I am of that age that they used to make you suffer in math and write everything out. And the teacher's saying, well, yeah, but you can't just plug it in, you have to know what to expect the output to be, because the calculator will only do what you tell it to do. It doesn't know what the intent of the answer should be. And I come back to that, I never thought I'd think about that again, and then, here, 100 years later, I'm out of school, and it's kind of like it's coming full circle of, yeah, you can ask this thing to do whatever, but if you don't know how to double check it, or you don't know what the expected answer should be, even for, I'm thinking through a lot of your examples, just running a report that returns a handful of rows, just so that you can check all of your filters, as opposed to creating a report that returns tens of thousands or whatever records, and you're like, oh, it's too much to check, is knowing what you're going to double check for.

Evan: Yeah. Yeah, having that sanity check, I mean, that's crucial, just to make sure that we're getting something valid. And I think trying to do that at the reporting stage is tough if that's the only place where you're doing the sanity check. And I think it's part of the process all the way through, you have to be making sure that your data architecture lines up with what you're trying to capture in the real world, and that the data you're putting into that structure actually aligns and is kept up to date, and has all the right validation rules in place, you're using automation to fill in the gaps, and auto calculate anything that you can. And I think it's important to take advantage of what computers are good at, putting in sound calculations that you can guarantee the arithmetic is going to be correct. But then, we still need to rely on humans to be sanity checking all of this stuff, making sure that we're asking the right questions, and that the answers that we're trying to get out of the system are actually addressing that question.

Mike: Yep, I couldn't have said it any better. Evan, I appreciate you coming back on the podcast and towing the straight and narrow line for us on reporting, and helping us get all of the good data out that we hope our users are putting in.

Evan: Yeah, thanks so much for having me. This is always a blast, I'll come back anytime.

Mike: You bet. Thanks, sir. I bet you never thought you'd hear Cartesian report types on this podcast. I know I didn't. If I had to go back 10 years ago and say words that are going to be said on this podcast, Cartesian report types probably wouldn't be on my list of things. But that's the beauty of the incredible intelligence that everybody has in the community, and some of the really fun stuff that you're doing, like Evan, on report types, and just tapping into all of the features and functionality. I hope you can get a chance to dive into some of the stuff that he's doing, I think it's really cool. Now, if you're doing something like listening to this on iTunes, do me a favor. Click the three dots in the upper right-hand corner. Okay, see, it says share episode? You can now text or post social, like, hey, I just listened to this episode with Evan about reporting, I think it's really cool. And you can share it with your community, share it with your friends, share it with fellow Salesforce admins. Maybe you have some people in your organization that are looking to up their reporting skills. Great idea to share the podcast with them. And of course, if you're looking for more great resources, you can always find everything admin at admin.salesforce.com, including a transcript of the show, so that you can read through, follow along. Of course, be sure to join the conversation, the Admin Trailblazer group, that is in the Trailblazer community. Of course, the link for that, where is it? Show notes, absolutely, you know where it's at. All right, so until next week, I'll see you in the cloud.