Magento Reports - Products - Products Ordered issue

Question

The issue: In Magento admin panel, when generating report via Reports - Products - Products Ordered, an error occurs:

**a:5:{i:0;s:877:"SELECT SUM(order_items.qty_ordered) AS `ordered_qty`, `order_items`.`name` AS `order_items_name`, `order_items`.`product_id` AS `entity_id`, `e`.`entity_type_id`, `e`.`attribute_set_id`, `e`.`type_id`, `e`.`sku`, `e`.`has_options`, `e`.`required_options`, `e`.`created_at`, `e`.`updated_at` FROM `sales_flat_order_item` AS `order_items`
 INNER JOIN `sales_flat_order` AS `order` ON `order`.entity_id = order_items.order_id AND `order`.state  'canceled' AND (`order`.created_at BETWEEN '2014-03-03 00:00:00' AND '2014-03-03 23:59:59')
 LEFT JOIN `catalog_product_entity` AS `e` ON (e.type_id NOT IN ('grouped', 'configurable', 'bundle')) AND e.entity_id = order_items.product_id AND e.entity_type_id = 4 WHERE (parent_item_id IS NULL) AND (order_items.store_id IN (1)) GROUP BY `order_items`.`product_id` HAVING (SUM(order_items.qty_ordered) > 0) ORDER BY `ordered_qty` DESC

";i:1;s:6063:"#0 /home/master/public_html/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#1 /home/master/public_html/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#2 /home/master/public_html/lib/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#3 /home/master/public_html/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('SELECT SUM(orde...', Array)
#4 /home/master/public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(428): Zend_Db_Adapter_Pdo_Abstract->query('SELECT SUM(orde...', Array)
#5 /home/master/public_html/lib/Zend/Db/Adapter/Abstract.php(737): Varien_Db_Adapter_Pdo_Mysql->query('SELECT SUM(orde...', Array)
#6 /home/master/public_html/lib/Varien/Data/Collection/Db.php(734): Zend_Db_Adapter_Abstract->fetchAll('SELECT SUM(orde...', Array)
#7 /home/master/public_html/app/code/core/Mage/Eav/Model/Entity/Collection/Abstract.php(1045): Varien_Data_Collection_Db->_fetchAll('SELECT SUM(orde...')
#8 /home/master/public_html/app/code/core/Mage/Eav/Model/Entity/Collection/Abstract.php(871): Mage_Eav_Model_Entity_Collection_Abstract->_loadEntities(false, false)
#9 /home/master/public_html/lib/Varien/Data/Collection.php(741): Mage_Eav_Model_Entity_Collection_Abstract->load()
#10 /home/master/public_html/app/code/core/Mage/Reports/Model/Totals.php(53): Varien_Data_Collection->getIterator()
#11 /home/master/public_html/app/code/core/Mage/Adminhtml/Block/Report/Grid.php(381): Mage_Reports_Model_Totals->countTotals(Object(Mage_Adminhtml_Block_Report_Product_Sold_Grid), '2014-03-03 00:0...', '2014-03-03 23:5...')
#12 /home/master/public_html/app/design/adminhtml/default/default/template/report/grid.phtml(138): Mage_Adminhtml_Block_Report_Grid->getReport('2014-03-03 00:0...', '2014-03-03 23:5...')
#13 /home/master/public_html/app/code/core/Mage/Core/Block/Template.php(241): include('/home/master/pu...')
#14 /home/master/public_html/app/code/core/Mage/Core/Block/Template.php(272): Mage_Core_Block_Template->fetchView('adminhtml/defau...')
#15 /home/master/public_html/app/code/core/Mage/Core/Block/Template.php(286): Mage_Core_Block_Template->renderView()
#16 /home/master/public_html/app/code/core/Mage/Adminhtml/Block/Template.php(81): Mage_Core_Block_Template->_toHtml()
#17 /home/master/public_html/app/code/core/Mage/Core/Block/Abstract.php(919): Mage_Adminhtml_Block_Template->_toHtml()
#18 /home/master/public_html/app/code/core/Mage/Core/Block/Abstract.php(637): Mage_Core_Block_Abstract->toHtml()
#19 /home/master/public_html/app/code/core/Mage/Core/Block/Abstract.php(581): Mage_Core_Block_Abstract->_getChildHtml('grid', true)
#20 /home/master/public_html/app/code/core/Mage/Adminhtml/Block/Widget/Grid/Container.php(77): Mage_Core_Block_Abstract->getChildHtml('grid')
#21 /home/master/public_html/app/design/adminhtml/default/default/template/widget/grid/container.phtml(36): Mage_Adminhtml_Block_Widget_Grid_Container->getGridHtml()
#22 /home/master/public_html/app/code/core/Mage/Core/Block/Template.php(241): include('/home/master/pu...')
#23 /home/master/public_html/app/code/core/Mage/Core/Block/Template.php(272): Mage_Core_Block_Template->fetchView('adminhtml/defau...')
#24 /home/master/public_html/app/code/core/Mage/Core/Block/Template.php(286): Mage_Core_Block_Template->renderView()
#25 /home/master/public_html/app/code/core/Mage/Adminhtml/Block/Template.php(81): Mage_Core_Block_Template->_toHtml()
#26 /home/master/public_html/app/code/core/Mage/Adminhtml/Block/Widget/Container.php(308): Mage_Adminhtml_Block_Template->_toHtml()
#27 /home/master/public_html/app/code/core/Mage/Core/Block/Abstract.php(919): Mage_Adminhtml_Block_Widget_Container->_toHtml()
#28 /home/master/public_html/app/code/core/Mage/Core/Block/Text/List.php(43): Mage_Core_Block_Abstract->toHtml()
#29 /home/master/public_html/app/code/core/Mage/Core/Block/Abstract.php(919): Mage_Core_Block_Text_List->_toHtml()
#30 /home/master/public_html/app/code/core/Mage/Core/Block/Abstract.php(637): Mage_Core_Block_Abstract->toHtml()
#31 /home/master/public_html/app/code/core/Mage/Core/Block/Abstract.php(581): Mage_Core_Block_Abstract->_getChildHtml('content', true)
#32 /home/master/public_html/app/design/adminhtml/default/default/template/page.phtml(74): Mage_Core_Block_Abstract->getChildHtml('content')
#33 /home/master/public_html/app/code/core/Mage/Core/Block/Template.php(241): include('/home/master/pu...')
#34 /home/master/public_html/app/code/core/Mage/Core/Block/Template.php(272): Mage_Core_Block_Template->fetchView('adminhtml/defau...')
#35 /home/master/public_html/app/code/core/Mage/Core/Block/Template.php(286): Mage_Core_Block_Template->renderView()
#36 /home/master/public_html/app/code/core/Mage/Adminhtml/Block/Template.php(81): Mage_Core_Block_Template->_toHtml()
#37 /home/master/public_html/app/code/core/Mage/Core/Block/Abstract.php(919): Mage_Adminhtml_Block_Template->_toHtml()
#38 /home/master/public_html/app/code/core/Mage/Core/Model/Layout.php(555): Mage_Core_Block_Abstract->toHtml()
#39 /home/master/public_html/app/code/core/Mage/Core/Controller/Varien/Action.php(390): Mage_Core_Model_Layout->getOutput()
#40 /home/master/public_html/app/code/core/Mage/Adminhtml/controllers/Report/ProductController.php(93): Mage_Core_Controller_Varien_Action->renderLayout()
#41 /home/master/public_html/app/code/core/Mage/Core/Controller/Varien/Action.php(418): Mage_Adminhtml_Report_ProductController->soldAction()
#42 /home/master/public_html/app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(250): Mage_Core_Controller_Varien_Action->dispatch('sold')
#43 /home/master/public_html/app/code/core/Mage/Core/Controller/Varien/Front.php(172): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
#44 /home/master/public_html/app/code/core/Mage/Core/Model/App.php(354): Mage_Core_Controller_Varien_Front->dispatch()
#45 /home/master/public_html/app/Mage.php(684): Mage_Core_Model_App->run(Array)
#46 /home/master/public_html/index.php(87): Mage::run('', 'store')
#47 {main}";s:3:"url";s:200:"/index.php/admin/report_product/sold/key/529df3bc4e480bd3d0ff55f6c33626ff/filter/cmVwb3J0X2Zyb209MDMlMkYwMyUyRjIwMTQmcmVwb3J0X3RvPTAzJTJGMDMlMkYyMDE1JnJlcG9ydF9wZXJpb2Q9ZGF5/form_key/1PQgaiQ336p4BkAA/";s:11:"script_name";s:10:"/index.php";s:4:"skin";s:5:"admin";}**

Answer 1

You have an error in your generated query. For some reason it is missing an '=' sign (before 'canceled'). Add that in and it should work. Try this in the MySQL command prompt:

SELECT SUM(order_items.qty_ordered) AS `ordered_qty`, `order_items`.`name` AS `order_items_name`, `order_items`.`product_id` AS `entity_id`, `e`.`entity_type_id`, `e`.`attribute_set_id`, `e`.`type_id`, `e`.`sku`, `e`.`has_options`, `e`.`required_options`, `e`.`created_at`, `e`.`updated_at` FROM `sales_flat_order_item` AS `order_items`  INNER JOIN `sales_flat_order` AS `order` ON `order`.entity_id = order_items.order_id AND `order`.state = 'canceled' AND (`order`.created_at BETWEEN '2014-03-03 00:00:00' AND '2014-03-03 23:59:59') LEFT JOIN `catalog_product_entity` AS `e` ON (e.type_id NOT IN ('grouped', 'configurable', 'bundle')) AND e.entity_id = order_items.product_id AND e.entity_type_id = 4 WHERE (parent_item_id IS NULL) AND (order_items.store_id IN (1)) GROUP BY `order_items`.`product_id` HAVING (SUM(order_items.qty_ordered) > 0) ORDER BY `ordered_qty` DESC

Without knowing how your report is generated, it is not easy to take matters further. It is not a known bug. Maybe check which of your files are different from the standard install. For example:

EDIT

Take a fresh dump of your live database and import it into a clean database on your local development machine.

Now do a git checkout to a an earlier commit, when the report was known to work, or if that is not known, checkout to an early commit, e.g. after Magento was freshly installed.

Test to see if the report runs.

If it does, then you now need to checkout a more recent commit. Run the report again. If it is broken then go to an earlier commit. If it is not broken go to a later commit. In this way you can find the code that is breaking your report.

An alternative approach is to do a fresh install of Magento on localhost/your development machine. Now edit the local.xml file to pick up your freshly downloaded live database. If the report works fine with vanilla Magento then you know there is a problem with your codebase. If not then update your question here!!!