function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Iñigo PuigPeyIñigo PuigPey 

Reports Too many query rows: 50001

Hello,

I have an issue executing a report from Apex. I am getting the Fatal Error "Too many query rows: 50001" when I execute the method "Reports.ReportManager.runReport(reportId, reportMetadata, includeDetails)".

The report returns 13.000 contacts, but when I execute it from apex, it only return the first 2,000 contacts. To get all the records y execute the same report adding a new filter to exclude the contacts I already got adding "Reports.ReportFilter('Contact.Id', 'greaterThan', lastContactId);"

So if the report must return 13,000 records, I will have to run the report 8 times. Every time I run the report I consult the limits and I always get 3 in "Limits.getQueryRows()", but when is executing the report for 5th time I get this exception "System.LimitException: reports:Too many query rows: 50001"

My conclusion is that as the report has 13,000 records, it adds to the limit 13,000 records to the limit everytime the report is executed, so if the report has 50,000 record I will not be able to execute the report not eve once. But if I check the limit with "Limits.getQueryRows()" I will always get 3 records, and never 50,001.

Can anyone help me with this issue? Has anyone launched a report with more than 50,000 records from apex?

Thank you
Rahul KumarRahul Kumar (Salesforce Developers) 
Hi,
Please check with below post from stack exchange community with the same issue while using batch apex.This might be helpful for you to accelerate with the above requirement.
http://salesforce.stackexchange.com/questions/15456/too-many-query-rows-50001
Kindly mark this post as solved if the information help's so that it gets removed from the unanswered queue and becomes a proper solution which results in helping others who are really in need of it.

I hope it will be helpful.

Best Regards
Rahul Kumar
Andreas Wolf 11Andreas Wolf 11
Hello Iñigo PuigPey,

I have got the exact same problem. Did you find a solution?

I'm sorry Rahul Kumar, but your answer is not a solution for the Apex/Report problem.

The following error messages are two different problems:
  1. Too many query rows: 50001
  2. reports.Too many query rows: 50001
The second one is our problem. Thanks in advance!
Aidan Harding 13Aidan Harding 13
You can solve this by running the report asynchronously and using a Queueable class to manage the completions of the report. Each execution of the Queueable get a fresh 50,000 rows to use and you can pass on the point where you left off to the next Queueable. It's moderately complicated, but it does work.

The ugly part of my solution is that a Queueable class ends up requeueing itself as a way of polling for the asynchronrous report run to finish. Which is not very nice, but it does seem to work. I've had this work on a report with 27k rows. 

Something like this:
 
public with sharing class ReportExportQueueable implements Queueable {

    private Report_Export_Job__c exportJob;
    private Integer paginationColumn;
    private String paginationField;

    public ReportExportQueueable(Report_Export_Job__c exportJob) {
        this.exportJob = exportJob;
        if(exportJob.Total_Rows__c == null) {
            exportJob.Total_Rows__c = 0;
        }
    }

    private String getFileSoFar() {
        // Get content from the attachment
    }

    private void writeFile(String data) {
        // Write to the attachment
    }

    private Reports.ReportMetadata reportMetadata;

    private Reports.ReportMetadata getReportMetadata() {
        // Get the metadata from  Reports.ReportManager.describeReport(exportJob.Report_Id__c)
        // Set the report params so that it is exportable e.g. make sure we include the field
        // to paginate on
    }

    public void execute(QueueableContext context) {
        if(exportJob.Report_Instance_Id__c == null) {
            exportJob.Report_Instance_Id__c = Reports.ReportManager.runAsyncReport(exportJob.Report_Id__c, getReportMetadata(), true).getId();
            update exportJob;
            System.enqueueJob(new ReportExportQueueable(exportJob));
            System.debug('Starting first instance of report');
            return;
        }
        Reports.ReportInstance instance = Reports.ReportManager.getReportInstance(exportJob.Report_Instance_Id__c);
        String instanceStatus = instance.getStatus();
        System.debug('instanceStatus: ' + instanceStatus);
        if(instanceStatus == 'Running' || instanceStatus == 'New') {
            System.enqueueJob(new ReportExportQueueable(exportJob));
            System.debug('Report is still running, re-queueing self');
            return;
        } else if(instanceStatus == 'Error') {
            exportJob.Status__c = 'Error';
            update exportJob;
            System.debug('Report has errors, exiting');
            return;
        }

        System.debug('Getting results...');

        Reports.ReportResults results = instance.getReportResults();

        Reports.ReportFactWithDetails factDetails =
                (Reports.ReportFactWithDetails) results.getFactMap().get('T!T');

        List<Reports.ReportDetailRow> reportRows = factDetails.getRows();
        if(reportRows.isEmpty()) {
            exportJob.Status__c = 'Complete';
            update exportJob;
            System.debug('Report has no results, exiting');
        } else {
            System.debug('Processing results...');

            // Get the data dn write to the Attachment
            writeFile(fileSoFar);

            exportJob.Total_Rows__c += reportRows.size();

            String paginationValue = (String) reportRows[reportRows.size() - 1].getDataCells()[paginationColumn].getValue();

            if(reportRows.size() == 2000) {
                // Add filters to the metadata, using paginationValue
                
                exportJob.Report_Instance_Id__c = Reports.ReportManager.runAsyncReport(exportJob.Report_Id__c, reportMetadata, true).getId();
                update exportJob;
                requeue();
                System.debug('Starting next instance of report');
            } else {
                exportJob.Status__c = 'Complete';
                update exportJob;
                System.debug('Less than 2000 rows returned, report complete');
            }
        }
    }

    private void requeue() {
        if(!(Test.isRunningTest() && System.isQueueable())) {
            System.enqueueJob(new ReportExportQueueable(exportJob));
        }
    }
}

 
SFDC New learnerSFDC New learner
Hi Iñigo PuigPey/Andreas Wolf 11,

I got the same issue too. Did you find the solution?

Please let me know.

Thanks,
Sirisha