function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Casey Hardesty 10Casey Hardesty 10 

Visualforce chart -- having trouble with group by field

I'm trying to build a simple visual force chart but am running into roadblocks on the couple ways I've tried to accomplish the task. First off, here is the relevant code from controller and page.

// Controller method
public List<annuityChartWrap> annuityChartList = new List<annuityChartWrap>();
public List<annuityChartWrap> getAnnuityChart() {
        AggregateResult[] AgR = [SELECT Hot_Rate__c, SUM(Annuity_Investment__c) sumAnnuity FROM Account WHERE OwnerId=:userid GROUP BY Hot_Rate__c];
        for (AggregateResult AnnuityTotals : AgR) {
            annuityChartList.add(new annuityChartWrap(Integer.valueOf(AnnuityTotals.get('Hot_Rate__c')), Integer.valueOf(AnnuityTotals.get('sumAnnuity'))));
        }
        return annuityChartList;
    }

// Wrapper class for annuity values per Hot Rating Sections
    public class annuityChartWrap {

        public Integer hotRating {get;set;}
        public Integer annuityTotal {get;set;}

        public annuityChartWrap (Integer hotRating, Integer annuityTotal) {

            this.hotRating = hotRating;
            this.annuityTotal = annuityTotal;

        }  
    }

  <!-- relevant vf-page code -->
<apex:chart data="{!AnnuityChart}" height="400" width="500">
        <apex:legend position="left"/>
        <apex:axis type="Numeric" position="left" title="Annuity Totals" grid="true"
            fields="annuityTotal" dashSize="2">
            <apex:chartLabel/>
        </apex:axis>
        <apex:axis type="Category" position="bottom" fields="hotRating" title="Hot Rating">
            <apex:chartLabel rotate="315"/>
        </apex:axis>
        <apex:barSeries orientation="vertical" axis="left" stacked="true"
            xField="hotRating" yField="annuityTotal" title=""/>
    </apex:chart>

So, I was getting an error originally when I was using a number field in the soql query to group by... the error -> <number field> cannot be grouped by in a query call.

So I went back and created a text field which is what you see in the code (Hot_Rate__c). The problem now is that the chart displays the numbers in the incorrect order as you can see in the image below.

User-added image

So, as you can see, I'm running into a few roadblocks on what seems like a very simple chart. Any help would be greatly appreciated.
Best Answer chosen by Casey Hardesty 10
pconpcon
I think this can be fixed by sorting your data before returning it.  You will need to implement comparable on your wrapper class [1] to do that.
 
public List<annuityChartWrap> annuityChartList = new List<annuityChartWrap>();

public List<annuityChartWrap> getAnnuityChart() {
    List<AggregateResult> AgR = [
        select Hot_Rate__c,
            sum(Annuity_Investment__c) sumAnnuity
        from Account
        where OwnerId = :userid
        group by Hot_Rate__c
    ];

    for (AggregateResult nnuityTotals : AgR) {
        annuityChartList.add(   
            new AnnuityChartWrap(
                Integer.valueOf(AnnuityTotals.get('Hot_Rate__c')),
                Integer.valueOf(AnnuityTotals.get('sumAnnuity'))
            )
        );  
    }

    annuityChartList.sort();
    
    return annuityChartList;
}

// Wrapper class for annuity values per Hot Rating Sections
public class AnnuityChartWrap implements Comparable{
    
    public Integer hotRating {get;set;}
    public Integer annuityTotal {get;set;}
    
    public AnnuityChartWrap (Integer hotRating, Integer annuityTotal) {
        this.hotRating = hotRating;
        this.annuityTotal = annuityTotal;
    }
    
    public Integer compareTo(Object obj) {
        AnnuityChartWrap other = (AnnuityChartWrap)(obj);
        
        if (this.hotRating > other.hotRating) {
            return 1;
        }
        
        if (this.hotRating < other.hotRating) {
            return -1;
        }
        
        return 0;
    }
}

[1] http://blog.deadlypenguin.com/blog/2015/10/10/comparable-sorting-objects-in-salesforce/

All Answers

pconpcon
I think this can be fixed by sorting your data before returning it.  You will need to implement comparable on your wrapper class [1] to do that.
 
public List<annuityChartWrap> annuityChartList = new List<annuityChartWrap>();

public List<annuityChartWrap> getAnnuityChart() {
    List<AggregateResult> AgR = [
        select Hot_Rate__c,
            sum(Annuity_Investment__c) sumAnnuity
        from Account
        where OwnerId = :userid
        group by Hot_Rate__c
    ];

    for (AggregateResult nnuityTotals : AgR) {
        annuityChartList.add(   
            new AnnuityChartWrap(
                Integer.valueOf(AnnuityTotals.get('Hot_Rate__c')),
                Integer.valueOf(AnnuityTotals.get('sumAnnuity'))
            )
        );  
    }

    annuityChartList.sort();
    
    return annuityChartList;
}

// Wrapper class for annuity values per Hot Rating Sections
public class AnnuityChartWrap implements Comparable{
    
    public Integer hotRating {get;set;}
    public Integer annuityTotal {get;set;}
    
    public AnnuityChartWrap (Integer hotRating, Integer annuityTotal) {
        this.hotRating = hotRating;
        this.annuityTotal = annuityTotal;
    }
    
    public Integer compareTo(Object obj) {
        AnnuityChartWrap other = (AnnuityChartWrap)(obj);
        
        if (this.hotRating > other.hotRating) {
            return 1;
        }
        
        if (this.hotRating < other.hotRating) {
            return -1;
        }
        
        return 0;
    }
}

[1] http://blog.deadlypenguin.com/blog/2015/10/10/comparable-sorting-objects-in-salesforce/
This was selected as the best answer
Casey Hardesty 10Casey Hardesty 10
Thanks pcon, that worked perfectly! I'm going to have to go through and read your blog post again because I don't fully understand whats going on with the compareTo method such as where is the method being called?

Thanks again for your help. Fixed the problem instantly and I have other charts I will need to build out a similar way.
pconpcon
The compareTo method isn't directly called.  By implementing Comparable you get the sort method for free.  Then the sort method iterates over the list and calls your compareTo method comparing it to another item in the list.
Casey Hardesty 10Casey Hardesty 10
Ahhh, got ya. Thanks man.