1

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:
  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.

2
  • 1
    This is a XY Problem. You need data from SF in your data warehouse. Using a Report to shuttle the data is a possible but poor implementation choice. A more typical solution is some form of ETL, where records would be exported via Bulk API. See architect.salesforce.com/decision-guides/data-integration and make a note of LDV/Bulk column.
    – identigral
    Oct 25, 2023 at 16:38
  • Maybe you're right. But we can't expect organizations to train people to use SQL, when they have the simple (but limited) SF reporting user interface. Hubspot, for instance, has an export to CSV button on all reports. If this feature is not available in SF, then I'll just accept @mariia-illarionova 's answer and let people know this can't be done.
    – luksfarris
    Oct 27, 2023 at 12:04

2 Answers 2

0

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)
4
  • Maybe I misunderstood your answer, but for this to work I would need to manually create a SOQL query for each report, right? And manually edit the queries when the reports change? If this is the case, this is not acceptable
    – luksfarris
    Oct 25, 2023 at 11:45
  • 1
    Yes, you understood correctly. As a workaround, you can create label with query and request it from batch start method, so you won't need to redeploy code on production. But with the limitations there is literally no other way around. Oct 25, 2023 at 12:31
  • 1
    This is unfortunate. Apparently people have asked for this feature for over 12 years ideas.salesforce.com/s/idea/a0B8W00000GdYYTUA3/…
    – luksfarris
    Oct 25, 2023 at 12:47
  • I'll accept your answer, because you are right about the fact that it can't be done at the moment
    – luksfarris
    Oct 27, 2023 at 12:16
1

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.

1
  • Yeah, this is what I ended up doing. It is the right course of action, and your comment about using the platform efficiently makes total sense. We decided to allocate more Analytics people to this, and achieve what we need properly.Thank you
    – luksfarris
    Dec 6, 2023 at 10:30

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .