+ Start a Discussion
Brian Oconnell 23Brian Oconnell 23 

How to retrieve AggregateResult in a controller for VF chart

Hello,

I am trying to make a gauge chart in a VF component.  There is one custom object that I want to summarize by record owner, so that logged in users should only see their own result.  I have hobbled together several samples I have found in documentation, but I am stuck on the concept of AggregateResult being an object.  I can't seem to retrieve the single decimal number that I need for each user.

Controller so far:
public class GaugeController {
    public String userId {get;set;}
    public String userName {get;set;}

    public GaugeController(ApexPages.StandardController controller){
        userId = UserInfo.getUserId();
        userName = UserInfo.getName();
    }

    public List<gaugeData> getData() {
          Integer thisYear = date.Today().year();

          AggregateResult groupedResults = [select SUM(Total_Hours_Out__c)sumhrs from Absence__c where Owner.Id =: userId and CALENDAR_YEAR(Start_Date__c) =: thisYear];
          Decimal TotalHours = (decimal)groupedResults.get('sumhrs');
          //for (AggregateResult ar : groupedResults) {
          //    TotalHours += ar.get('sumhrs');
          //    }
          
          List<gaugeData> data = new List<gaugeData>();
          data.add(new gaugeData(userName, TotalHours));
          return data;
          }
    public class gaugeData {
        public String name {get;set;}
        public Decimal hours {get;set;}
        
        public gaugeData(String name, Decimal data) {
            this.name = name;
            this.hours = data;
        }
    }
}
And VF page so far:
<apex:page standardController="User" sidebar="false" showHeader="false" extensions="GaugeController">
    <apex:chart name="TimeOff" height="300" width="300" animate="true" data="{!data}">
        <apex:axis type="Gauge" position="gauge" title="Time Off Hours Remaining This Year" minimum="0" maximum="100" steps="8"/>
        <apex:gaugeSeries dataField="hours" donut="50" colorSet="#0099ff,#ff0066"/>
    </apex:chart>
    
</apex:page>
I'm not a regular coder, so I would appreciate any help.

Thanks!
Best Answer chosen by Brian Oconnell 23
Himanshu MaheshwariHimanshu Maheshwari
When using Aggregate query you have to GROUP BY:

Try below query
AggregateResult groupedResults = [select SUM(Total_Hours_Out__c)sumhrs from Absence__c where Owner.Id =: userId and CALENDAR_YEAR(Start_Date__c) =: thisYear GROUP BY Owner.Id];
 Thanks,
Himanshu
 

All Answers

Daniel BallingerDaniel Ballinger
At first glance your Aggregate query looks reasonable. If you use the sample SOQL query in the developer console with literal values for the userId and thisYear does it bring back the expected results?
Himanshu MaheshwariHimanshu Maheshwari
When using Aggregate query you have to GROUP BY:

Try below query
AggregateResult groupedResults = [select SUM(Total_Hours_Out__c)sumhrs from Absence__c where Owner.Id =: userId and CALENDAR_YEAR(Start_Date__c) =: thisYear GROUP BY Owner.Id];
 Thanks,
Himanshu
 
This was selected as the best answer
Brian Oconnell 23Brian Oconnell 23
Thank you!