As a Salesforce Admin, giving actionable analytics to your users, stakeholders, and executives is a key part of your responsibilities. In fact, in our Essential Habits for Salesforce Admins series, we walk you through how you can make reporting out to your stakeholders and end users an actionable habit. That is to say, reporting is a fundamental part of what you do for your company. And the right report can provide critical insights into trends where you need to take action, as well as data quality and potential issues. Now, whether you’re just starting your journey as a Salesforce Admin or you’ve lost count of the number of dashboards and reports you’ve built over the years, there’s always something to learn about report building. Here are some advanced reporting concepts to help you build complex reports.
A joined report can contain data from multiple standard or custom report types, giving you a more holistic view of your data. You can add report types to a joined report if they have relationships with the same objects. As an admin, you need to create report types based on one or more objects.
So, for example, if you have a joined report that contains the Opportunities report type, you can add the Cases report type as well because they both have a relationship to the Accounts object. Or, let’s say that your regional leads are trying to figure out which accounts they should have the sales team focus their attention on. You can join the Accounts report type with the Opportunities report type and the Cases report type — since they all share common fields — in order to view the accounts with high-value opportunities that have escalated cases associated with them. Based on this information, you can take the appropriate next steps.
Cross filters are another great way to connect data, and they also help to de-duplicate reports. You can use cross filters to include or exclude records in your report results based on related objects and their fields. This allows you to look deep into your data without having to do any coding.
Let’s go back to our regional leads example. If your regional leads wanted to see Accounts with or without Opportunities report and filter on Amount in order to see high value opportunities and the related accounts. You can also add another cross filter for cases which are escalated in order to see high value opportunities with escalated cases.
Summary formulas can be used for various use cases and are a great way to calculate additional totals based on the numerical values in your report. If you’re familiar with writing formulas in spreadsheets, validation rules, or formula fields, then you’ll be familiar with the way reports use summary formulas.
So, how do you use summary formulas? Well, you can start by using the out-of-the-box summary formulas, such as “sum”, “avg”, “min”, or “max” for fields like Amount, Revenue, etc. But that might not give you everything you’re looking for, in which case you can add a new summary formula. This makes most sense for a summary report, where you need to write formulas for aggregates. You can add a summary formula by clicking the dropdown arrow next to Columns and selecting Add Summary Formula.
Let’s say, for an opportunity, you want to calculate the amount split between multiple reps per opportunity type. You can create a summary report based on Opportunity grouped by “Type”. Then, you can simply insert a summary formula, such as Amount: SUM/ Opportunity.Owner.Name:UNIQUE. You can always click Validate in the formula builder in order to check the syntax of your formula.
Summary formulas are meant to calculate data across multiple records, while row-level formulas are calculated across every single record that occurs on the row itself. You can add a row-level formula by clicking the dropdown next to Fields and selecting Add Row-Level Formula.
Here’s an example. Let’s say you need to create a report to figure out misaligned billing and shipping states for a product and make sure your deliveries are going to the right addresses. First, run a report on Product, adding the billing and shipping state columns to it. Next, add your row-level formula — If (Billing.State <> Shipping.State, 0, 1) — and validate it. Now, you can see misaligned states on your report. Keep in mind: You can only have one row-level formula per report.
When linking to reports or bookmarking a report, you can add filter value parameters to the URL to customize how the report filters when opened. Here’s an example.
Bookmark your opportunities report and add a filter value parameter to specify seeing New Business or Existing Business:
https://MyDomainName.my.salesforce.com/lighting/r/Report/00OR0000000PCHYMA4/view?t=1479844235107
Append the parameter &fv0=Filter Value to the end of a report’s URL.
Reporting is a key function and an essential habit for all Salesforce Admins. When you use advanced reporting, you build a path to becoming a collaborative, knowledgeable business partner because you’re better able to share valuable business insights with stakeholders and end users. In turn, those insights help your organization make critical business decisions. Practice and flex advanced admin reporting skills whenever possible, whether you’re creating reports for your company’s corporate objectives or your sales department’s KPIs.
Reports and dashboards are one of the go-to tools admins can use to make data actionable. I’ve heard countless stories from admins about how they built a dashboard, showed leadership, and got a “This is amazing!” response. Admins have shown me reports and dashboards to track all sorts of key performance indicators (KPIs) that have […]
If you’ve tuned into any podcast or blog post on data recently, chances are you’ve heard data referred to in lofty terms. “The oil of the 21st century” and “the lifeblood of any business” are some that come to mind for me. What is definitely true, in the context of your Salesforce environment, is that […]
Twenty years ago, while managing systems for the IT team at a small software company, I was encouraged by our CEO to think of myself as a business person first and an IT person second. He knew of my love for working with systems and data, and suggested that employing those talents to optimize processes […]
