Hello Trailblazers,

I have a visualforce page with 2 date inputs, a table with Oppty's List(as shown) with calculated schedules & custom button. On button click, i'm trying to calculate the lineitemschedules revenue sum by date - per each opportunity. To achieve this, i tried an aggregate soql, however the following exception is thrown. "Aggregate query does not support queryMore(), use LIMIT to restrict the results to a single batch Error"

soqlOliRevSchedStr = 'SELECT sum(Revenue), scheduledate, Opportunitylineitemschedule.Opportunitylineitem.OpportunityId oppId FROM OpportunityLineItemSchedule WHERE Opportunitylineitem.OpportunityId IN: oppIdSet AND Opportunitylineitem.Product2.Name = \'Revenue\'';   
        soqlOliRevSchedStr += ' AND ScheduleDate >=: tempFromDatePicker AND ScheduleDate <=: tempToDatePicker';  
        soqlOliRevSchedStr += ' GROUP BY OpportunityLineItemSchedule.Opportunitylineitem.OpportunityId, scheduledate ORDER BY ScheduleDate ASC';
        for(AggregateResult aggr : Database.query(soqlOliRevSchedStr)){
            oppIdRevAmtMap = aggregateResultProcess(aggr, oppIdRevAmtMap);
        }
        
        public Map<Id, Double> aggregateResultProcess(AggregateResult aggr, Map<Id, Double> tempOppIdAmtMap){
            Id tempOppId = (Id)aggr.get('oppId');
            if(!tempOppIdAmtMap.containsKey(tempOppId)){
                tempOppIdAmtMap.put(tempOppId,(Double)aggr.get('expr0'));
            }else{
                Double temp = tempOppIdAmtMap.get(tempOppId);
                tempOppIdAmtMap.put(tempOppId, temp+(Double)aggr.get('expr0'));
            }
            return tempOppIdAmtMap;
        }

I changed the above aggregate query into a normal soql on Lineitemschedule and looped through all the dates & calculated sum by date per each opportunity. But this throws another exception as shown below. "System.LimitException: Apex CPU time limit exceeded"
 

soqlOliRevSchedStr = 'SELECT Id, Revenue, ScheduleDate, Opportunitylineitem.OpportunityId FROM OpportunityLineItemSchedule WHERE Opportunitylineitem.OpportunityId IN: oppIdSet AND Opportunitylineitem.Product2.Name = \'Revenue\'';
        soqlOliRevSchedStr += ' AND ScheduleDate >=: tempFromDatePicker AND ScheduleDate <=: tempToDatePicker'; 
        soqlOliRevSchedStr += ' ORDER BY ScheduleDate ASC';
        for(OpportunityLineItemSchedule oliSch : Database.query(soqlOliRevSchedStr)){
             oppIdRevAmtMap = aggregateResultProcess(oliSch, oppIdRevAmtMap);       
        }
        public Map<Id, Double> aggregateResultProcess(OpportunityLineItemSchedule tempOliSched, Map<Id, Double> tempOppIdAmtMap){
            Id tempOppId = tempOliSched.OpportunityLineItem.OpportunityId;
            if(!tempOppIdAmtMap.containsKey(tempOppId)){
                tempOppIdAmtMap.put(tempOppId,(Double)tempOliSched.Revenue);
            }else{
                Double temp = tempOppIdAmtMap.get(tempOppId);
                tempOppIdAmtMap.put(tempOppId, temp+(Double)tempOliSched.Revenue);
            }
            return tempOppIdAmtMap;
        }

Dent on the CPU time
VFP
Either ways the logic/calculations are consuming most of the CPU time & i'm unable to workaround this. Please share your suggestions.

Thank you!