[SalesForce] Split a single field into 2 columns for a VF page

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.

Best Answer

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

}

Related Topic