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