Apex · Integration · REST · Salesforce

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
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();
Callout for updating the Report:
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);

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
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;
}
}
<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>

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

Advertisements

2 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.

    Liked by 1 person

    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. 🙂

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s