Apex Analytics API and Report Chart Component

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

  1. First, get report metadata from a describe request:Reports.ReportManager.describeReport(00OD0000001ZbNHMA0)
  2. 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.

Visualforce page
Visualforce page with <analytics:reportChart> component

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.

 

Available Reports

The code to display the available reports in the retrieveAvailableReports() method is just a simple query on the Report object.

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.

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.

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.

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.

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.

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.

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.

Existing Report Filters

The existing filters on the report cannot be ignored.  All of the filters added in the filter attribute are ANDed with them.  For example, if you have specified a Day of Week = Thursday filter on an actual report and you specify a Day of Week = Wednesday filter on the component the chart will be rendered with no data and a message stating “The report returned no results” because the original report only allows Thursdays on it.  There are methods to get the existing filters and filter logic for reports in the API.  There isn’t a way to use filter logic with the component (I hacktempted adding it to the JSON after the columns array).  To be clear, the documentation for the filter attribute on the component doesn’t specify that there should be.

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.

39 thoughts on “Apex Analytics API and Report Chart Component

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  11. request your help on restricting filters required, for example I want only Name and Stage filter, where should I modify
    Please help

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

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

Leave a Reply

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