7
2009
Create custom Reports in Magento Admin
An article by 
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>
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.
Related Posts
- Magento – Add custom comment box to each product in Cart
- Add to Cart with custom attributes & values
- Add custom input fields on Admin html forms in Magento
- Automated quick simple product creation in Magento
- Magento 1.4.1 – Onepage checkout Paypal WPP bug
About the Author: Jignesh Patel
Jignesh Patel is a geek, an engineer & a web developer, who focus on developing web sites which look great, work great and can still work on older web browsers. Expertise in PHP, MySQL, JS, Magento & Wordpress.
Hi Kapil, Excellent work mate, Its working like a charm.
good on you, Keep it up.
Rameez.
I have done it through collection.php file and it works.
Hi, Can u plz mensioned the procedure or steps to convert this product report into the sales part of admin section… like ADMIN->Reports->Sales->Artist Sold Works,
Thanks in advance.
Any luck getting a report to work from local?
Hi, Can U help me with Adding a column call Total for (Product Selling Price * QTY) in product sold report, what changes Should I bring to the collection.php file for get that column
@Alex, you will need to modify the phtml to add that dropdown to report.
The file is: adminhtml/default/default/template/report/grid.phtml.
But that will add the drop-down to all type of reports.
So I did it this way:
< ?php if(ereg('artistsold',$_SERVER['REQUEST_URI'])) { ?> < ?php echo $this->__('Artist') ?>:
< ?php } ?>
Hi,
How did you add the Artist to the filter that is next to “Show by”?
Can’t seem to add that bit.
thanks.
Alya, Me bahu mathakut kari, Cache Cleared kari, Carefully try kairyu pan salu natthi thatu. 2 di thi mathu chhu pan natthi med padto. Any Idea what could be the reason for the menu item is not clickable?
Hello Rameez,
If you have followed the above steps carefully, then it should work. May be you did not clear your cache. I have helped some people for the same & its working from them.
Hi Thanks for the article, but It didn’t work for me at all, I wantto know the technique to create report. Your coding is successfully creates a menu Item under Admin-Reports-Product-Artist Sold Works But it is not clickable. So we can not go pass this to check what is is doing.
Thanks. If you solved it, then can u please post it.
Thanks,
Rameez.
A pretty important omission from the above, in app/code/core/Mage/Adminhtml/Block/Report/Product/Artistsold/Grid.php you must change the protected function _prepareCollection() to use the new report.
Change
->initReport(‘reports/product_sold_collection’);
to
->initReport(‘reports/product_artistsold_collection’);
Hey thanks for a wonderful tutorials .. tried in Local but wasnt successful so will be waiting for it form you guys and Admin ..
Also i wanted to have the new added fields to not only show in the Grid but also Export (to CSV & Excel) the same set of data with new field values
so incase if anyone else need to do the same, here are the simple changes :
-> In ProductController.php Copy exportSoldCsvAction & exportSoldExcelAction methods to the same file and rename it to say exportArtistSoldCsvAction & exportArtistSoldExcelAction,
-> then you may change the $fileName
-> then change the createBlock to path as ‘adminhtml/report_product_artistsold_grid’
-> then in your app/code/core/Mage/Adminhtml/Block/Report/Product/Artistsold/Grid.php file
change the line
From :
$this->addExportType(‘*/*/exportSoldCsv’, Mage::helper(‘reports’)->__(‘CSV’));
$this->addExportType(‘*/*/exportSoldExcel’, Mage::helper(‘reports’)->__(‘Excel’));
To:
$this->addExportType(‘*/*/exportArtistSoldCsv’, Mage::helper(‘reports’)->__(‘CSV’));
$this->addExportType(‘*/*/exportArtistSoldExcel’, Mage::helper(‘reports’)->__(‘Excel’));
Somebody can do the report in local?, if yes please can you give me more information, I’m new in Magento and I want do local reports, not in core
thanks
when i try
if(key_exists(’report’,$_SESSION)) {
$artistId = $_SESSION['report']['artistid'];
}
$artistId not exist
Cas you help me
Hi
Thanks for the tutorial . I followed all your steps and created the fields that you created. I manage to get the menu under product
But When I click on Artist Sold Works . It gives me 404 error Page not found.
Can you please guide me what am I doing wrong
Thanks
@shehz, I guess you might not configure the xml files properly.
Hej man,
I am trying to develop a custom report for my company. So i managed to modify one of the existing reports but can not succeed in adding some extra search criteria as you added the Author. Your code was of great help for me as it pointed me where to search for solving the problem. I have one question for you, where is the following session set:
if(key_exists(‘report’,$_SESSION)) {
$artistId = $_SESSION['report']['artistid'];
}
my report is local and i have placed the if statement the same class as you did but seems can not find the report into the SESSION. So do you have any idea?? Any help would be greatly appreciated. Thanks in advance
Hi,
can anybody help me to develop a module in admin so that some specific users can add products.
like in my case i have created the wholeseller users and give them permission on my custom module.
i need to just a duplicate product management which can give facility to add/edit delete products on attribute basis.
thanks
Great Tutorial Mate!
But can you please describe little bit more if we want to add a big module in Administration!
Actually i am building this http://www.getmybrand.com website and need to add new module within its front end and back end!
Any help would be appreciated!
Regards,
Raj
@Raj,
You can try the module creator. Using that you can create a new Module, which will be very basic.
Then you will need to customize it based on your needs.
Hi, you made a good tutorial to do nice custom reports.
Can you give me some lights how to do a search filter, like you show in the image, to return only the artist we want?
cheers!
@mtness: Were you able to get move the custom report code into the local pool? If so, what steps did you take?
Thanks,
Mike
so i’m bumping down the road to getting this as “local” code as opposed to in the “core”. I will post my findings if/when i’m successful. my only question so far is about your attributes. i’m assuming that you have 2 attributes for artistid and sale_percentage? are these the code names in the database? i think the other two columns are calculated?
@john, I have actually a separate Module called Artists, where I can manage them so for each artist, there are some attributes. sale_percentage is one of them. So its an attribute for an Artist. On the other hand, artist_id is the attribute for the products. So this artist_id for product is related with the id of an artist in the Artists module.
Thank you so much for posting this. It was a huge help to me and I’m sure many others! I’m interested to know how you added your own custom filter in the grid view (for artist).
I have the same problem as ennecte, does anyone manage to display dropdown attribute value ??? Like manufacturer attribute ?
Please help !!
Thank you
Hello,
With the help of this I managed to change a sales report. But in the grid.php how do I retrieve the value of a “dropdown-attribute”. in the report it is always the ID of the option that shows up but not the value.
Thank You
eunecte
Jignesh Patel, please share your
app/code/core/Mage/Adminhtml/Block/Report/Product/Artistsold/Grid.php and app/code/core/Mage/Reports/Model/Mysql4/Product/Artistsold/Collection.php
Hi Salo,
I have updated the Post. Hope that will help you.
Hi there again!
I will try to describe you the problems I have run into, maybe you might be able to point me in the right direction.
I want to extend the “sales report” so that i have additional columns that show the SUM per payment Method.
As I see it, the table “sales_order” does not contain this information, and i have currently no idea where to place a join or whatever to get that information.
The file I altered to display an extra column is:
app\code\local\Mage\Reports\Model\Mysql4\Order\Collection.php
Would be nice to hear from you!
Kind regards,
mtness
Hi there!
Thank you very much for that insightful post.
I’m diving deeper into the subject right now, and found some stuff which will sureley be of interest to you:
To make the changes future compatible, you need to apply the changes in the “local” directory rather than “core”, which will be overwritten when Magento updates are applied. Found here:
http://www.magentocommerce.com/boards/viewthread/29535/#t104176
I will keep you informed about my progress.
Kind regards,
mtness
Hi Mtness,
I will try to switch to ‘local’ instead of ‘core’. Let me know if you get something.
Thanks.
looks good, will let you know if site still functional after my hacks