Issue with currency conversion

[martin_schffler]

We have a report that aggregates project values for different status of the projects. This looks like this:

	Count	Value
Running Projects	5	1250000 EUR
Completed Projects	23	23324233 EUR
Non-compliant Projects	3	250000 EUR

Now we introduced the possiblity to create projects in a differenct currency (e.g. GBP). We also used automatic currency conversion so that the user sees all values in his currency. So a user with currency GBP will see everything in GBP.

This works fine as long as there are no projects in GBP but as soon as there is a project in GBP the aggregated row gets duplicated:

	Count	Value
Running Projects	5	1000000 GBP
Running Projects	1	25000 GBP
Completed Projects	23	20020500 GBP
Non-compliant Projects	3	200000 GBP

It seems that the automatic currency conversion takes place after the SQL aggergation. The SQL GROUP BY has to have the currency as the field is used in the output and therefore I get two groups of running projects; one with the projects in EUR and one with the projects in GBP.

Is there a way to make the currency conversion in the SQL so that it takes effect before the SQL GROUP BY?

Or to use more general terms: is it possible to aggregate prices with different currencies into one value?

Kind regards,

Martin

[vikram_shanmugasundaram · Accepted solution]

Hi Martin,

Is this a standard query? If so, could you provide the query name?

Regards,

Vikram

[martin_schffler · Accepted solution]

No, this is a custom query.

But in the meantime I found the solution:

There is a built in function that lets you convert the value to one currency. It works like this:

<%SCHEMA%>.FCI_DOC_CUR_CONVERT(PRO.BUDGETED_PRICE, PRO.BUDGETED_OBJECT_ID, <%COMPANY_CURRENCY_OBJ_ID%>)

The first parameter is the value to convert, the second parameter is the currency the value refers to and the third parameter is the currency the value should be converted to.

That way I only get one currency for all the values, as all values are converted to whatever currency is set on the company. Then the aggregation of the values works as expected.