3

I am a new bee in Salesforce world. We already have an implementation of Salesforce where we are hitting analytics API via rest client in java application to receive the report data.

API: services/data/v39.0/analytics/reports/{report_id}

It is working fine until we have records less then 2k. But now as some of our customers are having >2k records in their reports we are facing challenge in pulling the records beyond 2000.

In my research so far I found that this is the limitation of the API and I will have go with download report option.

I have tried to use a solution mentioned in this answer where its suggesting to use this url to download the csv file:

https://instance.salesforce.com/00Ox0000000xxxx?export=1&enc=UTF-8&xf=csv

But its giving html result as below :

 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
 <html>
 
 <head>
     <meta HTTP-EQUIV="PRAGMA" CONTENT="NO-CACHE">
     <script>
         function redirectOnLoad() {
 if (this.SfdcApp && this.SfdcApp.projectOneNavigator) { SfdcApp.projectOneNavigator.handleRedirect('https://instance.my.salesforce.com?ec=302&startURL=%2F00O1U000008MaWAUA0%3Fenc%3DUTF-8%26export%3D1%26xf%3Dcsv'); }  else 
 if (window.location.replace){ 
 window.location.replace('https://instance.my.salesforce.com?ec=302&startURL=%2F00O1U000008MaWAUA0%3Fenc%3DUTF-8%26export%3D1%26xf%3Dcsv');
 } else {
 window.location.href ='https://instance.my.salesforce.com?ec=302&startURL=%2F00O1U000008MaWAUA0%3Fenc%3DUTF-8%26export%3D1%26xf%3Dcsv';
 } 
 } 
 redirectOnLoad();
     </script>
 
 </head>
 </html>
 
 <!-- Body events -->
 <script type="text/javascript">
     function bodyOnLoad(){if(window.PreferenceBits){window.PreferenceBits.prototype.csrfToken="null";};}function bodyOnBeforeUnload(){}function bodyOnFocus(){}function bodyOnUnload(){}
 </script>
 </body>
 </html>
 <!--
...................................................................................................
 ...................................................................................................
 ...................................................................................................
 ...................................................................................................
 -->

Here I am stuck in using an API which I can download the full report content.

If I will be able to do it via postman. I will be able to implement the same in our application easily.

2
  • There is a comment in your solution link page which suggested to add isdtp=p1 to your csv download url. eg. ?export=1&enc=UTF-8&xf=csv&isdtp=p1. I am using this method as well and it works. Mar 11 at 15:28
  • @HenkyDjapar. It did not work for me even after adding that param. Although I manage to refer the second solution giving in the answer.
    – A_01
    Mar 17 at 11:38

2 Answers 2

7

Looking at what you've attempted, I believe Hengky's comment is accurate for you. If you look at the URL, in a lightning org, when you export a report's csv results - you'll see the following appended:

/00OXXXXXXXXXXXX?isdtp=p1

So the following from that answer does seem to work with that extra parameter appended

https://instance.salesforce.com/00Ox0000000xxxx?export=1&enc=UTF-8&xf=csv&isdtp=p1

Just be aware it's not necessarily a documented/supported feature so you may have to worry about changes to the format of the URL or changes into its behavior without any notice.


For an alternative, working with the limitations imposed on you, you could try a similar style of workaround as noted in this answer.

It'll require you to leverage Filter Reports on Demand as you'll want to utilize a unique identifier on the record and possibly a CreatedDate so you can store what Ids you already have and filter your report to get the next data set

To get specific results on the fly, filter reports through the API. Filter changes made through the API does not affect the source report definition. Using the API, you can filter with up to 20 custom field filters and add filter logic (such as AND, OR)

Likewise, Run Reports Synchronously or Asynchronously mentions how to do this

To run a report synchronously:

Send a GET or POST request to the Execute Sync resource to get data. Use a POST request to get specific results on the fly by passing dynamic filters, groupings, and aggregates in the report metadata.

You'd have to do the following:

  1. check that aggregates in factMap is > 2000
  2. store the IDs you retrieved and last created date
  3. Call same API again, but this time with two filters. One for createdDate >= yourLastCreatedDate as well as a filter on ID not equal to your initial list of Ids that fall on the same last created date from initial call
  4. Repeat that until you've gotten all the results (or you're returned < 2000 records as you know you hit the end).

How do the above steps look with more specifics?

  1. initial call - services/data/v54.0/analytics/reports/yourReportId
  2. This serves to get the format/details of reportMetadata but is also where you'd want to check if you even need subsequent calls by checking the number of records in aggregates (> 2000).
"factMap" : {
    "T!T" : {
      "aggregates" : [ {
        "label" : "2,648",
        "value" : 2648
      } ],
      "rows" : [ { ...your 2000 rows of data .. }]

3.Using reportMetadata returned above, you'll need to add/check the details/setup you want is there. Every report will be different here. In the end, you want detailColumns to contain some sort of Date and unique field as well as sortOrder on the date.

{
    "reportMetadata" : {
        "detailColumns" : [ "ACCOUNT.NAME", "ACCOUNT_ID", "CREATED_DATE"],
        "sortBy" : [ {
            "sortColumn" : "CREATED_DATE",
            "sortOrder" : "Asc"
            } ]
    }
}
  1. Make a post call with the body above and same endpoint as the initial call.
  2. This data will be in an order you want. Store the last row data's created date as well as any Ids for row data that fall on the same date
  3. Modify body again for another POST call. This time, you'll add to reportFilters as well as standardDateFilter based on your data retrieved/stored above
{
    "reportMetadata" : {
        "detailColumns" : [ "ACCOUNT.NAME", "ACCOUNT_ID", "CREATED_DATE"],
        "reportFilters" : [ {
            "column" : "ACCOUNT_ID",
            "filterType" : "fieldValue",
            "isRunPageEditable" : true,
            "operator" : "notEqual",
            "value" : "0014P00003k49UoQAI,0014P00003k498nQAA"
        } ],
        "sortBy" : [ {
            "sortColumn" : "ACCOUNT_ID",
            "sortOrder" : "Asc"
        } ],
        "standardDateFilter" : {
            "column" : "CREATED_DATE",
            "durationValue" : "CUSTOM",
            "endDate" : null,
            "startDate" : "2022-03-10"
        }
    }
}
  1. Repeat above until all data is captured

You can also attempt the above in Apex. You'd want to store the results, get the latest date, and re-run the report with an updated date filter.

There's a code example in Filter Reports that shows replacing a value in an existing filter - whereas, you'd most likely want to add a filter (for IDS not equal to: ) or just replace the value in the standard date filter as shown below

// Get the report metadata (assumes you have queried report Id
Reports.ReportDescribeResult describe = Reports.ReportManager.describeReport(reportId);
Reports.ReportMetadata reportMd = describe.getReportMetadata();

// Create sorting to sort your data as expected by date
List<Reports.SortColumn> sortColumnList = new List<Reports.SortColumn>();
Reports.SortColumn sortcolumn = new Reports.SortColumn();
sortColumn.setSortColumn('CREATED_DATE');
sortColumn.setSortOrder(Reports.ColumnSortOrder.ASCENDING);
sortColumnList.add(sortColumn);
//set sorting
reportMd.setSortBy(sortColumnList);

//run report and parse results
Reports.ReportResults results = Reports.ReportManager.runReport(reportId, reportMd, true);
List<String> reportColumns = results.getReportMetadata().getDetailColumns();
Integer columnCount = -1;
//get column number for created date
for(String column : reportColumns){
    columnCount =+ 1;
    if(column == 'CREATED_DATE'){
        continue;
    }
}

//get data & last row's created date
Reports.ReportFactWithDetails reportDetails = (Reports.ReportFactWithDetails) results.getFactMap().get('T!T');
//get size of returned records
Integer recordSize = reportDetails.getRows().size();
//get last row of data
List<Reports.ReportDataCell> recordDataCell = reportDetails.getRows()[recordSize - 1].getDataCells();
//get identified column's data in the row
Date lastDate =  (Date) recordDataCell[columnCount].getValue();

// Override filter and run report again for next set of data
reportMd.getStandardDateFilter().setStartDate(lastDate.year() + '-' + lastDate.month() + '-' + lastDate.day());
reportMd.getStandardDateFilter().setEndDate(null);


results = Reports.ReportManager.runReport(reportId, reportMd, true);
reportDetails = (Reports.ReportFactWithDetails) results.getFactMap().get('T!T');
//repeat as necessary

3
  • Thank you kris for detailed answer. It is utter shame that salesforce doesn't provide a nice api to get report data. We had to do this in our Realtask product and we went with first option and it has been working fine so far. But as you said, days are for sure numbered as it is undocumented.
    – metasync
    Mar 11 at 19:17
  • @metasync You should vote on this idea. We could get it to be standardized with enough support. Also, this feature has been around since at least the Classic Console, perhaps even earlier. They likely can't retire the parameter until they retire Classic.
    – sfdcfox
    Mar 11 at 23:55
  • 1
    The first solution did not work for me.(?export=1&enc=UTF-8&xf=csv&isdtp=p1). It was still throwing the same html err page even after adding that param. Though I was able to manage the API pagination after reading the second solution you have given. Thank you very much! God bless.. :)
    – A_01
    Mar 17 at 11:33
2

In addition to the answer given by Kris Goncalves I would like to add another solution of downloading a file via analytics/reports api which is an official solution as well.

I am pasting the curl below for reference purpose.

curl --location --request GET 'https://{instance_url}/services/data/v45.0/analytics/reports/00O5j00000XXXXXXXX' \
--header 'Authorization: Bearer XXX--TOKEN--XXXX' \
--header 'X-PrettyPrint: 1' \
--header 'Accept: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'

where the value for instance_url, You will get in the response of services/oauth2/token API. Also please choose option of send and download when using postman.

Although this solution was not feasible for me since we are having more then 40k records and api is taking too much time to give back result. I choose to go with the api pagination solution given in the answer by Kris Goncalves.

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.