Apex Code Development You need to sign in to do that
Don't have an account?
sai harish Need help with Wrapper Class for AggregateResult queries
Hello,
I am trying to use a HTML table and I need to show some values by each month. I am planning to use 12 queries, 1 for each column. I can use only 1 apex:repeat for the table to maintain consistency in the table. I am kind of a beginer in salesforce. I am not understanding how to wrap all these queries with 1 wrapper class. I am attaching a screenshot of what I am looking for. Any help is Appreciated !!
I am trying to use a HTML table and I need to show some values by each month. I am planning to use 12 queries, 1 for each column. I can use only 1 apex:repeat for the table to maintain consistency in the table. I am kind of a beginer in salesforce. I am not understanding how to wrap all these queries with 1 wrapper class. I am attaching a screenshot of what I am looking for. Any help is Appreciated !!
Public Class SalesBudgetReport1{
Public List<RevenueForecast> RevenueForecasts {get; set;}
Public SalesBudgetReport1(){
List<AggregateResult> ar = [Select PM__c, sum(Revenue__c) rev from Revenue_Forecast__c WHERE PM__c!= null AND calendar_year(Forecast_Date__c ) = 2018 AND calendar_month(Forecast_Date__c ) = 1 Group By PM__c];
List<AggregateResult> ar1 = [Select PM__c, sum(Revenue__c) revenue1 from Revenue_Forecast__c WHERE PM__c!= null AND calendar_year(Forecast_Date__c ) = 2018 AND calendar_month(Forecast_Date__c ) = 2 Group By PM__c];
List<AggregateResult> ar2 = [Select PM__c, sum(Revenue__c) revenue2 from Revenue_Forecast__c WHERE PM__c!= null AND calendar_year(Forecast_Date__c ) = 2018 AND calendar_month(Forecast_Date__c ) = 3 Group By PM__c];
RevenueForecasts = new List<RevenueForecast>();
for(AggregateResult arr : ar){
RevenueForecasts.add(new RevenueForecast(arr));
}}
Public class RevenueForecast{
Public String Name {get; set;}
Public Decimal Revenue{get; set; }
Public Decimal Revenue1{get; set; }
Public RevenueForecast(AggregateResult arr){
Name = (String) arr.get('PM__c');
Revenue = (Decimal) arr.get('rev');
}}}
Public Class SalesBudgetReport1{ Public List<RevenueForecast> RevenueForecasts {get; set;} Public SalesBudgetReport1(){ List<AggregateResult> ar = [Select PM__c pm, sum(Revenue__c) rev from Revenue_Forecast__c WHERE PM__c!= null AND calendar_year(Forecast_Date__c ) = 2018 AND calendar_month(Forecast_Date__c ) = 1 Group By PM__c]; List<AggregateResult> ar1 = [Select PM__c pm, sum(Revenue__c) rev from Revenue_Forecast__c WHERE PM__c!= null AND calendar_year(Forecast_Date__c ) = 2018 AND calendar_month(Forecast_Date__c ) = 2 Group By PM__c]; List<AggregateResult> ar2 = [Select PM__c pm, sum(Revenue__c) rev from Revenue_Forecast__c WHERE PM__c!= null AND calendar_year(Forecast_Date__c ) = 2018 AND calendar_month(Forecast_Date__c ) = 3 Group By PM__c]; RevenueForecasts = new List<RevenueForecast>(); for(AggregateResult arr : ar){ RevenueForecasts.add(new RevenueForecast(arr)); }} Public class RevenueForecast{ Public String Name {get; set;} Public Decimal Revenue{get; set; } Public Decimal Revenue1{get; set; } Public RevenueForecast(AggregateResult arr){ Name = (String) arr.get('pm'); Revenue = (Decimal) arr.get('rev'); }}}Thank you fo the reply. I am able to show the names. My problem is with showing values from ar1 and ar2. I am not understanding if I have to use a different method for each query or if I can do it in the same method ?
for(AggregateResult arr : ar){ RevenueForecasts.add(new RevenueForecast(arr)); } for(AggregateResult arr1 : ar1){ RevenueForecasts.add(new RevenueForecast(arr1)); } Public class RevenueForecast{ Public String Name {get; set;} Public Decimal Revenue{get; set; } Public Decimal Revenue1{get; set; } Public RevenueForecast(AggregateResult arr){ Name = (String) arr.get('pm'); Revenue = (Decimal) arr.get('rev'); } Public RevenueForecast(AggregateResult arr1){ Name = (String) arr1.get('pm'); Revenue = (Decimal) arr1.get('rev'); } }When I try to do this, it is giving me an error saying method already defined. Can I do it in one method ? I am confused.Thanks.
Sai.
Here is my Apex class and VF page. I need to use 12 different queries and each query has to go in to 1 <td>. Thank you for all the help Raj !!
Apex Class: Public Class SalesBudgetReport1{ Public List<RevenueForecast> RevenueForecasts {get; set;} Public SalesBudgetReport1(){ List<AggregateResult> ar = [Select PM__c PM, sum(Revenue__c) rev from Revenue_Forecast__c WHERE PM__c!= null AND calendar_year(Forecast_Date__c ) = 2018 AND calendar_month(Forecast_Date__c ) = 1 Group By PM__c]; List<AggregateResult> ar1 = [Select PM__c PM, sum(Revenue__c) revenue1 from Revenue_Forecast__c WHERE PM__c!= null AND calendar_year(Forecast_Date__c ) = 2018 AND calendar_month(Forecast_Date__c ) = 2 Group By PM__c]; List<AggregateResult> ar2 = [Select PM__c PM, sum(Revenue__c) revenue2 from Revenue_Forecast__c WHERE PM__c!= null AND calendar_year(Forecast_Date__c ) = 2018 AND calendar_month(Forecast_Date__c ) = 3 Group By PM__c]; List<AggregateResult> ar2 = [Select PM__c PM, sum(Revenue__c) revenue2 from Revenue_Forecast__c WHERE PM__c!= null AND calendar_year(Forecast_Date__c ) = 2018 AND calendar_month(Forecast_Date__c ) = 4 Group By PM__c]; List<AggregateResult> ar2 = [Select PM__c PM, sum(Revenue__c) revenue2 from Revenue_Forecast__c WHERE PM__c!= null AND calendar_year(Forecast_Date__c ) = 2018 AND calendar_month(Forecast_Date__c ) = 5 Group By PM__c]; List<AggregateResult> ar2 = [Select PM__c PM, sum(Revenue__c) revenue2 from Revenue_Forecast__c WHERE PM__c!= null AND calendar_year(Forecast_Date__c ) = 2018 AND calendar_month(Forecast_Date__c ) = 6 Group By PM__c]; List<AggregateResult> ar2 = [Select PM__c PM, sum(Revenue__c) revenue2 from Revenue_Forecast__c WHERE PM__c!= null AND calendar_year(Forecast_Date__c ) = 2018 AND calendar_month(Forecast_Date__c ) = 7 Group By PM__c]; List<AggregateResult> ar2 = [Select PM__c PM, sum(Revenue__c) revenue2 from Revenue_Forecast__c WHERE PM__c!= null AND calendar_year(Forecast_Date__c ) = 2018 AND calendar_month(Forecast_Date__c ) = 8 Group By PM__c]; List<AggregateResult> ar2 = [Select PM__c PM, sum(Revenue__c) revenue2 from Revenue_Forecast__c WHERE PM__c!= null AND calendar_year(Forecast_Date__c ) = 2018 AND calendar_month(Forecast_Date__c ) = 9 Group By PM__c]; List<AggregateResult> ar2 = [Select PM__c PM, sum(Revenue__c) revenue2 from Revenue_Forecast__c WHERE PM__c!= null AND calendar_year(Forecast_Date__c ) = 2018 AND calendar_month(Forecast_Date__c ) = 10 Group By PM__c]; List<AggregateResult> ar2 = [Select PM__c PM, sum(Revenue__c) revenue2 from Revenue_Forecast__c WHERE PM__c!= null AND calendar_year(Forecast_Date__c ) = 2018 AND calendar_month(Forecast_Date__c ) = 11 Group By PM__c]; List<AggregateResult> ar2 = [Select PM__c PM, sum(Revenue__c) revenue2 from Revenue_Forecast__c WHERE PM__c!= null AND calendar_year(Forecast_Date__c ) = 2018 AND calendar_month(Forecast_Date__c ) = 12 Group By PM__c]; RevenueForecasts = new List<RevenueForecast>(); for(AggregateResult arr : ar){ RevenueForecasts.add(new RevenueForecast(arr)); } for(AggregateResult arr1 : ar1){ RevenueForecasts.add(new RevenueForecast(arr1)); } for(AggregateResult arr2 : ar2){ RevenueForecasts.add(new RevenueForecast(arr2)); } } Public class RevenueForecast{ Public String Name {get; set;} Public Decimal Revenue{get; set; } Public Decimal Revenue1{get; set; } Public RevenueForecast(AggregateResult arr){ Name = (String) arr.get('PM'); Revenue = (Decimal) arr.get('rev'); } Public RevenueForecast(AggregateResult arr1){ Name = (String) arr1.get('PM'); Revenue = (Decimal) arr1.get('rev'); } Public RevenueForecast(AggregateResult arr2){ Name = (String) arr2.get('PM'); Revenue = (Decimal) arr2.get('rev'); } } } VF Page: <apex:page controller="SalesBudgetReport1" showHeader="false"> <apex:form > <script src="http://code.jquery.com/jquery-1.11.3.min.js"></script> <link href="https://nightly.datatables.net/css/jquery.dataTables.css" rel="stylesheet" type="text/css" /> <script src="https://nightly.datatables.net/js/jquery.dataTables.js"></script> <script> $(document).ready( function () { var table = $('#example').DataTable() }); </script> <style> table{ border-collapse:collapse; width: 100%; } th, td { padding: 4px; border: 1px solid black; Padding-left: 8px; Padding-right: 8px; padding-top: 8px; padding-bottom: 8px; } </style> <apex:pageBlock > <body> <table id="example" class="display nowrap"> <thead> <th>Project Manager</th> <th>January</th> <th>February</th> <th>March</th> <th>April</th> <th>May</th> <th>June</th> <th>July</th> <th>August</th> <th>September</th> <th>October</th> <th>November</th> <th>December</th> </thead> <apex:repeat value="{!RevenueForecasts}" var="a"> <tr> <td>{!a['Name']}</td> <td>{!a['Revenue']}</td> <td></td> <td></td> <td></td> <td></td> <td></td> <td></td> <td></td> <td></td> <td></td> <td></td> <td></td> </tr> </apex:repeat> </table> </body> </apex:pageBlock> </apex:form> </apex:page>Public Class SalesBudgetReport1{ Public List<RevenueForecast> RevenueForecasts {get; set;} Public SalesBudgetReport1(){ List<AggregateResult> arList = new List<List<AggregateResult>() ; List<AggregateResult> ar = [Select PM__c PM, sum(Revenue__c) rev from Revenue_Forecast__c WHERE PM__c!= null AND calendar_year(Forecast_Date__c ) = 2018 AND calendar_month(Forecast_Date__c ) = 1 Group By PM__c]; List<AggregateResult> ar1 = [Select PM__c PM, sum(Revenue__c) revenue1 from Revenue_Forecast__c WHERE PM__c!= null AND calendar_year(Forecast_Date__c ) = 2018 AND calendar_month(Forecast_Date__c ) = 2 Group By PM__c]; List<AggregateResult> ar2 = [Select PM__c PM, sum(Revenue__c) revenue2 from Revenue_Forecast__c WHERE PM__c!= null AND calendar_year(Forecast_Date__c ) = 2018 AND calendar_month(Forecast_Date__c ) = 3 Group By PM__c]; List<AggregateResult> ar3 = [Select PM__c PM, sum(Revenue__c) revenue2 from Revenue_Forecast__c WHERE PM__c!= null AND calendar_year(Forecast_Date__c ) = 2018 AND calendar_month(Forecast_Date__c ) = 4 Group By PM__c]; List<AggregateResult> ar4 = [Select PM__c PM, sum(Revenue__c) revenue2 from Revenue_Forecast__c WHERE PM__c!= null AND calendar_year(Forecast_Date__c ) = 2018 AND calendar_month(Forecast_Date__c ) = 5 Group By PM__c]; List<AggregateResult> ar5 = [Select PM__c PM, sum(Revenue__c) revenue2 from Revenue_Forecast__c WHERE PM__c!= null AND calendar_year(Forecast_Date__c ) = 2018 AND calendar_month(Forecast_Date__c ) = 6 Group By PM__c]; List<AggregateResult> ar6 = [Select PM__c PM, sum(Revenue__c) revenue2 from Revenue_Forecast__c WHERE PM__c!= null AND calendar_year(Forecast_Date__c ) = 2018 AND calendar_month(Forecast_Date__c ) = 7 Group By PM__c]; List<AggregateResult> ar7 = [Select PM__c PM, sum(Revenue__c) revenue2 from Revenue_Forecast__c WHERE PM__c!= null AND calendar_year(Forecast_Date__c ) = 2018 AND calendar_month(Forecast_Date__c ) = 8 Group By PM__c]; List<AggregateResult> ar8 = [Select PM__c PM, sum(Revenue__c) revenue2 from Revenue_Forecast__c WHERE PM__c!= null AND calendar_year(Forecast_Date__c ) = 2018 AND calendar_month(Forecast_Date__c ) = 9 Group By PM__c]; List<AggregateResult> ar9 = [Select PM__c PM, sum(Revenue__c) revenue2 from Revenue_Forecast__c WHERE PM__c!= null AND calendar_year(Forecast_Date__c ) = 2018 AND calendar_month(Forecast_Date__c ) = 10 Group By PM__c]; List<AggregateResult> ar10 = [Select PM__c PM, sum(Revenue__c) revenue2 from Revenue_Forecast__c WHERE PM__c!= null AND calendar_year(Forecast_Date__c ) = 2018 AND calendar_month(Forecast_Date__c ) = 11 Group By PM__c]; List<AggregateResult> ar11 = [Select PM__c PM, sum(Revenue__c) revenue2 from Revenue_Forecast__c WHERE PM__c!= null AND calendar_year(Forecast_Date__c ) = 2018 AND calendar_month(Forecast_Date__c ) = 12 Group By PM__c]; arList.addAll(ar) ; arList.addAll(ar1) ; arList.addAll(ar2) ; arList.addAll(ar3) ; arList.addAll(ar4) ; arList.addAll(ar5) ; arList.addAll(ar6) ; arList.addAll(ar7) ; arList.addAll(ar8) ; arList.addAll(ar9) ; arList.addAll(ar10) ; arList.addAll(ar11) ; RevenueForecasts = new List<RevenueForecast>(); for(AggregateResult arr : arList){ RevenueForecasts.add(new RevenueForecast(arr)); } } Public class RevenueForecast{ Public String Name {get; set;} Public Decimal Revenue{get; set; } Public Decimal Revenue1{get; set; } Public RevenueForecast(AggregateResult arr){ Name = (String) arr.get('PM'); if(arr.get('rev')!=null){ Revenue = (Decimal) arr.get('rev'); } if(arr.get('revenue1')!=null){ Revenue1 = (Decimal) arr.get('revenue1'); } } } }Thank you for the reply. I tried to use your code. My Apex class and VF page saved fine. I used Revenue and Revenue1 which are in line 49 and 52, the page saved fine, when I try to preview, it said invalid field rev. so I removed that for loop and tried to used Revenue1. I am getting the same error. Any further ideas and help is appreciated.
Thanks.
Sai.