Add SKU column to Best Sellers Report

Question

I need to be able to add an SKU column to the Best Sellers report. I have managed to add the column to the page by adding:

$this->addColumn('sku', array(
            'header'    => Mage::helper('Sales')->__('Skus'),
            'width'     => '100px',
            'index'     => 'skus',
            'type'      => 'text',
        ));

to the _prepareColumns() function but I cannot work out where I need to select the SKUs from in the Mage_Sales_Model_Resource_Report_Bestsellers class.

Anyone have any ideas? Thanks in advance.

EDIT:

Added this join to _prepareColumns but still not getting any data:

$resourceCollection = Mage::getResourceModel($this->getResourceCollectionName()); $resourceCollection->join('catalog/product', 'product_id = entity_id', '*');
$resourceCollection->setPeriod($filterData->getData('period_type')); $resourceCollection->setDateRange($filterData->getData('from', null), $filterData->getData('to', null)); $resourceCollection->addStoreFilter($storeIds); $resourceCollection->setAggregatedColumns($this->_getAggregatedColumns());

Answer 1

What you will want to do is extend the Mage_Adminhtml_Block_Report_Sales_Bestsellers_Grid

Look at that class'es _prepareCollection() method

In your extended class, copy in the _prepareCollection() method, so you have a duplicate of it. Then modify it so that you are joining the product data against the collection.

$resourceCollection = Mage::getResourceModel($this->getResourceCollectionName()); 
$resourceCollection->join('catalog/product', 'product_id = entity_id', '*')
..

This should give you into your collection sku, and a number of other product information which you may wish to refine since we're selecting * in this case.

Now you need your column to reference the new columns which have been selected:

$this->addColumn('sku', array( 'header' => Mage::helper('Sales')->__('Skus'), 'width' => '100px', 'index' => 'sku', 'type' => 'text', ));

Note the lack of plural on sku (not skus).

This should now render with your grid showing SKU as intended.

Answer 2

This might be a very late answer. However, I will provide the way how I added the SKU into bestseller report grid in Magento Open Source 2.3.4

Assumptions: Custom module's namespace is Apple and the module name is Custom

01). Override the block Magento\Reports\Block\Adminhtml\Sales\Bestsellers\Grid In your custom module's app/code/Apple/Custom/etc/adminhtml/di.xml

<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:ObjectManager/etc/config.xsd">
    <preference for="Magento\Reports\Block\Adminhtml\Sales\Bestsellers\Grid" type="Apple\Custom\Block\Adminhtml\Sales\Bestsellers\Grid" />
</config>

02). Now create the block file app/code/Apple/Custom/Block/Adminhtml/Sales/Bestsellers/Grid.php

<?php

namespace Apple\Custom\Block\Adminhtml\Sales\Bestsellers;


class Grid extends \Magento\Reports\Block\Adminhtml\Sales\Bestsellers\Grid
{
    public function __construct(
        \Magento\Backend\Block\Template\Context $context,
        \Magento\Backend\Helper\Data $backendHelper,
        \Magento\Reports\Model\ResourceModel\Report\Collection\Factory $resourceFactory,
        \Magento\Reports\Model\Grouped\CollectionFactory $collectionFactory,
        \Magento\Reports\Helper\Data $reportsData,
        array $data = []
    ) {
        parent::__construct($context, $backendHelper, $resourceFactory, $collectionFactory, $reportsData, $data);
    }

    /**
     * {@inheritdoc}
     * @codeCoverageIgnore
     */
    public function getResourceCollectionName()
    {
        return \Magento\Sales\Model\ResourceModel\Report\Bestsellers\Collection::class;
    }

    /**
     * {@inheritdoc}
     */
    protected function _prepareColumns()
    {
        $this->addColumn(
            'period',
            [
                'header' => __('Interval'),
                'index' => 'period',
                'sortable' => false,
                'period_type' => $this->getPeriodType(),
                'renderer' => \Magento\Reports\Block\Adminhtml\Sales\Grid\Column\Renderer\Date::class,
                'totals_label' => __('Total'),
                'html_decorators' => ['nobr'],
                'header_css_class' => 'col-period',
                'column_css_class' => 'col-period'
            ]
        );

        $this->addColumn(
            'product_name',
            [
                'header' => __('Product'),
                'index' => 'product_name',
                'type' => 'string',
                'sortable' => false,
                'header_css_class' => 'col-product',
                'column_css_class' => 'col-product'
            ]
        );

        // add SKU
        $this->addColumnAfter(
            'sku',
            [
                'header' => __('SKU'),
                'index' => 'sku',
                'renderer' => \Apple\Custom\Block\Adminhtml\Grid\Column\Renderer\Sku::class,
                'sortable' => false,
                'header_css_class' => 'col-sku',
                'column_css_class' => 'col-sku'
            ],
            'period'
        );

        if ($this->getFilterData()->getStoreIds()) {
            $this->setStoreIds(explode(',', $this->getFilterData()->getStoreIds()));
        }
        $currencyCode = $this->getCurrentCurrencyCode();

        $this->addColumn(
            'product_price',
            [
                'header' => __('Price'),
                'type' => 'currency',
                'currency_code' => $currencyCode,
                'index' => 'product_price',
                'sortable' => false,
                'rate' => $this->getRate($currencyCode),
                'header_css_class' => 'col-price',
                'column_css_class' => 'col-price'
            ]
        );

        $this->addColumn(
            'qty_ordered',
            [
                'header' => __('Order Quantity'),
                'index' => 'qty_ordered',
                'type' => 'number',
                'total' => 'sum',
                'sortable' => false,
                'header_css_class' => 'col-qty',
                'column_css_class' => 'col-qty'
            ]
        );

        $this->addExportType('*/*/exportBestsellersCsv', __('CSV'));
        $this->addExportType('*/*/exportBestsellersExcel', __('Excel XML'));

        return parent::_prepareColumns();
    }
}

03). Now create the renderer class for our custom column SKU in app/code/Apple/Custom/Block/Adminhtml/Grid/Column/Renderer/Sku.php

<?php

namespace Apple\Custom\Block\Adminhtml\Grid\Column\Renderer;

use Magento\Catalog\Model\ProductFactory;
use Magento\Framework\DataObject;
use Magento\Backend\Block\Context;

class Sku extends \Magento\Backend\Block\Widget\Grid\Column\Renderer\AbstractRenderer
{
    /**
     * @var ProductFactory
     */
    protected $productloader;

    /**
     * @param Context $context
     * @param ProductFactory $productloader
     * @param array $data
     */
    public function __construct(
        Context $context,
        ProductFactory $productloader,
        array $data = []
    )
    {
        $this->productloader = $productloader;
        parent::__construct($context, $data);
    }

    /**
     * @param DataObject $row
     *
     * @return string
     * @throws \Magento\Framework\Exception\NoSuchEntityException
     */
    public function render(DataObject $row)
    {
        $id = (int)$row->getProductId();
        $product = $this->productloader->create()->load($id);
        return $product->getSku();
    }
}

4. Run below commands from your Magento root directory

bin/magento cache:flush
bin/magento setup:di:compile

Once you login to Magento admin you should see the new SKU column in bestseller grid as shown below.