The Analytics API has been made available in Apex in the Spring ’14 release (API version 30). Additionally, the Visualforce component <analytics:reportChart> has been made Generally Available (GA) in the Spring ’14 release and is available for use in pages with an API version of 29 and above. This article explores using the Analytics API in Apex in conjunction with the <analytics:reportChart> component.
The filter attribute
The <analytics:reportChart> component’s description in its documentation is:
Use this component to add Salesforce report charts to a Visualforce page. You can filter chart data to show specific results. The component is available in API version 29.0 or later.
Before you add a report chart, check that the source report has a chart in Salesforce app.
The filter attribute has the following description:
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.
For example,
[{column:’STAGE_NAME’,operator:’equals’,value:’Prospecting’},{column:’EXP_AMOUNT’,operator:’greaterThan’,value:’75000′}].
To get the API name of the field and the operator, make a describe request via the Analytics REST API or Analytics Apex Library as shown in these examples:
Analytics API
/services/data/v29.0/analytics/reports/00OD0000001ZbNHMA0/describe
Analytics Apex Library
- First, get report metadata from a describe request:Reports.ReportManager.describeReport(00OD0000001ZbNHMA0)
- Next, get operators based on the field’s data type using this method:Reports.ReportManager.getDatatypeFilterOperatorMap()
I built a Visualforce page and custom controller to test out using the Analytics Apex Library method of getting the API name of the field and operator to build the filter JSON. The goal of the page was to provide a way to allow users to specify input to dynamically filter any given chart. To allow for that, the page needed to display a label, the available operators, an input for the available columns, and a way to update the chart. The rest of this article details the code for the Visualforce page and controller, highlighting the portions of the Analytics API in Apex.
Page
This is a screen shot of the finished page. The user initially selects a report and clicks the Get Report Filters button which then displays the available report filters and chart. The user can then specify additional filters and get the updated chart.

Code
Below is the code for the Visualforce page followed by the code for the custom controller. The code is also available in this Gist.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
<apex:page controller="AnalyticsController"> <style> label { font-weight: bold; } #filters { overflow: hidden; width: 100% } #filterBox { float: left; align: center; padding: 5px 5px 5px 0px; } </style> <apex:form > <apex:outputLabel value="Select Report"/> <apex:selectList value="{!reportId}" multiselect="false" size="1"> <apex:selectOptions value="{!availableReports}"/> </apex:selectList> <apex:commandButton action="{!getReportInfo}" value="Get Report Filters" reRender="report"/><br/> <apex:outputPanel id="report" layout="block"> <apex:outputPanel rendered="{!reportId != null}"> <div id="filters"> <apex:repeat value="{!availableColumnFilters}" var="colFilter"> <div id="filterBox"> <apex:outputLabel >{!colFilter.label}</apex:outputLabel><br/> <apex:selectList value="{!colFilter.operator}" size="1" multiselect="false" style="width: 100px;"> <apex:selectOption itemLabel="--None--" itemValue=""/> <apex:selectOptions value="{!availableDataTypeFilterOperators[colFilter.dataType]}"/> </apex:selectList> <apex:inputText value="{!colFilter.value}"/> </div> </apex:repeat> </div> <apex:commandButton value="Get Chart with Filters" reRender="chart"/><br/> <apex:outputPanel layout="block" id="chart"> <analytics:reportChart reportId="{!reportId}" filter="{!chartFilter}"/> </apex:outputPanel> </apex:outputPanel> </apex:outputPanel> </apex:form> </apex:page> |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 |
public with sharing class AnalyticsController{ public List<SelectOption> availableReports { get; set; } public Id reportId { get; set; } public Map<String, List<SelectOption>> availableDataTypeFilterOperators { get; set; } public List<ColumnFilter> availableColumnFilters { get; set; } public AnalyticsController() { availableReports = retrieveAvailableReports(); availableDataTypeFilterOperators = retrieveAvailableDataTypeFilterOperators(); } public List<SelectOption> retrieveAvailableReports() { List<SelectOption> reptOpts = new List<SelectOption>(); for (Report r : [ Select Id, Name From Report Where Format In ('Summary','Matrix') Order By Name ]) { reptOpts.add(new SelectOption(r.Id, r.Name)); } return reptOpts; } public Map<String, List<SelectOption>> retrieveAvailableDataTypeFilterOperators() { Map<String, List<SelectOption>> dataTypeFilterOpts = new Map<String, List<SelectOption>>(); Map<String, List<Reports.FilterOperator>> filterOperatorMap = Reports.ReportManager.getDataTypeFilterOperatorMap(); for (String dataType : filterOperatorMap.keySet()) { List<SelectOption> operators = new List<SelectOption>(); // Append _DATA to match ColumnDataType from ReportTypeColumn dataTypeFilterOpts.put(dataType.toUpperCase() + '_DATA', operators); for (Reports.FilterOperator fo : filterOperatorMap.get(dataType)) { operators.add(new SelectOption(fo.getName(), fo.getLabel())); } } return dataTypeFilterOpts; } public PageReference getReportInfo() { Reports.ReportDescribeResult descRes = Reports.ReportManager.describeReport(reportId); availableColumnFilters = new List<ColumnFilter>(); for (Reports.ReportTypeColumnCategory category : descRes.getReportTypeMetadata().getCategories()) { for (Reports.ReportTypeColumn col : category.getColumns().values()) { if (col.getFilterable()) { ColumnFilter cf = new ColumnFilter( col.getLabel(), col.getName(), col.getDataType().name() ); availableColumnFilters.add(cf); } } } return null; } public String getChartFilter() { return JSON.serialize(getSelectedFilters()); } private List<ColumnFilter> getSelectedFilters() { List<ColumnFilter> selectedFilters = new List<ColumnFilter>(); for (ColumnFilter cf : availableColumnFilters) { if (String.isNotBlank(cf.operator)) { selectedFilters.add(cf); } } return selectedFilters; } public class ColumnFilter { public ColumnFilter(String lab, String col, String dt) { label = lab; column = col; dataType = dt; } // Values needed for apex:analytics component public String column { get; set; } public String operator { get; set; } public String value { get; set; } // Values need for display and operator select list public String label { get; set; } public String dataType { get; set; } } } |
Available Reports
The code to display the available reports in the retrieveAvailableReports() method is just a simple query on the Report object.
1 2 3 4 5 6 7 8 9 10 11 12 |
public List<SelectOption> retrieveAvailableReports() { List<SelectOption> reptOpts = new List<SelectOption>(); for (Report r : [ Select Id, Name From Report Where Format In ('Summary','Matrix') Order By Name ]) { reptOpts.add(new SelectOption(r.Id, r.Name)); } return reptOpts; } |
The query is limited to summary and matrix reports since those are the only types that might have a chart and are available through the API. If you do try to get a joined report with the Reports.ReportManager.describeReport() method you get the error “reports.FeatureNotSupportedException: You’re requesting data for an unsupported report format. Only summary or matrix formats are currently supported.” or if you try to use it in the component, the component renders with the error “The report chart is unavailable because the report’s format is not summary or matrix.”
I did not see any fields that specified whether a chart existed on the Report object or any related object that could be queried to determine that. If a chart doesn’t exist on the report it is still displayed as an option in the page. However, the component will handle the situation where a matrix or summary report that doesn’t have a chart is selected by displaying the following error message instead of the chart: “You can’t view the report chart because its report, report type, or chart has been deleted.”
Report Metadata
The filter attribute documentation states that information about the report can be retrieved with the Reports.ReportManager.describeReport(reportId) method. That returns a Reports.ReportDescribeResult instance that has a method getReportTypeMetadata() that returns a Reports.ReportTypeMetadata instance which has a method getCategories() that returns a List of Reports.ReportTypeColumnCategory. The Reports.ReportTypeColumnCategory has a getColumns() method which returns a Map<String, Reports.reportTypeColumn>, which maps the column’s API name to a Reports.ReportTypeColumn. Reports.ReportTypeColumn has the following methods that are useful for filtering:
- getDataType() returns a Reports.ColumnDataType, which is an enum.
- getFilterable() returns a Boolean that specifies whether the column is filterable or not
- getName() returns the API name (same as key)
- getFilterValues() returns the available values for a filter (i.e., what you see in the report builder field filter popup for picklists). I didn’t use this for simplicity’s sake.
The columns are grouped into the same categories in which they appear in the report builder. The following code gets the column information.
1 2 3 4 5 6 7 8 9 10 11 12 |
for (Reports.ReportTypeColumnCategory category : descRes.getReportTypeMetadata().getCategories()) { for (Reports.ReportTypeColumn col : category.getColumns().values()) { if (col.getFilterable()) { ColumnFilter cf = new ColumnFilter( col.getLabel(), col.getName(), col.getDataType().name() ); availableColumnFilters.add(cf); } } } |
I created a class ColumnFilter to represent the column filter. There is an existing class that represent columns, but I needed to create my own so that there would be a place to store the form inputs as well as a way to easily create the JSON string in the needed format. In the above loop each Reports.ReportTypeColumn’s label, name (API name), and Reports.ColumnDataType are stored in a ColumnFilter. The Reports.ColumnDataType is an enum. The name of the Enum is in the form of DATATYPE_DATA, e.g., STRING_DATA for strings, PHONE_DATA for phones, etc.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
public List<ColumnFilter> availableColumnFilters { get; set; } public class ColumnFilter { public ColumnFilter(String lab, String col, String dt) { label = lab; column = col; dataType = dt; } // Values needed for apex:analytics component public String column { get; set; } public String operator { get; set; } // form input public String value { get; set; } // form input // Values need for display and operator select list public String label { get; set; } public String dataType { get; set; } } |
Data Type Filter Operator Map
The filter attribute’s documentation states that the Reports.ReportManager.getDatatypeFilterOperatorMap() method can be used to get operators based on the field’s data type. The Reports.ReportManager.getDataTypeFilterOperatorMap() returns a Map<String, List<Reports.FilterOperator>> that maps the data type to the List of operator options available for that data type. The data type key is lower case, e.g., string, int, phone, picklist, etc. This is different than the Reports.ColumnDataType enum, so the key is converted to uppercase and the _DATA suffix is added to it. As the available columns are iterated over in the <apex:repeat> in the Visualforce page their available operator options will be retrieved from this map.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
public Map<String, List<SelectOption>> retrieveAvailableDataTypeFilterOperators() { Map<String, List<SelectOption>> dataTypeFilterOpts = new Map<String, List<SelectOption>>(); Map<String, List<Reports.FilterOperator>> filterOperatorMap = Reports.ReportManager.getDataTypeFilterOperatorMap(); for (String dataType : filterOperatorMap.keySet()) { List<SelectOption> operators = new List<SelectOption>(); // Append _DATA to match ColumnDataType from ReportTypeColumn dataTypeFilterOpts.put(dataType.toUpperCase() + '_DATA', operators); for (Reports.FilterOperator fo : filterOperatorMap.get(dataType)) { operators.add(new SelectOption(fo.getName(), fo.getLabel())); } } return dataTypeFilterOpts; } |
Using the filters on the page
At this point all of the column information has been retrieved and the available operators have been constructed as SelectLists. The Visualforce page is fairly straightforward. It just iterates over the ColumnFilter List and gets the List of SelectionOptions from the Map.
1 2 3 4 5 6 7 8 9 10 |
<apex:repeat value="{!availableColumnFilters}" var="colFilter"> <div id="filterBox"> <apex:outputLabel >{!colFilter.label}</apex:outputLabel><br/> <apex:selectList value="{!colFilter.operator}" size="1" multiselect="false" style="width: 100px;"> <apex:selectOption itemLabel="--None--" itemValue=""/> <apex:selectOptions value="{!availableDataTypeFilterOperators[colFilter.dataType]}"/> </apex:selectList> <apex:inputText value="{!colFilter.value}"/> </div> </apex:repeat> |
The chart is displayed as well. At this point the chartFilter JSON is empty, so no additional filtering is performed. If the report doesn’t have a chart an error message will be displayed.
1 2 3 |
<apex:outputPanel layout="block" id="chart"> <analytics:reportChart reportId="{!reportId}" filter="{!chartFilter}"/> </apex:outputPanel> |
A command button that reRenders the chart output section is used. No controller action is needed as the only necessary logic is the execution of the getChartFilter method with the updated column filters from the form.
1 |
<apex:commandButton value="Get Chart with Filters" reRender="chart"/> |
The getChartFilter() method just serializes the selected ColumnFilters to get them into the format specified by the <analytics:reportChart> filter attribute’s documentation. It is interesting that it doesn’t matter that there are extra attributes as part of the generated JSON String. The filter attribute seems to just ignore them. Pretty cool.
1 2 3 4 5 6 7 8 9 10 11 12 13 |
public String getChartFilter() { return JSON.serialize(getSelectedFilters()); } private List<ColumnFilter> getSelectedFilters() { List<ColumnFilter> selectedFilters = new List<ColumnFilter>(); for (ColumnFilter cf : availableColumnFilters) { if (String.isNotBlank(cf.operator)) { selectedFilters.add(cf); } } return selectedFilters; } |
Existing Report Filters
Conclusions
The Analytics Apex Library is extremely useful for getting information about reports and I only touched on a small subset of features that are available. The Analytics REST API was introduced in the Winter ’14 release (API version 29) and I used its documentation (Analytics REST API documentation) as a reference for writing the code. The <analytics:reportChart> provides some very nice features for just a single line of Visualforce. Hopefully there’s more analytics components on the way.
The Visualforce page and controller code is available in this Gist.
Brilliant Article Peter
Thanks Harshit. Glad you liked it!
Before you add a report chart, check that the source report has a chart in Salesforce app.
Can you explain on how can we add this in our org as I am unable to see this option to add it.
The apex:reportChart component is generally available (GA) in Spring ’14, so your org must be Spring ’14 to use it.
To determine if a report has a chart you must manually check it on the report. You can do that by viewing the report in the report builder. I suppose if you really wanted to you could use the metadata API and query the Report metadata. Using the metadata API is probably unnecessary for almost all uses cases, because the component itself will fail gracefully if the report doesn’t have a chart by displaying an error message within the component.
Amit,
You have to open a case with Salesforce, if this it not available in your org, which I did for my org and they made it available in no-time. Far as in my developer account (trial and free) this was available an in Spring 14.
I encountered the same issue when I developed and report chart was missing, also I realized this doesn’t support in Salesforce 1. Check this post as well, where I created a report chart but dumping a chart on visualforce page , then with following the steps in documentation and steps like Peter explained here in this awesome post, I added to a page to mobile navigation to take it to Salesforce 1 layout as this tag supposedly wasn’t supporting responsive behavior
See the details here as well
http://www.oyecode.com/2014/02/analytics-reportchart-tag-of.html
–
Thanks for sharing your nice post and experience with getting it enabled, Harshit!
Just to be clear, it should be available without needing to contact Salesforce in Spring ’14 as stated in the release notes. But in a Winter ’14 org Salesforce would need to be contacted because in Winter ’14 it is only available through a limited pilot program.
Excellent Article Peter. Helped me immensly!
Is anyone else having an issue where your VF page now constantly refreshses about 3 times before finally showing the VF page and report?
I believe this behavior has something to do with cache’ing but is a deal breaker for me 🙁
Also, a glitch I’m also facing is I am unable to filter on custom fields, but standard fields are a go.
Any one else experiencing these behaviors?
Thanks Syed! Glad to help. 🙂
I cannot reproduce the constant refresh at this time, although I do recall seeing something like that before where it seemed to refresh/blink after my ajax load to as if I hadn’t made the report selection yet. But, I am not seeing that now.
The filter not working on custom fields is actually a known issue which you can track here: https://success.salesforce.com/issues_view?id=a1p30000000T3MPAA0.
Appreciate the reply. Hmm, it really doens’t behave that way for you? that’s odd.
Without pestering you too much, any ideas what could be wrong with this tag here which is embedded in a VF page? If not no biggie, but maybe you can spot something I’m missing:
I can’t understand why a chart refresh would refresh the entire page when using the tag in this way.
I am having the ‘load 3 times or so’ before really loading issue, too.
Ah, to be clear, I need to get the report column names via controller first- correct?
So
won’t work without getting the column names from a custom controller?
thanks
Hi ja,
You do not need to get them from the custom controller. You can also use the metadata to get the names during your design time and then when you actually code the chart just use the hard coded names.
Peter, thanks. I guess I am at a loss, finding an example of the simpler method, any version of:
to work – any column is always invalid. I need to query for them, I imagine, or am missing something, thanks
There is an known issue with custom columns not working in the filter JSON.
What I meant by getting them at design time was that you can get there names while you are designing the page (i.e., before any code is written) and then hard code them in the analytics report chart. For example, the following filters an Account report chart by Owner Name. The API name to use is in the JSON string is USERS.NAME.
<analytics:reportChart reportId="00Oe000000012ef" filter="[{column:'USERS.NAME',operator:'equals',value:'Peter Knolle'}]"/>
The analytics:reportChart component reference filter attribute documentation explains the exact steps.
Of course, it might make your program more useful to build up the JSON dynamically. In that case, you could still determine the column’s API name (e.g, ‘USERS.NAME’) at design time and hard code it in your Controller code and make the value part variable.
Ah, I see the custom filter is a known issue, so it looks like I won’t be able to test what I really want until Sp14 is out.
But I am trying to work off standard report, just filtering a Contact by Account Id for example, and can’t get any column to work. LastName isn’t a valid filterable column on my report is one result I am getting. I guess I don’t know how to make a describe request via the Analytics REST API
I added some information about getting metadata information in this post.
Thanks!
Thanks for the article…it helped me a lot. I have a query. I am using this tag “analytics:reportChart” multiple times in my page. The issue here is that, most of the times when I load my page, it keeps refreshing without loading the page completely. More to add I am getting an JS error in IE only saying that “aura has already been initialized”. Could you please help? Thanks.
You might want to open a support case with Salesforce. I see the refreshing in a Chrome browser on my Mac when the page initially loads. It seems like once the page is loading the cached version it doesn’t refresh.
Thanks…this issue seems to be intermittent. On one of my page, I have 4-5 such graphs and that page sometimes loads without any issue.
I have already raised a case with salesforce, yet there is no reply from them. Thanks again.
Good Article. Thanks
Sure. Glad you liked it! 🙂
Nice article. Thank you! I have a question. My apex controller is using “without sharing”. Yet, the analytics api calls seem to ignore this and run in the context of the current user. Is there a way around this and execute the report in system mode? I am using analytics api in lieu of SOQL. SOQL runs in system mode but the analytics api calls do not. Thanks!
Hello,
This is very good article.
I am using the same code which you have mentioned on github but after selecting the filter it is showing me an error like ‘Include filter attributes in the correct JSON format’.
Please help me with this issue. Thanks in advance.
Hi Sarika,
I am not sure. It sounds like the JSON is either invalid or not in the format expected by the component. You might get quicker help by posting somewhere to the Salesforce community such as the Salesforce Stack Exchange or Salesforce Developer Boards.
Bible for analytical report charts. Thanks a lot Peter Knolle!
Sarika, check if the Column API name and the Operator API name are correct in the filter.
Hi Sarika, Peter,
I had the same error: Include filter attributes in correct JSON format. Found out that the filter does not like additional attributes (datatype, label) anymore. So I had to get rid of those.
Patched it pretty simply:
private List getSelectedFilters() {
List selectedFilters = new List();
for (ColumnFilter cf : availableColumnFilters) {
if (String.isNotBlank(cf.operator)) {
selectedFilters.add(new Filter(cf));
}
}
return selectedFilters;
}
public class Filter {
public Filter (ColumnFilter cf) {
column = cf.column;
operator = cf.operator;
value = cf.value;
}
public String column {get;set;}
public String operator {get;set;}
public String value {get;set;}
}
hi Chris,
Struggling to fetch the summary report ,can you please share the working code
Hi Pahar,
I included my patch in my reply. Replace the existing getSelectedFilters() with my code… 🙂
Hi!
This is great, but I have found that while this works (w/the patch noted) on report types of say Activities with Accounts, it does not work on Reports that are Report Type Task and Events.
I find this odd especially since I am successfully filtering on Task fields when using the Activities with Accounts, so it is recognizing that these fields are either Task or Activity Custom Fields, but the Charts just do not who on Task and Event Types.
I get this error:
You can’t view the report chart because of an error. If the problem continues, contact salesforce.com Customer Support with as much information as you have, including the error code {0}, so that we can try to reproduce and fix the problem.
Can you tell me why?
Thanks – this is still very awesome.
Jake
request your help on restricting filters required, for example I want only Name and Stage filter, where should I modify
Please help
Good post dear
anyone can easily learn through this post.
thanks
Excellent article. Two questions:
1) Is it possible to have OR criteria between filters
2) in my current, very simple proto type, I am just getting the spinning wheel of loading death. Any ideas of what might cause that.
Hi Peter,
Very much detailed post. Thanks for this. I have come across 2 major issues for which I have not found solution:
1. Passing date in the filters – after formatting/parsing the date, when I click on the report chart, in the URL I can see the date converted to mm/dd/yyyy hh:mm:ss always. Not able to find proper way to pass the date.
2. Opening the report in new tab. When I click on report chart it opens in the existing tab. I want it to be opened in new tab. I have already tried and onclick() methods, but no luck.
Please let me know.
After chosing the filters, it throws null pointer exception. Any advice on that?
Actually I get the error, as soon as I click the ‘Get Report Filters’.
ERROR: ‘Attempt to de-reference a null object ‘
Hi, I am trying to create a SOQL query based on report filters but I am not getting correct API name for some fields to be used directly into SOQL. For example Lead Source is displayed as Lead_Source in XML response whereas correct API name is LeadSource (as expected in SOQL).