Wednesday, November 24, 2010

Displaying Aggregate result on Visualforce using SOQL

There are some who jump out of bath-tub nearly naked when they are highly excited, fortunately I am not one of them, but when I saw the Aggregate functions in Spring 10 release, I was nearly in tears, ok, thats too dramatic but Aggregate functions giving a very beautiful meaning to data altogether.

For those of you who knew SQL you obviously know what aggregate functions are and how they beautify your code. Those who do not know, aggregate functions help you aggregate the date based on certain field.

Say for example, you want to show the count and Opportunities created on a day, you can aggregate them using SOQL.

AggregateResult[] groupedResults  = [SELECT Name, Count(Amount) FROM Opportunity GROUP BY CreatedDate];

So how do we display it on Visualforce:

Just iterate on the AggregateResults and get elements one by one.

for (AggregateResult ar : groupedResults)  {
    System.debug('Created Date' + ar.get('CreatedDate'));
}

A few pointers for debugging:

1. Always use API version 18.0+ while using Aggregate Functions. If you are eclipse, you might want to update it. And frankly if you are still using older version of eclipse, my mind wants to scream and ask you...why?

2. If you are Aggregating using a relationship e.g., Object1__r.object2__c in your SOQL, while fetching from AggregateResults you only need Object2__c, ar.get('Object2__c') I am not really sure why, because I found no documentation supporting this, but it worked for me in the code.


Thats it for today,
Happy Coding,
 How does a project get to be a year late?... One day at a time.
-- Fred Brooks


P.s. Did you see the new symbol I created for the site? Star wars and cloud in one icon. :D
Share:

1 comment:

  1. List results = [SELECT Date__c, MeasureID__c,SUM(MeasureValue__c) totalMeasureValue
    FROM UserAdoption__c GROUP BY Date__c,MeasureID__c ORDER BY Date__c];

    Date measureDate;
    String measureId;
    String measureName;
    Integer totalMeasureValue;
    for (AggregateResult ar : results){
    //Works:Below line works fine with out namespace
    measureDate= Date.valueOf(ar.get('Date__C'));
    //Not Working:But when i packaged it and give some namespace('XYZ') the above line fails
    // error:invalid field Date__C

    //Works:If prefix with namesapce the above line works

    measureDate= Date.valueOf(ar.get('XYZ__Date__C'));


    }

    ReplyDelete