Accessing Report data using "Salesforce1 Reporting API via Apex"

This post explains you a basic code structure for fetching data from an existing report and display it in visualforce page.

Report Screenshot





Visualforce Page Screenshot


Visualforce Page

<apex:page controller="OpportunityReportUsingAnalyticsAPI" readOnly="true">
<apex:pageMessages/>
<apex:form>  
  <apex:outputPanel id="reportResults" layout="block" style="overflow: auto;width:100%;height:435px;">
    <apex:outputText value="Fetching results. Please wait!" rendered="{!IsReportRunning}"/>
    <apex:pageBlock rendered="{!NOT(IsReportRunning)}" title="Opportunity Report.....">
    <table style="width: 100%;">
        <thead>
            <apex:variable value="1" var="count"/>
            <apex:repeat value="{!reportResults.reportMetadata.detailColumns}" var="colName">
            <th><apex:outputText value="{!reportResults.reportExtendedMetadata.detailColumnInfo[colName].label}"/></th>
            </apex:repeat>
        </thead>
        <tbody>
                <apex:repeat value="{!reportResults.factMap['T!T'].rows}" var="row">
                <tr>
                    <apex:repeat value="{!row.dataCells}" var="cell">                            
                        <td><apex:outputText value="{!cell.label}"/></td>
                    </apex:repeat>
                </tr>
                </apex:repeat>
        </tbody>
    </table>
    </apex:pageBlock>
  </apex:outputPanel>
<apex:actionPoller id="poller" reRender="reportResults" interval="5" action="{!checkReportRunningStatus}" enabled="{!IsReportRunning}" />
</apex:form>
</apex:page>

Apex Controller

public with sharing class OpportunityReportUsingAnalyticsAPI
{ 
    // get reportId from URL
    public Id reportId=ApexPages.currentPage().getParameters().get('reportId');
    public Id instanceId { get; set; }
    
    //flag to indicate whether report is still running or not
    public Boolean IsReportRunning { get; set; }
    
    // stores reportresults
    private transient Reports.ReportResults reportResults;          
    
    // constructor
    public OpportunityReportUsingAnalyticsAPI() 
    {                   
        if(reportId!=null)
        {             
            Reports.ReportInstance reportInstance = Reports.ReportManager.runAsyncReport(reportId,true);
            instanceId = reportInstance.getId();
            checkReportRunningStatus();
        }
        else
        {
            ApexPages.addMessage(new ApexPages.Message(ApexPages.Severity.FATAL,'Invalid URL, reportId is missing'));
        }
    }
    
    // method to get and populate reportresults
    public PageReference checkReportRunningStatus() 
    {
        Reports.ReportInstance reportInstance = Reports.ReportManager.getReportInstance(instanceId);      
        IsReportRunning = reportInstance.getStatus() == 'Running' || reportInstance.getStatus() == 'New';
        if (!IsReportRunning) 
        {
            reportResults = reportInstance.getReportResults();
        }        
        return null;
    }

    // returns reportresults
    public Reports.ReportResults getReportResults() 
    {
        return reportResults;
    }

}

This code structure remains the same for any apex implementing this API like filtering report dynamically based on filters applied in visual force page.

Salesforce1 Reporting API via Apex Introduction

This post explains you the concept of acessing salesforce report data using "Salesforce1 Reporting API via Apex".

This API provides options to integrate report data into any web or mobile application, inside or outside the Salesforce.com platform.

For example, if we have a report displaying top-performing sales reps for each quarter, then we can use this API to trigger a Chatter post with a snapshot of top-performing reps each quarter.

The Salesforce1 Reporting API via Apex revolutionizes the way that you access and visualize your data. You can:

• Integrate report data into custom objects (get data from Report and insert into custom object similiar to Analytic Snapshots)
• Integrate report data into rich visualizations to animate the data (additional plugins/visualizations can be used to display the same report data in a different format than with native report UI)
• Build custom dashboards (for ex. google charts can be used which will again function to salesforce report data, exactly similiar to salesforce native dashboards)
• Automate reporting tasks (schedule a batch class to run the report using this api and send it to recipients outside your salesforce org which native salesforce report scheduling cannot support)

At a high level, the API resources enable you to query and filter report data. You can:

• Run tabular, summary, or matrix reports synchronously or asynchronously.
• Filter for specific data on the fly (use the existing report as such by adding filter criteria at API level to pull the results)
• Query report data and metadata (provides options to access the report metadata

Requirements and Limitations:

The following restrictions apply to the Salesforce1 Reporting API, in addition to general API limits.

• Cross filters, standard report filters, and filtering by row limit are unavailable when filtering data.
• Historical trend reports are only supported for matrix reports.
• The API can process only reports that contain up to 100 fields selected as columns.
• A list of up to 200 recently viewed reports can be returned.
• Your organization can request up to 500 synchronous report runs per hour.
• The API supports up to 20 synchronous report run requests at a time.
• A list of up to 2,000 instances of a report that was run asynchronously can be returned.
• The API supports up to 200 requests at a time to get results of asynchronous report runs.
• Your organization can request up to 1,200 asynchronous requests per hour.
• Asynchronous report run results are available within a 24-hour rolling period.
• The API returns up to the first 2,000 report rows. You can narrow results using filters.
• You can add up to 20 custom field filters when you run a report.

In addition, the following restrictions apply to the Salesforce1 Reporting API via Apex.

• Asynchronous report calls are not allowed in batch Apex.
• Report calls are not allowed in Apex triggers.
• There is no Apex method to list recently run reports.
• The number of report rows processed during a synchronous report run count towards the governor limit that restricts the total number of rows retrieved by SOQL queries to 50,000 rows per transaction. This limit is not imposed when reports are run asynchronously.
• In Apex tests, report runs always ignore the SeeAllData annotation, regardless of whether the annotation is set to true or false. This means that report results will include pre-existing data that the test didn’t create. There is no way to disable the SeeAllData annotation for a report execution. To limit results, use a filter on the report.
• In Apex tests, asynchronous report runs will execute only after the test is stopped using the Test.stopTest method.

Running Reports using this API:

We can run a report synchronously or asynchronously through the Salesforce1 Reporting API via Apex.

Reports can be run with or without details and can be filtered by setting report metadata. When you run a report, the API returns data for the same number of records that are available when the report is run in the Salesforce user interface.

Syntax for running a report:

i)  Run a Report Synchronously

// Get Report Id using a SOQL or pass it via URL & access using getParameters method 

List <Report> reportList = [SELECT Id,DeveloperName FROM Report where
    DeveloperName = 'Closed_Sales_This_Quarter'];
String reportId = (String)reportList.get(0).get('Id');

// Run the report synchronously
Reports.ReportResults results = Reports.ReportManager.runReport(reportId, true);
System.debug('Synchronous results: ' + results);

ii) Run a Report Asynchronously 

// Get Report Id using a SOQL or pass it via URL & access it using getParameters method 

List <Report> reportList = [SELECT Id,DeveloperName FROM Report where
    DeveloperName = 'Closed_Sales_This_Quarter'];
String reportId = (String)reportList.get(0).get('Id');

// Run the report asynchrounously
Reports.ReportInstance instance = Reports.ReportManager.runAsyncReport(reportId, true);
System.debug('Asynchronous instance: ' + instance); 

Deciding Synchronous Vs Asynchronous Running: 

Synchronous run:

Go for Synchronous run only if you expect it to finish running quickly. 
For example, opportunities related to a specific account, contacts related to a specific account as most of the  organizations have these data always handful or less in number

Asynchronous run:

Go for Asynchronous run if,
- the report is expected to have large data and will take more time to run.
- Long-running reports have a lower risk of reaching the timeout limit when they are run asynchronously.
- The two-minute overall Salesforce API timeout limit doesn’t apply to asynchronous runs.
- The Salesforce1 Reporting API can handle a higher number of asynchronous run requests at a time.
- Because the results of an asynchronously run report are stored for a 24-hour rolling period, they’re available for recurring access.