+ Start a Discussion
Travis Malle 9Travis Malle 9 

WrapperList of AggregateResults

Hello All,
I'm attempting to write a class that will allow me to take AggregateResults from two unrelated objects and list them on a visualforce page for display and charting. I understand that I will need to use a wrapper class to accomplish this, but for the life of me, I can’t get it to work the way I expect. Although I am getting the information to pass to my visualforce page, when displayed, it shows the entire list in one column as opposed to being iterated. I have been reading the docs for a while now and can’t seem to find out what I’m doing wrong. Any guidance would be greatly appreciated

Apex:
public class ErrorTableClass3 {
    
	public ErrorTableClass3(){
        getWrapperC();
    }
    
    public list<WrapperClass> ErrorTable {get; set;}
    
    public class WrapperClass{
    	public list<integer> Mem {get; set;}
        public list<integer> Err {get; set;}
        public list<string> Errmon {get; set;}
        public list<string> MemMonth {get; set;}
            
        public WrapperClass (list<string> ErrorMonth, list<string> Memmonth, list<integer> Memberships, list<integer> Errors){
            Mem = Memberships;
            Err = Errors;
            ErrMon = ErrorMonth;
            MemMonth = MemMonth;
        }     
    }
  
    Public list<WrapperClass> getWrapperC(){

        list<AggregateResult> MembershipAggList = new list<AggregateResult>([SELECT SUM(New_Memberships__c) Memberships, Calendar_month(event_date__c) EventMonth FROM Campaign WHERE Facilitated_By_MD__C = '005i0000000z0nW'GROUP BY Calendar_month(event_date__c )]);  
        list<AggregateResult> ErrorAggList = new list<AggregateResult>([SELECT count(ID) Errors, Calendar_month (App_sign_Date__c) Datemonth FROM Missing_Info_Errors__C WHERE Officer__c = 'John Smith' AND App_Sign_Date__c = THIS_YEAR GROUP BY Calendar_month(App_Sign_Date__c)]);
        
       	list<integer> MembershipIntList = new list<integer>();
        list<string> MemMonth           = new list<string>();
        list<string> ErrorMonth         = new list<string>();
        list<integer> ErrorIntList      = new list<integer>();
		list<WrapperClass> ErrorTable = new list<WrapperClass>();
        
        for(AggregateResult m : MembershipAggList){
        	MembershipIntList.add(integer.valueOf(m.get('Memberships')));
            MemMonth.add(string.valueOf(m.get('EventMonth')));
       	}
        
        for(AggregateResult e : ErrorAggList){
        	ErrorIntList.add(integer.valueOf(e.get('Errors')));
            ErrorMonth.add(string.valueOf(e.get('DateMonth')));
        }
        

        ErrorTable.add(new WrapperClass(ErrorMonth, MemMonth, MembershipIntList, ErrorIntList));
            return ErrorTable;
        
    }
   
}
Markup:
<apex:page controller="ErrorTableClass3">
    <apex:pageBlock >
			<apex:pageBlockTable value="{!WrapperC}" var="e">
                <apex:column headerValue="Errors" footerValue="Total" value="{!e.Err}" />
                <apex:column headerValue="Memberships" footerValue="Total" value="{!e.Mem}" />
                <apex:column headerValue="Month" footerValue="Total" value="{!e.ErrMon}" />
            </apex:pageBlockTable>
    </apex:pageBlock>
</apex:page>

Result Screenshot
User-added image

 
Best Answer chosen by Travis Malle 9
Jerun JoseJerun Jose
Hi Travis,

In your chart are you trying to plot the number of memberships and errors on the same chart?

If so, the wrapper needs to only hold two integers. You will need a map with the month as the key and the wrapper as the value. Below is my attempt. 

Apex
 
public class WrapperClassIndivialItems{
	public integer Mem {get; set;}
	public integer Err {get; set;}
}

public Map<String,WrapperClassIndivialItems> wrapperMap{get;set;}

public list<String> getMonths(){
	list<String> months = new list<String>();
	list<AggregateResult> MembershipAggList = new list<AggregateResult>([SELECT SUM(New_Memberships__c) Memberships, Calendar_month(event_date__c) EventMonth FROM Campaign WHERE Facilitated_By_MD__C = '005i0000000z0nW' GROUP BY Calendar_month(event_date__c )]);

	list<AggregateResult> ErrorAggList = new list<AggregateResult>([SELECT count(ID) Errors, Calendar_month (App_sign_Date__c) Datemonth FROM Missing_Info_Errors__C WHERE Officer__c = 'John Smith' AND App_Sign_Date__c = THIS_YEAR GROUP BY Calendar_month(App_Sign_Date__c)]);

	wrapperMap = new Map<String,WrapperClassIndivialItems>();

	for(AggregateResult m : MembershipAggList){
		String month = string.valueOf(m.get('EventMonth'));
		Integer mem = integer.valueOf(m.get('Memberships'));
		WrapperClassIndivialItems wi = new WrapperClassIndivialItems();
		months.add(month);
		wi.mem = mem;
		wrapperMap.put(month, wi);
	}

	for(AggregateResult e : ErrorAggList){
		String month = string.valueOf(m.get('Datemonth'));
		Integer err = integer.valueOf(m.get('Errors'));
		WrapperClassIndivialItems wi = wrapperMap.get(month);
		if(wi == null){
			months.add(month);
			wi = new WrapperClassIndivialItems();
		}
		wi.err = err;
		wrapperMap.put(month, wi);
	}
	return months;
}

Visualforce
 
<apex:page controller="ErrorTableClass3">
    <apex:pageBlock >
			<apex:pageBlockTable value="{!months}" var="mon">
                <apex:column headerValue="Errors" footerValue="Total" value="{!wrapperMap[mon].Err}" />
                <apex:column headerValue="Memberships" footerValue="Total" value="{!wrapperMap[mon].mem}" />
                <apex:column headerValue="Month" footerValue="Total" value="{!mon}" />
            </apex:pageBlockTable>
    </apex:pageBlock>
</apex:page>

Hope it helps

All Answers

santanu boralsantanu boral
According to your code, individual items of ErrorTable contains the List of Mem, Err , ErrMon, MemMonth.
Thats why it is showing like that.

I could think to write this code as below.

1. Create a WrapperClassIndivialItems class which will hold individual items.
public class WrapperClassIndivialItems{
    	public integer Mem {get; set;}
        public integer Err {get; set;}
        public String Errmon {get; set;}
        public String MemMonth {get; set;}
            
        public WrapperClassIndivialItems (String ErrorMonth, String Memmonth, Integer Memberships, Integer Errors){
            Mem = Memberships;
            Err = Errors;
            ErrMon = ErrorMonth;
            MemMonth = MemMonth;
        }     
    }

2. I will change the getWrapperC() code as below, it will return a List<WrapperClassIndivialItems>
3. Create a Map wrapperMap like this which will hold row count and row of individual item.
Map<Integer,WrapperClassIndivialItems> wrapperMap = new Map<Integer,WrapperClassIndivialItems>();
4. Populate the map in the loop.
5. return the List<WrapperClassIndivialItems> from Map.
 
public list<WrapperClassIndivialItems> getWrapperC(){

        list<AggregateResult> MembershipAggList = new list<AggregateResult>([SELECT SUM(New_Memberships__c) Memberships, Calendar_month(event_date__c) EventMonth FROM Campaign WHERE Facilitated_By_MD__C = '005i0000000z0nW'GROUP BY Calendar_month(event_date__c )]);  
        list<AggregateResult> ErrorAggList = new list<AggregateResult>([SELECT count(ID) Errors, Calendar_month (App_sign_Date__c) Datemonth FROM Missing_Info_Errors__C WHERE Officer__c = 'John Smith' AND App_Sign_Date__c = THIS_YEAR GROUP BY Calendar_month(App_Sign_Date__c)]);
        
       	//list<integer> MembershipIntList = new list<integer>();
        //list<string> MemMonth           = new list<string>();
        //list<string> ErrorMonth         = new list<string>();
        //list<integer> ErrorIntList      = new list<integer>();		
		//list<WrapperClass> ErrorTable = new list<WrapperClass>();
		
        Map<Integer,WrapperClassIndivialItems> wrapperMap = new Map<Integer,WrapperClassIndivialItems>();
		Integer rowNum = 1;
		
        for(AggregateResult m : MembershipAggList){
        	//MembershipIntList.add(integer.valueOf(m.get('Memberships')));
            //MemMonth.add(string.valueOf(m.get('EventMonth')));
			wrapperMap.put(rowNum, new WrapperClassIndivialItems(null, string.valueOf(m.get('EventMonth')), integer.valueOf(m.get('Memberships')), null));
			rowNum++;
       	}
        rowNum = rowNum -1;
		
		
		Integer iCount = 1;
        for(AggregateResult e : ErrorAggList){
			if(iCount<= rowNum && wrapperMap.size()>0)
			{
				WrapperClassIndivialItems wrObj = (WrapperClassIndivialItems)wrapperMap.get(iCount);
				wrObj.Err = integer.valueOf(e.get('Errors'));
				wrObj.ErrMon = string.valueOf(e.get('DateMonth'));
			}
			else
			{
				wrapperMap.put(iCount, new WrapperClassIndivialItems(string.valueOf(e.get('DateMonth')), null, null, integer.valueOf(e.get('Errors'))));
			}
        	//ErrorIntList.add(integer.valueOf(e.get('Errors')));
            //ErrorMonth.add(string.valueOf(e.get('DateMonth')));
			iCount++;
        }
        return wrapperMap.values();
		
        //ErrorTable.add(new WrapperClass(ErrorMonth, MemMonth, MembershipIntList, ErrorIntList));
            //return ErrorTable;
        
    }

Please note that I have not compiled the code, but tried to put my thoughts into it.

Let me know if it serves your purpose. and if so then mark this answer.

 
Jerun JoseJerun Jose
Hi Travis,

In your chart are you trying to plot the number of memberships and errors on the same chart?

If so, the wrapper needs to only hold two integers. You will need a map with the month as the key and the wrapper as the value. Below is my attempt. 

Apex
 
public class WrapperClassIndivialItems{
	public integer Mem {get; set;}
	public integer Err {get; set;}
}

public Map<String,WrapperClassIndivialItems> wrapperMap{get;set;}

public list<String> getMonths(){
	list<String> months = new list<String>();
	list<AggregateResult> MembershipAggList = new list<AggregateResult>([SELECT SUM(New_Memberships__c) Memberships, Calendar_month(event_date__c) EventMonth FROM Campaign WHERE Facilitated_By_MD__C = '005i0000000z0nW' GROUP BY Calendar_month(event_date__c )]);

	list<AggregateResult> ErrorAggList = new list<AggregateResult>([SELECT count(ID) Errors, Calendar_month (App_sign_Date__c) Datemonth FROM Missing_Info_Errors__C WHERE Officer__c = 'John Smith' AND App_Sign_Date__c = THIS_YEAR GROUP BY Calendar_month(App_Sign_Date__c)]);

	wrapperMap = new Map<String,WrapperClassIndivialItems>();

	for(AggregateResult m : MembershipAggList){
		String month = string.valueOf(m.get('EventMonth'));
		Integer mem = integer.valueOf(m.get('Memberships'));
		WrapperClassIndivialItems wi = new WrapperClassIndivialItems();
		months.add(month);
		wi.mem = mem;
		wrapperMap.put(month, wi);
	}

	for(AggregateResult e : ErrorAggList){
		String month = string.valueOf(m.get('Datemonth'));
		Integer err = integer.valueOf(m.get('Errors'));
		WrapperClassIndivialItems wi = wrapperMap.get(month);
		if(wi == null){
			months.add(month);
			wi = new WrapperClassIndivialItems();
		}
		wi.err = err;
		wrapperMap.put(month, wi);
	}
	return months;
}

Visualforce
 
<apex:page controller="ErrorTableClass3">
    <apex:pageBlock >
			<apex:pageBlockTable value="{!months}" var="mon">
                <apex:column headerValue="Errors" footerValue="Total" value="{!wrapperMap[mon].Err}" />
                <apex:column headerValue="Memberships" footerValue="Total" value="{!wrapperMap[mon].mem}" />
                <apex:column headerValue="Month" footerValue="Total" value="{!mon}" />
            </apex:pageBlockTable>
    </apex:pageBlock>
</apex:page>

Hope it helps
This was selected as the best answer
Travis Malle 9Travis Malle 9
Santanu and Jerun,
Thank you both for you assistance. I was able to get it working as expected.
Jerun JoseJerun Jose
Glad to hear. Please post the working code for the benefit of others and mark the best answer to close the discussion.