SOQL aggregation with multi-currency

[dhoechst]

I have the following SOQL used in a VisualForce page:

"Select c.Product__r.PA_Product_Class__c productClass, sum(c.Rolling_12_Current__c) sales from PA_Customer_History__c c where c.Account__c = '{!account.Id}' group by c.Product__r.PA_Product_Class__c"

The field Rolling_12_Current__c is a currency field and the records I am querying are all in USD. Our corporate currency is EUR. My user is set up with a currency of USD. When the query is run, the sum() returns back a converted value in EUR instead of USD. According to the documentation at http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_calls_soql_querying_currency_fiel..., this shouldn't happen: "Currency data is converted to the user's locale and then processed by the aggregate function."

My user's currency is in USD, so it should display it properly in USD. As a temporary work around, I've changed our exchange rate to 1. Can someone please let me know how I can get the SOQL aggregate to return the values in my user's currency? I tried using convertCurrency, but I get a malformed query error.

[dhoechst]

Just bumping this. Has anybody else run into this problem?

[Smash]

Hi,

I have the same issue in my Production Org and Sandbox. According to the APEX documentation aggregated currency fields are converted automatically to the running user's currency. However, this is not the case.

SFDC- Developer support suggested to define the class with the "with sharing"-keyword. But this doesn't help either.

I'm now waiting for a response from SFDC-dev support. This seems to be a SFDC-Bug!

I'll keep you in the loop.

Cheers!

[Yishay]

HI Guys,

Do you have any news from SF regarding this bug

Thank you

Yishay

[Smash]

Hi Yishay,

SFDC support told me that they are working on this issue and it should be released in one of following releases.

Cheers

Sascha

[rov]

Does anyone know if this bug has been resolved?

[Cloudy]

Hi,

I just came into the same issue. I opened a case to the support.

Did you get any answers?

Regards,

[rov]

Salesforce premium support calls me every week, only to say that they are monitoring the case and don't know why this is happening. My case has gone from one Developer Support to second to third.

In our case, I have ruled out the possibility of any problems with the formula or trigger. We are trying to rewrite the Apex class. If I have a resolution, shall post it here.

[Cloudy]

Hi,

The Premier Support just gave me a solution.

I have to get the following aggregated result (SUM) from opportunities And copy it in a custom object (Quotation__c) field.

AggregateResult[] QuotedOps = [SELECT quotation__c, SUM(FeeProrated__c) FROM opportunity WHERE quotation__c =: quos
GROUP BY quotation__c];

Here are my quotation__c records:

List<Quotation__c> quotations = [SELECT Id, FeeTotal__c, CurrencyISOCode FROM quotation__c WHERE Id IN: quos];

I get the quotation currency ISO code:

for(Quotation__c quo: quotations){
    string quotationCurrency = quo.CurrencyIsoCode;

I get the conversion rate from my company default currency by querying the currencytype object:

    Double conversionRate = [SELECT conversionrate FROM currencytype WHERE isocode =: quotationCurrency LIMIT 1].conversionRate;

    for(AggregateResult ar: QuotedOps){
        qID = (Id)ar.get('quotation__c');
        if(quo.Id == qID){

Then to get the correct amount, I have to multiply the aggregated sum by the conversion rate to the quotation currency:

            quo.FeeTotal__c = (Decimal)ar.get('Total')*conversionRate;
        }
    }
}

I though that currency conversion was automatic, but it appears that you have to manage it in your code.

I hope this helps.

[dhoechst]

Thanks for the workaround. Looks like they changed the help docs to match functionality. I just posted an idea about this to allow convertCurrency on aggregates: https://sites.secure.force.com/success/ideaView?c=09a30000000D9xtAAC&id=08730000000b1lLAAQ. Vote it up, please!