Create custom Reports in Magento Admin

Want to create a custom report in Mageto Admin?

After taking help from some forums & all I was able to generate a new Report the way I wanted.

I was looking to generate the Report for the Products sold alog with the name of the Artist to whom the product belongs to.

These are the steps to be followed / I followed.

1. The title of the report is: ‘Artist Sold Works’. To add the new item under the Reports -> Products.
Open the ‘app/code/code/Mage/Reports/etc/config.xml’
Add the followind code in the ‘children of ‘products’ (near line 221).

	<title>Artist Sold Works</title>
	adminhtml/report_product/artistsold

Add the followind code in the of (near line 370).

	
		<title>Artists Sold Works</title>
	

2. Copy files

app/code/core/Mage/Adminhtml/Block/Report/Product/Sold.php to app/code/core/Mage/Adminhtml/Block/Report/Product/Artistsold.php.

3. Copy directories

app/code/core/Mage/Adminhtml/Block/Report/Product/Sold to
app/code/core/Mage/Adminhtml/Block/Report/Product/Artistsold

app/code/core/Mage/Reports/Model/Mysql4/Product/Sold to
app/code/core/Mage/Reports/Model/Mysql4/Product/Artistsold

4. In the file Artistsold.php, change the class name from

Mage_Adminhtml_Block_Report_Product_Sold to Mage_Adminhtml_Block_Report_Product_Artistsold.

Change the lines

        $this->_controller = 'report_product_sold';
        $this->_headerText = Mage::helper('reports')->__('Products Ordered');

to

        $this->_controller = 'report_product_artistsold';
        $this->_headerText = Mage::helper('reports')->__('Artist Sold Works');

5. Add/Modify the columns in the
app/code/core/Mage/Adminhtml/Block/Report/Product/Artistsold/Grid.php

Here in my case:

        $this->addColumn('artistId', array(
            'header'    =>Mage::helper('reports')->__('Artist'),
            'width'     =>'120px',
            'index'     =>'artistname',
        ));		

        $this->addColumn('sale_percentage', array(
            'header'    =>Mage::helper('reports')->__('Artist Share'),
            'width'     =>'60px',
            'index'     =>'sale_percentage',
            'align'     =>'right'
        ));

        $this->addColumn('base_price_total', array(
            'header'    =>Mage::helper('reports')->__('Total Product Base Price ($)'),
            'width'     =>'60px',
            'index'     =>'base_price_total',
            'align'     =>'right',
            'total'     =>'sum',
            'type'      =>'number'

        ));

        $this->addColumn('artist_earned', array(
            'header'    =>Mage::helper('reports')->__('Artist Earned ($)'),
            'width'     =>'60px',
            'index'     =>'artist_earned',
            'align'     =>'right',
            'total'     =>'sum',
            'type'      =>'number'
        ));

6. Add new functions to
app/code/core/Mage/Adminhtml/controllers/Report/ProductController.php

    public function artistsoldAction()
    {
        $this->_initAction()
            ->_setActiveMenu('report/product/artistsold')
            ->_addBreadcrumb(Mage::helper('reports')->__('Artists Sold Works'), Mage::helper('reports')->__('Artists Sold Works'))
            ->_addContent($this->getLayout()->createBlock('adminhtml/report_product_artistsold'))
            ->renderLayout();
    }

7. Open the file
app/code/core/Mage/Reports/Model/Mysql4/Product/Artistsold/Collection.php.

Rename the class name from
Mage_Reports_Model_Mysql4_Product_Sold_Collection to
Mage_Reports_Model_Mysql4_Product_Artistsold_Collection

Customize the function setDateRange() in the as per your need.

Here in my case:

    public function setDateRange($frmdate, $todate)
    {
        $this->_reset()
            ->addAttributeToSelect('*')
            ->addOrderedQtyForArtistSold($frmdate,$todate);
		return $this;
    }

8. To get the new fields, to alter the sql query I copied the function addOrderedQty() to addOrderedQtyForArtistSold() in the file
app/code/core/Mage/Reports/Model/Mysql4/Product/Collection.php
And I did changes in the functions as per my need to get the extra columns.

Here in my case:

	public function addOrderedQtyForArtistSold($frm = '', $to = '')
    {
		if(key_exists('report',$_SESSION)) {
    		$artistId = $_SESSION['report']['artistid'];
		}
		else {
			$artistId ='';
		}

        $qtyOrderedTableName = $this->getTable('sales/order_item');
        $qtyOrderedFieldName = 'qty_ordered';

        $productIdTableName = $this->getTable('sales/order_item');
        $productIdFieldName = 'product_id';

		$productEntityIntTable = (string)Mage::getConfig()->getTablePrefix() . 'catalog_product_entity_varchar';
		$adminUserTable = $this->getTable('admin_user');
		$artistsTable = $this->getTable('appartists');
		$eavAttributeTable = $this->getTable('eav/attribute');

        $compositeTypeIds = Mage::getSingleton('catalog/product_type')->getCompositeTypes();

        # This was added by Dev1 to get the configurable items in the list & not to get the simple products
        $compositeTypeIds = Array (
						    '0' => 'grouped',
						    '1' => 'simple',
						    '2' => 'bundle'
							);

        $productTypes = $this->getConnection()->quoteInto(' AND (e.type_id NOT IN (?))', $compositeTypeIds);

        if ($frm != '' && $to != '') {
            $dateFilter = " AND `order`.created_at BETWEEN '{$frm}' AND '{$to}'";
        } else {
            $dateFilter = "";
        }

        $this->getSelect()->reset()->f rom(
           array('order_items' => $qtyOrderedTableName),
           array('ordered_qty' => "SUM(order_items.{$qtyOrderedFieldName})",'base_price_total' => "SUM(order_items.price)")
        );

        $order = Mage::getResourceSingleton('sales/order');

        $stateAttr = $order->getAttribute('state');
        if ($stateAttr->getBackend()->isStatic()) {

            $_joinCondition = $this->getConnection()->quoteInto(
                'order.entity_id = order_items.order_id AND order.state<>?', Mage_Sales_Model_Order::STATE_CANCELED
            );
            $_joinCondition .= $dateFilter;

            $this->getSelect()->joinInner(
                array('order' => $this->getTable('sales/order')),
                $_joinCondition,
                array()
            );
        } else {

            $_joinCondition = 'order.entity_id = order_state.entity_id';
            $_joinCondition .= $this->getConnection()->quoteInto(' AND order_state.attribute_id=? ', $stateAttr->getId());
            $_joinCondition .= $this->getConnection()->quoteInto(' AND order_state.value<>? ', Mage_Sales_Model_Order::STATE_CANCELED);

            $this->getSelect()
                ->joinInner(
                    array('order' => $this->getTable('sales/order')),
                    'order.entity_id = order_items.order_id' . $dateFilter,
                    array())
                ->joinInner(
                    array('order_state' => $stateAttr->getBackend()->getTable()),
                    $_joinCondition,
                    array());
        }

        $this->getSelect()
            ->joinInner(array('e' => $this->getProductEntityTableName()),
                "e.entity_id = order_items.{$productIdFieldName}")
             ->group('e.entity_id')
            ->having('ordered_qty > 0');

        $artistIdConcat = $artistId != '' ? " AND artistId=$artistId" : "";

        $this->getSelect()
            ->joinInner(
                array('pei' => $productEntityIntTable),
                "e.entity_id = pei.entity_id",
                array())
            ->joinInner(
                array('ea' => $eavAttributeTable),
                "pei.attribute_id=ea.attribute_id AND ea.attribute_code='artistid'",
                array())
            ->joinInner(
                array('au' => $adminUserTable),
                "au.user_id=pei.value",
                array("artistname" => "CONCAT(firstname, ' ',lastname)"))
            ->joinInner(
                array('ar' => $artistsTable),
                "ar.artistId=au.user_id".$artistIdConcat,
                array("sale_percentage" => "CONCAT(sale_percentage,'%')","artist_earned" => "((SUM(order_items.price)) * (sale_percentage)) / 100"));

        return $this;
    }

Note: Please remove the space the line 38 in box above for the word ‘f rom’. I was not able to post the article if I keep the correct word.