Magento 2 - Add tax_amount to Orders/Invoice grid

Question

I would like to add the tax_amount to the orders and invoices grids to be able to have them in the export files. I found some documentation and tutorials on telling how to add a custom column, with a new column created in database, but not one showing existing data.

I assume I have to call existing data from sales_order and sales_invoice tables in

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

and

app/code/Vendor/Module/view/adminhtml/ui_component/sales_order_invoice_grid.xml

So I started with a new 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.

Any help would be appreciated.

I'm working on Magento 2.2.0.

Answer 1

I finally made it work.

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

    // Below function fixes error and endless loading when filters lead to empty page
    protected function _initSelect()
    {

        $this->addFilterToMap('created_at', 'main_table.created_at');

        parent::_initSelect();
    }
}

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>

I do not know if it's the best or most straight forward soluttion, but it works, I have now Tax amount and Shipping tax amount in the orders grid, and in the csv / xml export of the grid.

Unfortunately, the trick does not work for invoices, when module is enabled, I weirdly get 3 invoices for each order.

IMPORTANT EDIT After update to 2.4.5 the module works fine on first page load, but I can't filter the orders by date anymore.

I'm getting following error:

Next Zend_Db_Statement_Exception: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'created_at' in where clause is ambiguous, query was: SELECT DISTINCT main_table.*, prfx_braintree_transaction_details.transaction_source, so.tax_amount, so.shipping_tax_amount FROM prfx_sales_order_grid AS main_table LEFT JOIN prfx_braintree_transaction_details ON prfx_braintree_transaction_details.order_id = main_table.entity_id LEFT JOIN prfx_sales_order AS so ON main_table.entity_id = so.entity_id WHERE (((created_at >= '2022-10-03 22:00:00'))) AND (((created_at <= '2022-10-05 21:59:59'))) ORDER BY main_table.created_at DESC

Answer 2

David's answer is working great, I just used it, but changed slightly the sales_order_grid.xml file so the amount is displayed in the right currency with its code:

<?xml version="1.0"?>
<!--
/**
 * sales_order_grid
 *
 * @copyright Copyright © 2021 The Etailers. All rights reserved.
 * @author    [email protected]
 */
-->
<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" class="Magento\Sales\Ui\Component\Listing\Column\Price">
            <settings>
                <filter>textRange</filter>
                <label translate="true">Tax amount</label>
            </settings>
        </column>
        <column name="shipping_tax_amount" class="Magento\Sales\Ui\Component\Listing\Column\Price">
            <settings>
                <filter>textRange</filter>
                <label translate="true">Shipping tax amount</label>
            </settings>
        </column>
    </columns>
</listing>

Answer 3

Magento 2 — Add Custom Column to the Invoice grid: https://santhiyasugumar22.medium.com/magento-2-add-custom-column-to-the-invoice-grid-9fadbcb76f8e