Apex Code Development You need to sign in to do that
Don't have an account?
STar14 Report Api sorting isnt working in apex
Hi ,
I am trying to use setSortBy() method in apex while using Report API. The method is not sorting the columns.
Please let me know if i am missing anything in here.TIA
I am trying to use setSortBy() method in apex while using Report API. The method is not sorting the columns.
Please let me know if i am missing anything in here.TIA
Reports.ReportResults results = Reports.ReportManager.runReport('00O11000000l9wC',true);
Reports.ReportMetadata RM = results.getReportMetadata();
System.debug('Detail columns: ' + rm.getDetailColumns());
//Sorting code
Reports.SortColumn sortcolumn = new Reports.SortColumn();
sortcolumn.setSortColumn('CAT_Focal_File__c.Workday_Id__c');
sortcolumn.setSortOrder(ASCENDING);
System.debug('@@@1 '+results.getAlldata());
list<Reports.SortColumn> lstcolumns = new list<Reports.SortColumn>();
lstcolumns.add(sortcolumn);
rm.setSortBy(lstcolumns);
Reports.ReportFactWithDetails factDetails =
(Reports.ReportFactWithDetails)results.getFactMap().get('T!T');
List<Reports.ReportDetailRow> allRows = factDetails.getRows();
string s = (string)allRows.get(0).getDataCells().get(1).getValue();
system.debug('@@@@@s '+s)
- The class needs a reportId to know which report to run
- The class allows you to optionally set the Metadata for running a report. Observe that there is a method that intakes a Report Metadata apart from ReportId and a boolean flag to include the details. You can retrieve what is there using describe method
- You will have to use set Methods provided in case you want to override something from standard.
The below code is functional and works for the Tabular report I have in my org.Id REPORTID = '00O90000001wNWV'; // Query by developer name or store this in custom metadata. //WARNING: The Report Id changes between the environments // Find the Metadata of the Current Report in Salesforce by describe call Reports.ReportMetadata reportInputMetadata = Reports.ReportManager.describeReport(REPORTID).getReportMetadata(); //Prepare for the sort override Reports.SortColumn sortcolumn = new Reports.SortColumn(); sortcolumn.setSortColumn('Position__c.Status__c'); sortcolumn.setSortOrder('ASCENDING'); list<Reports.SortColumn> lstcolumns = new list<Reports.SortColumn>(); lstcolumns.add(sortcolumn); // Override with a custom sort reportInputMetadata.setSortBy(lstcolumns); // Run the Report to retrive results Reports.ReportResults results = Reports.ReportManager.runReport(REPORTID,reportInputMetadata, true); Reports.ReportMetadata reportOutputMetadata = results.getReportMetadata(); System.debug('Sort By: ' + reportOutputMetadata.getSortBy()); Reports.ReportFactWithDetails factDetails = (Reports.ReportFactWithDetails)results.getFactMap().get('T!T'); List<Reports.ReportDetailRow> allRows = factDetails.getRows(); System.debug('DATA' + JSON.serializePretty(allRows));All Answers
I was able to verify the posted code returns the sorted column which in my case is Account Name.
As per https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_class_reports_reportmetadata.htm you can only sort on one column.
Reports.ReportResults results = Reports.ReportManager.runReport('00O7F00000BL8HX',true); Reports.ReportMetadata RM = results.getReportMetadata(); System.debug('Detail columns: ' + rm.getDetailColumns()); //Sorting code Reports.SortColumn sortcolumn = new Reports.SortColumn(); sortcolumn.setSortColumn('Account_Name'); sortcolumn.setSortOrder('ASCENDING'); System.debug('@@@1 '+results.getAlldata()); list<Reports.SortColumn> lstcolumns = new list<Reports.SortColumn>(); lstcolumns.add(sortcolumn); rm.setSortBy(lstcolumns); system.debug('@@@rm'+lstcolumns); Reports.ReportFactWithDetails factDetails = (Reports.ReportFactWithDetails)results.getFactMap().get('T!T'); system.debug('@@factDetails'+factDetails); List<Reports.ReportDetailRow> allRows = factDetails.getRows(); String s = (String)allRows.get(0).getDataCells().get(4).getValue(); system.debug('@@@@@s '+s);Related: https://salesforce.stackexchange.com/questions/147222/can-we-sort-report-columns-using-report-api-via-apex-i-found-this-on-the-docume
- The class needs a reportId to know which report to run
- The class allows you to optionally set the Metadata for running a report. Observe that there is a method that intakes a Report Metadata apart from ReportId and a boolean flag to include the details. You can retrieve what is there using describe method
- You will have to use set Methods provided in case you want to override something from standard.
The below code is functional and works for the Tabular report I have in my org.Id REPORTID = '00O90000001wNWV'; // Query by developer name or store this in custom metadata. //WARNING: The Report Id changes between the environments // Find the Metadata of the Current Report in Salesforce by describe call Reports.ReportMetadata reportInputMetadata = Reports.ReportManager.describeReport(REPORTID).getReportMetadata(); //Prepare for the sort override Reports.SortColumn sortcolumn = new Reports.SortColumn(); sortcolumn.setSortColumn('Position__c.Status__c'); sortcolumn.setSortOrder('ASCENDING'); list<Reports.SortColumn> lstcolumns = new list<Reports.SortColumn>(); lstcolumns.add(sortcolumn); // Override with a custom sort reportInputMetadata.setSortBy(lstcolumns); // Run the Report to retrive results Reports.ReportResults results = Reports.ReportManager.runReport(REPORTID,reportInputMetadata, true); Reports.ReportMetadata reportOutputMetadata = results.getReportMetadata(); System.debug('Sort By: ' + reportOutputMetadata.getSortBy()); Reports.ReportFactWithDetails factDetails = (Reports.ReportFactWithDetails)results.getFactMap().get('T!T'); List<Reports.ReportDetailRow> allRows = factDetails.getRows(); System.debug('DATA' + JSON.serializePretty(allRows));