Custom reports on Magento

I've never found a realy good tutorial on how to create a custom report on Magento, so I hope you'll find this helpful.

- First of all, you need to generate a custom module, create the following files:

/app/etc/modules/Mycompany_Mymodule.xml /app/design/adminhtml/default/default/layout/mymodule.xml /app/code/local/Mycompany/Mymodule/Block/adminhtml/Mymodule/Grid.php /app/code/local/Mycompany/Mymodule/Block/adminhtml/Mymodule.php /app/code/local/Mycompany/Mymodule/Block/Mymodule.php /app/code/local/Mycompany/Mymodule/controllers/Adminhtml/MymoduleController.php /app/code/local/Mycompany/Mymodule/etc/config.xml /app/code/local/Mycompany/Mymodule/Helper/Data.php /app/code/local/Mycompany/Mymodule/Model/Mymodule.php

- Define your module on /app/etc/modules/Mycompany_Mymodule.xml:

<?xml version="1.0"?>
<config>
    <modules>
        <Mycompany_Mymodule>
            <active>true</active>
            <codePool>local</codePool>
        </Mycompany_Mymodule>
    </modules>
</config>

- Complete the layout file which will update the admin view (I always do this first because I don't want to forget it).

<?xml version="1.0"?>
<layout version="0.1.0">
    <mymodule_adminhtml_mymodule_index>
        <reference name="content">
            <block type="mymodule/adminhtml_mymodule" name="mymodule" />
        </reference>
    </mymodule_adminhtml_mymodule_index>
</layout>

- Create the config file with this content /app/code/local/Mycompany/Mymodule/etc/config.xml:

<?xml version="1.0"?>
<!-- 
/**
 * @category   Mycompany
 * @package    Mycompany_Mymodule
 * @author     Damian Alberto Pastorini
 */
 -->
<config>
    <modules>
        <Mycompany_Mymodule>
            <version>0.1.0</version>
        </Mycompany_Mymodule>
    </modules>
    <admin>
        <routers>
            <mymodule>
                <use>admin</use>
                <args>
                    <module>Mycompany_Mymodule</module>
                    <frontName>mymodule</frontName>
                </args>
            </mymodule>
        </routers>
    </admin>
    <adminhtml>
        <menu>
            <report>
                <children>
                    <mymodule translate="title" module="mymodule">
                        <title>Mymodule Report</title>
                        <action>mymodule/adminhtml_mymodule</action>
                    </mymodule>
                </children>
            </report>
        </menu>
        <acl>
            <resources>
                <all>
                    <title>Allow Everything</title>
                </all>
                <admin>
                    <children>
                        <report>
                            <children>
                                <mymodule translate="title" module="mymodule">
                                    <title>Mymodule Report</title>
                                    <action>mymodule/adminhtml_mymodule</action>
                                </mymodule>
                            </children>
                        </report>
                    </children>
                </admin>
            </resources>
        </acl>
        <layout>
            <updates>
                <mymodule>
                    <file>mymodule.xml</file>
                </mymodule>
            </updates>
        </layout>
    </adminhtml>
    <global>
        <models>
            <mymodule>
                <class>Mycompany_Mymodule_Model</class>
                <resourceModel>mymodule</resourceModel>
            </mymodule>
        </models>
        <resources>
            <mymodule_setup>
                <setup>
                    <module>Mycompany_Mymodule</module>
                </setup>
                <connection>
                    <use>core_setup</use>
                </connection>
            </mymodule_setup>
            <mymodule_write>
                <connection>
                    <use>core_write</use>
                </connection>
            </mymodule_write>
            <mymodule_read>
                <connection>
                    <use>core_read</use>
                </connection>
            </mymodule_read>
        </resources>
        <blocks>
            <mymodule>
                <class>Mycompany_Mymodule_Block</class>
            </mymodule>
        </blocks>
        <helpers>
            <mymodule>
                <class>Mycompany_Mymodule_Helper</class>
            </mymodule>
        </helpers>
    </global>
</config>

Here we define the controller, the menu access and permisions, the model, the blocks and the helper.

- Create the grid and specify all the columns /app/code/local/Mycompany/Mymodule/Block/adminhtml/Mymodule/Grid.php:

<?php
class Mycompany_Mymodule_Block_Adminhtml_Mymodule_Grid extends Mage_Adminhtml_Block_Report_Grid {

    public function __construct() {
        parent::__construct();
        $this->setId('mymoduleGrid');
        $this->setDefaultSort('created_at');
        $this->setDefaultDir('ASC');
        $this->setSaveParametersInSession(true);
        $this->setSubReportSize(false);
    }

    protected function _prepareCollection() {
        parent::_prepareCollection();
        $this->getCollection()->initReport('mymodule/mymodule');
        return $this;
    }

    protected function _prepareColumns() {
        $this->addColumn('ordered_qty', array(
            'header'    =>Mage::helper('reports')->__('Quantity Ordered'),
            'align'     =>'right',
            'index'     =>'ordered_qty',
            'total'     =>'sum',
            'type'      =>'number'
        ));
        $this->addColumn('item_id', array(
            'header' => Mage::helper('mymodule')->__('Item ID'),
            'align' => 'right',
            'index' => 'item_id',
            'type'  => 'number',
            'total' => 'sum',
        ));
        $this->addExportType('*/*/exportCsv', Mage::helper('mymodule')->__('CSV'));
        $this->addExportType('*/*/exportXml', Mage::helper('mymodule')->__('XML'));
        return parent::_prepareColumns();
    }

    public function getRowUrl($row) {
        return false;
    }

    public function getReport($from, $to) {
        if ($from == '') {
            $from = $this->getFilter('report_from');
        }
        if ($to == '') {
            $to = $this->getFilter('report_to');
        }
        $totalObj = Mage::getModel('reports/totals');
        $totals = $totalObj->countTotals($this, $from, $to);
        $this->setTotals($totals);
        $this->addGrandTotals($totals);
        return $this->getCollection()->getReport($from, $to);
    }
}

This file is clearest but I give you some tips about specific lines:

// this line indicates the model to use for get the data. $this->getCollection()->initReport('mymodule/mymodule'); // it's used to indicate that this field must be totalized at the end. 'total' =>'sum', // this is executed when you click on the rows grid, in case you return false (like the example) nothing will happen when you click on the rows grid. public function getRowUrl($row) {

- For the next step, create the grid container block /app/code/local/Mycompany/Mymodule/Block/adminhtml/Mymodule.php:

<?php
class Mycompany_Mymodule_Block_Adminhtml_Mymodule extends Mage_Adminhtml_Block_Widget_Grid_Container {

    public function __construct() {
        $this->_controller = 'adminhtml_mymodule';
        $this->_blockGroup = 'mymodule';
        $this->_headerText = Mage::helper('mymodule')->__('Mymodule Report');
        parent::__construct();
        $this->_removeButton('add');
    }

}

Here we add this line to remove the add button: // This must be always after the parent::__construct(); line. $this->_removeButton('add');

- Create the block container /app/code/local/Mycompany/Mymodule/Block/Mymodule.php:

<?php
class Mycompany_Mymodule_Block_Mymodule extends Mage_Core_Block_Template {

    public function _prepareLayout() {
        return parent::_prepareLayout();
    }

    public function getMymodule() {
        if (!$this->hasData('mymodule')) {
            $this->setData('mymodule', Mage::registry('mymodule'));
        }
        return $this->getData('mymodule');
    }

}

- Create the controller /app/code/local/Mycompany/Mymodule/controllers/Adminhtml/MymoduleController.php:

<?php

class Mycompany_Mymodule_Adminhtml_MymoduleController extends Mage_Adminhtml_Controller_Action {

    protected function _initAction() {
        $this->loadLayout();
        return $this;
    }

    public function indexAction() {
        $this->_initAction()
                ->renderLayout();
    }

    public function exportCsvAction() {
        $fileName = 'mymodule.csv';
        $content = $this->getLayout()->createBlock('mymodule/adminhtml_mymodule_grid')
                        ->getCsv();
        $this->_sendUploadResponse($fileName, $content);
    }

    public function exportXmlAction() {
        $fileName = 'mymodule.xml';
        $content = $this->getLayout()->createBlock('mymodule/adminhtml_mymodule_grid')
                        ->getXml();
        $this->_sendUploadResponse($fileName, $content);
    }

    protected function _sendUploadResponse($fileName, $content, $contentType='application/octet-stream') {
        $response = $this->getResponse();
        $response->setHeader('HTTP/1.1 200 OK', '');
        $response->setHeader('Pragma', 'public', true);
        $response->setHeader('Cache-Control', 'must-revalidate, post-check=0, pre-check=0', true);
        $response->setHeader('Content-Disposition', 'attachment; filename=' . $fileName);
        $response->setHeader('Last-Modified', date('r'));
        $response->setHeader('Accept-Ranges', 'bytes');
        $response->setHeader('Content-Length', strlen($content));
        $response->setHeader('Content-type', $contentType);
        $response->setBody($content);
        $response->sendResponse();
        die;
    }

}

- Then the empty helper /app/code/local/Mycompany/Mymodule/Helper/Data.php:

<?php
class Mycompany_Mymodule_Helper_Data extends Mage_Core_Helper_Abstract
{

}

- And for the last we create the model that will bring the data /app/code/local/Mycompany/Mymodule/Model/Mymodule.php:

<?php
class Mycompany_Mymodule_Model_Mymodule extends Mage_Reports_Model_Mysql4_Order_Collection
{
    function __construct() {
        parent::__construct();
        $this->setResourceModel('sales/order_item');
        $this->_init('sales/order_item','item_id');
   }

    public function setDateRange($from, $to) {
        $this->_reset();
        $this->getSelect()
             ->joinInner(array(
                 'i' => $this->getTable('sales/order_item')),
                 'i.order_id = main_table.entity_id'
                 )
             ->where('i.parent_item_id is null')
             ->where("i.created_at BETWEEN '".$from."' AND '".$to."'")
             ->where('main_table.state = \'complete\'')
             ->columns(array('ordered_qty' => 'count(distinct `main_table`.`entity_id`)'));
        // uncomment next line to get the query log:
        // Mage::log('SQL: '.$this->getSelect()->__toString());
        return $this;
    }

    public function setStoreIds($storeIds)
    {
        return $this;
    }

}
?>

This is a custom model that get the data from Magento core models, here you can define any model or if you already got your own DB/tables you can get the report data from it. // this line reset the original query that comes by default. $this->_reset();

All done!

Author

Comments

Hi! I tried your solution but i got only a blank page after clicked to the menu. (No error message in var/report or on the frontend ). Do you have any idea? Thx in forward!

I had this problem too. Change /app/code/local/Mycompany/Mymodule/Block/adminhtml to /app/code/local/Mycompany/Mymodule/Block/Adminhtml (first letter of Adminhtml must be uppercase) The error is in /var/log/exception.log ;)

Which version of Magento does this work on mate? People don't want to spend the time putting the module together only to find that it won't work on 1.4.1.0 or 1.5.0.1 etc... Well done though - good explaination.

Great tutorial. This helps me very much. I used most of the code and added a filter. Works on Magento 1.4.2

I tested this example on Magento 1.4 but it must work on 1.5 too, I only used Magento core functions.

Great job, thanks

I created files , but its not working. I got this error: ( ! ) Fatal error: Call to a member function setSaveParametersInSession() on a non-object in F:\wamp\www\mag\app\code\core\Mage\Adminhtml\Block\Widget\Grid\Container.php on line 59

Most problems results from giving camelcase names for modules. I thinks it's much easier and more fail-safe, not to give camelcase names to classes.