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

  • this will give you error. aggregate query return aggregate result not the list of ForecastingItem – Ratan Paul Oct 6 '16 at 9:40
  • How can i get the list and the do the grouping within it? – user37980 Oct 6 '16 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 '16 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 '16 at 9:50
  • yeah you can show ownerId and total amounts related to each owner – Ratan Paul Oct 6 '16 at 9:51
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>
  • 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 '16 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 '16 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 '16 at 10:20
  • @user37980 yeah no problem – Ratan Paul Oct 6 '16 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 '16 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/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.