6

We have a custom VF page that we use in our Org for time entry, and on that page employees select a project, iteration, and enter their time for that week. Note: some employees might be working on different projects and different iterations in the same week.
I'm supposed to display the total hours ,for each iteration, that has been worked by that employee , so I'm using AggregateResult SUM().

My code for getting the sum is :

list<AggregateResult> iterationResults = [select  SUM(duration__c) sumDuration 
                                          from time_entry__c
                                          where User__c = :UserID 
                                          group by Iteration__c];

I threw a system debug to make sure it works:

        for (AggregateResult ar: iterationResults) {
            decimal dec1 = (Decimal)ar.get('sumDuration');
            system.debug('\n Target is : \n' + dec1);
        }

I'm getting the correct SUM, which is good, but that decimal variable holds the right value but without differentiating between iterations. So here are the two things that i'm stuck on:

  1. How can I assign the SUM of each iteration to a variable where I can pass that value to my VF page?
  2. I understand that I cannot bind VF components to AggregateResult, so I'll need a wrapper class. How is my wrapper class going to look like?

Additional detail that might be helpful is the query I use to get the projects/iterations data from database:

[select  id, Memo__c, project__r.name, iteration__r.name, Milestones_PM_Task__r.name, date__c,  day_of_week__c, duration__c 
 from time_entry__c
 where User__c = :UserID 
 and date__c >= :firstDate and date__c < :lastDate
 order by project__r.name, iteration__r.name, date__c, createddate ]

I really appreciate your effort and help

3

First fine-tune your query so you get the results you're happy with. If you want the split per iteration - probably something like this:

SELECT Iteration__r.Name iName, SUM(duration__c) sumDuration
FROM Time_Entry__c
WHERE User__c = :UserInfo.getUserId()
GROUP BY ROLLUP(Iteration__r.Name)
  1. Eclipse IDE might behave funny when it comes to aggregate queries. Consider using the Workbench, RealForceExplorer or another tool if you'll see columns that appear to be blank.
  2. ROLLUP word is useful if you want to get a grand total row at the bottom. If you don't need it - go with simple GROUP BY Iteration__r.Name
  3. Add more fields as you see fit. Roughly speaking every field you'll add to main SELECT you'll also have to add to GROUP BY clause.

Once you're happy create a wrapper class, I'd go with

public class Wrapper{
    public Decimal duration {get; private set;}
    String iterationName {get; private set;}

    // Constructor
    public Wrapper(AggregateResult ar){
        duration = Decimal.valueOf(ar.get('sumDuration'));
        iterationName = String.valueOf(ar.get('iName'));
    }
}

And then simply loop through the results like you had system.debugs but keep adding Wrappers to a list:

List<Wrapper> wrappers = new List<Wrapper>(); // should be a publicly available variable so either declare it at class level or provide a getter ;)
for (AggregateResult ar: iterationResults) {
    wrappers.add(new Wrapper(ar));
}

Edit (re: comment about recalculating the list of wrappers on save)

Roughly speaking you have 2 options:

  1. Stick the code that queries & recalculates the wrappers (preferably a call to helper function) at the end of the method that's being called when you hit save button. This gets bit more interesting if you were using the {!save} from standard controller, let me know.
  2. Useful especially if it's a throwaway thing - you use this List<Wrapper> only to display it on VF and nowhere else - delete the class variable with the list and create a getter function instead. That way it'll be taken fresh every time VF "gets" the values:

    public List<Wrapper> getWrappers(){
        List<Wrapper> temp = new List<Wrapper>();
        // query here...
        return temp;
    }
    
3
  • Thank you so much, @eyescream for the help. Surprisingly, Eclipse IDE didn't complain about the aggregate query
    – Sem0
    Dec 9 '13 at 16:14
  • Ah, maybe something changed then. I recall spending some time wondering why the columns are blank... Data was flying back OK, it's just that it wasn't rendering properly in the grid view. Was one of reasons I've moved to another editor while prototyping the queries (the fact RFE remembers the history of queries and Apex snippets is a huge boon too). Good luck!
    – eyescream
    Dec 9 '13 at 16:17
  • I'm again looking forward to your help and others' too. Now I have an issue with the total hours for each iteration, when i click 'Save', the total hours is not displayed UNTIL I reload the page. Basically I must rerender a controller method in my VF page code to run the AggregateResult query when I click 'Save'. My question is, how does that controller method will look like that will run the same AggregateResult again when it's called in VF?
    – Sem0
    Dec 11 '13 at 16:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.