Cross Filters, Summary Formulas, and More: Advanced Reporting in Salesforce

Salesforce comes with report types out-of-the-box for all standard objects and standard object relationships eg. “Contacts & Accounts” or “Opportunities with Products”. You can view a list of report types when creating a new report. For the custom objects we build, Salesforce also creates report types automatically.

Salesforce Reporting is one of the most powerful features, used to give users many different views into their data, and for Admins to demonstrate the value Salesforce has upon the organization’s productivity. 

Building reports in the Lightning Report Builder, a drag and drop interface, is extremely effective – as a Salesforce Admin, you should have a solid idea of all the options available to you when reporting requirements come up.

What are advanced reporting features in Salesforce? These are the tricks Salesforce power-users should know to ultimately create the reports users need – even with challenging data models – in the most efficient and scalable way (with minimal additional configuration).  

Let’s have a look at some of the more advanced reporting options that Salesforce offers.

1. Custom Report Types

Salesforce provides out-of-the-box report types for standard objects and their relationships. When creating a new report, you can choose from a list of available report types.

For custom objects, Salesforce automatically generates report types. These report types establish a “with” or “and” relationship based on whether the two objects have a Lookup or Master-detail relationship.

Custom Report Types become essential when you need to report on:

• More than two objects simultaneously,
• “Without” relationships in addition to “with” relationships.

To create a custom report type:

• Select the objects you want to include.
• Depending on the Primary object, the wizard displays its child objects, allowing reporting on grandchild objects up to a total of 4 objects.
• Specify whether the selected object must have related records to appear in the report, useful for scenarios like showing Accounts with & without Opportunities.

Bonus: You can now automatically add fields to Custom Report Types without manually adding them to each report type.

2. Cross Filters (Exception Reports)

When filtering reports, Cross Filters help identify records with or without related records, creating exception reports. They are added like regular filters, using the format: “Show me — [primary object] — with/without — [related object].”

Example: “Show me — Contacts — without — Campaign History.”

Enhance Cross Filters with additional sub-filters to narrow down related records further. For instance, “Show me — Contacts — without — Activities — with status: ‘open'” will generate a report of contacts with no open activities.

Salesforce Admins can master Cross Filters to avoid unnecessary creation of custom report types or rollup fields.

3. Summary Formulas

Summary Formulas handle complex calculations at all summary levels, acting like fields once created. In Salesforce Lightning, they are referred to as Summary Formula Columns. To use them:

1. Add a number field to the report canvas.
2. Open the “Fields” left sidebar and locate the “Summary Formulas” section.
3. Search and insert fields and functions, defining calculations.
4. Validate and apply the formula, and it appears as the rightmost column in the report.

Summary Formulas offer powerful capabilities for in-depth calculations.

4. Row-level Formulas

Row-Level Formulas, distinct from Summary Formulas, are applied to single records and displayed on the row itself. An example is checking if two fields on the same record have the same value.

5. Reporting Snapshots

Reporting Snapshots capture a snapshot of Salesforce records, freezing data for future reference. Commonly used for tracking Opportunity Pipeline Change over time.

6. Historical Trend Reporting

Historical Trend Reporting glances back at historical field values on a specific date. Limited to certain objects and fields, it provides a targeted view of historical data changes.

7. Field-to-field Filters

These filters allow the comparison of values in different fields on a Salesforce report, refining report filter criteria.

8. Stacked Summaries

Stacked Summaries summarize extensive data for quick review and comparison, activated when adding fields to both rows and columns.

9. Joined Reports

Joined Reports combine two reports with different report types into a single view, offering a holistic data view.

10. Report URL Hack

Report URL Hacking is a technique to set filter values via URL parameters, supported in Lightning Experience. It allows dynamic filtering without the need for multiple reports, enhancing user experience.

This URL hacking can be used to launch specific reports with predefined filters, adding efficiency to report navigation.