+ Start a Discussion
satyareddyksatyareddyk 

How to display AggregateResult data in visualforce page

I'm using AggregateResult in order to return sum of shipments for every combination of customeregion,productfamily and year,here is the query i'm using
apex code:
public AggregateResult[] queryResult { get {return queryResult ;} set{ queryResult = value ;} }
public AggregateResult[] getschemalist22()
{
 queryResult = [select sum(shp_4000__c) salesshipments,sum(shp_4010__c) inventoryshipments,sum(sos_5000__c) salesoutshipments,Time__r.Years__c years,
   Customer__r.Region__c regions, JaguarProduct__r.Family__c families from FACT_LL__c
   group by Time__r.Years__c, Customer__r.Region__c, JaguarProduct__r.Family__c];
 return queryResult;
}

Now i required to display this aggregateresult in my visualforce page pageblocktable like

Customerregion       Productfamily        Year          Measures                                values

ASIA                             prodfamily1             2010         salesshipments                      2000
                                                                                          inventoryshipments                340
                                                                                          salesoutshipments                1090

 

NORTH AMERICA     prodfamily1            2010         salesshipments                      6100
                                                                                          inventoryshipments                900
                                                                                          salesoutshipments                5600

 

which displays 2 records with "sum of shipments for region asia,productfamily1,year 2010" and"sum of shipments for region northamerica,productfamily1,year 2010".

 

please specify if this is the right way to get aggregateresult,if yes please let me know how to display it in visualforce page

as desired or is there any other way to display the result.

Any help is much appreciated.

 

Thanks & Regards,

Satya.

Best Answer chosen by Admin (Salesforce Developers) 
sforce2009sforce2009

Sorry for not observing your mistake. And I have corrected it on my blog as well. Here is the culprit

public List<OppClass> getResults()
{
List<OppClass> lstResult = new List<OppClass>();
for (AggregateResult ar: lstAR)
{
oppClass objOppClass = new oppClass(ar);
lstResult.add(objOppClass);
}

All Answers

sforce2009sforce2009
I have explained it on my blog Aggregate data group by
satyareddyksatyareddyk

Hi Srinivas,

 Thanks for ur reply,but unfortunately script that you specified in your blog is not working for me.

It is throwing error as "Error: Invalid field Total for SObject AggregateResult"

Below is your suggested code that i used

 

public class TestGroupBy {
public List<AggregateResult> lstAR = new List<AggregateResult>();
public TestGroupBy()
{
lstAR = [SELECT CloseDate, COUNT(id) Total FROM Opportunity GROUP BY CloseDate];

}

public List<AggregateResult> getResults()
{
List<AggregateResult> lstResult = new List<AggregateResult>();
for (AggregateResult ar: lstAR)
{
oppClass objOppClass = new oppClass(ar);
lstResult.add(ar);
}
return lstResult;
}

class oppClass
{
public Integer Total
{ get;set; }

public Date CloseDate
{ get;set; }

public oppClass(AggregateResult ar)
{
Total = (Integer)ar.get('Total');
CloseDate = (Date)ar.get('CloseDate');
}
}
}

The only change I have done to your code is,In your script you have defined every list (for example) as below

public list lstAR = new list(); 

but this is throwing error when i am trying to save it,so I tried using List<AggregateResult> everywhere instead of just list,

As I am new to salesforce,I am unable to find the reason.

So please kindly suggest me how this will work, if possible can you send me a sample code that  gets me out.

 

Thanks,

Satya.

sforce2009sforce2009

If you are using eclipse older versions, it will not work because it is spring'10 feature. You have to save it from browser. or new eclipse IDE (I think it has all the ingredients need, not sure though)

satyareddyksatyareddyk

I saved the code from browser only,i am not using ecclipse IDE for this.But still it is showing the same error.

Please kindly help me with this one.

 

Thanks,

Satya. 

sforce2009sforce2009

Sorry for not observing your mistake. And I have corrected it on my blog as well. Here is the culprit

public List<OppClass> getResults()
{
List<OppClass> lstResult = new List<OppClass>();
for (AggregateResult ar: lstAR)
{
oppClass objOppClass = new oppClass(ar);
lstResult.add(objOppClass);
}

This was selected as the best answer
satyareddyksatyareddyk

Thank You Srinivas that worked like a treat.

 

Thanks,

Satya.

vasuvasu

Hi All,

 

I have 2 different Enter Prise Editions

 

AggregateResult not working in One EnterPrise Edition,

In Another Enter Prise Edition working AggregateResult Function Object ,

 

Y it shows  lilke ,Any Idea

 

Thanks

Vasu

satyareddyksatyareddyk

Hi Srinivas,

 

The script you suggested is working fine when it comes to only display,

But the problem i am facing is i need to display my aggregateresult based on user selection,

i.e; i had three dropdowns for customer,product and time.user will select region from customer,family from product,year from time dropdowns respectively and then we need to display aggrereate result.

Your code is working fine(for me) without any selection,but when i select above criteria and tried to display the result it doesn't work.

Do you have any idea about why it is happening.

 

Thanks,

Satya.

ttlttl

Hi guys,

I am getting error: Unknown property 'Accountscore_card.AccountSum.total1'  in my visualforce page  

VisualForce page is as follow:-


 <apex:page controller="Accountscore_card">
  <apex:form >
   <apex:pageBlock title="Account's spend on Telecom (Rs. Crore)">
   <apex:pageBlockSection title="Details">       
<apex:dataTable value="{!accountSumlist}" var="test" border="1" style="height:20;float: left;">
          <apex:column headerValue="Wireline Voice" width="5px">
             <apex:outputText value="{!test.total1}" style="align: center" />
          </apex:column>
          <apex:column headerValue="Wireline Data" width="5px" >
              <apex:outputText value="{!test.total2}"/>
          </apex:column>
                    <apex:column headerValue="Wireless Voice" width="5px" >
             <apex:outputText value="{!test.total3}"/>
          </apex:column>
          <apex:column headerValue="Wireless Data" width="5px" >
             <apex:outputText value="{!test.total4}"/>
          </apex:column>
          <apex:column headerValue="Enterprise Solutions" width="5px" >
              <apex:outputText value="{!test.total5}"/>
          </apex:column>
     </apex:dataTable>
    </apex:pageBlockSection>
  </apex:pageBlock>
</apex:form>
</apex:page>

 

And controller is,

public class AccountScore_card{
public AggregateResult SumResults =
      [SELECT SUM(Account_Score__c.wireline_voice__c) total1,
        SUM(Account_Score__c.wireline_data__c) total2,
        SUM(Account_Score__c.wireless_voice__c) total3,
        SUM(Account_Score__c.wireless_data__c) total4,
        SUM(Account_Score__c.enterprise__c) total5
        FROM Account_Score__c where Account__c=:str];
 
   public class AccountSum {
   public Double sum1{get;set;}
   //public AccountSum(AggregateResult ar){}
    }
 public List<AccountSum> accountSumlist = new List<AccountSum>();
 public List<AccountSum> getAccountSumlist(){
    Map<String, String> KeyNames = new Map<String, String>();
    KeyNames.put('total1', 'wirelinevoice');
    KeyNames.put('total2', 'wirelinedata');
    KeyNames.put('total3', 'wirelessvoice');
    KeyNames.put('total4', 'wirelessdata');
    KeyNames.put('total5', 'enterprise');
   for (String key : KeyNames.keySet()) {
      AccountSum cs = new AccountSum();
      cs.sum1 = (Double) SumResults.get(key);
      accountSumList.add(cs);
      }
      return AccountSumList;

  }
}

Guys my requirement is to show the total sum of field Wireline Voice, wireline data, wireless data wireless voice and enterprise. If any other way is possible to do this please share.

Any Suggestion would be appreciate.
Thanks,
Prince Kumar

kkr.devkkr.dev

Hi,

    

    How to display combination of aggregate result data and regular data in visualforce page?

 

 

Thanks

Amit Singh MumbaiAmit Singh Mumbai

Could you please provide me vf page for the same?

ScottishCalvinScottishCalvin

I managed to get this working now if it helps anyone.  In this case, returning production data by company to the visualforce page

 

TheQuery = TheQuery + 'SELECT Company_1__r.Name';
TheQuery = TheQuery + ',SUM(aProduction_2012__c) Production';
TheQuery = TheQuery + ' FROM Production_Site__c';
TheQuery = TheQuery + ' WHERE Product.Name = ' + SelectedOutput ;
TheQuery = TheQuery + ' GROUP BY Company_1__r.Name ORDER BY Company_1__r.Name DESC';
ChartData = Database.query(TheQuery);
for(AggregateResult temp:ChartData)
{
PieData.add(new PieWedgeData(

(String)temp.get('Company_1__r.Name'),
(Integer)temp.get('Production')
)
);
}

 

public class PieWedgeData
{
public string GroupBy { get; set; }
public integer Production { get; set; }


public PieWedgeData(String sGroupBy, Integer iProduction)
{
this.GroupBy = sGroupBy;
this.Production = iProduction;
}
}