Wednesday, October 6, 2010

GROUP BY - Count Method in Apex Controller

Scenario:
Need to do Totals of Stages in Opportunity for the entire org.

Thought Process:
  1. What are the columns involved? Answer: StageName and Count or Total
  2. What methods will we utilize to achieve these results? Answer: Group By SOQL Statement with Count.
  3. What UI to built this neat logic? Answer: Apex Controller and VisualForce Page
  4. Need to achieve this type of reporting for the user:

    Solution:
    1. Use Eclipse IDE or SOQL Explorer to create your SOQL Statement.
    2. SELECT StageName, Count(Name) ce 
      FROM Opportunity GROUP BY StageName
    3. Second create  Apex Controller name what you like I named it TTL_Lesson, with your logic
    public class TTL_Lesson{
    //Define your variables
    public class OppStageHolder {
        public String OPP {get; set;}
        public Integer TTL_Opp {get; set;}
    //Empty Array    
    public OppStageHolder (){}
    }
    //Results will be placed within this List
    public List queryResults{ get; set; }
    
    //Your Page
    public PageReference TTL() {
    
    AggregateResult[] groupedResults = [SELECT StageName, 
         Count(Name) ce FROM Opportunity 
         GROUP BY StageName];  
    System.Debug('zzavg ' + groupedResults.size());
    //Define your List
    queryResults = new List();
    
    for (AggregateResult ard : groupedResults)  {    
        OppStageHolder myObject = new OppStageHolder();    
        myObject.OPP = String.valueOf(ard.get('StageName'));    
        myObject.TTL_Opp = (Integer) ard.get('ce');         
        queryResults.add(myObject);    
    }
    return Page.TTL;
    }

    }

    Now create your VF Page to call this and display it whenever the user clicks on this page:

    <apex:page controller="TTL_Lesson" action="{!TTL}" showHeader="false" sidebar="false">
        <apex:dataTable value="{!queryResults}" var="a" id="theTable" border="2" cellpadding="1" cellspacing="1" bgcolor="#A9D0F5" >
                    <apex:column >
                            <apex:facet name="header">Stage</apex:facet>
                            <apex:outputText value="{!a.OPP}"/>
                    </apex:column>               
                    <apex:column >
                            <apex:facet name="header">&nbsp;&nbsp;&nbsp;Count</apex:facet>
                            <apex:outputText value="{!a.TTL_Opp}"/>
                    </apex:column>
        </apex:dataTable>
    </apex:page>

    End result is the image on top

    No comments:

    Post a Comment