Contact us Blog

Accessing Apsona reports via API

Apsona includes powerful cross-object filtering and reporting features with its single-step and multi-step reports. These reports are rendered directly within the Apsona UI, and can also set up as stand-alone Visualforce pages.

There are, however, situations where you would want to obtain just the raw data from an Apsona report, via a programming interface, and render that data according to your needs, without relying on the Apsona rendering. For example, suppose you have available an Apsona report that includes some complex filtering logic for retrieving salesforce data, and you need a customized rendering of this data where you want full control over where the data items appear, how they are laid out, colors, fonts, logos, and the like. This need would be met if you can access Apsona reports via an API call, extract its data, and render it according to your needs.

Apsona's Reports API provides for precisely this need. With this API, you can create Visualforce pages that render data produced by Apsona reports, with the layout and formatting that you control. You can also create pages in which you aggregate Apsona report data with other third-party data sources. The Apsona Reports API is available via simple JavaScript calls to retrieve data. With the availability of this API, you only need JavaScript, HTML and CSS skills to set up your customized renderings — no need for Apex or other Salesforce coding skills.

Caveats and notes

Technical outline

To use these API calls in a Visualforce page, you will need to include in the page a script tag to retrieve the Apsona asset, and a setting of the session ID to ensure that only licensed, logged-in users can use the page. A complete example Visualforce page is shown later, to illustrate these requirements.

Single-step reports

The JavaScript call to run a single step report looks like this:
apsona.runReport ('My ReportName', null, function (error, reportData) {
    if (error) {
        // Handle the error here, e.g.:
        alert ("Report run failed: " + error);
        return;
    }
    // Access the report data via the reportData parameter, which looks like this:
    //  reportData = {
    //      columns: [
    //          {dataType: string, label: string}, {dataType: string, label: string}, ...
    //      ],
    //      rows: [
    //          [ row0value0, row0value1, ... ],
    //          [ row1value0, row1value1, ... ],
    //          ...
    //      ]
    //  }
});
The apsona.runReport method requires three parameters: The name or ID of the report to run, an options object (which currently is required to be null), and a callback function. The method runs the report with the specified name or ID, and calls the callback with the results of the run.

The callback function is given two parameters: An error value and the report data. If an error of any sort occurred in the report run, the error parameter will contain an indication of the error. If the report ran successfully, the error parameter will be null, and the report data parameter contains the data produced by the report.

The report data parameter is an object with two keys, columns and rows.

Multi-step reports

The JavaScript call to run a multi-step report is very similar:
apsona.runMultistepReport ('My Multi-step Report Name', null, function (error, reportData) {
    if (error) {
        alert ("Report run failed: " + error);
        return;
    }
    // Access the report data via the reportData parameter, as with a single-step report above.
});

A complete example

To illustrate the idea, here is a simple Visualforce page that runs an existing Apsona report and renders it using the Handlebars library. (You are certainly not limited to using handlebars — there is a plethora of templating and widget libraries available, and you could use any of them.)

To try this code in your Salesforce org, simply copy the code below, paste it into a new Visualforce page in your org, and change the name of the report in the method call. You can then access the Visualforce page directly. For example, if you named the page ReportTestPage, you can access it via a URL like https://na12.salesforce.com/apex/ReportTestPage, assuming that your Salesforce instance is at https://na12.salesforce.com.
<apex:page sidebar="false" showHeader="false" >
<!-- Include the necessary scripts -->
<script src="https://cdnjs.cloudflare.com/ajax/libs/handlebars.js/4.0.6/handlebars.min.js"></script>
<script src="https://service.apsona.com/sfdc/apsona_embed.min.js"></script>

<!-- Set up the session ID (must be done AFTER including apsona_embed.min.js) -->
<script type="text/javascript">
sforce.connection.sessionId = "{!$Api.Session_ID}";
</script>

<!-- Set up Handlebars, the templating library -->
<script>
Handlebars.registerHelper ("format", function (object) {
    var txt = object instanceof Date ? object.format ("MMM dd, yyyy") : object;
    // Use Apsona's formatter method --^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    // see http://apsona.com/pages/sfdc/doc/js-tut.html
    return txt;
});
</script>

<!-- Rendering template for handlebars -->
<script type="text/template" id="layout">
  <div class="report">
    <h1>{{reportName}}</h1>
    <table class="report_tbl">
      <tr>
        {{#each columns}}
          <th>{{label}}</th>
        {{/each}}
      </tr>
      {{#each rows}}
      <tr>
        {{#each this}}
        <td>{{format this}}</td>
        {{/each}}
      </tr>
      {{/each}}
    </table>
  </div>
</script>

<!-- Set up styles -->
<style>
 .report_tbl td,.report_tbl th { border: 1px solid #ddd; padding: 4px; }
 .report_tbl { border-collapse: collapse; }
 .report { padding: 20px;}
</style>

<!-- Run the report and render it -->
<script>
var reportTemplate = Handlebars.compile (document.getElementById ("layout").innerHTML);
apsona.runMultistepReport ('oppties and owners', null, function (error, data) {
    if (error) {
        alert ("Report run failed: " + error);
        return;
    }
    document.getElementById ("report_rendered").innerHTML = reportTemplate (data);
});
</script>
<div id="report_rendered">
  <div style="padding: 20px; font-size: 150%;">Working...</div>
</div>
</apex:page>