Add tax_amount to Orders/Invoice grid

[ShapesGS]

Hello,

I would like to add the tax_amount to the orders and invoice grid.

I was able to create an empty column in the orders grid by creating a new module to override sales_order_grid.xml.

I added app/code/Vendor/Module/view/adminhtml/ui_component/sales_order_grid.xml :

<?xml version="1.0" encoding="UTF-8"?>
<!--
/**
 * Copyright © 2013-2017 Magento, Inc. All rights reserved.
 * See COPYING.txt for license details.
 */
-->
<listing xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:module:Magento_Ui:etc/ui_configuration.xsd">
    <columns name="sales_order_columns">
        <column name="tax_amount">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="filter" xsi:type="string">textRange</item>
                    <item name="visible" xsi:type="boolean">false</item>
                    <item name="label" xsi:type="string" translate="true">Tax amount</item>
                </item>
            </argument>
        </column>
    </columns>
</listing>

It shows an empty column, but I'm stuck on how to get the info, I need to call tax_amount column from sales_order table.

I'm working on Magento 2.1.9.

Any idea on how I could get this done please ?

Best regards,

David

[Sunil Patel]

@ShapesGS

There is two way

1) create column into sales_order_grid

i did for shipment grid

<virtualType name="ShipmentGridAggregator" type="Magento\Sales\Model\ResourceModel\Grid">
        <arguments>
            <argument name="columns" xsi:type="array">
                <item name="delivery_date" xsi:type="string">sales_shipment.delivery_date</item>
				<item name="delivery_slot" xsi:type="string">sales_shipment.delivery_slot</item>
            </argument>
        </arguments>
    </virtualType>

2)using join

[ShapesGS]

Hello,

Sorry for late answer.

I managed to display the column with the code I showed in my question, but I do not know how to call the tax amount from another table, that's my issue..

I can not see how I can use your example here.

[Sunil Patel]

@ShapesGS

https://magento.stackexchange.com/questions/91807/how-to-join-order-grid-collection-to-custom-table-...

[ShapesGS]

Thank you for the link, funny it was exactly the page I was looking at right now ;-)

Earlier I also made a try adding in app/code/Vendor/Module/etc/di.xml :

    <virtualType name="Magento\Sales\Model\ResourceModel\Order\Grid">
        <arguments>
            <argument name="joins" xsi:type="array">
                <item name="tax" xsi:type="array">
                    <item name="table" xsi:type="string">tax</item>
                    <item name="origin_column" xsi:type="string">entity_id</item>
                    <item name="target_column" xsi:type="string">order_id</item>
                </item>
            </argument>
            <argument name="columns" xsi:type="array">
                <item name="tax_amount" xsi:type="string">tax.tax_amount</item>
            </argument>
        </arguments>
    </virtualType>

But I must have made a mistake as it did not seem to work.

[Sunil Patel]

@ShapesGS if that link helpful for you then mark as solution

[ShapesGS]

Well it's not quite what I'm looking for, seemed to work on the orders grid, but was also messing up the invoices grid.

[ShapesGS]

Here's where I am now with my custom module.

app/code/Vendor/Module/etc/module.xml :

<?xml version="1.0" encoding="UTF-8"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../../../../../lib/internal/Magento/Framework/Module/etc/module.xsd">
    <module name="Vendor_Module" setup_version="2.0.0">  
    <sequence>
            <module name="Module"/>
    </sequence>
    </module>
</config>

app/code/Vendor/Module/etc/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">
    <virtualType name="Magento\Sales\Model\ResourceModel\Order\Grid">
        <arguments>
            <argument name="joins" xsi:type="array">
                <item name="sales_order" xsi:type="array">
                    <item name="table" xsi:type="string">sales_order</item>
                    <item name="origin_column" xsi:type="string">entity_id</item>
                    <item name="target_column" xsi:type="string">entity_id</item>
                </item>
            </argument>
            <argument name="columns" xsi:type="array">
                <item name="tax_amount" xsi:type="string">sales_order.tax_amount</item>
            </argument>
        </arguments>
    </virtualType>
</config>

app/code/Vendor/Module/view/adminhtml/ui_component/sales_order_grid.xml :

<?xml version="1.0" encoding="UTF-8"?>
<!--
/**
 * Copyright © 2013-2017 Magento, Inc. All rights reserved.
 * See COPYING.txt for license details.
 */
-->
<listing xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:module:Magento_Ui:etc/ui_configuration.xsd">
    <columns name="sales_order_columns">
        <column name="tax_amount">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="filter" xsi:type="string">text</item>
                    <item name="label" xsi:type="string" translate="true">Tax amount</item>
                </item>
            </argument>
        </column>
    </columns>
</listing>

But it does not seem to work, I only have an empty column.

I also tried with another module.

app/code/Vendor/Module/etc/module.xml :

<?xml version="1.0" encoding="UTF-8"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../../../../../lib/internal/Magento/Framework/Module/etc/module.xsd">
    <module name="Vendor_Module" setup_version="2.0.0">  
    <sequence>
            <module name="Module"/>
    </sequence>
    </module>
</config>

app/code/Vendor/Module/etc/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">
    <virtualType name="Magento\Sales\Model\ResourceModel\Order\Grid">
        <arguments>
            <argument name="columns" xsi:type="array">
                <item name="tax_amount" xsi:type="string">sales_order.tax_amount</item>
            </argument>
        </arguments>
    </virtualType>
</config>

app/code/Vendor/Module/view/adminhtml/ui_component/sales_order_grid.xml :

<?xml version="1.0" encoding="UTF-8"?>
<!--
/**
 * Copyright © 2013-2017 Magento, Inc. All rights reserved.
 * See COPYING.txt for license details.
 */
-->
<listing xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:module:Magento_Ui:etc/ui_configuration.xsd">
    <columns name="sales_order_columns">
        <column name="tax_amount">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="filter" xsi:type="string">text</item>
                    <item name="label" xsi:type="string" translate="true">Tax amount</item>
                </item>
            </argument>
        </column>
    </columns>
</listing>

But I have the same problem.

It seems like :

<virtualType name="Magento\Sales\Model\ResourceModel\Order\Grid">

Is getting data from sales_order_grid, as I managed to call status column for example.

So I tried with :

<virtualType name="Magento\Sales\Model\ResourceModel\Order\Collection">

But not better.

Any help would be appreciated.

[Sunil Patel]

@ShapesGS

 <column name="tax_amount" class="VendorName\Modulename\Ui\Component\Listing\Columns\Tax">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="sortable" xsi:type="boolean">false</item>
                    <item name="label" xsi:type="string" translate="true">Tax Amount</item>
					<item name="sortOrder" xsi:type="number">140</item>
                </item>
            </argument>
        </column>

<?php

namespace VenodorName\Modulename\Ui\Component\Listing\Columns;

class Lastdelivery extends \Magento\Ui\Component\Listing\Columns\Column
{
    public function __construct(
        \Magento\Framework\View\Element\UiComponent\ContextInterface $context,
        \Magento\Framework\View\Element\UiComponentFactory $uiComponentFactory,
        \Magento\Framework\ObjectManagerInterface $objectManager,
        array $components = [],
        array $data = []
    ){
        $this->objectManager = $objectManager;
        parent::__construct($context, $uiComponentFactory, $components, $data);
    }

    public function prepareDataSource(array $dataSource) {
        if (isset($dataSource['data']['items'])) {
            $i = 0;
            foreach ($dataSource['data']['items'] as & $item) {
               // custom join table logic
                $dataSource['data']['items'][$i]['tax_amount'] = // value;
                $i++;
            }
        }
        return $dataSource;
    }
}

[ShapesGS]

Hi,

Well, I missed some time to answer, but I managed to make it work another way.

app/code/Vendor/Module/etc/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">
    <type name="Magento\Framework\View\Element\UiComponent\DataProvider\CollectionFactory">
        <arguments>
            <argument name="collections" xsi:type="array">
                <item name="sales_order_grid_data_source" xsi:type="string">Vendor\Module\Model\ResourceModel\Order\Grid\Collection</item>
            </argument>
        </arguments>
    </type>
</config>

app/code/Vendor/Module/Model/ResourceModel/Order/Grid/Collection.php :

<?php
namespace Vendor\Module\Model\ResourceModel\Order\Grid;
use Magento\Sales\Model\ResourceModel\Order\Grid\Collection as OriginalCollection;
/**
* Order grid extended collection
*/
class Collection extends OriginalCollection
{
    protected function _renderFiltersBefore()
    {
        $this->getSelect()->joinLeft(
            ["so" => "prfx_sales_order"],
            'main_table.entity_id = so.entity_id',
            array('tax_amount','shipping_tax_amount')
        )
        ->distinct();
        
        parent::_renderFiltersBefore();
    }
}

app/code/Vendor/Module/view/adminhtml/ui_component/sales_order_grid.xml :

<?xml version="1.0" encoding="UTF-8"?>
<!--
/**
 * Copyright © 2013-2017 Magento, Inc. All rights reserved.
 * See COPYING.txt for license details.
 */
-->
<listing xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:module:Magento_Ui:etc/ui_configuration.xsd">
    <columns name="sales_order_columns">
        <column name="tax_amount">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="filter" xsi:type="string">text</item>
                    <item name="label" xsi:type="string" translate="true">Tax amount</item>
                </item>
            </argument>
        </column>
        <column name="shipping_tax_amount">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="filter" xsi:type="string">text</item>
                    <item name="label" xsi:type="string" translate="true">Shipping tax amount</item>
                </item>
            </argument>
        </column>
    </columns>
</listing>

But thanks a lot for your effort and concern!

Maybe your solution is better..?

I don't know, I think I will give it a try also.

Edit : The only problem remaining with my changes seems that there's an error when using custom administrator role, an error message is shown on grid load which loads endlessly.

Best regards,

David