+ Start a Discussion
Travis Malle 9Travis Malle 9 

Parsing grouped results to display in visualforce

Hello Community,
 
I’m attempting to use one SOQL query to get many data point that can be displayed on a Visualforce page. My query returns the expected results but I am not sure of how to parse through the query results and assign them to variables. My thought was that I could simply loop through the results and, based on the conditions, assign them to variables with getters and setters. This does not generate an error, but my page will anything when calling {!MembershipsYTD}. if someone could help point me in the right direction it would be greatly appreciated.

public class MDEventResults 
{

    // Properties
    public integer ThisMonth = date.today().month();
    public integer MembershipsYTD {get; set;}
    public integer MembershipsMTD {get; set;}


    
    // Query Event Data
    List <AggregateResult> EventAgg =  [SELECT Sum(Total_Memberships__c) MembershipSum, count(ID) CNT, Calendar_month(Event_Date__c) Month, Facilitated_by_MD__c FacilitatedBy, Booked_By__c BookedBy,
                                        GROUPING (Facilitated_by_MD__C) FacilGroup, GROUPING (Booked_By__c) BookedGroup, GROUPING (Event_Date__c) monthGroup
                                        FROM Campaign
                                        // WHERE Booked_by__r.id = :UserInfo.getUserid()//
                                        WHERE End_Date_Time__c = This_Year
                                        // AND Status = 'Completed' //
                                        GROUP BY ROLLUP (Facilitated_by_MD__c , Booked_By__c, Event_Date__c)];
    
    // method to parse results
    public void MembershipsYTD() {
        for (AggregateResult ar : EventAgg) {
            if (integer.valueOf(ar.get('monthGroup' )) == ThisMonth &&
                integer.valueOf(ar.get('BookedGroup')) == 1 &&
                integer.valueOf(ar.get('FacilGroup' )) == 1) 
            {
                MembershipsYTD = integer.valueOf(ar.get('MembershipSum'));
                system.debug('MembershipsYTD' + MembershipsYTD);    
            } 
        }
 
    }
}
 
Best Answer chosen by Travis Malle 9
Vivek DVivek D
Just adding code based on your example it may not be exactly what you want but will give you some idea
List<SalesTragetData> targetData {get;set;}

// Some where in the method before the for loop
targetData = new List<SalesTragetData>();
for (AggregateResult ar : EventAgg) {

            if (integer.valueOf(ar.get('monthGroup' )) == ThisMonth &&
                integer.valueOf(ar.get('BookedGroup')) == 1 &&
                integer.valueOf(ar.get('FacilGroup' )) == 1) 
            {
                MembershipsYTD = integer.valueOf(ar.get('MembershipSum'));
                system.debug('MembershipsYTD' + MembershipsYTD);  
                // add values into wrapper
	        targetData.add(new SalesTragetData('JAN',MembershipsYTD))		
            } 
        }
		
		
// WRAPPER CLASS

class SalesTragetData{
	public Integer ytd {get;set;}
	public String month_Name {get;set;}
	
	public SalesTragetData(Integer ytd,String month_Name){
		this.ytd = ytd;
		this.month_Name = month_Name;
	}
}

VF Page
<apex:pageblocktable value="{!targetData}" var="tar">
	<apex:column headerValue="Months" value="{!tar.month_Name}"/>
	<apex:column headerValue="Target" value="{!tar.ytd}"/>
</apex:pageblocktable>

 

All Answers

Vivek DVivek D
Hi Travis,
One way and the most common way for such kind of scenario is 
Use a wrapper class, create the wrapper with both MTD and YTD propertise and populate the list of that wrapper and pass it to your page.
Other way will be to use Analytics API to avoid lot of code. Check out my blog where you can create a report and use it in a VF page
https://vivekdeepak.wordpress.com/2015/06/28/salesforce-analytics-api-with-angularjs-part-1/
 
Travis Malle 9Travis Malle 9
Thank you sir,
 
That’s kinda what I was thinking, I tried to create a wrapper class and pass the results but couldn’t figure out how to make it work (I’m still a novice to ) would you be able to post a dumbed down example of passing this information to the wrapper class?
 
Thank you again sir, your help is greatly appreciated
Vivek DVivek D
Just adding code based on your example it may not be exactly what you want but will give you some idea
List<SalesTragetData> targetData {get;set;}

// Some where in the method before the for loop
targetData = new List<SalesTragetData>();
for (AggregateResult ar : EventAgg) {

            if (integer.valueOf(ar.get('monthGroup' )) == ThisMonth &&
                integer.valueOf(ar.get('BookedGroup')) == 1 &&
                integer.valueOf(ar.get('FacilGroup' )) == 1) 
            {
                MembershipsYTD = integer.valueOf(ar.get('MembershipSum'));
                system.debug('MembershipsYTD' + MembershipsYTD);  
                // add values into wrapper
	        targetData.add(new SalesTragetData('JAN',MembershipsYTD))		
            } 
        }
		
		
// WRAPPER CLASS

class SalesTragetData{
	public Integer ytd {get;set;}
	public String month_Name {get;set;}
	
	public SalesTragetData(Integer ytd,String month_Name){
		this.ytd = ytd;
		this.month_Name = month_Name;
	}
}

VF Page
<apex:pageblocktable value="{!targetData}" var="tar">
	<apex:column headerValue="Months" value="{!tar.month_Name}"/>
	<apex:column headerValue="Target" value="{!tar.ytd}"/>
</apex:pageblocktable>

 
This was selected as the best answer
Travis Malle 9Travis Malle 9
Thank you Vivek,
 
I’m at a bit of a loss on this one… Can you help me understand the ‘why’ behind using a wrapper class. My understanding was that a wrapper class is commonly used when trying to generate lists of various objects. In my scenario, I’m simply trying to pull more than one piece of data from an SOQL query. I am able to accomplish what I want by using a controller that has an SOQL query and a get method for every datapoint I need; although this works, it seems incredibly inefficient. My goal was to use a query leveraging the grouping feature to get more than one data point at a time… feel like I’m missing the mark here. Can you help me understand


Here is the code I’m currently using, it works, but I think it can be done in a more efficient way (less queries)
@testvisible  
Public with sharing class InfoPanelController {

     Public integer PacketsOutstanding = 0;
     public integer PayrollFormsOutstanding = 0;
     public integer BranchReferrals = 0;
     Public integer EventsMTD = 0;
     Public integer ErrorsMTD = 0;
     Public integer ErrorsYTD = 0;
     Public integer MembershipsMTD = 0;
     Public integer MembershipsYTD = 0;
     Public string  UserFullName = UserInfo.getFirstname() + ' ' + UserInfo.getLastname();
        
         
    
List <AggregateResult> PacketResults         =  [SELECT Count(ID)PacketCount FROM Campaign WHERE Facilitated_by_MD__r.id = :UserInfo.getUserId() AND Expecting_packet__c = 'True']; 
    
List <AggregateResult> PayrollResults        =  [SELECT Count(ID)PayrollCount FROM Campaign WHERE Facilitated_by_MD__r.id = :UserInfo.getUserId() AND Payroll_DateStamp__c = null AND   End_Date_Time__c >= Today AND End_Date_Time__c = This_Year AND Status = 'Completed' AND Payroll_Forms__c >0];
                                                  
List <AggregateResult> BranchReferralResults =  [SELECT Count(ID)BranchReferralCount FROM Branch_Referrals__c WHERE Referral_Assigned_to__r.id = :UserInfo.getUserId() AND Referral_Status__c = 'Pending'];
                                                  
List <AggregateResult> EventsMTDResults      =  [SELECT Count(ID)EventsMTDCount FROM Campaign WHERE Booked_by__r.id = :UserInfo.getUserId() AND End_Date_Time__c = This_Month AND status = 'Completed'];                                              
                                                      
List <AggregateResult> ErrorsMTDResults      =  [SELECT Count(ID)ErrorsMTDCount FROM Missing_Info_Errors__c WHERE Officer__c = :userfullname AND App_Sign_Date__c = This_Month AND Known__c = 'Known' ];
    
List <AggregateResult> ErrorsYTDResults      =  [SELECT Count(ID)ErrorsYTDCount FROM Missing_Info_Errors__c WHERE Officer__c = :userfullname AND App_Sign_Date__c = This_Year AND Known__c = 'Known' ];
                                                      
List <AggregateResult> MembershipsMTDResults =  [SELECT Sum(Total_Memberships__c)MembershipsMTDCount FROM Campaign WHERE Booked_by__r.id = :UserInfo.getUserid() AND End_Date_Time__c = This_Month AND Status = 'Completed'];
    
List <AggregateResult> MembershipsYTDResults =  [SELECT Sum(Total_Memberships__c)MembershipsYTDCount FROM Campaign WHERE Booked_by__r.id = :UserInfo.getUserid() AND End_Date_Time__c = This_Year AND Status = 'Completed'];
    
     
       Public integer getpacketsOutstanding(){
            packetsOutstanding = integer.valueof(PacketResults[0].get('PacketCount'));
            Return PacketsOutstanding;
        }
        
        Public integer getPayrollFormsOutstanding(){
            PayrollFormsOutstanding = integer.valueof(PayrollResults[0].get('PayrollCount'));
            Return PayrollFormsOutstanding;
        }
        
        Public integer getBranchReferrals(){
            BranchReferrals = integer.valueof(BranchReferralResults[0].get('BranchReferralCount'));
            Return BranchReferrals;
        }
        
        Public integer getEventsMTD(){
            EventsMTD = integer.valueof(EventsMTDResults[0].get('EventsMTDCount'));
            Return EventsMTD;   
        }
        
        Public integer getErrorsMTD(){
            ErrorsMTD = integer.valueof(ErrorsMTDResults[0].get('ErrorsMTDCount'));
            Return ErrorsMTD;   
        }    
    
        Public integer getErrorsYTD(){
            ErrorsYTD = integer.valueof(ErrorsYTDResults[0].get('ErrorsYTDCount'));
            Return ErrorsYTD;   
        } 
     
        Public integer getMembershipsMTD(){
            MembershipsMTD = integer.valueof(MembershipsMTDResults[0].get('MembershipsMTDCount'));
            If (MembershipsMTD == null)
                MembershipsMTD = 0;
            Return MembershipsMTD;
        }
            
        Public integer getMembershipsYTD(){
            MembershipsYTD = integer.valueof(MembershipsYTDResults[0].get('MembershipsYTDCount'));
            If (MembershipsYTD == null)
                MembershipsYTD = 0;
            Return MembershipsYTD;    
        } 
}