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).

1	<title>Artist Sold Works</title>

2	adminhtml/report_product/artistsold

Add the followind code in the of

(near line 370).

1	<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

1	$this->_controller = 'report_product_sold';

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

to

1	$this->_controller = 'report_product_artistsold';

2	$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:

01	$this->addColumn('artistId', array(

02	'header'    =>Mage::helper('reports')->__('Artist'),

03	'width'     =>'120px',

04	'index'     =>'artistname',

05

));

06

07	$this->addColumn('sale_percentage', array(

08	'header'    =>Mage::helper('reports')->__('Artist Share'),

09	'width'     =>'60px',

10	'index'     =>'sale_percentage',

11	'align'     =>'right'

12

));

13

14	$this->addColumn('base_price_total', array(

15	'header'    =>Mage::helper('reports')->__('Total Product Base Price ($)'),

16	'width'     =>'60px',

17	'index'     =>'base_price_total',

18	'align'     =>'right',

19	'total'     =>'sum',

20	'type'      =>'number'

21

22

));

23

24	$this->addColumn('artist_earned', array(

25	'header'    =>Mage::helper('reports')->__('Artist Earned ($)'),

26	'width'     =>'60px',

27	'index'     =>'artist_earned',

28	'align'     =>'right',

29	'total'     =>'sum',

30	'type'      =>'number'

31

));

6. Add new functions to

app/code/core/Mage/Adminhtml/controllers/Report/ProductController.php

1	public function artistsoldAction()

2

{

3	$this->_initAction()

4	->_setActiveMenu('report/product/artistsold')

5	->_addBreadcrumb(Mage::helper('reports')->__('Artists Sold Works'), Mage::helper('reports')->__('Artists Sold Works'))

6	->_addContent($this->getLayout()->createBlock('adminhtml/report_product_artistsold'))

7	->renderLayout();

8

}

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:

1	public function setDateRange($frmdate, $todate)

2

{

3	$this->_reset()

4	->addAttributeToSelect('*')

5	->addOrderedQtyForArtistSold($frmdate,$todate);

6	return $this;

7

}

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:

001	public function addOrderedQtyForArtistSold($frm = '', $to = '')

002

{

003	if(key_exists('report',$_SESSION)) {

004	$artistId = $_SESSION['report']['artistid'];

005

}

006

else {

007	$artistId ='';

008

}

009

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

011	$qtyOrderedFieldName = 'qty_ordered';

012

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

014	$productIdFieldName = 'product_id';

015

016	$productEntityIntTable = (string)Mage::getConfig()->getTablePrefix() .'catalog_product_entity_varchar';

017	$adminUserTable = $this->getTable('admin_user');

018	$artistsTable = $this->getTable('appartists');

019	$eavAttributeTable = $this->getTable('eav/attribute');

020

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

022

023	# This was added by Dev1 to get the configurable items in the list & not to get the simple products

024	$compositeTypeIds = Array (

025	'0' => 'grouped',

026	'1' => 'simple',

027	'2' => 'bundle'

028

);

029

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

031

032	if ($frm != '' && $to != '') {

033	$dateFilter = " AND `order`.created_at BETWEEN '{$frm}' AND '{$to}'";

034

} else {

035	$dateFilter = "";

036

}

037

038	$this->getSelect()->reset()->from(

039	array('order_items' => $qtyOrderedTableName),

040	array('ordered_qty' => "SUM(order_items.{$qtyOrderedFieldName})",'base_price_total' => "SUM(order_items.price)")

041

);

042

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

044

045	$stateAttr = $order->getAttribute('state');

046	if ($stateAttr->getBackend()->isStatic()) {

047

048	$_joinCondition = $this->getConnection()->quoteInto(

049	'order.entity_id = order_items.order_id AND order.state<>?', Mage_Sales_Model_Order::STATE_CANCELED

050

);

051	$_joinCondition .= $dateFilter;

052

053	$this->getSelect()->joinInner(

054	array('order' => $this->getTable('sales/order')),

055	$_joinCondition,

056

array()

057

);

058

} else {

059

060	$_joinCondition = 'order.entity_id = order_state.entity_id';

061	$_joinCondition .= $this->getConnection()->quoteInto(' AND order_state.attribute_id=? ', $stateAttr->getId());

062	$_joinCondition .= $this->getConnection()->quoteInto(' AND order_state.value<>? ', Mage_Sales_Model_Order::STATE_CANCELED);

063

064	$this->getSelect()

065	->joinInner(

066	array('order' => $this->getTable('sales/order')),

067	'order.entity_id = order_items.order_id' . $dateFilter,

068

array())

069	->joinInner(

070	array('order_state' => $stateAttr->getBackend()->getTable()),

071	$_joinCondition,

072

array());

073

}

074

075	$this->getSelect()

076	->joinInner(array('e' => $this->getProductEntityTableName()),

077	"e.entity_id = order_items.{$productIdFieldName}")

078	->group('e.entity_id')

079	->having('ordered_qty > 0');

080

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

082

083	$this->getSelect()

084	->joinInner(

085	array('pei' => $productEntityIntTable),

086	"e.entity_id = pei.entity_id",

087

array())

088	->joinInner(

089	array('ea' => $eavAttributeTable),

090	"pei.attribute_id=ea.attribute_id AND ea.attribute_code='artistid'",

091

array())

092	->joinInner(

093	array('au' => $adminUserTable),

094	"au.user_id=pei.value",

095	array("artistname" => "CONCAT(firstname, ' ',lastname)"))

096	->joinInner(

097	array('ar' => $artistsTable),

098	"ar.artistId=au.user_id".$artistIdConcat,

099	array("sale_percentage" =>"CONCAT(sale_percentage,'%')","artist_earned" => "((SUM(order_items.price)) * (sale_percentage)) / 100"));

100

101	return $this;

102

}