I have Magento CE installed in my site.

Now I want to also include custom made columns "Grandtotal Excl. Tax" and "Payment method" to my Admin >> Sales >> Invoice grid and also include those columns in Grid export.

How can I do that by editing the collection query in _prepareCollection function of app/code/local/Mage/Adminhtml/Block/Sales/Invoice/Grid.php ?

I don't know much about having subtract operation in sql, so can somebody help me out here ?

2 Answers 2


Hello You have different possibilities to include custom column in grid:

https://www.atwix.com/magento/column-to-orders-grid/ (Via layout)

Add Company column to Customers Admin Grid with Observer (Via observer)

  • Yes, I am already aware of that, but as I said, I don't know anything about magento collection query subtraction operation as we do in regular mysql query, so can you help me mainly in that area ?
    – Vicky Dev
    Commented Mar 1, 2016 at 11:42
  • Yes no problem. what are your custom table columns?. Table name and table columns.
    – osrecio
    Commented Mar 1, 2016 at 11:48
  • The table, grid fetches default data from is sales_flat_invoice_item but there aren't any custom columns created in it right now. I want to generate custom columns("grandtotal excl. tax" & "payment method") dynamically in collection query just like we generate mysql views inside query itself.
    – Vicky Dev
    Commented Mar 1, 2016 at 11:56
  • You can add custom columns of your collection follow this: $collection->getSelect()->columns('payment_method' as customColumn','ivented_column_value' as 'custom_column'). I don't know if it is your goal.
    – osrecio
    Commented Mar 1, 2016 at 12:10
  • For fetching payment_method, I think all I need to do is to join my Invoice grid collection query with table that contains payment method Id and it's details, but I don't know which table is it and how to join with that table.
    – Vicky Dev
    Commented Mar 1, 2016 at 12:14

please follow this

change _prepareCollection() function with this

protected function _prepareCollection()
        $collection = Mage::getResourceModel($this->_getCollectionClass());
        $collection->getSelect()->join(array('invoice' => 'sales_flat_invoice'),
    'invoice.entity_id = main_table.entity_id', null)
    ->columns('(invoice.grand_total - invoice.tax_amount) AS grand_excl_tax'); 

         return parent::_prepareCollection();

and add Column in _prepareColumns() function 

$this->addColumn('grand_excl_tax', array(
            'header'    => Mage::helper('customer')->__('GrandTotal(excl. tax)'),
            'index'     => 'grand_excl_tax',
            'filter_condition_callback' => array($this, '_applyMyFilter'),
            'type'      => 'currency',
            'align'     => 'right',
            'currency'  => 'order_currency_code',

and add this _applyMyFilter function for filter_condition_callback

protected function _applyMyFilter(Varien_Data_Collection_Db $collection, Mage_Adminhtml_Block_Widget_Grid_Column $column)

    $select = $collection->getSelect();
    $field = $column->getIndex();
    $value = $column->getFilter()->getValue();
    $select->where("invoice.grand_total - invoice.tax_amount>?", $value['from']);
    $select->where("invoice.grand_total - invoice.tax_amount<?", $value['to']);
  • protected function _applyMyFilter(Varien_Data_Collection_Db $collection, Mage_Adminhtml_Block_Widget_Grid_Column $column) { $select = $collection->getSelect(); $field = $column->getIndex(); $value = $column->getFilter()->getValue(); $select->where("invoice.grand_total - invoice.tax_amount>?", $value['from']); $select->where("invoice.grand_total - invoice.tax_amount<?", $value['to']); } Commented Sep 17, 2019 at 10:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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