How to increase number of records obtained from Salesforce Report API

[jasonyeung87]

Hi,
I have a Power BI report which imports data from a couple of Salesforce Reports. One of these reports have more than 2,000 records. When I tried to display the data, it would only grab the first 2,000 records of the report if there are more than 2,000 records.

I did some investigation and it appears that it's by design for Salesforce Reports. https://developer.salesforce.com/docs/atlas.en-us.api_analytics.meta/api_analytics/sforce_analytics_....

I was wondering is there a way to increase the number of records that can be imported to Power BI? Or is there a workaround on how to do this?

Jason
​​​​​​​

[EugeneLebedev]

You can work around this limitation using third-party tools. Here is the approach that I would recommend: https://vidi-corp.com/connect-salesforce-reports-to-power-bi/

This approach essentially extracts data from Salesforce reports in chunks of 2000 rows each and then appends them together. As a result you get all the columns you need in a single table.

[v-yanjiang-msft]

Hi  @jasonyeung87 ,

You can use a filter on the object id column to exclude the first 2000 results and get the second 2000 until you fetch all the data:

    Reports.ReportResults results = Reports.ReportManager.runReport('00O58000003sea0',true);
    // pars the result to recive the first 2000 ids.
    Reports.ReportFilter RF = new Reports.ReportFilter('ACCOUNT_ID', 'notEqual', ConcatOfReturnIds);
    Reports.ReportMetadata RM = results.getReportMetadata();
    list<Reports.ReportFilter>allfilters = new list<Reports.ReportFilter> {RF};
    allfilters.addAll(RM.getReportFilters());
    RM.setReportFilters(allfilters);
    results = Reports.ReportManager.runReport('00O58000003sea0', RM,true);
    System.debug(LoggingLevel.INFO,'res: '+results.getFactMap());

For more information see this blog, but don't use the callouts.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please considerAccept it as the solution to help the other members find it more quickly.

[jasonyeung87 · Accepted solution]

Hi @v-yanjiang-msft ,

thanks for your help. I ended up recreating the report using "Salesforce Objects" instead of using "Salesforce Reports". It looks like the 2,000 row limit is due to Salesforce's API and it has been like this for a while.

"Salesforce Objects" is more flexible, but it involves getting more familiar with Salesforce itself (e.g how the data is represented and how to find out what entity is what, etc.).

Jason

[v-yanjiang-msft]

Hi @jasonyeung87 ,

Glad your problem has been solved, if you have not other problems, would you mind accept your reply as solution.

Then we are able to close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.

Best Regards,
Community Support Team _ kalyj

[jasonyeung87]

@v-yanjiang-msft ,

ok, thanks

Sincerely,

Jason