5

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

12

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.

  • Thank you Peter I saw your 2 blog post and I found them very useful. I got as far as displaying metadata on my own. I'm trying to save the loop with getRows method and I'm getting an error – user682217 Sep 16 '14 at 8:26
  • @user682217 - Updated the answer to include bit about downcasting – Peter Knolle Sep 16 '14 at 9:34
  • sorry :-) my comment was too long It didn't get posted when I wrote an update. I run the manager with include details=true and I made sure it shows some data when I run it via browser. Yet in the code I get nulls Reports.ReportFactWithDetails[aggregates(Reports.SummaryValue[label=0, value=0]), key=T!T, rows=null]} Is it because I try to run it synchronously? – user682217 Sep 16 '14 at 10:31
  • Did you try to use the rows or just debug it out? Perhaps the rows property is lazy loaded and not set until getRows() is called. – Peter Knolle Sep 16 '14 at 10:45
  • thank you for your interest. Yes I did. I put my debug between the loops. The inner one prints nothing. I think the filters & dates are the problem. <br/> Looks like the ReportFilter works the same way the filters in drag and drop section do. (I set it to -124 days ago + 7 days) My report has a date range set in the Time Frame section. (yesterday only). I get logical AND when my code runs and so no results... hmm I'm not sure how to approach it now – user682217 Sep 16 '14 at 15:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.