Dynamically Changing Filters in Reports using Analytical API

Dynamically Changing Filters in Reports using Analytical API
Scenario :-

Let us Assume there are 3  representatives  working under the same territory . Now based on the selection of each representative in VisualForce page , his data should be displayed in the report  .

The Reports and Dashboards REST API gives you programmatic access to your report and dashboard data and to visualize the  data.

The Analytics API has been made available in Apex in the Spring ’14 release (API version 30).  and available for use in pages with an API version of 29 and above.

Here, in this case, we would use Analytical API to access the reports and make some changes(Update the report)  in report names ,filters ,folder etc.. for above scenario whenever the representative is changed ,we will change the filter value as that selected representative and  save changes to a report through  HTTP callouts  by sending a PATCH request to the Report resource.

This PATCH request /services/data/v29.0/analytics/reports/****000000JgMf to the Report resource ,updates and saves the report.

Filter a report chart by fields in addition to field filters already in the report to get specific data. Note that a report can have up to 20 field filters. A filter has these attributes in the form of a JSON string:

  • column: The API name of the field that you want to filter on.
  • operator:The API name of the condition you want to filter a field by. For example, to filter by “not equal to,” use the API name “notEqual.”
  • value: The filter criteria.

{column:’ OWNER ’,

operator:’ equals ’,

value:’ Representative Name ’}

While using the Reports and Dashboards, REST API with a POST request body, we  must use content-type: application/JSON. We might get unexpected results if we don’t use this content type.

Check the below sample code :
  Callout for getting report data

[sourcecode language=”java”]
Httprequest req= new HttpRequest();
req.setEndpoint(URL.getSalesforceBaseUrl().toExternalForm()+
‘/services/data/v29.0/analytics/reports/*****000000JgMf/’);
req.setMethod(‘GET’);
req.setHeader(‘Content-Type’,’application/json’);
req.setHeader(‘Authorization’,’Bearer’+UserInfo.getSessionID());
Http httpReq = new Http();
HttpResponse res =httpReq.send(req);
string body =res.getBody();
[/sourcecode]

Callout for updating the Report:

[sourcecode language=”java”]
Httprequest reqt =new HttpRequest();
reqt.setEndpoint(URL.getSalesforceBaseUrl().toExternalForm()+’/services
/data/v34.0/analytics/reports/*****000000JgMf?_HttpMethod=PATCH’);
reqt.setMethod(‘POST’);
reqt.setbody(newbody);
reqt.setHeader(‘Content-Type’,’application/json’);
reqt.setHeader(‘Authorization’,’Bearer ‘+UserInfo.getSessionID());
Http httpReq2= new Http();
HttpResponse ress=httpReq2.send(reqt);
[/sourcecode]

Before making any callouts please add Endpoint URL in Remote site settings, please check Setup->Security->Remote site settings

Select Satish lokin from the list of Representatives and now refresh the report to view the changes in Account owner filter value.

VF Page

screen-shot-2016-09-29-at-23-47-58

Check the Logic for the above example

[sourcecode language=”java”]
public with sharing class DynamicController {

public String RepName { get; set; }
public PageReference changeOwer() {
//Getting the report data
Httprequest req= new HttpRequest();
req.setEndpoint(URL.getSalesforceBaseUrl().toExternalForm()+’/services/data/v29.0/analytics/reports/00O90000009BCxY/’);
req.setMethod(‘GET’);
req.setHeader(‘Content-Type’,’application/json’);
req.setHeader(‘Authorization’,’Bearer ‘+UserInfo.getSessionID());
Http httpReq = new Http();
HttpResponse res =httpReq.send(req);
string body =res.getBody();
system.debug(‘ReportStr’+body);

//The Logic will be changed based on te requirement
body = body.subStringBetween(‘”reportMetadata”‘, ‘}}’);
string subString = body.subStringBetween(‘”column”:”USERS.NAME”,’, ‘}’);
System.debug(‘Chek’+subString);
string newBody = ‘”operator”:”equals”,”value”:”‘+RepName+'”‘;
string LatestBody = body.replace(subString, newBody);
string PostBody = ‘{“reportMetadata”‘+LatestBody+’}}}’;
System.debug(‘PostBody’+PostBody);
//Updating the Report
Httprequest reqt =new HttpRequest();
reqt.setEndpoint(URL.getSalesforceBaseUrl().toExternalForm()+’/services/data/v34.0/analytics/reports/00O90000009BCxY?_HttpMethod=PATCH’);
reqt.setMethod(‘POST’);
reqt.setbody(PostBody);
reqt.setHeader(‘Content-Type’,’application/json’);
reqt.setHeader(‘Authorization’,’Bearer ‘+UserInfo.getSessionID());
Http httpReq2= new Http();
HttpResponse ress=httpReq2.send(reqt);

return null;
}
}
[/sourcecode]

[sourcecode language=”HTML”]
<apex:page controller=”DynamicController”>
<apex:form >
<h> Select any Representative</h>
<apex:selectList multiselect=”false” value=”{!RepName}” size=”1″>
<apex:actionSupport event=”onchange” action=”{!changeOwer}”/>
<apex:selectOption itemLabel=”–None–” itemValue=”–None–“/>
<apex:selectOption itemLabel=”Alex” itemValue=”Alex”/>
<apex:selectOption itemLabel=”Shar” itemValue=”Shar”/>
<apex:selectOption itemLabel=”Satish Lokin” itemValue=”Satish lokin”/>
</apex:selectList>
</apex:form>
</apex:page>
[/sourcecode]

All limits that apply to reports created in the report builder also apply to the API,  For more information, see “Salesforce Reports and Dashboards Limits”

Related links:

View Code in GitHub

Analytical API Documentation

Leave a Comment

Your email address will not be published. Required fields are marked *

Recent Posts

salesforce for financial services transforming customer engagement operational effectiveness
Salesforce for Financial Services: Transforming Customer Engagement & Operational Effectiveness
einstein copilot transform your salesforce experience
How Einstein Copilot Actions Can Transform Your Salesforce Experience
how can salesforce experience cloud drive customer engagement rates
How Can Salesforce Experience Cloud Drive Customer Engagement Rates?
salesforce world tour essentials mumbai
Salesforce World Tour Essentials Mumbai (19 June, 2024)
benefits of using salesforce for higher education Industry
Benefits Of Using Salesforce For Higher Education Industry?
Scroll to Top