Export data from large reports programmatically

Question

we have several reports that are over 100K rows. We would like to export them to a data warehouse to combine their data and run large analysis on that data. The reports need to be extracted daily to keep our insights always up-to-date, therefore this process cannot be done manually. I have tried:

1. using the analytics API to GET the report data in a JSON format. This approach however is not suitable because:
   • results are limited to 2000 rows, and fetching all detail rows would require some a priori knowledge of the report's column.
   • results come in this fact-map format which would require a lot of source code on our side to parse
2. using the analytics API to GET the reports in an excel format. This approach is not suitable because:
   • results are limited to 100K rows, as documented in their unrelated export help guide.
3. using the undocumented URL https://{ORG}.my.salesforce.com/{REPORT_ID}?csv=1&exp=1&enc=UTF-8&isdtp=p1 (also tried with isdtp=p1&export=1&enc=UTF-8&xf=csv) to GET the results in a CSV format. This approach is not suitable because:
   • although it works on small reports, it returns only the CSV header for the larger reports.

Am I missing something in the above? Is there any way to export Salesforce report data programmatically?

---

Update: Apparently there is no way to do this in SF, even though it's a requested feature for over a decade, and competitors have this feature. I've decided to embrace the SF xlsx row limit and ask people in the org not to create reports larger than 100K rows.

Answer 1

To avoid limitations given in API, you can emulate the reports through schedulable batches:

• create stable batch on your object with WHERE condition based on report filters.
• run batches in 2000 record chunks.
• save the rows to the variable in the batch outside the execute method.
• form the csv in the finish method and send it.

Downsides:

• if the report filters are changed, you need to change the batch code.
• if the csv is too large, you'll be out of heap. In this case try to send it in several csvs as you come up to the heap limit.

Further improvements:

• you can create Custom Label with query in the value, so when the report is changed, you will need to update only label instead of actual Apex Class (helps to avoid re-deployments on production)

Answer 2

My take on the original question is that this is about a relatively straight forward data extract - a series of fields from an object, where the rows meet specific criteria.

To convert a tabular Salesforce report managed by non technical users (I assume you'd not be trying to export grouped results) into SOQL 'on the fly' from the report metadata would not be overly complicated and achievable using some apex.

That said, if you want all fields and all rows then why not skip the report part and just roll a dynamic SOQL query to use with the bulk export API? Then no reports are required at all, the recommended design pattern and tooling are being used and you get an efficient and fast solution. Using reporting tools to export so many rows of data is not an efficient use of multi-tenant platform resources, so not surprising that there are such limits imposed.