Missing product data on Magento reports (bestseller, products ordered) although in database

Question

Yesterday I ran a modified XML export at the Sales -> Orders overview. During this process there was an error and now I have a reporting issue:

My reports seem to be reset and all information prior to the export appears to be missing.

However, everything works fine with the reports in the interval after the incident, so no unusual behaviour - topsellers etc. are shown like you would expect them to.

The following reports are affected (not a complete list):

1. Reports -> Products -> Bestsellers
2. Reports -> Products -> Products Ordered
3. Reports -> Customers -> Customers by Orders Total
4. Reports -> Sales -> Tax

However, for example this report works fine:

• Reports -> Shopping Cart -> [any]

When going to Sales -> Orders I have a complete list of all sales ever done in the shop. Whichever order I open, there is all the required information available (e.g. products bought, subtotal, date etc.).

I read that the

Reports -> Products -> Bestsellers

table is based on the sales_flat_order_item & sales_flat_order table. I checked these, all entries up to the first day of the shop are there.

As far as I can tell, all shop history is available and no data has been deleted. I also tried Reports -> Refresh statistics (lifetime) but it had no effect.

Does anybody know where else to look or what to check?

Answer 1

After some database examination I discovered that in the table sales_flat_order_item the column "state" had Null everywhere whereas the column "status" was fine and had the right information.

I updated the table via SQL

SELECT * FROM sales_flat_order UPDATE sales_flat_order SET state = "processing" WHERE state is Null

and refresehd the lifetime statistics in the backend (reports -> update statistics) and the reports now show everything correctly once more.