One Report to Rule Them All: Reduce Clutter with Salesforce Dynamic Report Filters

The beloved ‘hack’ from Salesforce Classic that lets you override report filter values with URL parameters is back in Lightning Experience. Whether you’ve heard of this unsupported feature from Salesforce Classic or not, read on. We recently introduced dynamic report filtering in Lightning Experience (with a few nuances).

Why do you need dynamic report filters?

Say Michelle is a Sales Manager in your organization, and she oversees 20 sales reps, each of whom owns an average of 10 accounts. Michelle has asked you to create a button on the Account detail page to link to a report, filtered for opportunities owned by the user clicking that button, and for the account that the user is coming from. 20 sales reps X 10 accounts — that’s 200 reports right? Nope. With this feature you need only one report, and here’s how.

In this blog post, I’m going to walk you through the process of creating a base report, adding filters to it; then adding a button/link to the Accounts page layout that goes to the base report and dynamically sets filter values.

First let’s take a look at the syntax and break it down:

1. https://danielsgh.lightning.force.com/one/one.app#/sObject/00O6A000002B0sSUAS/view — This is a Report’s base URL, which contains the Report ID: 00O6A000002B0sSUAS.
2. ?fv0=0016A000005GwP1QAK — ? introduces a parameter to the URL, followed by fv0 (or filter-value-zero) — the first URL parameter which corresponds to the first custom report filter. Then =0016A000005GwP1QAK or equals <AccountId>.
3. &fv1=Michelle%20Smith — & to add the second filter value, and fv1=Michelle%20Smith to filter on ‘Michelle Smith’.

Setting up the report

In our example we are only going to use two filters, so we need to set a report and add two filters with the ‘equals’ operator, and add null filter values. Note that URL parameters can only override existing filter values — Filters and their operator cannot be edited, added, or removed.

When a user opens the report by clicking on the custom button that will be added to the Accounts tab, the first filter Account ID equals “” will be mapped to URL parameter fv0, and its value will be overridden by the value that follows fv0= in the URL. Similarly fv1 will be mapped to the second report filter — Opportunity Owner.

Once you save the report, copy its base-URL after clicking on the report name from the Reports tab.

Creating a custom button on the Account Detail Page

From the Object Manager in Setup, open the Account object and create a custom Detail Page Button. Make sure you set the button’s Content Source to ‘URL’.

In the formula field paste the Report base-URL, and add an ?fv0= to introduce the first URL parameter, which will override the first filter value on the report — Account Id. To dynamically set that value based on the account record, add the merge field ‘{!Account.Id}’.

The second URL parameter is introduced using an &fv1= followed by the {!User.Name} merge field to dynamically override the second filter value — Opportunity Owner.

Save and make sure to add this button to the account detail page layout.

Now Michelle or any other member of her team can click on View Opptys Report button from an account detail page, and get a personalized report, filtered on the opportunities that they own and on the account from where they came from.

Summary

With just a few simple steps we’ve created one report that will serve 200 reps and accounts. This powerful feature provides an effective way for maintaining a sane amount of reports in your Salesforce implementation, and proactively reduce the clutter in your organization.

Make sure to watch the video we created on dynamic report filters, and stay tuned for more blog posts covering Reports and Dashboards tips and tricks.