Run reports with filters and timeframe in apex

Run Reports salesforce documentation

Why you need to run reports through apex

1. Consider a scenario, you have report combining multiple objects with multiple filter conditions. along with that you report should consider records within certain timeframe. and report has summary variables. 

2. Users need to run report multiple times to see aggregate of fields (summary variable) in different timeframes. 

3. It's good idea if we store aggregate or total of fields for different timeframes. 

so that user can see summary of fields yearwise, quartewise in single glance. 

4. we can diplay how much sale was done last year, last quarter or last two quarters. 

5. To implement this, we don't have to implement or mimic report logic through apex instead we can run report by passing necessary inputs and get results, further we can just display these results on vf page or LWC or we can store this in variables and display that on record detail page. 

Example 1. 

In this example start date, end date and accountId are input to report. 

Dates are used to set duration for report. Filters are added as per sequence.

In the result we get aggregate result for mentioned accountid. 

 

 public String runReport(Date startDate, Date endDate, Id accountId){
 List <Report> reportList = [SELECT Id,DeveloperName FROM Report where
                                    DeveloperName = 'reportDeveloperName'];
        
       Id reportId = (String)reportList.get(0).get('Id');
        Reports.ReportDescribeResult describe = Reports.ReportManager.describeReport(reportId);
        Reports.ReportMetadata reportMd = describe.getReportMetadata();
        
        Reports.StandardDateFilter dateFilter = new Reports.StandardDateFilter();
        dateFilter.setDurationValue('CUSTOM');
        String setStartDate = startDate.year()+'-'+startDate.Month()+'-'+startDate.Day();
        String setEndDate = endDate.year()+'-'+endDate.Month()+'-'+endDate.Day();
        
        dateFilter.setEndDate(setEndDate);
        dateFilter.setStartDate(setStartDate);
        dateFilter.setColumn('createddate');
        reportMd.setStandardDateFilter(dateFilter);
        // Override filter and run report
        Reports.ReportFilter filter1 = reportMd.getReportFilters()[0];
        filter1.setValue(accountId); // Account id on which we want to run report
        Reports.ReportFilter filter2 = reportMd.getReportFilters()[1];
        filter2.setValue('filter value'); 
        Reports.ReportResults results = Reports.ReportManager.runReport(reportId, true);
        Reports.ReportFactWithSummaries factSum =
            (Reports.ReportFactWithSummaries)results.getFactMap().get('T!T');
          System.debug('Value...'+ factSum.getAggregates()[0].getvalue());
          System.debug('Value of Result: ' + factSum.getAggregates()[0].getLabel());

        return factSum.getAggregates()[0].getvalue().toString();
        
    }

Example 2. 

There is limit on how many times we can run report per hour, above example code may hit that limit because we are running report for each accountid separately. Learn more on limits

• Your org can request up to 500 synchronous report runs per hour.
• Your organization can request up to 1,200 asynchronous requests per hour.

To overcome this, group rows in report by accountid, now we can run report only once for multiple accountids. 

Start date, end date and accountIds (accountids separated by comma). 

Map is returned from method with accountid and respective result value. 

 
  
  
 public static Map<Id,String> runReport(Date startDate, Date endDate, String accountIds){
      List <Report> reportList = [SELECT Id,DeveloperName FROM Report where
                                    DeveloperName = 'reportDeveloperName'];
        
       Id reportId = (String)reportList.get(0).get('Id');
        
        Reports.ReportDescribeResult describe = Reports.ReportManager.describeReport(reportId);
        Reports.ReportMetadata reportMd = describe.getReportMetadata();
        
        Reports.StandardDateFilter dateFilter = new Reports.StandardDateFilter();
        dateFilter.setDurationValue('CUSTOM');
        String setStartDate = startDate.year()+'-'+startDate.Month()+'-'+startDate.Day();
        String setEndDate = endDate.year()+'-'+endDate.Month()+'-'+endDate.Day();
        
        dateFilter.setEndDate(setEndDate);
        dateFilter.setStartDate(setStartDate);
        dateFilter.setColumn('createddate');
        reportMd.setStandardDateFilter(dateFilter);
        // Override filter and run report
        Reports.ReportFilter filter1 = reportMd.getReportFilters()[0];
        filter1.setValue(accountIds); // Account id on which we want to run report
        Reports.ReportFilter filter2 = reportMd.getReportFilters()[1];
        filter2.setValue('Report filter value');
  
        Reports.ReportResults results = Reports.ReportManager.runReport(reportId, reportMd);
        
        Reports.ReportFactWithSummaries factSum =
            (Reports.ReportFactWithSummaries)results.getFactMap().get('T!T');
        
        // Get the first down-grouping in the report
        Reports.Dimension dim = results.getGroupingsDown();
        
        Reports.GroupingValue groupingVal = dim.getGroupings()[0];
        
        Map<Id,String> Account_Summary_val = new Map<Id,String>();
        for(Reports.GroupingValue gp:dim.getGroupings()){
        
            
            // Construct a fact map key, using the grouping key value
            String factMapKey = gp.getKey() + '!T';
            
            // Get the fact map from the report results
            Reports.ReportFactWithSummaries factDetails =
                (Reports.ReportFactWithSummaries)results.getFactMap().get(factMapKey);
            
            // Get the first summary amount from the fact map
            Reports.SummaryValue sumVal = factDetails.getAggregates()[0];
            Account_Summary_val.put(gp.getLabel().remove(','),sumVal.getlabel().remove(',').toString());
  
            
        }
        return Account_Summary_val;        
    }
    
  

 

Please comment if you have any queries