+ Start a Discussion
sml9099sml9099 

Display aggregate function results on Visual force page

Hey Guys, 

I have written a VF page and apex class . Users have to enter certain information. Based on the critirea , code filters out the records and display the selected records on VF page which meet the critirea. It is able to fetch the records. But there are is Count() and Sum () aggregate functions which are also used in apex class. I am not able to print the those values on VF page. I have seen previous posts also and i cannot figure out what to do.Kindly tale a look at the code and please help.  In short I want to display the values of sum(impressions) , sum(requests) and count() on VF page.

Apex class:

public class siteplacementFetch{
public Site_Placements__c  sp{get;set;}
    public List<Site_Placements__c > spRec{get;set;}
    String matchString;
    String matchString1;
    String matchString2;
    public siteplacementFetch(){
        sp=new Site_Placements__c ();
        spRec = new List<Site_Placements__c>();
        matchString = '';
        matchString1 ='';
        matchString2 ='';
    }
  
    public void FetchSPRec(){
        matchString = '%'+sp.Device_Type__c+'%';
        matchString1 = '%'+sp.Auto_Play__c+ '%';
       matchString2 = '%'+sp.Number_of_Strikes_Given__c+ '%';
        spRec=[select Name, total_Impressions__c from Site_Placements__c where
        ((Device_Type__c like :matchString) AND (Auto_Play__c like :matchString1) AND (Number_of_Strikes_Given__c like :matchString2)) ];}
       
     
                public integer total_values(){
      
       Integer counter = [ Select count()
                    FROM Site_Placements__c where ((Device_Type__c like :matchString) AND (Auto_Play__c like :matchString1) AND (Number_of_Strikes_Given__c like :matchString2)) ];
   
    List<AggregateResult> results= [ SELECT sum(impressions__c)
            FROM site_placement_data__c
            WHERE site_placement__c in (select id FROM Site_Placements__c Where ((Device_Type__c like :matchString) AND (Auto_Play__c like :matchString1) AND (Number_of_Strikes_Given__c like :matchString2))) ];
               
            List<AggregateResult> results1= [ SELECT sum(requests__c)
            FROM site_placement_data__c
            WHERE site_placement__c in (select id FROM Site_Placements__c Where ((Device_Type__c like :matchString) AND (Auto_Play__c like :matchString1) AND (Number_of_Strikes_Given__c like :matchString2))
) GROUP BY site_placement__c];
return counter;}               
                   
     public pagereference CancelSPRec(){
    
    PageReference page = new PageReference('https://cs1.salesforce.com/a36/o');
    page.SetRedirect(true);
    return page;
    }}

Visual force page:

<apex:page controller="siteplacementFetch" tabStyle="Site_Placements__c">
  
    <apex:form >
        <apex:pageMessages />
        <apex:pageBlock >
            <apex:pageBlockButtons location="Top">
           
            <apex:commandButton value="Fetch" action="{!FetchSPRec}"/>

            </apex:pageBlockButtons>

             <apex:pageBlockSection title="Please select the Critirea">
                <apex:pageBlockSectionItem >
                    <apex:outputLabel value="Auto Play" for="autoplay"/>
                    <apex:inputText value="{!sp.Auto_Play__c}" id="autoplay"/>
                </apex:pageBlockSectionItem>
                <apex:pageBlockSectionItem >
                    <apex:outputLabel value="Number of Strikes" for="numberofstrikes"/>
                    <apex:inputField value="{!sp.Number_of_Strikes_Given__c}" id="numberofstrikes"/>
                </apex:pageBlockSectionItem>
                <apex:pageBlockSectionItem >
                    <apex:outputLabel value="Device Type" for="devicetype"/>
                    <apex:inputField value="{!sp.Device_Type__c}" id="devicetype"/>
                </apex:pageBlockSectionItem>
               
               
             </apex:pageBlockSection>
            <apex:pageBlockSection title="Results">
                <apex:pageBlockTable value="{!spRec}" var="site" >
                    <apex:column value="{!site.Name}"/>
                    <apex:column value="{!site.total_Impressions__c}"/>
                    

                 </apex:pageBlockTable>
               
           
       
            </apex:pageBlockSection>
            </apex:pageblock>    
           
        </apex:form>
</apex:page>
Best Answer chosen by sml9099
Elie.RodrigueElie.Rodrigue
i wrote that all in notepad and didnt tested.
For both sum, you can do SumOfImpression = (Decimal)theQuery, same for sumofrequest
As for the missing property, add {get;set;} to the declaration in the class.

public class siteplacementFetch{
public Site_Placements__c  sp{get;set;}
    public List<Site_Placements__c > spRec{get;set;}
    String matchString;
    String matchString1;
    String matchString2;
    public siteplacementFetch(){
        sp=new Site_Placements__c ();
        spRec = new List<Site_Placements__c>();
        matchString = '';
        matchString1 ='';
        matchString2 ='';
    }
    //Declare variable to be accessible in vf page
    public integer NumberOfSitePlacements {get;set;}
    public decimal SumOfImpressions{get;set;}
    public decimal SumOfRequests{get;set;}
    //end of variable declaration
     //Helper method to fill the aggregate variables
     public void FillAggregates()
     {
           NumberOfSitePlacements = [ Select count()
                    FROM Site_Placements__c where ((Device_Type__c like :matchString) AND (Auto_Play__c like :matchString1) AND (Number_of_Strikes_Given__c like :matchString2)) ];
           SumOfImpressions = (Decimal)([ SELECT sum(impressions__c)
            FROM site_placement_data__c
            WHERE site_placement__c in (select id FROM Site_Placements__c Where ((Device_Type__c like :matchString) AND (Auto_Play__c like :matchString1) AND (Number_of_Strikes_Given__c like :matchString2))) ][0].get('expr0'));
           SumOfRequests = (Decimal)([ SELECT sum(requests__c)
            FROM site_placement_data__c
            WHERE site_placement__c in (select id FROM Site_Placements__c Where ((Device_Type__c like :matchString) AND (Auto_Play__c like :matchString1) AND (Number_of_Strikes_Given__c like :matchString2))
) GROUP BY site_placement__c][0].get('expr0'));
     }



    public void FetchSPRec(){
        matchString = '%'+sp.Device_Type__c+'%';
        matchString1 = '%'+sp.Auto_Play__c+ '%';
       matchString2 = '%'+sp.Number_of_Strikes_Given__c+ '%';
        spRec=[select Name, total_Impressions__c from Site_Placements__c where
        ((Device_Type__c like :matchString) AND (Auto_Play__c like :matchString1) AND (Number_of_Strikes_Given__c like :matchString2)) ];
      //Dont forget to call the method to fill the aggregates
       FillAggregates();
}

All Answers

Elie.RodrigueElie.Rodrigue
From your class code, it seems like your aggregateresult lists are not exposed.
Then you need to work with the list and get expr0 value as your are not naming your aggregate value.

Here's some code that should do the job : 

public class siteplacementFetch{
public Site_Placements__c  sp{get;set;}
    public List<Site_Placements__c > spRec{get;set;}
    String matchString;
    String matchString1;
    String matchString2;
    public siteplacementFetch(){
        sp=new Site_Placements__c ();
        spRec = new List<Site_Placements__c>();
        matchString = '';
        matchString1 ='';
        matchString2 ='';
    }
    //Declare variable to be accessible in vf page
    public integer NumberOfSitePlacements;
    public decimal SumOfImpressions;
    public decimal SumOfRequests;
    //end of variable declaration
     //Helper method to fill the aggregate variables
     public void FillAggregates()
     {
           NumberOfSitePlacements = [ Select count()
                    FROM Site_Placements__c where ((Device_Type__c like :matchString) AND (Auto_Play__c like :matchString1) AND (Number_of_Strikes_Given__c like :matchString2)) ];
           SumOfImpressions = [ SELECT sum(impressions__c)
            FROM site_placement_data__c
            WHERE site_placement__c in (select id FROM Site_Placements__c Where ((Device_Type__c like :matchString) AND (Auto_Play__c like :matchString1) AND (Number_of_Strikes_Given__c like :matchString2))) ][0].get('expr0');
           SumOfRequests = [ SELECT sum(requests__c)
            FROM site_placement_data__c
            WHERE site_placement__c in (select id FROM Site_Placements__c Where ((Device_Type__c like :matchString) AND (Auto_Play__c like :matchString1) AND (Number_of_Strikes_Given__c like :matchString2))
) GROUP BY site_placement__c][0].get('expr0');
     }



    public void FetchSPRec(){
        matchString = '%'+sp.Device_Type__c+'%';
        matchString1 = '%'+sp.Auto_Play__c+ '%';
       matchString2 = '%'+sp.Number_of_Strikes_Given__c+ '%';
        spRec=[select Name, total_Impressions__c from Site_Placements__c where
        ((Device_Type__c like :matchString) AND (Auto_Play__c like :matchString1) AND (Number_of_Strikes_Given__c like :matchString2)) ];
      //Dont forget to call the method to fill the aggregates
       FillAggregates();
}
           
     public pagereference CancelSPRec(){
   
    PageReference page = new PageReference('https://cs1.salesforce.com/a36/o');
    page.SetRedirect(true);
    return page;
    }}



Visual force page:

<apex:page controller="siteplacementFetch" tabStyle="Site_Placements__c">
 
    <apex:form >
        <apex:pageMessages />
        <apex:pageBlock >
            <apex:pageBlockButtons location="Top">
          
            <apex:commandButton value="Fetch" action="{!FetchSPRec}"/>

            </apex:pageBlockButtons>

             <apex:pageBlockSection title="Please select the Critirea">
                <apex:pageBlockSectionItem >
                    <apex:outputLabel value="Auto Play" for="autoplay"/>
                    <apex:inputText value="{!sp.Auto_Play__c}" id="autoplay"/>
                </apex:pageBlockSectionItem>
                <apex:pageBlockSectionItem >
                    <apex:outputLabel value="Number of Strikes" for="numberofstrikes"/>
                    <apex:inputField value="{!sp.Number_of_Strikes_Given__c}" id="numberofstrikes"/>
                </apex:pageBlockSectionItem>
                <apex:pageBlockSectionItem >
                    <apex:outputLabel value="Device Type" for="devicetype"/>
                    <apex:inputField value="{!sp.Device_Type__c}" id="devicetype"/>
                </apex:pageBlockSectionItem>
              
              
             </apex:pageBlockSection>
            <apex:pageBlockSection title="Results">
                <apex:pageBlockTable value="{!spRec}" var="site" >
                    <apex:column value="{!site.Name}"/>
                    <apex:column value="{!site.total_Impressions__c}"/>
                   

                 </apex:pageBlockTable>
              
           Number of site placements : {!NumberOfSitePlacements}<br/>
           SumOfImpressions : {!SumOfImpressions}<br/>
           SumOfRequests : {!SumOfRequests}<br/>
      
            </apex:pageBlockSection>
            </apex:pageblock>   
          
        </apex:form>
</apex:page>

sml9099sml9099
Hey , 
Thanks for your reply. Its giving me this error. 
Compile Error: Illegal assignment from Object to Decimal at line 24 column 12


public class siteplacementFetch{
public Site_Placements__c  sp{get;set;}
    public List<Site_Placements__c > spRec{get;set;}
    String matchString;
    String matchString1;
    String matchString2;
    public siteplacementFetch(){
        sp=new Site_Placements__c ();
        spRec = new List<Site_Placements__c>();
        matchString = '';
        matchString1 ='';
        matchString2 ='';
    }
    //Declare variable to be accessible in vf page
    public integer NumberOfSitePlacements;
    public decimal SumOfImpressions;
    public decimal SumOfRequests;
    //end of variable declaration
     //Helper method to fill the aggregate variables
     public void FillAggregates()
     {
           NumberOfSitePlacements = [ Select count()
                    FROM Site_Placements__c where ((Device_Type__c like :matchString) AND (Auto_Play__c like :matchString1) AND (Number_of_Strikes_Given__c like :matchString2)) ];
           SumOfImpressions = [ SELECT sum(impressions__c)
            FROM site_placement_data__c
            WHERE site_placement__c in (select id FROM Site_Placements__c Where ((Device_Type__c like :matchString) AND (Auto_Play__c like :matchString1) AND (Number_of_Strikes_Given__c like :matchString2))) ][0].get('expr0');
           SumOfRequests = [ SELECT sum(requests__c)
            FROM site_placement_data__c
            WHERE site_placement__c in (select id FROM Site_Placements__c Where ((Device_Type__c like :matchString) AND (Auto_Play__c like :matchString1) AND (Number_of_Strikes_Given__c like :matchString2))
) GROUP BY site_placement__c][0].get('expr0');
     }



    public void FetchSPRec(){
        matchString = '%'+sp.Device_Type__c+'%';
        matchString1 = '%'+sp.Auto_Play__c+ '%';
       matchString2 = '%'+sp.Number_of_Strikes_Given__c+ '%';
        spRec=[select Name, total_Impressions__c from Site_Placements__c where
        ((Device_Type__c like :matchString) AND (Auto_Play__c like :matchString1) AND (Number_of_Strikes_Given__c like :matchString2)) ];
      //Dont forget to call the method to fill the aggregates
       FillAggregates();
}
          
     public pagereference CancelSPRec(){
  
    PageReference page = new PageReference('https://cs1.salesforce.com/a36/o');
    page.SetRedirect(true);
    return page;
    }}



Then I try to comment the " sumofimpressions" and "sumofrequests" just to see if its working for " Numberofsiteplacements". It did not give me the error in apex class but it was giving me errors in VF page..

Unknown property 'siteplacementFetch.NumberOfSitePlacements'
Quick Fix Create Apex property 'siteplacementFetch.NumberOfSitePlacements'
Quick Fix Create Apex method 'siteplacementFetch.getNumberOfSitePlacements'



Your help will be highly appreciated.

Thanks



Elie.RodrigueElie.Rodrigue
i wrote that all in notepad and didnt tested.
For both sum, you can do SumOfImpression = (Decimal)theQuery, same for sumofrequest
As for the missing property, add {get;set;} to the declaration in the class.

public class siteplacementFetch{
public Site_Placements__c  sp{get;set;}
    public List<Site_Placements__c > spRec{get;set;}
    String matchString;
    String matchString1;
    String matchString2;
    public siteplacementFetch(){
        sp=new Site_Placements__c ();
        spRec = new List<Site_Placements__c>();
        matchString = '';
        matchString1 ='';
        matchString2 ='';
    }
    //Declare variable to be accessible in vf page
    public integer NumberOfSitePlacements {get;set;}
    public decimal SumOfImpressions{get;set;}
    public decimal SumOfRequests{get;set;}
    //end of variable declaration
     //Helper method to fill the aggregate variables
     public void FillAggregates()
     {
           NumberOfSitePlacements = [ Select count()
                    FROM Site_Placements__c where ((Device_Type__c like :matchString) AND (Auto_Play__c like :matchString1) AND (Number_of_Strikes_Given__c like :matchString2)) ];
           SumOfImpressions = (Decimal)([ SELECT sum(impressions__c)
            FROM site_placement_data__c
            WHERE site_placement__c in (select id FROM Site_Placements__c Where ((Device_Type__c like :matchString) AND (Auto_Play__c like :matchString1) AND (Number_of_Strikes_Given__c like :matchString2))) ][0].get('expr0'));
           SumOfRequests = (Decimal)([ SELECT sum(requests__c)
            FROM site_placement_data__c
            WHERE site_placement__c in (select id FROM Site_Placements__c Where ((Device_Type__c like :matchString) AND (Auto_Play__c like :matchString1) AND (Number_of_Strikes_Given__c like :matchString2))
) GROUP BY site_placement__c][0].get('expr0'));
     }



    public void FetchSPRec(){
        matchString = '%'+sp.Device_Type__c+'%';
        matchString1 = '%'+sp.Auto_Play__c+ '%';
       matchString2 = '%'+sp.Number_of_Strikes_Given__c+ '%';
        spRec=[select Name, total_Impressions__c from Site_Placements__c where
        ((Device_Type__c like :matchString) AND (Auto_Play__c like :matchString1) AND (Number_of_Strikes_Given__c like :matchString2)) ];
      //Dont forget to call the method to fill the aggregates
       FillAggregates();
}
This was selected as the best answer
sml9099sml9099
Hi Elie, 

Thanks a lot for your help. It complelety solved the purpose. :)
I was just wondering , can you help me in adding bar graph in the page. I took help from google but it doesnt seem to help me. i am already fetching the records based on the below query as you can see in the class.

spRec=[select Name, total_Impressions__c from Site_Placements__c where
        ((Device_Type__c like :matchString) AND (Auto_Play__c like :matchString1) AND (Number_of_Strikes_Given__c like :matchString2)) ];

Plot a graph with a X axis ( Name) and Yaxis( Total_Impressions__c).  when fetch button is pressed.

Thanks..
Elie.RodrigueElie.Rodrigue
Have a look at this : http://www.salesforce.com/us/developer/docs/pages/index_CSH.htm#pages_charting.htm Elie Rodrigue Nubik.ca 1-888-NUBIK-55 ext 700