[SalesForce] How to get a report to return more than 2,000 rows using the “ReportResults” class in the “Reports” namespace

Using cues from the sample here:

and the tutorial here:

I wrote some code to go through a report in which the first column is always the id of the contact, and create campaign members based off of those values.

This works fine for reports that contain less than 2,000 results. But I can't get it to return more than 2,000 results.

I tried using the getAllData() method from the ReportResults class, but it just returns false… I'm not sure why.

Can anyone give me a pointer on how to run the report programmatically so that it returns all data?

Here's the anonymous code I'm using.

//Add variables here:
        id campaignId ='701E0000000blYG';
        String userSetStatus='Sent';
        id reportId = '00OE0000003BncO';

// Run a report synchronously
        Reports.reportResults ActiveISOContacts = Reports.ReportManager.runReport(reportId, true);
        ActiveContacts.getAllData();
        System.debug('Value of ActiveContacts.getAllData()' + ActiveContacts.getAllData());


        // Get the fact map from the report results
        Reports.ReportFactWithDetails factDetails = 
        (Reports.ReportFactWithDetails)ActiveContacts.getFactMap().get('T!T');

        //create a list of report rows and populate it with the result rows from fact map
        List<Reports.ReportDetailRow> reportRows = factDetails.getRows();
        System.debug('Value of reportRows.size()' +reportRows.size());

        //create a list of campaign memebers. To be populated in the for loop with contacts.
        List<CampaignMember> campaignmembers = new List<CampaignMember>();
        //loop through each report detail row and create a campaign member


        for(Reports.ReportDetailRow reportRow : reportRows){

            List<Reports.ReportDataCell> datacells = reportRow.getDataCells();
            Reports.ReportDataCell datacell = datacells[0];
            id conid = (id)datacell.getValue();
            CampaignMember campaignmember = new CampaignMember(CampaignId=campaignId,ContactId=conid,Status=userSetStatus);
            campaignmembers.add(campaignmember);

        }
        database.insert(campaignmembers,false);

Thanks!

Best Answer

The documentation is fairly explicit here:

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

You'll need to execute your report multiple times, using filters that you can guarantee will return fewer than 2000 rows (such as by using date ranges, report run #1 uses THIS_QUARTER, run #2 uses LAST_QUARTER, or you use first letter of name starts with A on run #1, then B on run #2)

Related Solutions

[SalesForce] How to decode fact map for matrix report using Analytics API

This guide explains how to decode the fact map: https://resources.docs.salesforce.com/sfdc/pdf/salesforce_analytics_rest_api.pdf

Basically, the 0!0 you're getting is just the results of the first subtotal of the first grouping, and each grouping has subresults that you have to process to get the underlying data. Here's the relevant section of the docs:

[SalesForce] SOQL: How to handle relationship query with more than 50,000 results

I assume that you already considered to use a for loop without assigning the query result to List. (The "For loop list batch size" will apply.)

However, let's improve the overall design:

The problem in detail

In apex every FROM statement counts as a separate SOQL query. Your query contains 3 FROM statements.

In a SOQL query with parent-child relationship sub-queries, each parent-child relationship counts as an additional query. - Understanding Execution Governors and Limits

Almost every salesforce limit applies only for one execution context. This means you can create a bunch of execution contexts and you will workaround limits.

The batchable interface works as follows:

The start method is called at the beginning of a batch Apex job. Use the start method to collect the records or objects to be passed to the interface method execute. This method returns either a Database.QueryLocator object or an Iterable that contains the records or objects being passed into the job. - Using Batch Apex

And:

Each execution of a batch Apex job is considered a discrete transaction. For example, a batch Apex job that contains 1,000 records and is executed without the optional scope parameter from Database.executeBatch is considered five transactions of 200 records each. - Using Batch Apex

Remember: Every call of execute() creates a new execution context. Therefore you are able to query more records. However, the following solution reduces the number of query per execution context to 1. And lowers the number of queried records.

Query Enhancements

1. Remove unnecessary queries:

Replace the following line

RecordTypeId = (SELECT Id FROM RecordType WHERE Name = \'Personal Touch\')

with

RecordType.Name =  \'Personal Touch\'

2. Split your query in 2 different queries

This will allow you to use Batch functionality.

In a first step you have to query the campaigns:

SELECT Id, StartDate, EndDate FROM Campaign WHERE [CRITERIA] Limit ??

In a second query all CampaignMembers

SELECT Id FROM CampaignMembers WHERE CampaignId IN :[Campaign ID List] LIMIT 10000

3. Make sure that the functionality works fine (in your sandbox)

Batchify your program

Create a new class which implements the interface Database.Batchable make sure that you are using Database.QueryLocator. Move your campaign query to the batchable class and call your "functionality class" in the execute statement.

Your batchable class should look similar to this:

global class SearchAndReplace implements Database.Batchable<sObject>
{

   global final String query = 'SELECT ID FROM Campaign WHERE ??';


   global Database.QueryLocator start(Database.BatchableContext BC)
   {
      return Database.getQueryLocator(query);
   }

   global void execute(Database.BatchableContext BC, List<sObject> scope)
   {
        FunctionalityClass.execute((List<Campaign>) scope);
    }

   global void finish(Database.BatchableContext BC)
   {
   }
}

And your functionality class:

class FunctionalityClass
{
    public static void execute(List<Campaign> campaigns)
    {
        List<CampaignMember> cm = [SELECT ID FROM CampaignMember WHERE ID IN :campaigns];
    }
}

Best Answer

Related Solutions

[SalesForce] How to decode fact map for matrix report using Analytics API

[SalesForce] SOQL: How to handle relationship query with more than 50,000 results

Related Topic