4

I am trying to add Shipping country as extra column and filter,

how that can be achieved. can anyone help me on this.

enter image description here

referred the below Url, but which was not helpful for me. Magento 2: How to add custom filters in Orders Report?

How to add new field in the Sales order report with the shipping country drop down list filter.

Vendor/Module/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\Sales\Block\Adminhtml\Report\Filter\Form\Order" type="Vendor\Module\Block\Adminhtml\Report\Filter\Form\Order" />
   <preference for="Magento\Reports\Block\Adminhtml\Sales\Sales\Grid" type="Vendor\Module\Block\Adminhtml\Sales\Sales\Grid" />
</config>

Vendor\Module\Block/Adminhtml/Report/Filter/Form/Order.php

class Order extends \Magento\Sales\Block\Adminhtml\Report\Filter\Form\Order
{
 protected function _prepareForm()
 {
     parent::_prepareForm();        
     $fieldset = $this->getForm()->getElement('base_fieldset');

    if (is_object($fieldset) && $fieldset instanceof \Magento\Framework\Data\Form\Element\Fieldset) {
        $fieldset->addField(
            'shipping_country',
            'select',
            [
                'name' => 'shipping_country',
                'options' => ['gbr' => __('United Kingdom'), 'in' => __('India')],
                'label' => __('Shipping Country')
            ]
        );
    }

    return $this;
 }
}

Vendor/Module/Block/Adminhtml/Sales/Sales/Grid.php

use Magento\Framework\DataObject;
use Magento\Reports\Block\Adminhtml\Grid\Column\Renderer\Currency;
use Magento\Framework\App\ObjectManager;
use Magento\Sales\Model\Order\ConfigFactory;
use Magento\Sales\Model\Order;

class Grid extends \Magento\Reports\Block\Adminhtml\Sales\Sales\Grid
{
  protected $_columnGroupBy = 'period';

  private $configFactory;

   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 = [],
    ConfigFactory $configFactory = null
) {
    parent::__construct(
        $context,
        $backendHelper,
        $resourceFactory,
        $collectionFactory,
        $reportsData,
        $data
    );
    $this->configFactory = $configFactory ?: ObjectManager::getInstance()->get(ConfigFactory::class);
}

protected function _prepareColumns()
{
    $this->addColumn(
        'shipping_country',
        [
            'header' => __('Shipping Country'),
            'index' => 'shipping_country',
            'type' => 'number',
            'sortable' => false,
            'header_css_class' => 'col-sales-items',
            'column_css_class' => 'col-sales-items'
        ]
    );
    
    return parent::_prepareColumns();
   }
}

I have used the above code, a column shown in the grid with the filter.

How to get shipping country in report? I am getting empty values for the column.

please someone update me how this can be achieved?

Thanks in Advance!!

10
  • Do you have any console errors?
    – Ben Crook
    Jul 15 '19 at 14:25
  • What error currently it show to you after implement the above things? Aug 7 '20 at 6:31
  • @DhirenVasoya, The new column not shown in the grid as well as in filter.
    – Manjunath
    Aug 7 '20 at 6:41
  • Share the code which you implement? Aug 7 '20 at 6:47
  • @DhirenVasoya, Updated my question. Please check it
    – Manjunath
    Aug 7 '20 at 7:20
4
+150

First you need to add a new column using db schema or setup scripts in following table.

sales_order_aggregated_created

In this, you have to add you column country_id

After that, you need to modify query on below class method to have country in above table.

Magento\Sales\Model\ResourceModel\Report\Order\Createdat::_aggregateByField

Here, you need to modify query as per your needs to have data in aggregated table. Since this is a protected method, you may have to use preference for this.

After that, you have to add your filter dropdown in form, as well as in grid, and apply following function in Grid.php file.

protected function _addOrderStatusFilter($collection, $filterData)
{
        parent::_addOrderStatusFilter($collection, $filterData);
        $collection->setCountryId($filterData->getData('country_id'));
        return $this;
}

Then in your collection file, apply following code.

public function applyAdditionalFilters($select = null)
{
    if (!$select) {
        $select = $this->getSelect();
    }
    if ($countryId = $this->getCountryId()) {
        $select->where('country_id IN (?)', $countryId);
    }

    return $this;
}

public function getCountryId()
{
    return $this->countryId;
}

public function setCountryId($countryId)
{
    $this->countryId = $countryId;
}

apply this function in _beforeLoad and _makeBoundarySelect functions before return statement.

This way it would work.

1
  • hi @yash, Thanks for the answer, This almost worked for me, but has some issue, count of orders for shipping country is wrong.
    – Manjunath
    Aug 18 '20 at 7:11

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.