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 #

  • The Reports API provides only the raw data for the report — it does not provide the visualized versions (such as grouped or pivoted data).
  • You are not limited to just one call on a page. You can include as many calls to the API as you need on the same page.

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.

  • The columns key contains an array of pairs, one for each column of the report, indicating the label and data type of the column. Data types can be any of stringdatedatetimeintegercurrencypicklist or reference. The length of the columns array is the number of columns in the report.
  • The rows key contains the data records, as an array of arrays. The length of the rows array is the number of records (rows) produced by the report. Each element of this array is a single row of the report, and is itself represented as an array of simple values, whose length is the number of columns in the report. Another way of looking at this data structure is to say that the rows key contains the matrix of data records for the report. The data types of the values in the cells of this matrix correspond to the data types of the columns of the report. For example, a Date or DateTime column produces a JavaScript Date value, and a Currency column produces a JavaScript number.

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" >

<script type="text/javascript">
  var Apsona = {
      "user": {
          "name": "{!$User.FirstName} {!$User.LastName}",
          "loginId": "{!$User.Username}",
          "email": "{!$User.Email}",
          "id": "{!$User.Id}",
          "orgId": "{!$Organization.Id}",
          "company": "{!$Organization.Name}"
      }
  };
</script>

<!-- 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.min.js"></script>

<!-- Set up the session ID (must be done AFTER including apsona.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>

Powered by BetterDocs