What is the intention for the currency calculation in the orders report?

Question

In order to display reports Magento relies on the aggregated data in sales_order_aggregated_created and sales_order_aggregated_updated. In the aggregated data the total of each order are multiplied by the base_to_global_rate of that order, then the order totals are summed up.

When I now access the sales orders report in the global scope it will display the totals of all websites and stores in the global scope currency which is achieved by the multiplication of the order total with the base_to_global_rate. But if I now change the scope to a website with a currency other than the global Magento will calculate the totals back to the currency used in that website. The value used to calculate back from the global currency to the website currency is the current currency rate defined.

But if I update the currency rates on a daily basis the data in the website or store scope will never be shown accurate since the rate used for the aggregated data is the rate of the day when the order was placed and the rate used to calculate back is the current one of when I hit the show report button.

For a less abstract example lets imagine the following currency setup:

• Global: EUR
• Website "shop_gb": GBP
• Website "shop_de": EUR (Use default)

And this currency rate:

	EUR	GBP
EUR	1	0.85
GBP	1.17	1

So if I now place an order with a total of 123 GPB it will be saved to the database with a base_to_global_rate of 1.17 and generating the aggregated data it will save 143.91 for the total. Now accessing the order report in the global scope it will show me 143.91 EUR since the global scope is EUR and the aggregated data is in the global scope currency. When I change the scope to website shop_gb it will show me 122.32 GBP. And lets say I wait a week and the EUR -> GBP rate drops to 0.8 it would show me 115.13 GBP which might be even less accurate because I might have already received the money at the 0.85 rate.

So my question now is what is the intention of this whole calculation? I would have expected to see 123 GBP when looking at the scoped orders report.

With the current setup of Magento just having one (well two based on created_at or updated_at) table for the aggregated data this will always lead to false scoped data. My idea now was to simply put into all the currency rates a 1, then the scopes data would always show the exact amount they payed in the configured currency but in this case the global values would be false since GBP and EUR will be summed up and just displayed as EUR. So how I see it with this setup I either can have accurate global values or accurate scoped values but not both.

Am I misunderstanding or missing something in how Magento handles all of this or am I missing some configuration?

Answer 1

Thanks for your question, you've put a lot of details and it is clear you have experimented various scenarios to work out what is the report doing. Now, the focus of your question is the currency rather than the calculations made.

I have taken a look and it is clear someone from the Magento team would likely be more reliable than myself.

However, I believe you have spotted several facts that we may summarise here:

• first of all, the rates used for the calculation are the rates for each order from base (website currency) to global (system currency) at the date when the order was placed: each rate is specific to the order (not the report) and that means later when the report is processed, each multiplication of the order totals bring the aggregation to a correct sum independently of the rate we would have at another date.
• Also, the fact that for all orders are brought to the global currency, we find ourselves with all the totals sharing one common currency and that is the global one, we are safe to say that all the aggregated totals are all in the same currency and an accountant would be reassured with this

Now, it seems you have understood all the above and my explanations could be tuned to be simpler. However, from here our unknown is whether the aggregation should use a different rate at another date or not where we select a website scope.

My answer is twofold:

1. any Magento system will use a global currency and that currency is the one the various accountant activities will be using to share their financial results. IN short, the fact the global currency is accurate is sufficient since it will be the one all the finance resources will be using
2. when you switch the scope, you'd want to find yourself with the value that was rendered at the time of the order. But, it may be subjective here. I'd like to say the current behaviour is correct as the money paid at the time did generate a different amount (correct) but if an accountant looks at the report at a date when the local rate is different, he would want to see the value that the sales have come up to with a rate that is relevant to the date of the report