0

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());

2

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.

6
  • Thanks for the reply. I will write a module and try this out. I'm guessing the process will be the same for the other reports? Jun 17 '15 at 19:35
  • 1
    It should be yes. Provided that the collection in use has entity_id as a column representing the product. I should expect all the product focused ones will have this. Jun 18 '15 at 6:55
  • Hi John, just looking into this now and there doesn't seem to be a _prepareCollection() function in Mage_Adminhtml_Block_Report_Sales_Bestsellers_Grid. There is only _prepareColumns(). Jun 22 '15 at 12:44
  • 1
    _prepareCollection is inherited from the parent class, Mage_Adminhtml_Block_Report_Grid_Abstract. By putting this within your class you are overriding the functionality to provide your custom collection loading. Jun 22 '15 at 12:52
  • Ah yeah just checked that now. Knew it would be something like that. So I really only need to add the join condition to the existing $resourceCollection object? Jun 22 '15 at 13:02
0

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();
    }
}
  1. 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.

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.