How to get all records from Report using JSforce NodeJS

Question

I am trying to get all records from a specific Report. I am using NodeJS and the library JSforce.

I have tried using the following code:

var report = conn.analytics.report(reportId);
report.execute({ details: true }...)

But I'm only able to fetch 2000 records(salesforce limitation)

Answer 1

This is a documented limitation of the Analytics API:

The API returns up to the first 2,000 report rows. You can narrow results using filters.

You could approach this by segmenting your data using narrower filters and running multiple reports to aggregate the total data set.

Answer 2

Below are some methods to get all records via soql, but not through analytics api.

Digging around in the docs for reporting execution shows that there are no values such as "autofetch" or "queryall" that could be used here.

You have a few options still:

1. Ask the author on GitHub (maybe we missed something or theres some upcoming feature that could help)
2. Translate your report metadata back into a query and execute the query using the below code.
3. Just write a soql query & use that instead.

No matter what if you want over 2k records you'll need to run a soql query. Guessing the reporting page does some kind of translating as well to get the full 2k records.

---

This example shows how to query "all" records from a table without using the autofetch option provided by the developer.

describe("query big tables without autoFetch", function() {
    it("should scan records in one query fetch", function(done) {
      var records = [];
      var query = conn.query("SELECT Id, Name FROM " + (config.bigTable || 'Account'));
      query.on('record', function(record, i, cnt){
        records.push(record);
      });
      query.on('end', function() {
        callback(null, { query : query, records : records });
      });
      query.on('error', function(err) {
        callback(err);
      });
      query.run({ autoFetch : false });
      var callback = function(err, result) {
        if (err) { throw err; }
        assert.ok(result.query.totalFetched === result.records.length);
        assert.ok(result.query.totalSize > 2000 ?
                  result.query.totalFetched === 2000 :
                  result.query.totalFetched === result.query.totalSize
        );
      }.check(done);
    });
  });

---

This example shows how to use the autofetch option provided by the developer to obtain all rows from a table.

describe("query big tables with autoFetch", function() {
    it("should scan records up to maxFetch num", function(done) {
      var records = [];
      var query = conn.query("SELECT Id, Name FROM " + (config.bigTable || 'Account'));
      query.on('record', function(record) {
             records.push(record);
           })
           .on('error', function(err) {
             callback(err);
           })
           .on('end', function() {
             callback(null, { query : query, records : records });
           })
           .run({ autoFetch : true, maxFetch : 5000 });
      var callback = function(err, result) {
        if (err) { throw err; }
        assert.ok(result.query.totalFetched === result.records.length);
        assert.ok(result.query.totalSize > 5000 ?
                  result.query.totalFetched === 5000 :
                  result.query.totalFetched === result.query.totalSize
        );
      }.check(done);
    });
  });

---

Both examples are taken from the repo's test classes, found here.