Create custom Reports in Magento Admin

Want to create a custom report in Magento 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 along 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>

 Copy files

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

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