Mfrhtyj

We use the Sales orders report to know how many bucks we earn. For this reason we update the statistic every day by a cron job. But the report has wrong values. Nothing it's equal to the values that we get by adding manually or adding by SQL query over the sales_flat_order table (only for status='complete').

The view Sales orders show all orders correctly.

I can't understand why the report supply wrong data. What can I do to fix this problem?

2 issues may be here,

First the timezone one - There’s been an issue for some time with the date pickers in the admin screens being in inconsistent timezones. Internally everything is saved to the database in Universal Time (essentially GMT) and the user facing elements such as date pickers and report results should be shifted to your locale’s time, however some actions haven’t been caught so you’re actually getting GMT based results in some cases.

The bigger issue for reports though is refreshing the statistics. Try hitting

Report > Refresh Statistics

and refreshing them all manually and see if it’s still wrong. Even though the last updated dates look right for me and all cron jobs appear to be working I find I get these same errors unless I do this.

I have found a link that explain the problem with the daily statisctic refresh.

http://www.neptuneweb.com/blog/31-problems-with-order-reporting-in-magento.html

And now I know whats going wrong. The daily refresh script use wrong/not-logical queries. Orders who are not updated on the creation date will be ignored.

!!! The solution is to use the lifetime statistic refresh instead of the daily statistic refresh. !!!

Steps to reproduce:

1. Login to Admin Panel


2. Go to Reports->Sales->Orders screen


3. Filter orders for some date range which should fall in Daylight savings time.


4. Notice the totals which are shown, are not 100% correct.

Expected Result:

The totals in Reports->Sales->Orders screen should match totals in Sales->Orders grid screen when downloading CSV from there. I have attached the issue in detail along with solution in the attached file.

Actual Result:

The totals in Reports->Sales->Orders screen is not coming correct when date range filter falls in daylight savings time.

Magento Version affected:

It seems all CE and EE versions, I only checked CE 1.8, 1.9 and EE 1.13, 1.14

Files Affected:

app/code/core/Mage/Reports/Model/Resource/Report/Abstract.php

app/code/core/Mage/Reports/Model/Mysql4/Report/Abstract.php (In older magento versions using Mysql4 instead of Resource)

Technical Details:

In the file app/code/core/Mage/Reports/Model/Resource/Report/Abstract.php

function: _getTZOffsetTransitions

line 418, $dateTimeObject->set($tr['time']);

I am facing issue for order data(prices) not showing correct in reports grid Reports > Sales > Orders for particular store. all other stores having correct data.

I have selected specific store, data range and click on Show Report button. Order reports grid is showing correct order count but all records having 0 prices.

It is showing correct prices in order grid Sales > Order.

I am having multi website + multi store website.
My all stores are showing correct data(all prices) but all prices for one store is showing price 0 always.

I have checked that order table sales_flat_order is having correct data, but order reports table sales_order_aggregated_created and sales_order_aggregated_updated are having price 0.

Table: sales_flat_order:

Table: sales_order_aggregated_created:

So, as per database, Order is having correct data but reports tables don't have correct data.

I have refreshed lifttime statics every time. it generates new entries but having wrong value for one store.

I have checked Mage_Sales_Model_Resource_Report_Order_Createdat::_aggregateByField method and logged queries, after that I have run that queries to database. It is shoving 0 price for all.

That means There is something wrong when data fetch from order table and add it to reports table.