6

Using cues from the sample here:

Sample in Salesforce Documentation

and the tutorial here:

Blog by Modelit with great sample

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!

4

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)

  • Ah, I was hoping that wasn't the case. Thank you for the link! – Sam Kamensky May 2 '16 at 22:00
  • @SamKamensky: a more interesting question is why your execute anonymous didn't use normal soql rather than a report. You'd be able to handle up to 50,000 rows queried and 10,000 rows inserted – cropredy May 3 '16 at 0:07
  • the anonymous code is a simplified version of a class I'm implementing. The point of the class is to allow for users to add campaign members from custom report types. The easy of rewriting the report as a SOQL is just a tease! – Sam Kamensky May 3 '16 at 2:54
2

In the end, I ended up getting around this limitation using the following strategy:

Create an auto number on the object you're trying to report on (make sure to check off the option to back-populate existing records).

Add the auto number to the report and sort by that number (can be in descending or ascending order, you will need to handle that later on). Add a report filter using that number and set it to "greater than blank" so it includes all records.

Create a while loop (or for loop, depending on your use case. I used a while loop) and within the while loop, run a report with metadata. Then, dynamically update the filter so that at each incremental run, the report returns different results, effectively getting around the 2k limitation. This worked on 9k+ records synchronously.

Here's a code abstract of how I did it.

public class CreateCampaignMembers{    

    public static void CreateCampaignMembersFromReport(id campaignId, String userSetStatus,id reportId){

        //This will be the initial filter for the report until it runs a second time.
        //the second time and on, the filter gets dynamically assigned the autonumuber related to the bottommost id on the report.
        String filterNumber = '0';

        //initialize numOfRow at 1. When it gets reassigned to 0, stop the while loop
        id firstconid;
        Integer numOfRows = 1;
        Integer loopCount = 0;

        //you do your setup here. i'm setting up a campaign member list.

        //create a set of campaign memebers. To be populated in the for loop with contacts. Sets removes possibility of dupes.
        Set<CampaignMember> campaignmembers = new Set<CampaignMember>();
        //create list for insert at the end
        List<CampaignMember> campaignMemberList = new List<CampaignMember>();
        Boolean manualBreak = false;

        //break loop when report stops returning results, or when told to break.
        while(numOfRows>0 && !manualBreak){
            loopCount++;                

            // Get the report metadata in order to create dynamic filter (since reports run with a maximum of 2,000 return results)
            Reports.ReportDescribeResult describe = Reports.ReportManager.describeReport(reportId);
            Reports.ReportMetadata reportMd = describe.getReportMetadata();           
            Reports.ReportFilter filter = reportMd.getReportFilters()[0];
            filter.setValue(String.valueOf(filterNumber));                              

            // Run a report synchronously
            Reports.reportResults ActiveISOContacts = Reports.ReportManager.runReport(reportId, reportMd, true);
            ActiveISOContacts.getAllData();

            // Get the fact map from the report results
            Reports.ReportFactWithDetails factDetails = 
            (Reports.ReportFactWithDetails)ActiveISOContacts.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();

            numOfRows = reportRows.size();
            System.debug('Value of numOfRows: ' + numOfRows);
            //loop through each report detail row and create a campaign member

            //loop throgh row by row the get the data
            for(integer count = 0; count<numOfRows;count++){                        

                List<Reports.ReportDataCell> datacells = reportRows[count].getDataCells();
                Reports.ReportDataCell datacell = datacells[0];
                id conid = (id)datacell.getValue();

                //make sure loop doesn't enter infinite loop
                if(count==0 && loopCount>1 && firstconid==conid){
                    manualBreak = true;
                    break;
                }

                //set conid to firstconid for reference in the next loop
                if(count==0){                       
                    firstconid = conid;                     
                }

                CampaignMember campaignmember = new CampaignMember(CampaignId=campaignId,ContactId=conid,Status=userSetStatus);
                campaignmembers.add(campaignmember);

                **//HERE's WHERE YOU SET THE FILTER TO DYNAMICALLY CHANGE (only get it when you're in the last row of the report results)**
                if(count==numOfRows-1){
                   filterNumber= [Select Record_Number_Id__c FROM Contact WHERE id= :conid].Record_Number_Id__c;
                }

            }
        }
        campaignMemberList.addall(campaignmembers);
        Database.insert(campaignMemberList,false);
    }       
}
  • @Vigneshwaran G, just curious, what changes did you make? Did you change the spacing from tabs to actual spaces? – Sam Kamensky May 27 '16 at 19:09
  • If you paste proper indented code to editor its not showing properly. So i add another tab to all the proper indented code and paste to editor and it looks like the above. – Vigneshwaran G May 27 '16 at 19:18

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.