Parameterized Reports for Objects and their Related Records from Custom Buttons in Salesforce

June 28, 2017 Appirio

By Vikas Menon

Here’s the scenario: A user wants to see the open activities of a particular account, and other related objects, by clicking a button on the account page layout. To achieve this, you will need a dynamic report that displays activities based on the given account and its related records.

Here’s the approach: Create a report in which data will be passed dynamically via the detail page custom button. If you want to see account activities only, you can pass the Account ID to your report, but if you want to display its related record activities, then you must query its child records and pass their IDs separated by comma. A user will click on the Custom button that will redirect them to the report. Results in the report are then displayed on the basis of IDs passed in the first URL parameter of the report (i.e., pv0).
Parameter pv0 = <RecordID1, RecordID2, RecordID3>

A URL will be generated as follows, when Custom button is clicked on Account:
https:///? pv0=0019000001gx6co, a0m9000000fjtte, a0m9000000fjttf

Steps to perform:
1. Create a formula field on the Activity named “What ID” with the following formula.
Formula text: What ID (refers to related object on which activity is created)
NOTE: We know that the ‘Related To’ field (What ID) is available on activity but
‘Related To’ takes only the Record name as a filter (not ID). Hence, we need to create a formula field.

2. Create a report displaying open Activities (Tasks and Events) with the following criteria: [What ID
equals “”], this report will take What ID values dynamically.

3. Create a custom label “ViewAllActivitiesReportID” which will store the Report ID created above (18
digit report ID).
4. Create a new Custom Button on the applicable object.
5. Set the Display Type to “Detail Page Button” and its Behavior to “Execute JavaScript.”
Update the content in the text box as follows:
Here Invoice__c is the custom object which is the child object of the Account.

{! REQUIRESCRIPT ("/soap/ajax/30.0/connection.js")};
{! REQUIRESCRIPT ("/soap/ajax/30.0/apex.js")}
var record IDs = '{!Account.Id}';
// Get invoice records related to account
Var results = sforce.connection.query ("Select Id,Account__c from Invoice__c where Account__c='{!Account.Id}'");
results = results.getArray ("records");
var recId;
for(var i = 0;i< results.length;i++){
recId = results[i].Id;
recordIds = recordIds +","+recId.substring(0,15);
var reportId = '{!$Label.ViewAllActivitiesReportID}';"/"+reportId+"?pv0="+recordIds,"_blank");

6. Add button to Account layout.

7. Click the button and it will redirect us to the report as it dynamically passes the
Account ID and its related invoice record IDs to What ID field of activity separated by comma.
So, all activities related to Account and its related invoices are shown to the user in one report.

Thus we see that if we want to display a list of related records then we can just pass the values as report parameters. Using this approach will save a lot of time, as we don’t have to create a Visualforce Page to display records. No Code OR Low code approach is always better than a Customization approach.

Previous Article
Recent Enhancements to G Suite Security
Recent Enhancements to G Suite Security

Next Article
The Best Way To Sort Date And Date/Time Columns in Data Tables
The Best Way To Sort Date And Date/Time Columns in Data Tables