1

I have the following custom object:

*Object* Revenue__c

*Fields* Date__c, Type__c, Amount__c

Where Type__c is a picklist and can be 'Budget' or 'Actual'.

I'm trying to display this in a VF page so that it has the following columns:

Date | Budget | Actual | Variance(%) | Variance($)

Where the Budget and Actual values are the sum of the Amounts for that type for a given year and the Variances are calculated based on Budget and Actualk. This is similar to how it would appear in a report if I grouped on Date and Type.

How do I do this? Is it possible, or does this require a re-design of the object structure?

My current code:

public RevenueController(){
    List<AggregateResult> result =  [
        SELECT FISCAL_YEAR(Date__c) Date, Type__c, sum(Amount__c) Amount
        FROM Revenue__c
        GROUP BY FISCAL_YEAR(Date__c), Type__c];

        RW = new List<RevenueWrapper>();

        for(AggregateResult ar : result){
            RW.add(new RevenueWrapper(ar));
        }
    }

// Wrapper class to hold AggregateResult for VF page
public class RevenueWrapper{
    public String revenueDate { get; private set; }
    public String type { get; private set; }
    public Integer amount { get; private set; }

    public RevenueWrapper(AggregateResult ar){
        revenueDate = String.valueOf(ar.get('Date'));
        type = (String) ar.get('Type__c');
        amount = Integer.valueOf(ar.get('Amount'));
    }
}

This code results in two rows per year, one row where Type == 'Actual', one where Type == 'Budget'. I want this condensed into a single row with a column for 'Actual' and a column for 'Budget'.

EDIT:
Thanks for the input. These ideas got me thinking and I managed to solve it by creating 2 new formula fields called 'Budget' and 'Actual'. They look at the 'Type' to determine whether the Amount should be included.
(ie. in the Budget formula: IF(ISPICKVAL(Type__c, "Budget"), Amount__c, 0)

I don't really like this approach as it's not scalable and feels like a hack, but I couldn't get the Budget and Actual split to line up with JKraybill's suggestion.

If anyone knows how to do this, I'd be very grateful.

  • Hi Nick, Did you have a try at using my answer as it should get you the budget and actual (and variances) on the same line? – Jon Hazan Sep 25 '12 at 10:34
  • I don't think I tried it properly, but I've now managed to get it working using your method with a few changes. The variance amounts I needed to place in a separate get method, otherwise it would try to calculate this before both 'budget' and 'amount' values were set. – Nick Cook Sep 28 '12 at 0:34
  • I'm glad it worked for you (with a little encouragement). Im surprised you had to make the variance calculate in a separate function, but please feel free to edit my answer with your changes if you think they will help someone else. – Jon Hazan Sep 28 '12 at 9:05
  • Yeah, the code deploys correctly, however when accessing it via a VF page it displays a 'System.NullPointerException: Attempt to de-reference a null object' exception, referring to the variance calculation. – Nick Cook Oct 1 '12 at 1:01
  • I think that may be as simple as setting the budget and actual values to 0 in the constructor. – Jon Hazan Oct 1 '12 at 9:40
2

This is untested so you may need to have play, however this should return a List of the RevenueWrapper classes which contains the 4 columns you are looking to use in your VF page:

public class RevenueController{

Map<String, RevenueWrapper> ResultList = new Map<String, RevenueWrapper>(); 

public RevenueController(){
    List<AggregateResult> result =  [
    SELECT FISCAL_YEAR(Date__c) Date, Type__c, sum(Amount__c) Amount
    FROM Revenue__c
    GROUP BY FISCAL_YEAR(Date__c), Type__c];

    for(AggregateResult ar : result){

        RevenueWrapper RW = new RevenueWrapper();

        //If the RevenueWrapper already exists grab it from the Map
        if(ResultList.containsKey(String.valueOf(ar.get('Date')))){
            RW = ResultList.get(String.valueOf(ar.get('Date')));
        }

        //Set the amount
        RW.setAmount(ar);

        //Save back to the Map
        ResultList.put(String.valueOf(ar.get('Date')), RW);
    }
}

public List<RevenueWrapper> getResults() {
    return ResultList.values();
}


public class RevenueWrapper{
    public String revenueDate { get; set; }
    public Integer budgetAmount { get; set; }
    public Integer actualAmount { get; set; }
    public Integer varianceFYPercent { set; }
    public Integer varianceFYAmount { set; }

    public RevenueWrapper(){

    }

    public void setAmount(AggregateResult ar){
        revenueDate = String.valueOf(ar.get('Date'));

            //Set the amount value based on type
        if(ar.get('Type__c') == 'Budget'){
            budgetAmount = Integer.valueOf(ar.get('Amount'));
        }else if(ar.get('Type__c') == 'Actual'){
            actualAmount = Integer.valueOf(ar.get('Amount'));
        }else{ //Catch all for a type that isn't recognised
            return;
        }

    }

    public Integer getVarianceFYAmount(){
        return actualAmount - budgetAmount;
    }

    public Decimal getVarianceFYPercent(){
        if(budgetAmount != 0){
            return Decimal.valueOf((Double.valueOf(actualAmount) / Double.valueOf(budgetAmount)) * 100).setscale(1);
        } else{
            return 0;
        }
    }
}

}

5

Rewrite your wrapper class so that it holds data from two rows: the budget and actual.

public class RevenueWrapper {
    public String revenueDate { get; private set; }
    public Integer budgetAmount { get; private set; }
    public Integer actualAmount { get; private set; }

    public Decimal getVarianceAmount() { // exercise for the reader
    }

    public Decimal getVariancePct() { // exercise for the reader
    }

    /**
     * You'll obviously need to efficiently check through your query results to determine
     * which two AR's to pass in. Also I'm not handling nulls, probably need to.
     */
    public RevenueWrapper(AggregateResult arBudget, AggregateResult arActual) {
        revenueDate = String.valueOf(arBudget.get('Date'));
        budgetAmount = Integer.valueOf(arBudget.get('Amount'));
        actualAmount = Integer.valueOf(arActual.get('Amount'));
    }
}
  • Hi Jer, thanks for the response. It's the splitting into the two ARs that I'm finding hard. I've tried a range of solutions with Maps and Lists to ensure I get the right 'Types' together for the right year, but it's just not working. Any idea what the best way to do this is? I'm assuming I need to group it together by date somehow so I can make sure the right actual/budget pairs together. – Nick Cook Sep 15 '12 at 7:25

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.