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:
- check that
aggregates in factMap is > 2000
- store the IDs you retrieved and last created date
- 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
- 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?
- initial call -
services/data/v54.0/analytics/reports/yourReportId
- 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"
} ]
}
}
- Make a post call with the body above and same endpoint as the initial call.
- 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
- 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"
}
}
}
- 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
isdtp=p1to your csv download url. eg.?export=1&enc=UTF-8&xf=csv&isdtp=p1. I am using this method as well and it works.