In order to display reports Magento relies on the aggregated data in
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:
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?