+ Start a Discussion
domdickdomdick 

Help to calculate subtotal....

Hi,

 

I am working with a client that wants to produce a quote that lists all OpportunityLineItems sub-divded by Product family. I have controller that display Opportunitylineitems on VF page as below...

 

Basic                 1.0  1    03/08/2012  12  USD 149.0  USD 1788.0
                            1.0  12  25/07/2012  10  USD 300.0  USD 3000.0
                            1.0  12  16/08/2012  11   USD 273.0 USD 3003.0

                                                                                              subtotal????

 

Custom              1.0   03/08/2012   USD 1800.0
Single                 1.0    03/08/2012  USD 5500.0
                                                             subtotal????

 

How can i do the subtotal of each product family? Here is the controller that works fine without subtotal.

class:

public class OppsControllerExtension {

    private final Opportunity opps;
    
   public OppsControllerExtension(ApexPages.StandardController stdController) {
        this.opps = (Opportunity)stdController.getRecord();

}

List<OpportunityLineItem> OppsLicence = [SELECT id, ServiceDate, Opportunity.name FROM OpportunityLineItem 
                                         WHERE License_service__c = 1 AND Opportunity.id = 
            :System.currentPageReference().getParameters().get('id')];
   
        public integer getLicenceRowNumber() {
            if (OppsLicence.size() >= 1)
            return OppsLicence.size() - 1;       
           
        else
            return 0;
        }
        public list <OpportunityLineItem> getLicenceServicelist () {
        return [SELECT id, ServiceDate, License_service__c, PricebookEntry.Product2.Quote_service_group__c, PricebookEntry.Product2.Quote_service_schedule__c, Opportunity.name FROM OpportunityLineItem 
                                         WHERE License_service__c = 1 AND Opportunity.id = 
            :System.currentPageReference().getParameters().get('id')];
     }
        
List<OpportunityLineItem> OppsSponsor = [SELECT id, ServiceDate, Opportunity.name FROM OpportunityLineItem 
                                         WHERE Sponsorship_Service__c = 1 AND Opportunity.id = 
            :System.currentPageReference().getParameters().get('id')];
    
     
    public integer getSponsorRowNumber() {
        if (OppsSponsor.size() >= 1) 
                 return OppsSponsor.size() - 1;
        else
            return 0;
        }
    
    public list <OpportunityLineItem> getSponsorServicelist () {
        return [SELECT id, ServiceDate, Sponsorship_Service__c, PricebookEntry.Product2.Quote_service_group__c, PricebookEntry.Product2.Quote_service_schedule__c, Opportunity.name FROM OpportunityLineItem 
                                         WHERE Sponsorship_Service__c = 1 AND Opportunity.id = 
            :System.currentPageReference().getParameters().get('id')];
     }
}

 

Many thanks,

domdickdomdick

Any expert - I would appriciate any help on ths.

Daniel.ReidDaniel.Reid

Hello, 

 

Just to see if I'm following what you're trying to do.

 

For this block --

Basic                 1.0  1    03/08/2012  12  USD 149.0  USD 1788.0
                            1.0  12  25/07/2012  10  USD 300.0  USD 3000.0
                            1.0  12  16/08/2012  11   USD 273.0 USD 3003.0

                                                                                              subtotal 7791


and for this one --

 

Custom              1.0   03/08/2012   USD 1800.0
Single                 1.0    03/08/2012  USD 5500.0
                                                             subtotal 7300

 

Displaying a subtotal for each of these lists (example in red).  Each of these lists are generated from the functions getLicenceServicelist() and getSponsorServicelist(), respectively.  Is that correct?

 

If so, have you tried using the SOQL aggregate function SUM()?   

 

This of course would change the return type of your function to AggregrateResult[] from List<OppurtunityLineItem> and will require a little bit of handling to extract the info for your VF page.

 

Alternatively, you may want to consider putting the query results into a List, doing some summing, storing the sum and returning the list. For example:

public Decimal  serviceListSubTotal=0.0; //if the amount is a double
public list <OpportunityLineItem> getLicenceServicelist () {
        List<OpportunityLineItem> tmpList = [SELECT id, ServiceDate, License_service__c, PricebookEntry.Product2.Quote_service_group__c, PricebookEntry.Product2.Quote_service_schedule__c, Opportunity.name FROM OpportunityLineItem WHERE License_service__c = 1 AND Opportunity.id = :System.currentPageReference().getParameters().get('id')];
        for(OpportunityLineItem opp: tmpList)
        {
           serviceListSubTotal += PricebookEntry.Product2.Quote_service_schedule__c; //If this is the amount field you are trying to sum
        }
        return tmpList;
     }

 

 

 Either way will require some code restructuring to get it just right.

 

Let me know if you would like more information, or if I misunderstood what you are trying to acomplish.

 

Daniel Reid
Contact us - We can help!

Salesforce Superheroes
------------------------------
help@salesforcesuperheroes.com
www.salesforcesuperheroes.com
1-888-407-9578 x102

 

 

 

domdickdomdick

Hi,

 

Yes, the lists are generated from the functions getLicenseServicelist() and getSposorServiceList().

 

I have tried aggregate function SUM() but haven't got any sucess because of lake of exp. in apex code.

 

I have used your example but didn't get through. It came with an error: "variable does not exist: PricebookEntry.Product2.Quote_service_schedule__c"

 

Any solution to make it right?

 

Thanks,