Codegento Who Let Mage Out Of The Cage?

11Mar/1122

Creating Custom Magento Reports

This post is going to be LONG. And not fun. To be honest, I'm not sure how some of this stuff works, but I do know that I have successfully created two different kinds of reports.

Some things to note:

  • You should never create reports off of a transactional table. Running the report could potentially create locks that would affect your customer facing website.
  • This blog post will not contain examples of "aggregation observers and tables", but you should use them when creating reports. You run them through cron and an example observer can be found here: Mage_Sales_Model_Observer.

Let's get started shall we? I will give examples of two different kinds of reports in this post. They will be called:

  • Simple - A report that has the thin/horizontal form for selecting "to", "from" and "period". (See Reports > Customers > New Accounts)
  • Complex - A report that has a seperatly defined filter block for filtering the report data. ( See Reports > Products > Best Sellers)

Simple Report

Table structure (setup script):

<<?php

$installer = $this;

$installer->startSetup();

$installer->run("

-- DROP TABLE IF EXISTS {$this->getTable('super_awesome_example_simple')};
CREATE TABLE {$this->getTable('super_awesome_example_simple')} (
`id` INT NOT NULL AUTO_INCREMENT ,
`description` VARCHAR( 100 ) NOT NULL ,
`value` DECIMAL(12,2) NOT NULL ,
`period` DATE NOT NULL ,
PRIMARY KEY ( `id` )
) ENGINE = MYISAM ;

INSERT INTO {$this->getTable('super_awesome_example_simple')} (`description`, `value`, `period`) values ('Example One Description', 10.00, '2011-02-01');
INSERT INTO {$this->getTable('super_awesome_example_simple')} (`description`, `value`, `period`) values ('Example Two Description', 12.50, '2011-02-15');
INSERT INTO {$this->getTable('super_awesome_example_simple')} (`description`, `value`, `period`) values ('Example Three Description', 5.35, '2011-03-01');
INSERT INTO {$this->getTable('super_awesome_example_simple')} (`description`, `value`, `period`) values ('Example Four Description', 7.67, '2011-03-04');
INSERT INTO {$this->getTable('super_awesome_example_simple')} (`description`, `value`, `period`) values ('Example Dupe', 1.23, '2011-03-01');
INSERT INTO {$this->getTable('super_awesome_example_simple')} (`description`, `value`, `period`) values ('Example Dupe', 2.34, '2011-03-02');
INSERT INTO {$this->getTable('super_awesome_example_simple')} (`description`, `value`, `period`) values ('Example Dupe', 3.45, '2011-03-03');

");

$installer->endSetup();

The types of classes needed:

  • Report Controller
  • Report Block
  • Report Grid
  • Configs (Menu and Table Definitions)
  • Model, Resource Model, Collection Model

The Folder Structure (can be different if you want it to be):

Super
  |_ Awesome
      |_Block
      |   |_Adminhtml
      |       |_Report
      |          |_Simple
      |          |   |_Grid.php
      |          |_Simple.php
      |_controllers
      |    |_Adminhtml
      |       |_Report
      |          |_ExampleController.php
      |_etc
      |   |_adminhtml.xml
      |   |_config.xml
      |_Helper
      |   |_Data.php
      |_Model
            |_Mysql4
            |    |_Report
            |    |   |_Simple
            |    |      |_Collection.php
            |    |_Simple.php
            |_Simple.php

The first thing we need is to create a menu item to get to the report. Most likely, you will put your reports under the "Report" section, but I choose to put it in my own section:

Contents of adminhtml.xml -

<?xml version="1.0"?>
<config>
    <menu>
        <awesome translate="title" module="awesome">
            <title>Awesome</title>
            <sort_order>15</sort_order>
            <children>
                <simple translate="title" module="awesome">
                    <title>Simple Report</title>
                    <sort_order>1</sort_order>
                    <action>adminhtml/report_example/simple</action>
                </simple>
            </children>
        </awesome>
    </menu>
</config>

Now that you have an "action" element pointing to "adminhtml/report_example/simple", we need to make sure that route works. To do that, we need to configure the route in the config.xml and then create the controller. While we are in the config.xml, I will put my table definitions in.

Partial of config.xml

	<admin>
		<!--
			Here we are telling the Magento router to look for the controllers in the Super_Awesome_controllers_Adminhtml before we look in the
			Mage_Adminhtml module for all urls that begin with /admin/controller_name
		 -->
		<routers>
			<adminhtml>
				<args>
					<modules>
						<awesome before="Mage_Adminhtml">Super_Awesome_Adminhtml</awesome>
					</modules>
				</args>
			</adminhtml>
		</routers>
	</admin>
	<models>
            <awesome>
                <class>Super_Awesome_Model</class>
                <resourceModel>awesome_mysql4</resourceModel>
            </awesome>
             <awesome_mysql4>
                <class>Super_Awesome_Model_Mysql4</class>
                <entities>
                    <simple>
                        <table>super_awesome_example_simple</table>
                    </simple>
                </entities>
            </awesome_mysql4>
        </models>

        <resources>
            <awesome_setup>
                <setup>
                    <module>Super_Awesome</module>
                </setup>
                <connection>
                    <use>core_setup</use>
                </connection>
            </awesome_setup>
            <awesome_write>
                <connection>
                    <use>core_write</use>
                </connection>
            </awesome_write>
            <awesome_read>
                <connection>
                    <use>core_read</use>
                </connection>
            </awesome_read>
        </resources>

The contents of Super_Awesome_Adminhtml_Report_ExampleController:

<?php

class Super_Awesome_Adminhtml_Report_ExampleController extends Mage_Adminhtml_Controller_Action
{

	public function _initAction()
	{

		$this->loadLayout()
		->_addBreadcrumb(Mage::helper('awesome')->__('Awesome'), Mage::helper('awesome')->__('Awesome'));
		return $this;
	}

	public function simpleAction()
	{
		$this->_title($this->__('Awesome'))->_title($this->__('Reports'))->_title($this->__('Simple Report'));

		$this->_initAction()
		->_setActiveMenu('awesome/report')
		->_addBreadcrumb(Mage::helper('awesome')->__('Simple Example Report'), Mage::helper('awesome')->__('Simple Example Report'))
		->_addContent($this->getLayout()->createBlock('awesome/adminhtml_report_simple'))
		->renderLayout();

	}

	public function exportSimpleCsvAction()
	{
		$fileName   = 'simple.csv';
		$content    = $this->getLayout()->createBlock('awesome/adminhtml_report_simple_grid')
		->getCsv();

		$this->_prepareDownloadResponse($fileName, $content);
	}

}

The line that says: "->_addContent($this->getLayout()->createBlock('awesome/adminhtml_report_simple'))" really is the important one here. This is telling Magento which block to use to drive the WHOLE report. Oh, and look at the exportSimpleCsvAction() - that all you need to export a grid into CSV.

So let's take a look at the block Super_Awesome_Block_Adminhtml_Report_Simple -

<?php

class Super_Awesome_Block_Adminhtml_Report_Simple extends Mage_Adminhtml_Block_Widget_Grid_Container
{
 	public function __construct()
    {
    	$this->_blockGroup = 'awesome';
        $this->_controller = 'adminhtml_report_simple';
        $this->_headerText = Mage::helper('awesome')->__('Simple Report');
        parent::__construct();
        $this->_removeButton('add');
    }
}

There isn't much to it. It will use the parent class to auto generate the name of the "grid" block (aka the block with all of the report data):

Here is the content of that block (Super_Awesome_Block_Adminhtml_Report_Simple_Grid) -

<?php

class Super_Awesome_Block_Adminhtml_Report_Simple_Grid extends Mage_Adminhtml_Block_Report_Grid
{

	public function __construct()
	{
		parent::__construct();
		$this->setId('gridSimple');
	}

	protected function _prepareCollection()
	{
		parent::_prepareCollection();
		$this->getCollection()->initReport('awesome/report_simple_collection');

	}

	protected function _prepareColumns()
	{
		$this->addColumn('description', array(
            'header'    =>Mage::helper('reports')->__('Description'),
            'index'     =>'description',
            'sortable'  => false
		));

		$currencyCode = $this->getCurrentCurrencyCode();

		$this->addColumn('value', array(
            'header'    =>Mage::helper('reports')->__('Value'),
            'index'     =>'value',
	       	'currency_code' => $currencyCode,
            'total'     =>'sum',
            'type'      =>'currency'
            ));

            $this->addExportType('*/*/exportSimpleCsv', Mage::helper('reports')->__('CSV'));

            return parent::_prepareColumns();
	}

}

The important parts:

  1. The fact that it extends Mage_Adminhtml_Block_Report_Grid - This will require you to do things specific ways and will also automatically put the filter piece at the top of your report so you can query by date and period.
  2. The collection that it is using - By default it uses the Mage::getResourceModel('reports/report_collection') collection, but you need to call the initReport() function on that collection with the collection class that will handle YOUR data. Ours is: initReport('awesome/report_simple_collection')
  3. The columns - Like I said earlier, you really should be running these reports on "already aggregated" data, but you still might want to sum some things up for totals. Any column that has a 'total' => 'sum" attribute will be included in the total section correctly.
  4. The export types - One thing that I discovered while doing this is that every grid has the ability to export itself as CSV or XML file. Cool huh?

We mentioned the collection in #2, so let's take a look at that. The contents of the collection -

<?php

class Super_Awesome_Model_Mysql4_Report_Simple_Collection extends Mage_Core_Model_Mysql4_Collection_Abstract
{

	protected function _construct()
	{
		$this->_init('awesome/simple');
	}

	protected function _joinFields($from = '', $to = '')
	{
		$this->addFieldToFilter('period' , array("from" => $from, "to" => $to, "datetime" => true));
		$this->getSelect()->group('description');
		$this->getSelect()->columns(array('value' => 'SUM(value)'));

		return $this;
	}

	public function setDateRange($from, $to)
	{
		$this->_reset()
		->_joinFields($from, $to);
		return $this;
	}

	public function load($printQuery = false, $logQuery = false)
	{
		if ($this->isLoaded()) {
			return $this;
		}
		parent::load($printQuery, $logQuery);
		return $this;
	}

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

There really isn't anything special about this collection. It is a normal collection that inits the resource model (so you will need a normal one of those). The only hitch here is that the Mage_Adminhtml_Block_Report_Grid is expecting specific methods to be in this collection. Those methods are:

  • public function setStoreIds($storeIds)
  • public function setDateRange($from, $to)

I am not running the query by store, so I don't care about that. There are examples in other classes if you need to see how to do it, but basically it is adding the store_id to the where clause.

I do however want to add some date range stuff, so I simply call a protected method in my collection to set up the SQL.

If you are having trouble with your SQL, you can debug it easily by changing your load to: parent::load(true, true). This will print out the SQL to the screen and the system.log. Also something that stumped me for a while was that my data was not necessarily showing up on the correct date in my report. This has to do with some code in Mage_Reports_Model_Mysql4_Report_Collection class.

 public function getReportFull($from, $to)
    {
        return $this->_model->getReportFull($this->timeShift($from), $this->timeShift($to));
    }

    public function timeShift($datetime)
    {
        return Mage::app()->getLocale()->utcDate(null, $datetime, true, Varien_Date::DATETIME_INTERNAL_FORMAT)->toString(Varien_Date::DATETIME_INTERNAL_FORMAT);
    }

It does some "time shifting" based on locale, so BE AWARE.

The only other things I haven't talked about are the (empty) Data.php (helper) -

<?php

class Super_Awesome_Helper_Data extends Mage_Core_Helper_Abstract
{

}

...and the normal model class that you should already know how to create. All that has is the init of the resource model in the construct -

<?php

class Super_Awesome_Model_Simple extends Mage_Core_Model_Abstract
{
	protected function _construct()
	{
		parent::_construct();
		$this->_init('awesome/simple');
	}
}

Complex Report

With this, you will see a lot of duplication, but I want to be thorough.

The table structure (setup script):

<?php

$installer = $this;

$installer->startSetup();

$installer->run("

-- DROP TABLE IF EXISTS {$this->getTable('super_awesome_example_complex')};
CREATE TABLE {$this->getTable('super_awesome_example_complex')} (
`id` INT NOT NULL AUTO_INCREMENT ,
`description` VARCHAR( 100 ) NOT NULL ,
`value` DECIMAL(12,2) NOT NULL ,
`period` DATE NOT NULL ,
PRIMARY KEY ( `id` )
) ENGINE = MYISAM ;

INSERT INTO {$this->getTable('super_awesome_example_complex')} (`description`, `value`, `period`) values ('Example One Description', 10.00, '2011-02-01');
INSERT INTO {$this->getTable('super_awesome_example_complex')} (`description`, `value`, `period`) values ('Example Two Description', 12.50, '2011-02-15');
INSERT INTO {$this->getTable('super_awesome_example_complex')} (`description`, `value`, `period`) values ('Example Three Description', 5.35, '2011-03-01');
INSERT INTO {$this->getTable('super_awesome_example_complex')} (`description`, `value`, `period`) values ('Example Four Description', 7.67, '2011-03-04');
INSERT INTO {$this->getTable('super_awesome_example_complex')} (`description`, `value`, `period`) values ('Example Dupe', 1.23, '2011-03-01');
INSERT INTO {$this->getTable('super_awesome_example_complex')} (`description`, `value`, `period`) values ('Example Dupe', 2.34, '2011-03-02');
INSERT INTO {$this->getTable('super_awesome_example_complex')} (`description`, `value`, `period`) values ('Example Dupe', 3.45, '2011-03-03');

");

$installer->endSetup();

The types of classes needed:

  • Report Controller
  • Report Block
  • Report Grid
  • Configs (Menu and Table Definitions)
  • Model, Resource Model, Collection Model
  • The layout.

The Folder Structure (can be different if you want it to be):

Super
  |_ Awesome
      |_Block
      |   |_Adminhtml
      |       |_Report
      |          |_Complex
      |          |   |_Grid.php
      |          |_Complex.php
      |_controllers
      |    |_Adminhtml
      |       |_Report
      |          |_ExampleController.php
      |_etc
      |   |_adminhtml.xml
      |   |_config.xml
      |_Helper
      |   |_Data.php
      |_Model
            |_Mysql4
            |    |_Report
            |    |   |_Complex
            |    |      |_Collection.php
            |    |_Complex.php
            |_Complex.php

And we also have the awesome.xml in design/adminhtml/default/default/layout.

First we create the menu item -
Contents of adminhtml.xml -

<?xml version="1.0"?>
<config>
    <menu>
        <awesome translate="title" module="awesome">
            <title>Awesome</title>
            <sort_order>15</sort_order>
            <children>
                <complex translate="title" module="awesome">
                    <title>Complex Report</title>
                    <sort_order>1</sort_order>
                    <action>adminhtml/report_example/complex</action>
                </complex>
            </children>
        </awesome>
    </menu>
</config>

Then we create the necessary config.xml settings:

<config>
    <modules>
        <Super_Awesome>
            <version>0.1.2</version>
        </Super_Awesome>
    </modules>
    <adminhtml>
        <!-- The <layout> updates allow us to define our block layouts in a seperate file so are aren't messin' with the magento layout files.  -->
    	<layout>
			<updates>
				<awesome>
					<file>awesome.xml</file>
				</awesome>
			</updates>
		</layout>
	</adminhtml>
	<admin>
		<!--
			Here we are telling the Magento router to look for the controllers in the Super_Awesome_controllers_Adminhtml before we look in the
			Mage_Adminhtml module for all urls that begin with /admin/controller_name
		 -->
		<routers>
			<adminhtml>
				<args>
					<modules>
						<awesome before="Mage_Adminhtml">Super_Awesome_Adminhtml</awesome>
					</modules>
				</args>
			</adminhtml>
		</routers>
	</admin>

    <global>
    	<models>
            <awesome>
                <class>Super_Awesome_Model</class>
                <resourceModel>awesome_mysql4</resourceModel>
            </awesome>
            <awesome_mysql4>
                <class>Super_Awesome_Model_Mysql4</class>
                <entities>
                    <complex>
                        <table>super_awesome_example_complex</table>
                    </complex>
                </entities>
            </awesome_mysql4>
        </models>

        <resources>
            <awesome_setup>
                <setup>
                    <module>Super_Awesome</module>
                </setup>
                <connection>
                    <use>core_setup</use>
                </connection>
            </awesome_setup>
            <awesome_write>
                <connection>
                    <use>core_write</use>
                </connection>
            </awesome_write>
            <awesome_read>
                <connection>
                    <use>core_read</use>
                </connection>
            </awesome_read>
        </resources>

   	 	<blocks>
            <awesome>
                <class>Super_Awesome_Block</class>
            </awesome>
        </blocks>
        <helpers>
            <awesome>
                <class>Super_Awesome_Helper</class>
            </awesome>
        </helpers>
    </global>
</config>

Now we create our controller -

<?php

class Super_Awesome_Adminhtml_Report_ExampleController extends Mage_Adminhtml_Controller_Action
{

	public function _initAction()
	{

		$this->loadLayout()
		->_addBreadcrumb(Mage::helper('awesome')->__('Awesome'), Mage::helper('awesome')->__('Awesome'));
		return $this;
	}

	public function _initReportAction($blocks)
	{
		if (!is_array($blocks)) {
			$blocks = array($blocks);
		}

		$requestData = Mage::helper('adminhtml')->prepareFilterString($this->getRequest()->getParam('filter'));
		$requestData = $this->_filterDates($requestData, array('from', 'to'));
		$params = new Varien_Object();

		foreach ($requestData as $key => $value) {
			if (!empty($value)) {
				$params->setData($key, $value);
			}
		}

		foreach ($blocks as $block) {
			if ($block) {
				$block->setPeriodType($params->getData('period_type'));
				$block->setFilterData($params);
			}
		}
		return $this;
	}

	public function complexAction()
	{
		$this->_title($this->__('Awesome'))->_title($this->__('Reports'))->_title($this->__('Complex Report'));

		$this->_initAction()
		->_setActiveMenu('awesome/report')
		->_addBreadcrumb(Mage::helper('awesome')->__('Complex Example Report'), Mage::helper('awesome')->__('Complex Example Report'));

		$gridBlock = $this->getLayout()->getBlock('adminhtml_report_complex.grid');
		$filterFormBlock = $this->getLayout()->getBlock('grid.filter.form');

		$this->_initReportAction(array(
		$gridBlock,
		$filterFormBlock
		));

		$this->renderLayout();

	}

	public function exportComplexCsvAction()
	{
		$fileName   = 'complex.csv';
	 	$grid       = $this->getLayout()->createBlock('awesome/adminhtml_report_complex_grid');
        $this->_initReportAction($grid);
        $this->_prepareDownloadResponse($fileName, $grid->getCsvFile($fileName));
	}

}

So this is where the "complex" report is a little more complex. The layout that is loaded at first is from the awesome.xml file:


        
        
            
                
                    
                        report_type
                        0
                    
                 
            
        
    

So with the layout and the controller combined we know that we have to worry about 3 different blocks:

  1. awesome.xml: awesome/adminhtml_report_complex (Super_Awesome_Block_Adminhtml_Report_Complex)
  2. controller: adminhtml_report_complex.grid (Super_Awesome_Block_Adminhtml_Report_Complex_Grid)
  3. awesome.xml and controller: grid.filter.form (Mage_Adminhtml_Block_Report_Filter_Form) - This is a Mage class and we don't need to touch this.

The first block is the Grid container. Here is his contents -

<?php

class Super_Awesome_Block_Adminhtml_Report_Complex extends Mage_Adminhtml_Block_Widget_Grid_Container
{
 	public function __construct()
    {
    	$this->_blockGroup = 'awesome';
        $this->_controller = 'adminhtml_report_complex';
        $this->_headerText = Mage::helper('awesome')->__('Complex Report');
        $this->setTemplate('report/grid/container.phtml');
        parent::__construct();
        $this->_removeButton('add');
        $this->addButton('filter_form_submit', array(
            'label'     => Mage::helper('awesome')->__('Show Report'),
            'onclick'   => 'filterFormSubmit()'
        ));
    }
	public function getFilterUrl()
    {
        $this->getRequest()->setParam('filter', null);
        return $this->getUrl('*/*/complex', array('_current' => true));
    }
}

Every line is important, so pay close attention to this file. In the parent of this class, it has the function ...

protected function _prepareLayout()
    {
        $this->setChild( 'grid',
            $this->getLayout()->createBlock( $this->_blockGroup.'/' . $this->_controller . '_grid',
            $this->_controller . '.grid')->setSaveParametersInSession(true) );
        return parent::_prepareLayout();
    }

... which auto generates the block "awesome/adminhtml_report_complex_grid" with the name adminhtml_report_complex.grid. This is #2 in the "blocks we care about" list.

Here is the contents of Super_Awesome_Block_Adminhtml_Report_Complex_Grid -

<?php

class Super_Awesome_Block_Adminhtml_Report_Complex_Grid extends Mage_Adminhtml_Block_Report_Grid_Abstract
{

	protected $_columnGroupBy = 'period';

	public function __construct()
	{
		parent::__construct();
		$this->setCountTotals(true);
	}

	public function getResourceCollectionName()
    {
        return 'awesome/report_complex_collection';
    }

	protected function _prepareColumns()
	{

           $this->addColumn('period', array(
            'header'        => Mage::helper('awesome')->__('Period'),
            'index'         => 'period',
            'width'         => 100,
            'sortable'      => false,
            'period_type'   => $this->getPeriodType(),
            'renderer'      => 'adminhtml/report_sales_grid_column_renderer_date',
            'totals_label'  => Mage::helper('adminhtml')->__('Total'),
            'html_decorators' => array('nobr'),
		));

        $this->addColumn('description', array(
            'header'    =>Mage::helper('awesome')->__('Description'),
            'index'     =>'description',
            'sortable'  => false
        ));

        $currencyCode = $this->getCurrentCurrencyCode();

        $this->addColumn('value', array(
            'header'    => Mage::helper('awesome')->__('Value'),
        	'currency_code' => $currencyCode,
                'index'     =>'value',
        	'type'      => 'currency',
        	'total'     => 'sum',
                'sortable'  => false
        ));

        $this->addExportType('*/*/exportComplexCsv', Mage::helper('awesome')->__('CSV'));

        return parent::_prepareColumns();
	}

Notice that we are extending Mage_Adminhtml_Block_Report_Grid_Abstract (this is different than the "simple" report example).

In here, the part that we don't want to screw up is the getResourceCollectionName() function. It contains the Collection model that is the bane of my existance. Ready to see it?

BAAAAAAAAAAAAAAAAAM -

<?php

class Super_Awesome_Model_Mysql4_Report_Complex_Collection extends Mage_Core_Model_Mysql4_Collection_Abstract
{

	protected $_periodFormat;
	protected $_selectedColumns 	= array();
	protected $_from                = null;
	protected $_to                  = null;
	protected $_orderStatus         = null;
	protected $_period              = null;
	protected $_storesIds           = 0;
	protected $_applyFilters        = true;
	protected $_isTotals            = false;
	protected $_isSubTotals         = false;
	protected $_aggregatedColumns   = array();

	/**
	 * Initialize custom resource model
	 *
	 * @param array $parameters
	 */
	public function __construct()
	{
		$this->setModel('adminhtml/report_item');
		$this->_resource = Mage::getResourceModel('awesome/complex')->init('awesome/complex');
		$this->setConnection($this->getResource()->getReadConnection());
		$this->_applyFilters = false;
	}

	protected function _getSelectedColumns()
	{
		if (!$this->_selectedColumns) {
			if ($this->isTotals()) {
				$this->_selectedColumns = $this->getAggregatedColumns();
			} else {
				$this->_selectedColumns = array(
                    'period'         => 'period',
                    'value'    		 => 'value',
                    'description'    => 'description',
				);
				if ('year' == $this->_period) {
					$this->_selectedColumns['period'] = 'YEAR(period)';
				} else if ('month' == $this->_period) {
					$this->_selectedColumns['period'] = "DATE_FORMAT(period, '%Y-%m')";
				}
			}
		}
		return $this->_selectedColumns;

	}

	protected  function _initSelect()
	{

		if (!$this->_period) {
			$cols = $this->_getSelectedColumns();
			$cols['value'] = 'SUM(value)';
			$this->getSelect()->from($this->getTable('awesome/complex'), $cols);
			$this->_applyDateRangeFilter();
			$this->getSelect()
			->group('description')
			->order('value DESC');
			return $this;
		}
		$this->getSelect()->from($this->getTable('awesome/complex'), $this->_getSelectedColumns());

		if (!$this->isTotals()) {
			$this->getSelect()->group(array('period', 'description'));
		}

		//
		$selectUnions = array();

		// apply date boundaries (before calling $this->_applyDateRangeFilter())
		$dtFormat   = Varien_Date::DATE_INTERNAL_FORMAT;
		$periodFrom = (!is_null($this->_from) ? new Zend_Date($this->_from, $dtFormat) : null);
		$periodTo   = (!is_null($this->_to)   ? new Zend_Date($this->_to,   $dtFormat) : null);
		if ('year' == $this->_period) {

			if ($periodFrom) {
				if ($periodFrom->toValue(Zend_Date::MONTH) != 1 || $periodFrom->toValue(Zend_Date::DAY) != 1) {  // not the first day of the year
					$dtFrom = $periodFrom->getDate();
					$dtTo = $periodFrom->getDate()->setMonth(12)->setDay(31);  // last day of the year
					if (!$periodTo || $dtTo->isEarlier($periodTo)) {
						$selectUnions[] = $this->_makeBoundarySelect($dtFrom->toString($dtFormat), $dtTo->toString($dtFormat));

						$this->_from = $periodFrom->getDate()->addYear(1)->setMonth(1)->setDay(1)->toString($dtFormat);  // first day of the next year
					}
				}
			}

			if ($periodTo) {
				if ($periodTo->toValue(Zend_Date::MONTH) != 12 || $periodTo->toValue(Zend_Date::DAY) != 31) {  // not the last day of the year
					$dtFrom = $periodTo->getDate()->setMonth(1)->setDay(1);  // first day of the year
					$dtTo = $periodTo->getDate();
					if (!$periodFrom || $dtFrom->isLater($periodFrom)) {
						$selectUnions[] = $this->_makeBoundarySelect($dtFrom->toString($dtFormat), $dtTo->toString($dtFormat));

						$this->_to = $periodTo->getDate()->subYear(1)->setMonth(12)->setDay(31)->toString($dtFormat);  // last day of the previous year
					}
				}
			}

			if ($periodFrom && $periodTo) {
				if ($periodFrom->toValue(Zend_Date::YEAR) == $periodTo->toValue(Zend_Date::YEAR)) {  // the same year
					$dtFrom = $periodFrom->getDate();
					$dtTo = $periodTo->getDate();
					$selectUnions[] = $this->_makeBoundarySelect($dtFrom->toString($dtFormat), $dtTo->toString($dtFormat));

					$this->getSelect()->where('1<>1');
				}
			}

		}
		else if ('month' == $this->_period) {
			// Start of custom hackish...
			if (!$this->isTotals()) {
				$columns = $this->getSelect()->getPart('columns');
				foreach($columns as $index => $column){
					if ($column[1] == 'value'){
						$column[1] = new Zend_Db_Expr('sum(value)');
						$columns[$index] = $column;
					}
				}
				$this->getSelect()->setPart('columns', $columns);

			}

			$this->getSelect()->reset('group');
			$this->getSelect()->group(array(new Zend_Db_Expr("DATE_FORMAT(period, '%Y-%m')"), 'description'));
			// End of custom hackish...

			if ($periodFrom) {
				if ($periodFrom->toValue(Zend_Date::DAY) != 1) {  // not the first day of the month
					$dtFrom = $periodFrom->getDate();
					$dtTo = $periodFrom->getDate()->addMonth(1)->setDay(1)->subDay(1);  // last day of the month
					if (!$periodTo || $dtTo->isEarlier($periodTo)) {
						$selectUnions[] = $this->_makeBoundarySelect($dtFrom->toString($dtFormat), $dtTo->toString($dtFormat));

						$this->_from = $periodFrom->getDate()->addMonth(1)->setDay(1)->toString($dtFormat);  // first day of the next month
					}
				}
			}

			if ($periodTo) {
				if ($periodTo->toValue(Zend_Date::DAY) != $periodTo->toValue(Zend_Date::MONTH_DAYS)) {  // not the last day of the month
					$dtFrom = $periodTo->getDate()->setDay(1);  // first day of the month
					$dtTo = $periodTo->getDate();
					if (!$periodFrom || $dtFrom->isLater($periodFrom)) {
						$selectUnions[] = $this->_makeBoundarySelect($dtFrom->toString($dtFormat), $dtTo->toString($dtFormat));

						$this->_to = $periodTo->getDate()->setDay(1)->subDay(1)->toString($dtFormat);  // last day of the previous month
					}
				}
			}

			if ($periodFrom && $periodTo) {
				if ($periodFrom->toValue(Zend_Date::YEAR) == $periodTo->toValue(Zend_Date::YEAR)
				&& $periodFrom->toValue(Zend_Date::MONTH) == $periodTo->toValue(Zend_Date::MONTH)) {  // the same month
					$dtFrom = $periodFrom->getDate();
					$dtTo = $periodTo->getDate();
					$selectUnions[] = $this->_makeBoundarySelect($dtFrom->toString($dtFormat), $dtTo->toString($dtFormat));

					$this->getSelect()->where('1<>1');
				}
			}

		}

		$this->_applyDateRangeFilter();

		// add unions to select
		if ($selectUnions) {
			$unionParts = array();
			$cloneSelect = clone $this->getSelect();
			$unionParts[] = '(' . $cloneSelect . ')';
			foreach ($selectUnions as $union) {
				$unionParts[] = '(' . $union . ')';
			}
			$this->getSelect()->reset()->union($unionParts, Zend_Db_Select::SQL_UNION_ALL);
		}

		if ($this->isTotals()) {
			// calculate total
			$cloneSelect = clone $this->getSelect();
			$this->getSelect()->reset()->from($cloneSelect, $this->getAggregatedColumns());
		} else {
			// add sorting
			$this->getSelect()->order(array('period ASC', 'value DESC'));
		}

		return $this;
	}

	protected function _makeBoundarySelect($from, $to)
	{
		$cols = $this->_getSelectedColumns();
		$cols['value'] = 'SUM(value)';
		$sel = $this->getConnection()->select()
		->from($this->getResource()->getMainTable(), $cols)
		->where('period >= ?', $from)
		->where('period <= ?', $to)
		->group('description')
		->order('value DESC');
		return $sel;
	}

	public function addStoreFilter($storeIds)
	{
		$this->_storesIds = $storeIds;
		return $this;
	}

	public function addOrderStatusFilter($orderStatus)
	{
		$this->_orderStatus = $orderStatus;
		return $this;
	}

	protected function _applyStoresFilterToSelect(Zend_Db_Select $select)
	{
		return $this;
	}

	public function setAggregatedColumns(array $columns)
	{
		$this->_aggregatedColumns = $columns;
		return $this;
	}

	public function getAggregatedColumns()
	{
		return $this->_aggregatedColumns;
	}

	public function setDateRange($from = null, $to = null)
	{
		$this->_from = $from;
		$this->_to = $to;
		return $this;
	}

	public function setPeriod($period)
	{
		$this->_period = $period;
		return $this;
	}

	protected function _applyDateRangeFilter()
	{
		if (!is_null($this->_from)) {
			$this->getSelect()->where('period >= ?', $this->_from);
		}
		if (!is_null($this->_to)) {
			$this->getSelect()->where('period <= ?', $this->_to);
		}
		return $this;
	}

	public function setApplyFilters($flag)
	{
		$this->_applyFilters = $flag;
		return $this;
	}

	public function isTotals($flag = null)
	{
		if (is_null($flag)) {
			return $this->_isTotals;
		}
		$this->_isTotals = $flag;
		return $this;
	}

	public function isSubTotals($flag = null)
	{
		if (is_null($flag)) {
			return $this->_isSubTotals;
		}
		$this->_isSubTotals = $flag;
		return $this;
	}

	public function load($printQuery = false, $logQuery = false)
	{
		if ($this->isLoaded()) {
			return $this;
		}
		$this->_initSelect();
		if ($this->_applyFilters) {
			$this->_applyDateRangeFilter();
		}
		return parent::load($printQuery, $logQuery);
	}

}

So remember the part about "I'm not sure how it all works"? Yeah. So I will talk about snippets that I found are important, but might not have a lot of info on why:
1. When I didn't have this, it complained about "period" in the where clause:

$this->_applyFilters = false;

2. Because I am not already aggregating the sums in my table (BAD ME!), I had to introduce the following code:

			// Start of custom hackish...
			if (!$this->isTotals()) {
				$columns = $this->getSelect()->getPart('columns');
				foreach($columns as $index => $column){
					if ($column[1] == 'value'){
						$column[1] = new Zend_Db_Expr('sum(value)');
						$columns[$index] = $column;
					}
				}
				$this->getSelect()->setPart('columns', $columns);

			}

			$this->getSelect()->reset('group');
			$this->getSelect()->group(array(new Zend_Db_Expr("DATE_FORMAT(period, '%Y-%m')"), 'description'));
			// End of custom hackish...

I found that if I wanted to group my period by a date that was formated, I had to include the SAME formatted date in the group clause.
3. I wanted the SUM of the value column, so you will see this in lots of places:

$cols['value'] = 'SUM(value)';

Just like in the simple report, this report also needs the "resource model", the "model", and the empty helper.

I hope this gives you SOME hints and/or direction in creating reports in magento.

Posted by Ben Robie

Comments (22) Trackbacks (0)
  1. Thanks fo much for this! It is a much better explanation than any other attempt at this process. I really appreciate the “whys” rather than just dumping out the example code.

  2. Hi there,

    Thank you for the post, it’s really awesome. I have a doubt, though: you talk about an awesome.xml archive but you don’t say where you put it. I suppose it’s on etc subdirectory, as usual, but I’m pretty new to Magento and still learning.

    Also, I’m gonna ask you about a problem I have. Perhaps you can help me a little bit.

    My problem is that I need to put some extra filter to a “complex” report (in fact to sales->Orders report) and I’ve tried and looked everywhere in the code for an example, but I cannot find my way between so many controllers, collections, models and functions. It’s like a little inferno to me.

    So far I’ve deduced that I need to add some functions to some collection class but I neither know which is the collection/class nor I know which functions I need to use.

    ¿Any pointing or idea?

    Thank you,


    Dani

  3. Dani,

    To your first question – I put the xml file in: app/design/adminhtml/default/default/layout/awesome.xml

    Your second question, I can’t answer, but I can give a few classes to look at:
    Mage_Sales_Block_Adminhtml_Report_Filter_Form – to put your new filter in.
    Mage_Sales_Model_Mysql4_Report_Collection_Abstract – the filters are applied here. Keep in mind that you can’t rewrite the abstract directly, so you will have to do some extra work here.

    Good luck!

  4. Hey there again, Ben,

    Your pointings where correct but I couldn’t have any success as the table from which is build sales->orders report is not related with any other table and I cannot make any joins.

    So I’m trying a similar approach. My idea is to populate a table using an event observer and then building the report using your tutorial from that table.

    So far I’ve replicated the Grid Block container and Grid, the controller, config files, Collection and Model. I have written a simple Resource from another post from your blog. Now I’m getting this error when I try to display the values on the report:

    Fatal error: Call to a member function getReportFull() on a non-object in /home/hhnavvlf/public_html/magento/app/code/core/Mage/Reports/Model/Mysql4/Report/Collection.php on line 189

    It’s the simple report which I’m working on. It seems on that class there’s an attribute ($_model) which should contain some object but at the time of calling getFullReport it’s null.

    ¿Any ideas on that?

    Thank you,


    Dani

  5. “your pointings were correct”, sorry for typo… v_v

  6. Thanks for the article Ben; the only place on the web that I’ve found that explains the hows, whys, and wherefores behind Magento’s rather bizarre design decisions, rather than simply copy-paste reliance on Magento’s automagic.

  7. Can someone please post a link to a zip file with files from a working example? I can’t seem to get this to work.

  8. You forgot to include:

    Super_Awesome_Block

    on your simple example.

    The adminhtml hierarchy of the blocks in the module won’t be recognized without that snippet of xml in the section.

    Great article by the way!

  9. hello all,
    you can follow my website to get any help for report building…
    enjoy.

  10. Trenzign creates high quality Magento extensions for your online business needs.

    Visit us at http://trenzign.com/ for creative and useful extensions!

    Some of our extensions: One Step Checkout | Out of Stock Notification

    Hire Us!

    • Hi Trenzign,
      Either remove your website name while submitting the post or at least make sure that your website is running smoothly. Each of the click is redirecting the user to 404 error (might be you removed index.php from the URLs wrongly or have not gone well), please check with that. With this type of blunder you are wishing to get hired….Really Amazing.

      Thanks.

  11. Yes,

    You forgot to include:
    Super_Awesome_Block
    on your simple example.
    The adminhtml hierarchy of the blocks in the module won’t be recognized without that snippet of xml in the section.
    Great article by the way!

    Trenzign creates high quality Magento extensions for your online business needs.

    Visit us at http://trenzign.com/ for creative and useful extensions!

    Some of our extensions: One Step Checkout | Out of Stock Notification

    Hire Us!

  12. Hello all,

    I need to add Fields in Customer reports like gender. date of birth

    Cam any one help me please?

  13. Hi Ben,

    Nice Article,thanks for sharing this information in very detail.

    Magento Developers

  14. Hi. Could someone explain me, what exactly in awesome.xml do? I got issue with this layout update.
    Thank you all.
    Andrew

  15. hi,
    I was doing the “simple report” but doens’t work =( I don’t kown where the mistake is (It is my firts time with magento)… My grid doesn’t appear… I imagine if the problem is with the collection (I think the Block is ok, I included “Super_Awesome_Block”)… some one can help me?

  16. Hi there! Do you know if they make any plugins to assist with Search Engine Optimization? I’m trying to
    get my blog to rank for some targeted keywords but I’m
    not seeing very good gains. If you know of any please share.
    Kudos!

  17. It’s hard to find your blog in google. I found it on 22 spot, you should build quality backlinks
    , it will help you to get more visitors. I know how to help you, just search
    in google – k2 seo tips

  18. It’s hard for me to make such reports programmatically, thus I prefer ready-made solutions. For advanced reports I’m using Handy sales tabs by Amasty


Leave a comment

(required)

Trackbacks are disabled.