Apex Code Development You need to sign in to do that
Don't have an account?
satyareddyk 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.
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
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.
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)
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.
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);
}
Thank You Srinivas that worked like a treat.
Thanks,
Satya.
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.
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
Hi,
How to display combination of aggregate result data and regular data in visualforce page?
Thanks
Could you please provide me vf page for the same?
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;
}
}