0

I cant seem to get the table to be grouped by the ownerID and Sum the Amount withoutadjustments

Controller

public class ForecastListController {

private String GroupParameter = 'Ownerid';

public List<ForecastingItem> getForecastingItem() {

List<ForecastingItem> results = Database.query('SELECT id, SUM(AmountWithoutAdjustments), Ownerid, PeriodID, ProductFamily ' + 'FROM ForecastingItem ' + 'Group BY ' + GroupParameter);

return results;}

}

VF Page

<apex:page controller="ForecastListController">


<apex:pageBlock >
    <apex:pageBlockTable value="{!ForecastingItem}" var="FI">
         <apex:column value="{!FI.id}"/>
         <apex:column value="{!FI.ownerid}"/>
         <apex:column value="{!FI.PeriodId}"/>
         <apex:column value="{!FI.ProductFamily}"/>
         <apex:column value="{!FI.AmountWithoutAdjustments}"/>
    </apex:pageBlockTable>
</apex:pageBlock>

</apex:page>

Thank you

5
  • this will give you error. aggregate query return aggregate result not the list of ForecastingItem
    – Ratan Paul
    Oct 6, 2016 at 9:40
  • How can i get the list and the do the grouping within it?
    – user37980
    Oct 6, 2016 at 9:44
  • if you use aggregate query based on ownerId and calculate the amount then you can't show the other fields like id, productfamily and AmountWithoutAdjustments fields
    – Ratan Paul
    Oct 6, 2016 at 9:47
  • Ok, Is there a way to do what im looking at? just group the Forecast items by the owner and show the amounts?
    – user37980
    Oct 6, 2016 at 9:50
  • yeah you can show ownerId and total amounts related to each owner
    – Ratan Paul
    Oct 6, 2016 at 9:51

2 Answers 2

0

So you should return aggregate result from your method

public class ForecastListController {

    private String GroupParameter = 'Ownerid';

        public List<AggregateResult> getForecastingItem() {

            List<AggregateResult> results = Database.query('SELECT  SUM(AmountWithoutAdjustments) total, Ownerid FROM ForecastingItem ' + 
                                                            ' Group BY ' + GroupParameter);

            return results;
        }
    }

And in VF page use like below

<apex:page controller="ForecastListController">
<apex:pageBlock >
    <apex:pageBlockTable value="{!ForecastingItem}" var="FI">

         <apex:column value="{!FI['total']}"/>
         <apex:column value="{!FI['Ownerid']}"/>
    </apex:pageBlockTable>
</apex:pageBlock>
</apex:page>
9
  • Thank you, so there is no way to display the owner. My aim was eventually to have this to be displayed per forecast owner and grouped by product family and bucketed in correct forecast periods. If this isnt possible i will have to look at alternatives
    – user37980
    Oct 6, 2016 at 10:18
  • @user37980 you have ownerId in VF page. You can use another map to get the ower name as well
    – Ratan Paul
    Oct 6, 2016 at 10:20
  • Ok thanks i will have a look into how to do this, sorry if these are silly questions this is quite new to us
    – user37980
    Oct 6, 2016 at 10:20
  • @user37980 yeah no problem
    – Ratan Paul
    Oct 6, 2016 at 10:21
  • 1
    Thank you for this, think this may be beyond my skill will have to get it written for us, thank you for improving my understanding much apprecitated
    – user37980
    Oct 6, 2016 at 10:34
0

Your approach towards aggregate result is wrong. You cant have the id in the query.Also please check periodid,productfamily have only one value when grouped by owner id.Please return a list of aggregateResults rather than returning list,It will result in error Please do the following,

  List<AggregateResult> ForecastingItem= Database.query('SELECT id, SUM(AmountWithoutAdjustments) total, Ownerid, PeriodID, ProductFamily ' + 'FROM ForecastingItem ' + 'Group BY ' + GroupParameter);

Also in the visualforce page

<apex:column value="{!FI.total}"/>

You can also refer to the below url for further information:

http://sfdcintegration.blogspot.in/2014/03/displaying-aggregateresult-on.html http://blog.jeffdouglas.com/2010/04/12/using-aggregateresult-in-salesforce-com-soql/

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .