Split a single field into 2 columns for a VF page

Question

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.

Answer 1

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;
        }
    }
}

}

Answer 2

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'));
    }
}