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

3 thoughts on “Dynamically Changing Filters in Reports using Analytical API”

  1. Had a great experience with the blog of about dynamically changing Filters in Reports using Analytical API. Thank you for sharing the blog. I feel more useful with this blog, since I am working in the same Salefoce Field.

    1. Thank you for the comment and we are glad that it was helpful to you. Please follow us, we will be posting more such information covering different topics. 🙂

      1. Thank you for the approach, but when i tried this on report with chart and when tried to change the filter using this approach, my chart got deleted. Do you have any idea about this issue?

Leave a Comment

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

Recent Posts

prover automation tool a guide to salesforce automation
Provar Automation Tool: A Guide to Salesforce Automation
salesforce experience cloud features
Salesforce Experience Cloud Features & Its Benefits
why should you choose salesforce revenue cloud and cpq
Why Should You Choose Salesforce Revenue Cloud & CPQ!
5 most important salesforce marketing cloud features
5 Most Important Salesforce Marketing Cloud Features
absyz your trusted salesforce managed services provider
ABSYZ: Your Trusted Salesforce Managed Services Provider
Scroll to Top