[SalesForce] Help displaying aggregate results in footer of pageblock table

I am trying to create a Visualforce page to display a list of related records (lookup relationship) in a PageBlockTable. I would like to include a footer row that displays the sum of one of the columns for all the records in the resulting list.

I am close, but having trouble with displaying the sum on the footer row.

I have a controller extension that defines which records and fields to include in the list. The extension also defines the Aggregate Results to total the amount for the related records (I have run the query in the query editor, and have confirmed that the resulting 'totalamount' is correct).

The VF page is properly displaying all columns, related records, and the footer row with a label for the Total Amount, but I cannot figure out how to get the 'totalamount' to display.

Here is the code for my VF page.

<apex:page showheader="false" sidebar="false" standardController="MyCustomObject__c" extensions="MyCustomObjectOpportunitiesExtension">
<style>
.fewerMore { display:none;}
</style>
<apex:form >
    <apex:pageBlock id="MyCustomObjectOpptys">
        <apex:pageBlockTable value="{!MyCystomObjectOpptys}" var="opp" id="opppb" rendered="{!NOT(ISNULL(MyCustomObjectOpptys))}">
            <apex:column headerValue="Opportunity"><apex:outputLink value="/{!opp.Id}" target="_parent">{!opp.Name}</apex:outputLink> </apex:column>
            <apex:column headerValue="Account Name"><apex:outputLink value="/{!opp.Account.Id}" target="_parent">{!opp.Account.Name}</apex:outputLink> </apex:column>
            <apex:column value="{!opp.Solution_Type__c}"/>
            <apex:column value="{!opp.StageName}"/>
            <apex:column value="{!opp.CloseDate}">
                <apex:facet name="footer">
                    <apex:outputText value="Total Amount" style="float: right;"/>
                </apex:facet>
            </apex:column>                
            <apex:column value="{!opp.Amount}">
                <apex:facet name="footer">
                    {!sumAmount.totalamount} //this is a holder value, this is where I want the 'totalamount' from the Aggregate Results to display.  I get an "Unknown Property 'MyCustomObject__cStandardController.sumAmount' error.
                </apex:facet>
            </apex:column>
        </apex:pageBlockTable>
    </apex:pageBlock>
</apex:form>    
</apex:page>

Here is the code for my Controller Extension:

public class MyCustomObjectOpportunitiesExtension{

  private List<Opportunity> MyCustomObjectOpptys;
  private MyCustomObject__c MyCustomObject;
public MyCustomObjectOpportunitiesExtension(ApexPages.standardController controller)
    {
    this.MyCustomObject=(MyCustomObject__c)controller.getRecord();
    }

     public List<Opportunity> getMyCustomObjectOpptys()
        {
        SSTProjOpptys = [SELECT Id, Name, Amount, CloseDate, StageName, Solution_Type__c, Account.Name, MyCustomField__c 
        FROM Opportunity 
        WHERE MyCustomField__c = :MyCustomObject.Id ORDER BY Name];
        return MyCustomObjectOpptys;
        }
AggregateResult[] groupedresults
    = [SELECT SUM(Amount)totalamount FROM Opportunity 
        WHERE MyCustomField__c = :MyCustomObject.Id];
        Object sumAmount = groupedResults [0].get('totalamount');
  }

I understand the issue is that the VF page is not understanding the reference to the sumAmount Object in the extension and can't get the value for 'totalamount'.

What I don't understand is how to add another variable to the footer of the pageblocktable so it recognizes the sumAmount and 'totalamount' fields. I know something needs to be added to the VF page, and possibly to the extension.

I am simply stuck. Any help would be greatly appreciated.

Michele

Best Answer

You can convert your object to the a double property with getter setter and display on your page

 //Declare a property at the Top of double data type
public double sumamt{get;set;}

AggregateResult[] groupedresults
= [SELECT SUM(Amount)totalamount FROM Opportunity 
    WHERE MyCustomField__c = :MyCustomObject.Id];
    Object sumAmount = groupedResults [0].get('totalamount');
    sumamt=(double)sumAmount;

The next step would be to use the variable as a merge field on your visualforce

   <apex:column value="{!opp.Amount}">
            <apex:facet name="footer">
                {!sumamt} //this is a holder value, this is where I want the 'totalamount' from the Aggregate Results to display.
            </apex:facet>
Related Topic