I've got a fairly long code piece here that aims to extract all the information I can from multiple reports using report metadata. I have a file where I have the names of reports in each line, so I can loop through each report by their name.
StaticResource sr = [select body from StaticResource Where Name = 'test'];
List<String> reportNames = sr.body.toString().replaceAll('\r\n','\n').replaceAll('\r','\n').split('\n');
Report[] reportsInfo = [SELECT Name, FolderName FROM Report WHERE Name = :reportNames];
for(Report reportRecord: reportsInfo) {
// get report folder api name
String fname = (String) reportRecord.get('FolderName');
List <Folder> folderAPI = [SELECT DEVELOPERNAME FROM FOLDER where Name = :fname];
// Get report ID
String reportId = (String)reportRecord.get('Id');
// Use report ID to get report Metadata
Reports.ReportMetadata metadata = Reports.ReportManager.describeReport(reportId).getReportMetadata();
// Run a report
Reports.reportResults results = Reports.ReportManager.runReport(reportId, true);
// Get the report extended metadata
Reports.ReportExtendedMetadata rmd = results.getReportExtendedMetadata();
// begin collecting and formatting data
string header = 'Report Info \n\n';
string finalstr = header ;
// report name, type, folder
string recordString1 = 'Report Name: '+metadata.getName() +'\n'+ 'Report API Name: '+metadata.getDeveloperName() +'\n'
+ 'Report Type Name: ' + metadata.getReportType().getLabel() + '\n'
+'Report Type API Name: '+ metadata.getReportType().getType() + '\n'+ 'Report Folder: '
+ fname +'\n' + 'Report Folder API Name: ' + folderAPI.get(0).get('DEVELOPERNAME') + '\n\n';
// report filters
string recordString11 = 'Report Filters: \n';
for(Reports.ReportFilter rf : metadata.getreportFilters()){
recordString11 += 'Filter Name: '+rf.getcolumn() + ', Filter Value: '+rf.getValue() + '\n';
}
// report cross filters
string recordString12 = 'Report Cross Filters: \n';
for(Reports.CrossFilter rf : metadata.getCrossFilters()){
recordString12 += 'Filter Object: '+rf.getRelatedEntityJoinField() + ', Filter Value: '+rf.getRelatedEntity() + '\n';
}
// report date, scope
string recordString2 = recordString11 +'\n\n' + recordString12 +'\n\n' +
'Report Start Date: '+ metadata.getStandardDateFilter().getStartDate()+'\n' +
'Report End Date: ' + metadata.getStandardDateFilter().getEndDate() + '\n'
+ 'Report Scope: ' + metadata.getScope() + '\n\n';
// report groupings info
Map<String,Reports.GroupingColumn> groupings = rmd.getGroupingColumnInfo();
string recordString3 = 'Groups: \n';
// used to give numbered list in output
Integer count_a = 1;
for (String key : groupings.keySet()) {
recordString3 += count_a + ': ' + groupings.get(key).getLabel() + '\n';
/*+ ' , Type: ' + groupings.get(key).getDataType() */
//This line gives the datatype of the groupings
count_a += 1;
}
// report column info
Map<String,Reports.DetailColumn> colMap = rmd.getDetailColumnInfo();
// used to give numbered list in output
Integer count_b = 1;
string recordString4 = '\nColumns: \n';
for(String key : colMap.KeySet()){
recordString4 += count_b + ': ' + 'Column Label = ' + colMap.get(key).getLabel() + ' , Column API Name = ' + Key + '\n';
count_b += 1;
}
// report fields info
// set new http callout request
Http http = new Http();
HttpRequest req = new HttpRequest();
// use rest api with appropriate parameters to get info about report's fields and its parent object
req.setEndpoint(URL.getSalesforceBaseUrl().toExternalForm() + '/services/data/v51.0/analytics/reports/' + reportId + '/describe');
req.setHeader('Authorization', 'Bearer ' + UserInfo.getSessionId());
req.setMethod('GET');
HttpResponse res = http.send(req);
Map<String, Object> resBody = (Map<String, Object>) JSON.deserializeUntyped(res.getBody());
Map<String, Object> reportExtendedMetadata = (Map<String, Object>) resBody.get('reportExtendedMetadata');
Map<String, Object> detailColumnInfo = (Map<String, Object>) reportExtendedMetadata.get('detailColumnInfo');
String recordString5 = '\nField and its Parent Object: \n';
for (String key : detailColumnInfo.keySet()) {
Map<String, Object> tmp = (Map<String, Object>) detailColumnInfo.get(key);
recordString5 += (String) tmp.get('label') + ' -> ' + (String) tmp.get('entityColumnName') + ' -> ' + ((String) tmp.get('entityColumnName'))?.substringBefore('.') + '\n';
// E.g, Account Name -> Account.Name -> Account
}
//putting it all together
finalstr = finalstr +recordString1 + recordString2 + recordString3 + recordString4 + recordString5 + '\n\n';
// create a csv file to send the data to, and then download the file into your org
ContentVersion file = new ContentVersion(
title = 'asset.csv',
versionData = Blob.valueOf(finalstr),
pathOnClient = '/accounts.csv'
);
insert file;
}
The problem I'm having right now is near the end with this code:
// set new http callout request
Http http = new Http();
HttpRequest req = new HttpRequest();
// use rest api with appropriate parameters to get info about report's fields and its parent object
req.setEndpoint(URL.getSalesforceBaseUrl().toExternalForm() + '/services/data/v51.0/analytics/reports/' + reportId + '/describe');
req.setHeader('Authorization', 'Bearer ' + UserInfo.getSessionId());
req.setMethod('GET');
HttpResponse res = http.send(req);
Map<String, Object> resBody = (Map<String, Object>) JSON.deserializeUntyped(res.getBody());
Map<String, Object> reportExtendedMetadata = (Map<String, Object>) resBody.get('reportExtendedMetadata');
Map<String, Object> detailColumnInfo = (Map<String, Object>) reportExtendedMetadata.get('detailColumnInfo');
String recordString5 = '\nField and its Parent Object: \n';
for (String key : detailColumnInfo.keySet()) {
Map<String, Object> tmp = (Map<String, Object>) detailColumnInfo.get(key);
recordString5 += (String) tmp.get('label') + ' -> ' + (String) tmp.get('entityColumnName') + ' -> ' + ((String) tmp.get('entityColumnName'))?.substringBefore('.') + '\n';
// E.g, Account Name -> Account.Name -> Account
}
On this very first line it gives me an System.CalloutException: You have uncommitted work pending. Please commit or rollback before calling out error. This part of the code is responsible for finding the information about the report fields and their parent object. I used this same method before when I was building this program to run on one report at a time. Now I'm trying to do it for multiple reports at a time through a loop and getting this error. How can I fix this? Or is there another way I can get the info I want without using my method? Any help is appreciated.
Thanks.
Best Answer
The issue here, assuming your provided code is correct, is the DML that you're performing at (what I presume is) the end of your loop.
You're making your first callout just fine, then performing DML, and then trying to make the next callout. Making a callout after performing any DML is not allowed, so you get the error on your second iteration of the loop.
Fixing this should be as simple as removing that DML from your loop. DML inside of a loop is at least as bad as having a query in a loop, and has the potential to be much worse. You should never have DML inside of a loop unless you can articulate why it needs to be in a loop.
Just create a list to hold the
ContentVersionrecords you generate, add to the list inside of your loop, then DML outside of the loop.