Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
bozotheclownbozotheclown 

Displaying Aggregate Result List on VF Page

Hello.  I am trying to display a consolidated list of my customer sales on a VF page.  I am wanting summarize the various values of Bookings__c and Profit__c for each customer number (CustNumber__c).

 

I am pretty sure that I am doing the aggregate result computation properly.  However, I am having issues creating the List that would be referenced on the VF page.  Can someone suggest how to set up the getter?

 

Thanks in advance.

 

 

AggregateResult[] AgR = [SELECT CustNumber__c, SUM(Bookings__c), SUM(Profit__c) FROM IndivOrders__c WHERE Customers__r.id=:CustID GROUP BY CustNumber__c ORDER BY CustNumber__c]; 
for (AggregateResult SalesList : AgR) { 
dddd = String.valueOf(SalesList.get('CustNumber__c'));
xxxx = String.valueOf(SalesList.get('expr0'));
yyyy = String.valueOf(SalesList.get('expr1')); 
}

 

 

JimRaeJimRae

I would use a wrapper class to get the data into a usable list, something like this:

 

 

public class CustSum{
	String Custnumber {get; set;}
	String Bookings {get; set;}
	String Profits {get; set;}
	public CustSum(string c,string b, string p){
		this.CustNumber=c;
		this.Bookings=b;
		this.Profits=p;
	}
}
public List<CustSum> CustSumList = new List<CustSum>();

public List<CustSum> getCustSumOut(){

	AggregateResult[] AgR = [SELECT CustNumber__c, SUM(Bookings__c), SUM(Profit__c) FROM IndivOrders__c WHERE Customers__r.id=:CustID GROUP BY CustNumber__c ORDER BY CustNumber__c]; 
	for (AggregateResult SalesList : AgR) { 
		CustSumList.add(new CustSum(String.valueOf(SalesList.get('CustNumber__c')), String.valueOf(SalesList.get('expr0')), String.valueOf(SalesList.get('expr1'))));
	}
	return CustSumList;
}

 

 

Then, on your page, you need a pageblocktable or datatable to consume the custom wrapper list

 

 

<apex:page controller="mycontroller">
<apex:pageblock title="My Customer Summary">
<apex:pageblocktable value="{!CustSumOut}" var="cso">
<apex:column value="{!cso.Custnumber}" headervalue="Customer Number" />
<apex:column value="{!cso.Bookings}"headervalue="Total Bookings" />
<apex:column value="{!cso.Profits}"headervalue="Total Profits" />
</apex:pageblocktable>
</apex:pageblock>
</apex:page>

 

Hope that helps!

 
bozotheclownbozotheclown

I appreciate the help...but I am now experiencing a strange issue.

 

I am encountering the error "Unknown property 'myController.CustSum.Custnumber'" when I try to save my VF page.

The strange thing is that I know that the aggregateresult computation and variable assignments are working.  The reason why I know this is because I added the text {!CustSumOut} within its own page block.  And as expected, I saw a large block of values such as the below within this page block.

"myController.CustSum:[Bookings=2523.02, Custnumber=23284, Profits=1429.03], myController.CustSum:[Bookings=3789.43, Custnumber=23390, Profits=2777.82].....and so on"

 

In short, the values and variables are being computed...but I just cannot get them to render in a datatable.

 

Any thoughts?  Thanks again.

 
JimRaeJimRae

Can you post your VF page code?
bozotheclownbozotheclown

Sorry.  Should have done that initially.

 

Below, I have added two items to the page.  The {!CustSumOut} lists the 15-or-so individual CustNumber/Bookings/Profits values computed by the aggregate result code.  The {!CustSumOut[2]} lists the three values for the [2] member of the list.

 

<apex:page controller="mycontroller">
"{!CustSumOut}"
<br/>
{!CustSumOut[2]}
<apex:pageblock title="My Customer Summary">
</apex:pageblocktable>
</apex:pageblock>
</apex:page>

 

 
JimRaeJimRae

Your whole page must not have pasted in, as the error you posted earlier makes no sense for that page, since the Custnumber property isn't even referenced.

Are you sure you posted the same page code that was causing the error to occur?
bozotheclownbozotheclown

Oh...I was just trying to show what was successfully working.  In other words, I am able to successfully save the VF page with the code I just displayed (and find it interesting that the specific code is working).

 

The error I am referencing happens when I try to add the below two lines inside of the Page Block.

 

<apex:pageblocktable value="{!CustSumOut}" var="cso">
<apex:column value="{!cso.Custnumber}" headervalue="Customer Number" />

 
JimRaeJimRae

Sorry,

My fault, the String members in the wrapper need to be made public, like this:

 

 

public class CustSum{
	public String Custnumber {get; set;}
	public String Bookings {get; set;}
	public String Profits {get; set;}
	public CustSum(string c,string b, string p){
		this.CustNumber=c;
		this.Bookings=b;
		this.Profits=p;
	}
}
public List<CustSum> CustSumList = new List<CustSum>();

public List<CustSum> getCustSumOut(){

	AggregateResult[] AgR = [SELECT CustNumber__c, SUM(Bookings__c), SUM(Profit__c) FROM IndivOrders__c WHERE Customers__r.id=:CustID GROUP BY CustNumber__c ORDER BY CustNumber__c]; 
	for (AggregateResult SalesList : AgR) { 
		CustSumList.add(new CustSum(String.valueOf(SalesList.get('CustNumber__c')), String.valueOf(SalesList.get('expr0')), String.valueOf(SalesList.get('expr1'))));
	}
	return CustSumList;
}

 

 
bozotheclownbozotheclown

Great...thanks a lot.  I appreciate this.
sfdcFanBoysfdcFanBoy

 

Hey thanks for the post, the code works but it doesn't calculate the aggregates.  Below is the output I have got:

 

My Customer Summary 
Name              Total Booking    Total Profits
  Total Bookings                                   Total Profits
C00015000.02500.0
C123451250.0  250.0
 
All I am looking for is the Sum of the Total Bookings(6250) and Sum of Total Profits(2750) to be displayed.  How do we get the aggregates?
 
Please let me know if I missed anything.  Help!
 
JimRaeJimRae

It should work, would you post your code? Page and controller?
sfdcFanBoysfdcFanBoy

public class OpportunityOverviewInAccount {


    private Account Acc;
    
    public String Custnumber {get; set;}
        public String Bookings {get; set;}
        public String Profits {get; set;}
        public OpportunityOverviewInAccount (string c,string b, string p){
            this.CustNumber=c;
            this.Bookings=b;
            this.Profits=p;
        }


    public OpportunityOverviewInAccount(ApexPages.StandardController controller) {
        this.Acc = (Account)controller.getRecord();

        }
            
    }
    
    public List<OpportunityOverviewInAccount > CustSumList = new List<OpportunityOverviewInAccount>();

    public List<OpportunityOverviewInAccount > getCustSumOut(){
    
        AggregateResult[] AgR = [Select Name,SUM(BPC_Total_Value__c),SUM(BPC_Monthly_Value__c) from Contract__c where Customer_Name__c=:Acc.Id and Status__c=:'Active' GROUP BY Name]; 
        for (AggregateResult SalesList : AgR) { 
            CustSumList.add(new OpportunityOverviewInAccount(String.valueOf(SalesList.get('Name')), String.valueOf(SalesList.get('expr0')), String.valueOf(SalesList.get('expr1'))));
        }
        return CustSumList;
    }
   
}

 

        <apex:pageblock title="My Customer Summary">
        <apex:pageblocktable value="{!CustSumOut}" var="cso">
            <apex:column value="{!cso.Custnumber}" headervalue="Customer Number" />
            <apex:column value="{!cso.Bookings}" headervalue="Total Bookings" />
            <apex:column value="{!cso.Profits}" headervalue="Total Profits" />
        </apex:pageblocktable>
        </apex:pageblock>

 

I just used your code, replaced the query with mine and made some changes in the constructor. 

 

Just to give you background on the objects, i am using Account standard object and contract__c custom object. Its a lookup relationship. contract is the related list. Account standard controller with the above class as extension.

 

 

Thanks a lot!

 

 
JimRaeJimRae

You need to use the GROUP BY ROLLUP if you also want the total of the summarized data.

 

AggregateResult[] AgR = [Select Name,SUM(BPC_Total_Value__c),SUM(BPC_Monthly_Value__c) from Contract__c where Customer_Name__c=:Acc.Id and Status__c=:'Active' GROUP BY ROLLUP Name];

 the last record added to your list should be the rollup, or, if you want to do more logic on it, it would be the one with no value for the Name field.  You could also look at the GROUPING keyword as well, as it can provide a signal for when the totals are being rendered

 
sfdcFanBoysfdcFanBoy

Hey JimRae,

 

Thanks, yes it worked. Need to use GROUP BY ROLLUP.

 

Slight change in the syntax though.

 

AggregateResult[] AgR = [Select Name,SUM(BPC_Total_Value__c),SUM(BPC_Monthly_Value__c) from 
Contract__c where Customer_Name__c=:Acc.Id and Status__c=:'Active' GROUP BY ROLLUP (Name)];

 

 Here is the final output.  The last row is the totals. 

 

Name

BPC

CAB

Coupler

C0001

5000.0

 

3200000.0

C12345

1250.0

 

 

 

6250.0

 

3200000.0

 

The above table looks as if the 'Name' is missing for the last row.  

 

Just to further improve it, can we add the 'Name' for the last row as well, something like 'Totals'/'Grand Totals' or make the entire row 'bold'.   I tried but don't think anything can be done to the last row. 

 

Any Ideas, please ?

 

Thanks a ton

Cheers!

 
JimRaeJimRae

What I would suggest is you do it in the for loop.  Look for the condition where the name field is blank, and then replace that blank with "Total" before you add it to your list.

 

 for (AggregateResult SalesList : AgR) { 
     String tname='';
     if(String.valueOf(SalesList.get('Name'))==''){
        tname='Total';
     }else{
        String.valueOf(SalesList.get('Name'))
     }       
     CustSumList.add(new OpportunityOverviewInAccount(tname, String.valueOf(SalesList.get('expr0')), String.valueOf(SalesList.get('expr1'))));
        }
        return CustSumList;
    }

 
sfdcFanBoysfdcFanBoy

 

 Wonderful.  It works.  The 'Name' is null in the last row.

 

if(String.valueOf(SalesList.get('Name'))==NULL)
        tname='Total';
else
    .......

 

Cheers.  Thanks.

 
sfdcFanBoysfdcFanBoy

Can you help on this as well.. related to AggregateResult again.

 

http://boards.developerforce.com/t5/Visualforce-Development/Group-By-Order-By-LIMIT-for-each-GROUP-BY/m-p/550773/highlight/false#M58973

 

 

Thanks much!
sfdcFanBoysfdcFanBoy

Hi All,

 

I have the following SOQL query to display List of ABCs in my Page block table.

 

Public List<ABC__c> getABC(){
    List<ABC__c> ListABC = [Select WB1__c, WB2__c, WB3__c, Number, tentative__c, Actual__c, PrepTime__c, Forecast__c from ABC__c ORDER BY WB3__c];
    return ListABC;
}

 

Here is the display.

 

As you can see in the above image, WB3 has number of records for each A, B and C. But  I want to display only 1 record for each WB3 based on Actual__c.   Only latest Actual__c must be displayed for each WB3.

 

i.e., Ideally I want to display only 3 rows in this example.

 

For this, I have used GROUPBY and displayed the result using AggregateResults.

 

I got the Latest Actual Date for each WB3.  But the Tentative date is not corresponsding to it.  The Tentative Date is also the MAX in the list. 

 

Every column in the SOQL query must be either GROUPED or AGGREGATED. That's weird.

 

 

I am forced to use MAX() for tentative date.  I want the actual corresponding date related to the MAX Actual Date. Not the Max Tentative Date.

 

here is the code.

public List<SiteMonitoringOverview> getSPM(){
        AggregateResult[] AgR = [Select WB_3__c, MAX(Tentaive_Date__c) dtTentativeDate , MAX(Actual_Date__c) LatestCDate FROM Site_progress_Monitoring__c GROUP BY WBS_3__c];
        
        if(AgR.size()>0){                    
            
            for(AggregateResult SalesList : AgR){                                                        
                CustSumList.add(new SiteMonitoringOverview(String.ValueOf(SalesList.get('WB_3__c')), String.valueOf(SalesList.get('dtTentativeDate')), String.valueOF(SalesList.get('LatestCDate')) ));            
            }    
        }
        return CustSumList;
    }

 

Any help ? How can this be acheived? Please let me know.