+ Start a Discussion
JoeyLenJoeyLen 

Group By with Formula field

I have been struggling with a class and visualforce page all day only to find out that I cannot group by a formula field.  I want to aggregate the sales results for each of my 3 sales regions, East, Central & West and show the values in the visualforce table in descending order.  Anyone have any workarounds?

Class:

public with sharing class CEOMTD {
    public List<AggregateResult> getData() {
        return
            [SELECT SUM(Amount) monthlyRevenue, COUNT(Name) numOppsClosedWon,
                Region__c
             FROM Opportunity
             WHERE stageName = 'Closed Won' AND CALENDAR_YEAR(CloseDate) = 2014
             GROUP BY Region__c
             ORDER BY Region__c];
    }
}
JoeyLenJoeyLen
OK, I broke down and added a text formula field to the opportunity (CEO_Region__c) and uploaded the text.  I rebuilt my class and VF page, they compile fine but don't produce any results.  Thoughts?

Class:
public with sharing class CEOMTD {
    public List<AggregateResult> getData() {
        return
            [SELECT SUM(Amount) monthlyRevenue, COUNT(Name) numOppsClosedWon,
               CEO_Region__c
             FROM Opportunity
             WHERE stageName = 'Closed Won' AND CALENDAR_YEAR(CloseDate) = 2014
             GROUP BY CEO_Region__c
             ORDER BY CEO_Region__c];
    }
}

VF page:
<apex:page controller="CEOMTD" readOnly="true">
    <apex:chart width="600" height="275" data="{!data}">
         <apex:axis type="Numeric" position="left" fields="monthlyRevenue"
            title="Region " grid="true"/>
            <apex:barSeries title="Monthly Sales" orientation="vertical"
            axis="left" xField="Region" yField="monthlyRevenue"/>
    </apex:chart>
</apex:page>