Sep
7
2009

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>

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

Artist_sold_works_report

Related Posts

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.

  • http://www.windy-beach.co.uk sam

    looks good, will let you know if site still functional after my hacks :)

  • 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

  • admin

    Hi Mtness,
    I will try to switch to ‘local’ instead of ‘core’. Let me know if you get something.
    Thanks.

  • mtness

    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

  • salo

    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

  • admin

    Hi Salo,
    I have updated the Post. Hope that will help you.

  • eunecte

    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

  • benak

    I have the same problem as ennecte, does anyone manage to display dropdown attribute value ??? Like manufacturer attribute ?
    Please help !!
    Thank you

  • http://stereointeractive.com/blog stereoscott

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

  • john

    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?

  • admin

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

  • Mike

    @mtness: Were you able to get move the custom report code into the local pool? If so, what steps did you take?

    Thanks,
    Mike

  • Ruben

    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!

  • http://www.getmeafreelancer.com Raj

    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

  • admin

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

  • Om

    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

  • Andrea

    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

  • http://www.mshops.biz shehz

    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

  • admin

    @shehz, I guess you might not configure the xml files properly.

  • rija

    when i try
    if(key_exists(’report’,$_SESSION)) {
    $artistId = $_SESSION['report']['artistid'];
    }

    $artistId not exist

    Cas you help me

  • Pedro Victor

    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

  • Kapil

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

  • http://www.bluelotuscreative.com/ Bobby

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

  • Rameez

    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.

  • Anonymous

    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.

  • Rameez

    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?

  • Alex

    Hi,
    How did you add the Artist to the filter that is next to “Show by”?
    Can’t seem to add that bit.

    thanks.

  • Anonymous

    @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 } ?>
  • Rameez

    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

  • Jonathan123

    Any luck getting a report to work from local?

  • Khojani

    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.

  • Khojani

    I have done it through collection.php file and it works.

  • Khojani

    Hi Kapil, Excellent work mate, Its working like a charm.
    good on you, Keep it up.
    Rameez.

  • Dave

    Hi

    Thanks for the tutorial, workes like a charm!
    I think the post on how to add Artist next to show by is missing some code cause i cant seem to get it to work, any chance you can re post the xeact code for the phtml?

    That would really help!

    Thanks .

  • Mitali Mehta

    Hi,

    How did you use addAttributeToSelect() function on this collection.
    I tried to create same function in which it calls addAttributeToSelect() function.but it gave me error that this method is undefined.

    Thanks in Advance……..

  • Anonymous

    Hello Mitali

    addAttributeToSelect() is one of the core Magento functions. It should not give an error. However if its giving an error then can you send me the screenshot, what error its showing ?

  • Dave

    Hi

    Please Can You Repost how to add Artist next to show by, i cant seem to get it to work, any chance you can re post the xeact code for the phtml?

    That would really help!

    Thanks .

  • Auk

    Hello Jignesh ,

    Nice work .. really helped me a lot the addOrderedQtyForArtistSold() isn’t working but others is fine.

    I have also check the last 2 codes carefully there is nothing wrong but it is not working … can you tell me where i should look for..

    Thanks in advance

  • Auk

    after checking for long time, i found that it is call from the
    D:xampplitehtdocsmagshopappcodecoreMageReportsModelMysql4ProductSoldCollection.php all the time .. no matter what you change in setDateRange

  • ianh

    @jaggsr

    Hi would you have a copy of getArtists() available to share? (and file location)
    thanks in advance.

  • Zaid_22

    getSelect() not prepare collection

  • Jinesh

    hi,

    Good Post…

    But i got some issues with show by combo…

    When i select show by month or year, result will show but when i select show by day, it will give no records found.

    Please help me to solve this issue.

    Thanks
    Jinesh soni

  • Emre

    Hi Jignesh, great Tutorial.
    I wanted to create an extra column for the Report Customer Totals.
    I wanted to add the field “Suffix” from the Customer Name to the Report, do you have any ideas how to solve this?
    Many thanks for your advice.

    Emre

  • Arunsharma817

     Hi Jignesh SIr,

              Could you please post your whole Artist Module …….? So that we can get the more info. and all will stop to disturb you…………Because i need same module but authorsid in place of artist id.

    Thanks In Advance

    Arun Sharma From Indore

  • Dgent

    One idea to copy whatever you have done in core, to local, could be just copy the complete files from core and keep it under local creating new extension and changing the class name and all.

    In the same way we copied checkmo and gave it new name however we extended it to interact with  Bank.

    Dgent!

  • http://www.gowebbaby.com/ecommerce Ecommerce Developer

    Great article …Thanks for your great information, the contents are quiet interesting.I will be waiting for your next post.

  • http://www.hirephpprogrammerindia.com/magento-development.php Magento Development India

    Hi, Jignesh

    Very good post. This is useful tutorial. I really do think that I have learned so much. This was really awesome informative post.
     

  • Three1dec

    Hi can any one provide zip archive for this module/files. I followed this instruction but it doesn’t work for me.

blog comments powered by Disqus