Tabular report data via analytics API

Question

I was wondering if I could run my tabular report via API and create a csv file by traversing the result. I had a look at the examples and wrote a piece of code but.... Unfortunately I have no idea how to get the rows data. My report has no groupings
results.getGroupingsDown() returns Reports.Dimension[groupings=null]

I can see in the API classes like ReportDetailRow but no example how to get an instance from report result. Could anybody help?
thanks

Answer 1

You need to make sure that you run the report with the boolean includeDetails set to true if you want the row data. For synchronous execution use runReport and for asynchronous use runAsyncReport.

After that you can get the ReportResults from the ReportInstance if you are running asynchronously or directly from the return of the runReport if running synchronously. Once you have the ReportResults you can get the information as follows.

To get the column headers

From the ReportResults you iterate over the ReportMetadata’s detailColumnNames and use the name as a key into the ReportResults’ ReportExtendedMetadata’s detailColumnInfo:

for (String colName : reportResults.getReportMetadata().getDetailColumns()) {
    System.debug('header value:  ' + reportResults.getReportExtendedMetadata().getDetailColumnInfo().get(colName).getLabel() );
}

Or, if you are accessing the ReportResults directly in your Visualforce page:

<apex:repeat value="{!reportResults.reportMetadata.detailColumns}" var="colName">
    <apex:outputText value="{!reportResults.reportExtendedMetadata.detailColumnInfo[colName].label}”/>,
</apex:repeat>

To get the actual row data

You need to iterate over the factMap’s rows. Then, for each row iterate over each cell, outputting the label. Note that the key to the factMap is T!T in the case of a tabular report. There aren’t any aggregations. Note that if you do not run the report with the includeDetails equal to true, you will not get any results for the rows. Additionally, you must downcast to a Reports.ReportFactWithDetails because the getRows() method is on that class and not on the super class, ReportFact.

    Reports.ReportFactWithDetails factWithDetails =
           (Reports.ReportFactWithDetails) reportResults.getFactMap().get('T!T');
for (Reports.ReportDetailRow row : factWithDetails.getRows()) {
    for (Reports.ReportDataCell cell : row.getDataCells()) {
        System.debug('The cell data: ' + cell.getLabel());
    }
}  

Or, if you are accessing the ReportResults directly in Visualforce it would be the following. Note that it is not necessary to downcast in the Visualforce.

  <apex:repeat value="{!reportResults.factMap['T!T'].rows}" var="row">
      <apex:repeat value="{!row.dataCells}" var="cell">
        <apex:outputText value="{!cell.label}”/>,
      </apex:repeat>
      <br />
  </apex:repeat>

Also, if you need to override the filters or add filters see the Apex Developer's Guide section 'Filter Reports' . There are some limitations to what can be filtered. You may think that you are overriding all of the filters, but not actually be. For example, the standard filters cannot be overridden/removed.

Using the API, you can filter with up to 20 custom field filters and add filter logic (such as AND and OR). But standard filters (such as range), filtering by row limit, and cross filters are unavailable.

It's not a CSV, but close: I have a working example of outputting a tabular report in an HTML table on a VF page in this article.