Dynamic Reports – A How-to Guide

Open reports with a click of a button and dynamically pass parameters to report filters!

Purpose:

This blog will show you how to create dynamic contextual reports that users can access directly from the record,  (ie: Account or Opportunity).  Instead of having to navigate to the Reports tab, search for your report, and add the record as a filter, the same can be achieved straight from the record page. This nifty usability hack will make your end users love you!

Scenario:

We all know how painless it is to create reports in Salesforce. Just a few clicks and your report is ready to go and you’re off to making better-informed decisions for your organization. Now, here comes a scenario where your senior account manager wants to view a report of all the opportunities and their respective stages along with forecasted and actual revenue for a particular account.

Now, before you read this blog, you might have achieved this by following these steps:

1. Navigate to the Reports tab
2. Create a new report or open an existing report
3. Set the account name filter to ‘United Oil & Gas Corp‘ (for example purposes) 
4. Save & run the report

What was previously a 4-step process is now done in 1-step.

As a Salesforce admin, you can share this trick with your entire user base.

Steps to achieve the purpose:

Step 1: Create a folder and report

1. Create a folder called ‘Dynamic Reports’ 
2. Share the folder to users who should access these reports 

 These reports act as templates which is why we want to save them in different folders.

3. Go into the folder and click on ‘New Report’ 
4. Select report type, ‘Opportunities’
5. Click ‘Continue’

You will be taken to the following screen:

Step 2: Adjust and add new filters

Every report has standard filters which cannot be removed. On top of this, you will be adding more custom filters and filter logic to build your desired report.

For this report the following filters are standard:

• Show Me
• Created Date
• Opportunity Status
• Probability Status 

1. We made this parameter null so that we can pass the Account Name value (field value) into the filter from our button.
2. Then, hit ‘Save & Run’  and name your report., 
3. Copy the URL of the report up until ‘url…/view’https://xxxxxxxxxxx/Report/reportid/view)]

Step 3: Create a button and pass the value of fields that are needed for the report.

1. Go to the Account object in object manager
2. Go to ‘Buttons, Links, and Actions’ and click on new ‘Button or Link Add description

3. Then, give a name to the button, say ‘Opportunities’ and make sure the display type is  ‘Detail Page Button’.

The rest of the options can be left as is.

4. The report URL that you copied in Step 2 needs to be pasted in the canvas.

Now, here are how the parameters are passed through the filter value:

After you have pasted the URL in the canvas, 

1. Add a ‘?’ after ‘view’
2. Add a variable ‘fv0’ and equate it to field, ‘Account Name’, so that value of account name will be passed into that variable.

[ie:https://abcd.force.com/lightning/r/Report/00O7F00000AF5t6UAD/view?fv0={!Account.Name}]

‘fv0’ is the custom filter that you have used in your report; (see the below screenshot to understand more). Prior to that, save the settings and add the button to the page layout.

Then, go to the ‘United Oil & Gas Corp’ account and click on the button you have created to open the report which will display the opportunities for the selected account.

Soon after the report opens, look at the URL and observe that the account name parameter is passed and click on the filter of the report to see that the account name filter is equal to ‘United Oil & Gas Corp’.

https://abcd.force.com/lightning/r/Report/00O7F00000AF5t6UAD/view?fv0=United%20Oil%20%26%20Gas%20Corp.

Let’s decode the magic!

Salesforce uses these UI tags: fv0, fv1, fv2 for custom filters added after standard filters in URLs to run the report. The ‘fv(n)’ tag is used for lighting reports and ‘pv(n)’ tag is used for classic reports.

So,  each custom filter you add has a tag as shown in the below picture.

These tags are used only to input the field’s value from the button in order to obtain the desired dynamic report.

Tips:

Tip #1:

To pass more values in a button, use “&&” operator after each tag as shown below:

https://abcd.force.com/lightning/r/Report/00O7F00000AF5t6UAD/view?fv0={!Account.Name}&&fv1={!Account.OwnerFullName}

Tip #2:

If the data type of the fields are the same in two different objects, you can create a custom report button for object A and get the desired report for object B by passing the value of a field to object B field.

Example: 

Object A = Account | Object B = Cases

You have SLA for an account where your company resolves these cases under these agreed SLAs. As a service manager, you have defined the SLA hours for each priority in Account. 

P1 = 2 hours, this field P1 is a number field representing time.

Now you would like to get a report of cases with priority P1 exceeded the SLA agreed for the account.

In the Case object, there is a field which clocks the hours it took to resolve a ticket. Field name is ‘Hours’ and also ‘Priority’ picklist. You create a report with two hours filters with “Or” filter logic like this.

Hours >= “” OR Hours <= “” . Null is where you input the parameter from account to case.

As both fields in Account & Hours in Case are of the same data type, we were able to input the value by following the above steps to create a dynamic report.

Conclusion:

Now that you have this trick up your sleeve, spread the knowledge to your users and help them become more productive!