Salesforce Developer Evangelist alumnus Ron Hess asked me this question a few days ago: “I love the new Analytics API! I want to pull the data into a Visualforce Page (or Apex Class). Can I use ForceTK to do this? Can I access the Analytics REST API using the proxy?“. That’s a great question, but before I go ahead and explain the answer, I’d better explain the question, for folks that might be scratching their heads at the mention of ForceTK and proxies.

When we released the original Force.com REST API, back in Spring ’11, one of the most frequently asked questions was ‘Can I call the REST API from a Visualforce Page?”. At the time, the REST API was not exposed on the Visualforce servers (c.instance.visual.force.com), and, due to JavaScript’s Same Origin Policy, it was impossible to call the REST API endpoint on instance.salesforce.com directly. I realized that the Ajax Proxy, created to allow JavaScript on Visualforce pages to call out to third party web services, such as Google, would also allow JavaScript on Visualforce pages to call in to the Force.com REST API. I wrote a little library, the Force.com JavaScript REST Toolkit, or ForceTK for short, enhanced it a couple of times to support JavaScript served from web servers outside the Salesforce Platform, and hybrid apps on PhoneGap, and all was good.

ForceTK’s main job is to wrap the REST API with a JavaScript interface – you can call methods such as create() and upsert() rather than dealing directly with XmlHttpRequest or jQuery.ajax(), but it also exposes its own low-level ForceTK.ajax() method, which accepts a partial URL path, HTTP method, payload, etc, but saves you from having to deal with OAuth tokens. ForceTK.ajax() is the key to calling REST APIs such as Analytics without any heavy lifting.

The Analytics REST API itself is new in the Winter ’14 release, and it’s worth mentioning that the longer term plan is to provide similar functionality in an Apex API, in the same way that the Chatter REST API preceded Chatter in Apex. Having said that, though, right now, the Analytics REST API is the only supported mechanism for loading Report data, so we’ll go ahead and work with it.

Now we’re all on the same page, let’s look at how you’d use the Analytics API on a Visualforce Page to pull Report data from Salesforce so you can show a custom visualization using Google Charts. I’ll walk through the code highlights in this blog entry, but you can grab the entire file here.

Code Walkthrough

After defining some CSS, we include the jQuery, ForceTK and Google API Loader (JSAPI) JavaScript libraries. We’ll use JSAPI to dynamically load the Google Charts library.

<apex:includeScript value="{!$Resource.jquery}" />
<apex:includeScript value="{!$Resource.forcetk}" />
<apex:includeScript value="https://www.google.com/jsapi" />

Skipping down to the $(document).ready() handler, the first thing we do is read a Report ID from a query parameter. Passing reportId to the page makes it much more versatile.

// When the DOM is ready...
$(document).ready(function() {
    // Pass in the report ID like so:
    // https://c.prerelna1.visual.pre.force.com/apex/AnalyticsDemo?reportId=00Ox0000000fX7XEAU
    var reportId = '{!$CurrentPage.parameters.reportId}';

If we successfully found a Report ID, we initialize ForceTK and define a variable to hold the Report data:

if (reportId) {
    // Get an instance of the REST API client and set the session ID
    var client = new forcetk.Client();
    client.setSessionToken('{!$Api.Session_ID}');

    // We'll keep the report data around for the life of the page
    var report = null;

Now we can go ahead and call ForceTK.ajax() to run the report synchronously. Notice that the URL path is relative to /services/data:

client.ajax("/v29.0/analytics/reports/"+reportId+"?includeDetails=true", function(response){

In the anonymous callback, the first thing we do is save the report data in the report variable, then write it to a <pre> element on the page. You wouldn’t do this in production, but it’s essential for development – you really want to be able to see the raw report data.

// Save the report data
report = response;

// For debugging
$("#output").text(JSON.stringify(report, null, ' '));

In this example, I’m using one of the standard example reports included in Developer Edition: ‘Opportunities by Type’. In the regular report view, this tells us the total amount, expected revenue and age of our Opportunities, broken out by their Type:

What I want to do is show a Pie Chart of a single column of that data, and let the user switch between columns via a drop-down list, like this:

So the first thing we need to do is populate that drop-down from the list of aggregate columns in the report metadata. The Analytics API documentation discusses the Report data format in some detail, suffice to say here that I’m iterating through the aggregate columns in the report, extracting the column labels:

// Grab the aggregate metadata and load it into a <select>
$.each(report.reportMetadata.aggregates, function(index, agg) {
    $("#selectAgg").append('<option value="'+index+'">'+
        report.reportExtendedMetadata.aggregateColumnInfo[agg].label+
    '</option>');
});

Now we can render the pie chart. We break this out into its own function, renderPieChart(), so we can call it both after the data loads, and when the user selects an aggregate column in the drop-down. After setting a default column, renderPieChart() loads the Google Chart library:

google.load("visualization", "1", {packages:["corechart"], 'callback' : function() {

Once the library has loaded, we can extract labels for the grouping and aggregate columns. The first entry in the data array that we pass to the Google Charts library has to contain metadata, rather than the data itself, even though it’s not shown on the pie chart. Still, we populate the array with the actual labels, rather than dummy strings, in case we want to reuse the code in a different context.

// Metadata for the aggregate column
var columnInfo = report.reportExtendedMetadata.aggregateColumnInfo[report.reportMetadata.aggregates[column]];

// Legends (not shown on pie chart)
var dataArray = [[
    report.reportExtendedMetadata.groupingColumnInfo[report.reportMetadata.groupingsDown[0].name].label,
    columnInfo.label
]];

Now we can iterate through the report summary data and create the DataTable object that the Charts API uses:

$.each(report.groupingsDown.groupings, function(index, grouping) {
    dataArray.push([grouping.label, report.factMap[index.toString()+"!T"].aggregates[column].value]);
});

var data = google.visualization.arrayToDataTable(dataArray);

We want to label the chart according to the data being shown, and format the data appropriately when we’re showing currency fields:

var options = {
    title: report.attributes.reportName + ": " + columnInfo.label,
    is3D: true,
};

if (columnInfo.dataType === "currency") {
    var formatter = new google.visualization.NumberFormat({
        prefix: '$' // This is just sample code - should really determine the correct currency symbol!
    });
    formatter.format(data, 1);
}

Now, at last, we can create the chart, and pass in the options and data:

// Create and draw the chart
var chart = new google.visualization.PieChart(document.getElementById('piechart_3d'));
chart.draw(data, options);

And the finished result. Again, you wouldn’t show the raw JSON report data to users, but it’s essential during development for understanding the report structure:

This example showed JavaScript calling the API from a Visualforce page, but the great thing about ForceTK is that you can use exactly the same code from a page hosted anywhere – even in a hybrid mobile app – by just tweaking the initial call to pass a session ID to the ForceTK client.

The Salesforce Analytics REST API unlocks analytics data for developers, and the ability to call the API from JavaScript on Visualforce pages and elsewhere enables a whole new world of dynamic apps leveraging the Salesforce Platform. What are you going to build with the Analytics API? Let us know in the comments!

tagged , , , Bookmark the permalink. Trackbacks are closed, but you can post a comment.
  • Ron Hess

    Very nice ! I think there are good reasons to build a few helper functions ( jquery plugin ?) around this API that decode things like the fact map, and build filters for reports. I’m excited to explore this API for document generation also !

  • ram_sj

    Cool feature! going to save some good amount of time instead of writing custom aggregations by leveraging reports to do that.

    • Mark Epperson

      If you’re using the Google jsapi already, then the google.visualization.data.group() method is probably an easier way to go. I wanted a pie chart on the Account record that would show the customer’s preferred method of communication.. and I wanted it done QnD… so I used the tag inside of the addRows() method to load the individual activityhistory objects:

      data.addRows([

      ['{!activity.Type__c}',1],

      ]);

      That alone would generate a Pie Chart with 1 slice for every record.. not what I wanted, so I ran it through the google.visualization.data.group() method to aggregate the data for me:

      var data_aggregate = new google.visualization.data.group(
      data,
      [0],
      [{'column': 1, 'aggregation': google.visualization.data.sum, 'type': 'number'}]
      );

      Passing the data_aggregate array instead of the data array yields the aggregate data I wanted… with no custom controller.

      The VF Page attached can be displayed on any Account’s Detail Page and will show a breakdown of the types of communications… granted you can do this with a Report Chart… but IMO the Report Charts take up too much GUI space, and the VF Pages work on the Salesforce 1 Mobile Platform too!

      The same type of thing can also be done with the new tags using javascript arrays, but I haven’t figured out how to aggregate data without the Google jsapi, and if I’m going to load the jsapi I’m more likely to just go ahead and use it. It still has more options than Visual Force Charting… at least, for now!

      • ram_sj

        I think your right if you want to group by/roll up some columns in one table, but if you want to do it for join operation, currently not sure you can do it with google data table, these aggregation need to happen either in javascript before you build datatable or in serverside.

  • Cory Cowgill

    Aside from being awesome in and of itself, this is a great replacement for all the Javascript Hacks that are in the wild. Previously developers created brittle, hard coded Javascript code that would invoke the Report page and pass parameters to the filters since they couldn’t access report data programmatically. This should solve those use cases! Awesome.

  • mailtoharshit

    Wooh, that’s nice

  • Abhimanyu Raut

    This is exactly my type, what I wanted. AWESOMELY AWESOME…thanks……

  • ram_sj
  • Sunil

    Hi
    I was successful in displaying single gauge chart for one report ID using above suggested process, but
    how to display multiple Gauge Charts (using this same structure) on the same page with dynamic min and max values?

  • [email protected]

    I spent about an hour struggling how to get POST requests to work correctly for the analytics API. First off the salesforce documentation is not quite correct, in that it does not mention the fields need to be a child of reportMetadata in the request, but in their post example they clearly are. Next, you can’t simply send the describe results, as the other fields will give you a bad request error. Finally, you have to force the data into a JSON format in you post. I finally ended up with code that looks like:

    $.ajax(‘/services/data/v29.0/analytics/reports/{!ReportId}/describe’,{
    beforeSend: function(xhr) { xhr.setRequestHeader(‘Authorization’, ‘Bearer {!$Api.Session_ID}’);},
    success: function (describeResponse) {
    updateFilters(describeResponse.reportMetadata.reportFilters);
    $.ajax({
    type:’POST’,
    url:’/services/data/v29.0/analytics/reports/{!ReportId}’,
    data: JSON.stringify({ reportMetadata: describeResponse.reportMetadata }),
    contentType: “application/json; charset=utf-8″,
    beforeSend: function(xhr) {xhr.setRequestHeader(‘Authorization’, ‘Bearer {!$Api.Session_ID}’);},
    success: function(response) {
    processResponse(response) … process results …
    }
    });
    }
    });

    Obviously updateFilters() and processResponse() functions are unique to my needs.

  • Mohammad Jashim Uddin

    Hello: great post and saved me lot of time.

    Just wondering, Is there any working code sample to render data in a Table instead of Pie Chart?. I need to load the data coming from Salesforce dynamically (similar to what you did for pie chart)

    google.load(“visualization”, “1″, {packages:["table"], ‘callback’ : function()
    < do something here >
    var table = new google.visualization.Table(document.getElementById(‘table_div’));
    table.draw(data, {showRowNumber: true});

    I was able to render the column header by doing this:

    var data = new google.visualization.DataTable();
    for (var i=0;i < report.reportMetadata.detailColumns.length;i++)
    {
    var columnInfo = report.reportExtendedMetadata.detailColumnInfo[report.reportMetadata.detailColumns[i]];
    data.addColumn(‘string’, columnInfo.label);
    }

    Need help with loading the Cell data. Thank you in advance.