How to Create Custom Reports in Magento

Magento provide many types of report to help you to know better your store performance. However, a lot of users don’t want to use the default one but generate their own report.

In this tutorial, we will guide you to create your own Magento custom report.

Step 1: Open app/code/code/Mage/Reports/etc/config.xml to add new item under  Reports/Products. (The title of example report is “Artist Sold Works”)

Step 2: Add the following code in the “Children of Products” (near line 221)

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

Step 3: Add the following code (near line 370)

<title>Artists Sold Works</title>

Step 4: Copy the following file

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

to

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

Step 5: Copy the following directories

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

to

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

and

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

to

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

Step 4: Change the class name in the file Artistsold.php from

Mage_Adminhtml_Block_Report_Product_Sold

to

Mage_Adminhtml_Block_Report_Product_Artistsold

Change these 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');

Step 5:  Add/Modify the columns

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

$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'
));

Step 6:  Add new functions

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

Step 7: Open the file

app/code/core/Mage/Reports/Model/Mysql4/Product/Artistsold/Collection.php

Step 8: Rename the class name from
Mage_Reports_Model_Mysql4_Product_Sold_Collection
to
Mage_Reports_Model_Mysql4_Product_Artistsold_Collection

Step 9: Customize the function setDateRange() as per your need

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

Step 10: Copy the function addOrderedQty() to addOrderedQtyForArtistSold()

in the file

app/code/core/Mage/Reports/Model/Mysql4/Product/Collection.php

It allows you to get the new fields, to alter the SQL query. (You can change the function to get extra columns as per your need.)

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

This is the result: