+ Start a Discussion
SFDC LSFDC L 

Calculations on 2 separate AggregateResult lists

I'm using AggregateResult to display 2 values, first is a sum of a custom field Hours_Used__c which is in a custom object Holiday__c. Second is a sum of a custom field Hours_Available__c which is on the User object.

I am able to show these results separately on my visualforce page shown here:

vfpage

My requirement is to be able to display Hours_Available__c - Hours_Used__c to leave me with a column that shows the Staff Member Name and the total hours that are left.

For example using the screenshot above I would need a pageblocktable that shows:

Staff Member   | Hours Left
Adam west      | 252
Duke Young     | 225
Jim Taylor       | 198

Controller:
public class HolidayManagementController {
    
    Public List<Holiday__c> HolidayList {get;set;}
    Public List<AggregateResult> HoursUsedList;
    Public List<AggregateResult> AllowanceTotalList;
 
    String cname {get;set;}
    
    Public HolidayManagementcontroller(){
        cname		= UserInfo.getUserName();
        user cu	  	= [SELECT Name FROM User WHERE username =:cname];
        
        HolidayList 		= [SELECT Hours_Used__c, Staff_Member__c, Staff__r.Name, Manager_Name__c
                               FROM Holiday__c
                               WHERE Manager_Name__c =:cu.Name
                               ORDER BY Staff__r.Name];
       
        HoursUsedList 		= [SELECT SUM(Hours_Used__c) hours, Staff__r.Name staff
                               FROM Holiday__c
                               WHERE Manager_Name__c =:cu.Name
                               GROUP BY Staff__r.Name
                               ORDER BY Staff__r.Name asc];
        
        AllowanceTotalList 	= [SELECT SUM(Hours_Available__c) allowance, Name
                              FROM User
                              WHERE Manager__c =:cu.Name
                              GROUP BY Name
                              ORDER BY Name asc];
        
    }
    
    public list<AggregateResult> HoursList {
 	get { return HoursUsedList;}
 	}
    
    public list<AggregateResult> AllowanceList {
 	get { return AllowanceTotalList;}
 	}
}

VF Page:
<apex:page controller="HolidayManagementController" sidebar="false">
    <apex:form>
        <apex:pageBlock>
			<apex:pageBlockTable value="{!HolidayList}" var="hlist">

                <apex:column headerValue="Staff Member">
                <apex:outputField value="{!hlist.Staff__r.Name}"/>
                </apex:column>

                <apex:column headerValue="Manager">
                <apex:outputField value="{!hlist.Manager_Name__c}"/>
                </apex:column>
  
                <apex:column headerValue="Hours Used">
                <apex:outputField value="{!hlist.Hours_Used__c}"/>
                </apex:column>
                
            </apex:pageBlockTable>            
        </apex:pageBlock>
        
        <apex:pageBlock>
        <apex:pageBlockTable value="{!HoursList}" var="h">
        	  
           <apex:column >
            <apex:facet name="header">Staff Member</apex:facet>
            {!h['staff']}</apex:column>
            
            <apex:column >
            <apex:facet name="header">Hours Used</apex:facet>
            {!h['hours']}</apex:column>
            

            
        </apex:pageBlockTable>
            
        <apex:pageBlockTable value="{!AllowanceList}" var="a">
        	  
           <apex:column >
            <apex:facet name="header">Staff Member</apex:facet>
            {!a['Name']}</apex:column>
            
            <apex:column >
            <apex:facet name="header">Hours Available</apex:facet>
            {!a['allowance']}</apex:column>
            

            
        </apex:pageBlockTable>    
            
        </apex:pageBlock>
    </apex:form>
</apex:page>

Any help would be greatly appreciated. I'm pretty new to programming in general so i'm open to suggesstions if i'm going about this the wrong way. 

Thanks
L
Best Answer chosen by SFDC L
pconpcon
This is how I would do it.  By creating a local class to your controller, you can do the data manipulation related to the individual set of data.  I would recommend that if you can you change this to be triggered off of a UID (such as the UserId).  If you were to have multiple people with the same name then their hours would be combined.

Controller
public class HolidayManagementController {
    public List<Holday__c> holidays {
        get;
        private set;
    }

    public class StaffMember {
        public String memberName {
            get;
            private set;
        }
         
        public Decimal hoursUsed {
            get;
            private set;
        }

        public Decimal hoursAvailable {
            get;
            private set;
        }

        public Decimal getHoursRemaining() {
            this.hoursAvailable - this.hoursUsed;
        }

        public StaffMember(String memberName, Decimal hoursUsed, Decimal hoursAvailable) {
            this.memberName = memberName;
            this.hoursUsed = hoursUsed;
            this.hoursAvailable = hoursAvailable
        }
    }   

    public List<StaffMember> staffMembers {
        get;
        private set;
    }

    public HolidayManagementcontroller() {
        this.holidays = [
            select Hours_Used__c,
                Staff_Member__c,
                Staff__r.Name,
                Manager_Name__c
            from Holiday__c
            where Manager_Name__c = :UserInfo.getName()
            order by Staff__r.Name
        ];  

        Map<String, Decimal> nameToHourMap = new Map<String, Decimal>();
        Map<String, Decimal> nameToAllowanceMap = new Map<String, Decimal>();

        for (AggregateResult ar : [
            select sum(Hours_Used__c) hours,
                Staff__r.Name staff
            from Holiday__c 
            where Manager_Name__c = :UserInfo.getName()
            group by Staff__r.Name
            order by Staff__r.Name asc
        ]) {
            nameToHourMap.put((String) ar.get('staff'), (Decimal) ar.get('hours'));
        }

        for (AggregateResult ar : [
            select sum(Hours_Available__c) allowance,
                Name
            from User
            where Manager__c = :UserInfo.getName()
            group by Name
            order by Name asc
        ] {
            nameToAllowanceMap.put((String) ar.get('Name'), (Decimal) ar.get('allowence'));
        }

        this.staffMembers = new List<StaffMembers>();

        for (String name : nameToHourMap.keySet()) {
            this.staffMembers.add(new StaffMember(
                name,
                nameToHourMap.get(name),
                nameToAllowanceMap.get(name)
            ));
        }
    }
}

VisualForce Page
 
<apex:page controller="HolidayManagementController" sidebar="false">
    <apex:form>
        <apex:pageBlock>
            <apex:pageBlockTable value="{!holidays}" var="hlist">
                <apex:column headerValue="Staff Member">
                    <apex:outputField value="{!hlist.Staff__r.Name}"/>
                </apex:column>

                <apex:column headerValue="Manager">
                    <apex:outputField value="{!hlist.Manager_Name__c}"/>
                </apex:column>

                <apex:column headerValue="Hours Used">
                    <apex:outputField value="{!hlist.Hours_Used__c}"/>
                </apex:column>
            </apex:pageBlockTable>
        </apex:pageBlock>
        <apex:pageBlock>
            <apex:pageBlockTable value="{!staffMembers}" var="member">
                <apex:column >
                    <apex:facet name="header">Staff Member</apex:facet>
                    {!member.memberName}
                </apex:column>

                <apex:column >
                    <apex:facet name="header">Hours Used</apex:facet>
                    {!member.hoursUsed}
                </apex:column>
            </apex:pageBlockTable>

            <apex:pageBlockTable value="{!staffMember}" var="member">
                <apex:column >
                    <apex:facet name="header">Staff Member</apex:facet>
                    {!member.memberName}
                </apex:column>

                <apex:column >
                    <apex:facet name="header">Hours Available</apex:facet>
                    {!member.hoursAvailable}
                </apex:column>
            </apex:pageBlockTable>

            <apex:pageBlockTable value="{!staffMember}" var="member">
                <apex:column >
                    <apex:facet name="header">Staff Member</apex:facet>
                    {!member.memberName}
                </apex:column>

                <apex:column >
                    <apex:facet name="header">Hours Remaining</apex:facet>
                    {!member.hoursRemaining}
                </apex:column>
            </apex:pageBlockTable>
        </apex:pageBlock>
    </apex:form>
</apex:page>

NOTE: This code has not been tested and may contain typographical or logical errors

All Answers

pconpcon
This is how I would do it.  By creating a local class to your controller, you can do the data manipulation related to the individual set of data.  I would recommend that if you can you change this to be triggered off of a UID (such as the UserId).  If you were to have multiple people with the same name then their hours would be combined.

Controller
public class HolidayManagementController {
    public List<Holday__c> holidays {
        get;
        private set;
    }

    public class StaffMember {
        public String memberName {
            get;
            private set;
        }
         
        public Decimal hoursUsed {
            get;
            private set;
        }

        public Decimal hoursAvailable {
            get;
            private set;
        }

        public Decimal getHoursRemaining() {
            this.hoursAvailable - this.hoursUsed;
        }

        public StaffMember(String memberName, Decimal hoursUsed, Decimal hoursAvailable) {
            this.memberName = memberName;
            this.hoursUsed = hoursUsed;
            this.hoursAvailable = hoursAvailable
        }
    }   

    public List<StaffMember> staffMembers {
        get;
        private set;
    }

    public HolidayManagementcontroller() {
        this.holidays = [
            select Hours_Used__c,
                Staff_Member__c,
                Staff__r.Name,
                Manager_Name__c
            from Holiday__c
            where Manager_Name__c = :UserInfo.getName()
            order by Staff__r.Name
        ];  

        Map<String, Decimal> nameToHourMap = new Map<String, Decimal>();
        Map<String, Decimal> nameToAllowanceMap = new Map<String, Decimal>();

        for (AggregateResult ar : [
            select sum(Hours_Used__c) hours,
                Staff__r.Name staff
            from Holiday__c 
            where Manager_Name__c = :UserInfo.getName()
            group by Staff__r.Name
            order by Staff__r.Name asc
        ]) {
            nameToHourMap.put((String) ar.get('staff'), (Decimal) ar.get('hours'));
        }

        for (AggregateResult ar : [
            select sum(Hours_Available__c) allowance,
                Name
            from User
            where Manager__c = :UserInfo.getName()
            group by Name
            order by Name asc
        ] {
            nameToAllowanceMap.put((String) ar.get('Name'), (Decimal) ar.get('allowence'));
        }

        this.staffMembers = new List<StaffMembers>();

        for (String name : nameToHourMap.keySet()) {
            this.staffMembers.add(new StaffMember(
                name,
                nameToHourMap.get(name),
                nameToAllowanceMap.get(name)
            ));
        }
    }
}

VisualForce Page
 
<apex:page controller="HolidayManagementController" sidebar="false">
    <apex:form>
        <apex:pageBlock>
            <apex:pageBlockTable value="{!holidays}" var="hlist">
                <apex:column headerValue="Staff Member">
                    <apex:outputField value="{!hlist.Staff__r.Name}"/>
                </apex:column>

                <apex:column headerValue="Manager">
                    <apex:outputField value="{!hlist.Manager_Name__c}"/>
                </apex:column>

                <apex:column headerValue="Hours Used">
                    <apex:outputField value="{!hlist.Hours_Used__c}"/>
                </apex:column>
            </apex:pageBlockTable>
        </apex:pageBlock>
        <apex:pageBlock>
            <apex:pageBlockTable value="{!staffMembers}" var="member">
                <apex:column >
                    <apex:facet name="header">Staff Member</apex:facet>
                    {!member.memberName}
                </apex:column>

                <apex:column >
                    <apex:facet name="header">Hours Used</apex:facet>
                    {!member.hoursUsed}
                </apex:column>
            </apex:pageBlockTable>

            <apex:pageBlockTable value="{!staffMember}" var="member">
                <apex:column >
                    <apex:facet name="header">Staff Member</apex:facet>
                    {!member.memberName}
                </apex:column>

                <apex:column >
                    <apex:facet name="header">Hours Available</apex:facet>
                    {!member.hoursAvailable}
                </apex:column>
            </apex:pageBlockTable>

            <apex:pageBlockTable value="{!staffMember}" var="member">
                <apex:column >
                    <apex:facet name="header">Staff Member</apex:facet>
                    {!member.memberName}
                </apex:column>

                <apex:column >
                    <apex:facet name="header">Hours Remaining</apex:facet>
                    {!member.hoursRemaining}
                </apex:column>
            </apex:pageBlockTable>
        </apex:pageBlock>
    </apex:form>
</apex:page>

NOTE: This code has not been tested and may contain typographical or logical errors
This was selected as the best answer
SFDC LSFDC L
Wow, I spent a short while fixing errors but this pretty much works exactly how i need right out of the box, unbelieveable! Thanks for the UserID tip too, i'll look to implement that next. Again, thank you for taking your time to write this up I really appreciate it. 

Here is my current code (haven't implemented the UserID change as of yet):

(Controller)
public class HolidayManagementController {
    public List<Holiday__c> holidays {
        get;
        private set;
    }

    public class StaffMember {
        public String memberName {
            get;
            private set;
        }
         
        public Decimal hoursUsed {
            get;
            private set;
        }

        public Decimal hoursAvailable {
            get;
            private set;
        }

        public Decimal getHoursRemaining() {
            return (this.hoursAvailable - this.hoursUsed);
        }

        public StaffMember(String memberName, Decimal hoursUsed, Decimal hoursAvailable) {
            this.memberName = memberName;
            this.hoursUsed = hoursUsed;
            this.hoursAvailable = hoursAvailable;
        }
    }   

    public List<StaffMember> staffMembers {
        get;
        private set;
    }

    public HolidayManagementcontroller() {
        holidays = [
            select Hours_Used__c,
                Staff_Member__c,
                Staff__r.Name,
                Manager_Name__c
            from Holiday__c
            where Manager_Name__c = :UserInfo.getName()
            order by Staff__r.Name
        ];  

        Map<String, Decimal> nameToHourMap = new Map<String, Decimal>();
        Map<String, Decimal> nameToAllowanceMap = new Map<String, Decimal>();

        for (AggregateResult ar : [
            select sum(Hours_Used__c) hours,
                Staff__r.Name staff
            from Holiday__c 
            where Manager_Name__c = :UserInfo.getName()
            group by Staff__r.Name
            order by Staff__r.Name asc
        ]) {
            nameToHourMap.put((String) ar.get('staff'), (Decimal) ar.get('hours'));
        }

        for (AggregateResult ar : [
            select sum(Hours_Available__c) allowance,
                Name
            from User
            where Manager__c = :UserInfo.getName()
            group by Name
            order by Name asc
        ]) {
            nameToAllowanceMap.put((String) ar.get('Name'), (Decimal) ar.get('allowance'));
        }

        staffMembers = new List<StaffMember>();

        for (String name : nameToHourMap.keySet()) {
            this.staffMembers.add(new StaffMember(
                name,
                nameToHourMap.get(name),
                nameToAllowanceMap.get(name)
            ));
        }
    }
}
(Visualforce Page)
<apex:page controller="HolidayManagementController" sidebar="false">
    <apex:form>
       
        <apex:pageBlock>
            <apex:pageBlockTable value="{!holidays}" var="hlist">
                <apex:column headerValue="Staff Member">
                    <apex:outputField value="{!hlist.Staff__r.Name}"/>
                </apex:column>

                <apex:column headerValue="Manager">
                    <apex:outputField value="{!hlist.Manager_Name__c}"/>
                </apex:column>

                <apex:column headerValue="Hours Used">
                    <apex:outputField value="{!hlist.Hours_Used__c}"/>
                </apex:column>
            </apex:pageBlockTable>
        </apex:pageBlock>
		
        <apex:pageBlock>
            <apex:pageBlockTable value="{!staffMembers}" var="member">
                <apex:column >
                    <apex:facet name="header">Staff Member</apex:facet>
                    {!member.memberName}
                </apex:column>

                <apex:column >
                    <apex:facet name="header">Hours Used</apex:facet>
                    {!member.hoursUsed}
                </apex:column>
            </apex:pageBlockTable>

            <apex:pageBlockTable value="{!staffMembers}" var="member">
                <apex:column >
                    <apex:facet name="header">Staff Member</apex:facet>
                    {!member.memberName}
                </apex:column>

                <apex:column >
                    <apex:facet name="header">Hours Available</apex:facet>
                    {!member.hoursAvailable}
                </apex:column>
            </apex:pageBlockTable>

            <apex:pageBlockTable value="{!staffMembers}" var="member">
                <apex:column >
                    <apex:facet name="header">Staff Member</apex:facet>
                    {!member.memberName}
                </apex:column>

                <apex:column >
                    <apex:facet name="header">Hours Remaining</apex:facet>
                    {!member.hoursRemaining}
                </apex:column>
            </apex:pageBlockTable>
        </apex:pageBlock>
    </apex:form>
</apex:page>


 
pconpcon
Not a problem at all.  Glad it worked for you.  I'm always happy when it works like I hope when I'm writing the code off the top of my head :)