3

I'm attempting to summarize invoice data by type of invoice grouped by time period. Income_Class__c is a picklist field and upon running this code I get an error: Invalid conversion from runtime type integer to string.

1 - What do I need to modify here to get the correct Income_Class__c picklist value to display?

2 - When this runs I get more results than desired. Any idea how I can make this only create records grouped by Income Class and Month (but also display the calendar year?).

AggregateResult[] RevDataMonth = [SELECT GROUPING(Income_Class__c)         IncomeGroup, CALENDAR_MONTH(Transaction_Date__c) CalMonth, 
                                CALENDAR_YEAR(Transaction_Date__c) CalYear, SUM(Net_Revenue__c) SumRev
                                FROM Invoice__c
                                WHERE Company__c = '001Q000000VDkpq'
                                GROUP BY ROLLUP(Income_Class__c, CALENDAR_MONTH(Transaction_Date__c), CALENDAR_YEAR(Transaction_Date__c))];
{
for (AggregateResult a : RevDataMonth){
        RevRollupAug12__c r = new RevRollupAug12__c();
        r.month__c = (Integer)a.get('CalMonth');
        r.year__c = (Integer)a.get('CalYear');
        r.Amount__c = (Decimal)a.get('SumRev');
        r.company__c = '001Q000000VDkpq'; 
        r.Time_Period__c = 'Month';
        r.Income_Class__c = (String)a.getvalue('IncomeGroup');
        insert r; }}}
1

2 Answers 2

5

The GROUPING() function is used to identify subtotal rows - it identifies subtotal rows in a result set, and returns 1 if the row is a subtotal for the field, and 0 otherwise - it doesn't yield the field value itself.

If you want the sum of the revenue for each combination of calendar month, year and income class, I think the SOQL query is a bit simpler:

AggregateResult[] RevDataMonth = 
   [SELECT CALENDAR_MONTH(Transaction_Date__c) CalMonth, 
        CALENDAR_YEAR(Transaction_Date__c) CalYear, 
        SUM(Net_Revenue__c) SumRev, Income_Class__c 
    FROM Invoice__c 
    WHERE Company__c = '001Q000000VDkpq'
    GROUP BY CALENDAR_MONTH(Transaction_Date__c), 
        CALENDAR_YEAR(Transaction_Date__c), Income_Class__c];
for (AggregateResult a : RevDataMonth) {
    RevRollupAug12__c r = new RevRollupAug12__c();
    r.month__c = (Integer)a.get('CalMonth');
    r.year__c = (Integer)a.get('CalYear');
    r.Amount__c = (Decimal)a.get('SumRev');
    r.company__c = '001Q000000VDkpq'; 
    r.Time_Period__c = 'Month';
    r.Income_Class__c = (String)a.get('Income_Class__c');
    insert r; 
}
2
  • 1
    @MMcNamara : if this solved your question please accept the answer so others know you've been helped. ( you can do this by clicking the (V) icon to the left of metadaddy's answer.) Sep 4, 2012 at 7:30
  • 1
    Done - thx for the tip
    – MMcNamara
    Sep 4, 2012 at 13:33
0

I don't think you can do a simple casting from a numeric type to String. Have you tried this:

r.Income_Class__c = String.valueOf(a.getvalue('IncomeGroup'));

2
  • No luck - "Method does not exist or incorrect signature: [SOBJECT:AggregateResult].getvalue(String)"
    – MMcNamara
    Sep 4, 2012 at 1:19
  • My bad. r.Income_Class__c = String.valueOf(a.get('IncomeGroup'));
    – pchittum
    Oct 14, 2012 at 22:56

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .