How to Create custom Reports in Magento Admin

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.

1. The title of the report is: ‘Artist Sold Works’. To add the new item under the Reports -> Products.

Open the ‘app/code/core/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 line (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.

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;
   }

Hope it Helps... Thanks...