8

I need to create custom report which is just like magento default reports. I go through the Internet I found this https://github.com/degdigital/magento2-customreports but In this report data is already fetched and they give column level filter functionality like this

enter image description here

I need the data fetch based on filters

For Reference I need simple report like this:

enter image description here

I hope, you guys got my point, if you have any free extension, git code and your experience please share it

Thanks

  • Did you find any solution on this? – Muhammad Anas Apr 16 '19 at 9:29
  • Though I made custom report but I know what I follow is not a best practice. I want a solution for custom report just like Magento Default report. – Muhammad Hasham Apr 16 '19 at 9:30
  • Can I start bounty on this question? – Muhammad Anas Apr 16 '19 at 9:31
  • Yes you can if you want – Muhammad Hasham Apr 16 '19 at 9:33
  • Hi I am working on same type of reports. I can provide you the extension. – Amrit Pal Singh Apr 23 '19 at 7:38
8

I had created custom report for one project. I have created sample code with needed file and uploaded on github.

You can look at below URL. https://github.com/yash7690/magento2-customreport

| improve this answer | |
  • let me check this then I will get back to you – Muhammad Hasham Apr 19 '19 at 14:38
  • No it is not working. Though it will add the filter but may be your code is incomplete because I didn't find the target action controller of filter form in your code. I also made the custom report by using same approach by storing filter data in database tables and used in collection file as I mentioned in comments. So I don't think so this should be the best practice like magento 2 default report. – Muhammad Hasham Apr 22 '19 at 12:23
  • I have checked this code before posting the comment and it worked for me. So even if you freshly install extension, it should work. I have also checked magento sales report and it is using the same approach to save data in aggregate table. It is really strange that it is not working for you. Anyways good luck. – Yash Shah Apr 22 '19 at 12:27
  • It is not working at my end. I tell you some of reasons in my above comment. Forget about whether it is working or not just tell me the detail working flow of your module in answer like explain each parts of your code including models, block, plugin, database tables and controller why you add them and what is that particular part working. I still mark it correct and it also helps others as well. Quick guide how anyone use your custom report? – Muhammad Hasham Apr 22 '19 at 13:02
  • Thanks Yash Shah for the time your have taken in this question. +1 for that – Muhammad Anas Apr 23 '19 at 7:39
8
+50

I share my working what I do, in order to do this achieve this functionality

This is my collection file, What I do, I store the filter data against admin user wise in custom table and made custom model for that table. Use it during collection loading.

Path should be like: Vendor\Module\Model\ResourceModel\Brandwisereport\Collection.php

<?php
namespace Vendor\Module\Model\ResourceModel\Brandwisereport;
//use Vendor\Module\Model\TempdataFactory;

class Collection extends \Magento\Framework\Model\ResourceModel\Db\Collection\AbstractCollection
{

    /**
     * Define resource model
     *
     * @return void
     */
    protected function _construct()
    {
        $this->_init('Vendor\Module\Model\Brandwisereport', 'Vendor\Module\Model\ResourceModel\Brandwisereport');
        $this->_map['fields']['page_id'] = 'main_table.page_id';
        $this->addFilterToMap('brand', 'eavatt.value');
        $this->addFilterToMap('sku', 'main_table.sku');
        $this->addFilterToMap('name', 'main_table.name');

        //$this->_modelTempdataFactory = $modelTempdataFactory;
        //$this->authSession = $authSession;

    }

    public function filterOrder()
    {

    $catalog_product_entity_table = $this->getTable("catalog_product_entity");
    $catalog_product_entity_inttable = $this->getTable("catalog_product_entity_int");
    $eav_attributeoptionvalue = $this->getTable("eav_attribute_option_value");
    $sales_order = $this->getTable("sales_order");
    $sales_order_address = $this->getTable("sales_order_address");


    $this->getSelect()->joinLeft(array('cpe' =>$catalog_product_entity_table), 'main_table.product_id = cpe.entity_id')
    ->columns(array('sum_qty_ordered' => new \Zend_Db_Expr("SUM(main_table.qty_ordered)"), 'total_amount' => new \Zend_Db_Expr("SUM(main_table.base_row_total)")))
    ->joinLeft(array('cpei' =>$catalog_product_entity_inttable),'cpei.row_id= cpe.row_id 
      AND cpei.attribute_id = "83" AND cpei.store_id = 0')
    ->joinLeft(array('eavatt' =>$eav_attributeoptionvalue), 'eavatt.option_id= cpei.value',array('brand' => 'eavatt.value'))
    ->joinLeft(array('so' =>$sales_order), 'so.entity_id= main_table.order_id',array('status' => 'so.status'))
    ->joinLeft(array('soa' =>$sales_order_address), 'soa.parent_id= main_table.order_id AND soa.address_type = "shipping"',array('shippingcity' => 'soa.city'))
    ->group('cpe.entity_id');

    $this->getSelect()->where("cpe.sku IS NOT NULL");

    $authSession = \Magento\Framework\App\ObjectManager::getInstance()->create(\Magento\Backend\Model\Auth\Session::class);
    $username = $authSession->getUser()->getUsername();
    $TempdataModel = \Magento\Framework\App\ObjectManager::getInstance()->create('Vendor\Module\Model\TempdataFactory')->create();


    $TempdataCollection = $TempdataModel->getCollection()->addFieldToFilter('identifier','brandwisereport')->addFieldToFilter('data_1',$username);

    if($TempdataCollection->count()) {
        foreach ($TempdataCollection as $Tempdata){
            $datearray = explode('_',$Tempdata->getData2());
            $orderstatuses = $Tempdata->getData3();
            $brand = $Tempdata->getData4();
        }
        $orderstatusesarray = explode(',',$orderstatuses);
        $orderstatusesforquery = implode("','",$orderstatusesarray);


        $from  = date('Y-m-d', strtotime($datearray[0]));
        $to = date('Y-m-d', strtotime($datearray[1]));

        $this->getSelect()->where("main_table.created_at >= '".$from." 00:00:00'");
        $this->getSelect()->where("main_table.created_at <= '".$to." 23:59:59'");
        $this->getSelect()->where("so.status IN ('$orderstatusesforquery')");
        $this->getSelect()->where('eavatt.value LIKE "'.$brand.'"');
    }   
    else {
        $this->getSelect()->where("cpe.entity_id = 0");
    }       
    //echo $this->getSelect();
    //die;      
   }

}
?>

I added the filter form including all filter field in one of my admin phtml file. call that phtml file using module admin layout. After form submit, It store filter form data against each admin user in custom table by using admin controller which will be used during collection loading against each admin user.

My grid file is look like this

<?php
namespace Vendor\Module\Block\Adminhtml\Brandwisereport;

class Grid extends \Magento\Backend\Block\Widget\Grid\Extended
{

    protected $_countTotals = true;
    /**
     * @var \Magento\Framework\Module\Manager
     */
    protected $moduleManager;

    /**
     * @var \Vendor\Module\Model\brandwisereportFactory
     */
    protected $_brandwisereportFactory;

    /**
     * @var \Vendor\Module\Model\Status
     */
    protected $_status;

    /**
     * @param \Magento\Backend\Block\Template\Context $context
     * @param \Magento\Backend\Helper\Data $backendHelper
     * @param \Vendor\Module\Model\brandwisereportFactory $brandwisereportFactory
     * @param \Vendor\Module\Model\Status $status
     * @param \Magento\Framework\Module\Manager $moduleManager
     * @param array $data
     *
     * @SuppressWarnings(PHPMD.ExcessiveParameterList)
     */
    public function __construct(
        \Magento\Backend\Block\Template\Context $context,
        \Magento\Backend\Helper\Data $backendHelper,
        \Vendor\Module\Model\BrandwisereportFactory $BrandwisereportFactory,
        \Vendor\Module\Model\Status $status,
        \Magento\Framework\Module\Manager $moduleManager,
        array $data = []
    ) {
        $this->_brandwisereportFactory = $BrandwisereportFactory;
        $this->_status = $status;
        $this->moduleManager = $moduleManager;
        parent::__construct($context, $backendHelper, $data);
    }

    /**
     * @return void
     */
    protected function _construct()
    {
        parent::_construct();
        $this->setId('postGrid');
        $this->setDefaultSort('item_id');
        $this->setDefaultDir('DESC');
        $this->setSaveParametersInSession(true);
        $this->setUseAjax(false);
        //$this->setVarNameFilter('post_filter');
        $this->setFilterVisibility(false);
        //$this->buttonList->remove('selectall');
        //$this->buttonList->remove('save'); 
        //$this->buttonList->remove('reset');
        //$this->_removeButton('selectall');    
        //$this->buttonList->remove('delete'); 
    }

    public function getTotals()
    {
        $totals = new \Magento\Framework\DataObject;
        $fields = array(
            'sum_qty_ordered' => 0,
            'base_price' => 0,
            'total_amount' => 0
        );
        foreach ($this->getCollection() as $item) {
            foreach($fields as $field=>$value){
                    $fields[$field] += $item->getData($field);
            }
        }
        $fields['name']='Totals';
        $totals->setData($fields);
        return $totals;
    }


    /**
     * @return $this
     */ 
    protected function _prepareCollection()
    {
        $collection = $this->_brandwisereportFactory->create()->getCollection();

        /********************** This code will apply filter on grid collection *****/
        $collection->filterOrder();

        $this->setCollection($collection);

        parent::_prepareCollection();

        return $this;
    }

    /**
     * @return $this
     * @SuppressWarnings(PHPMD.ExcessiveMethodLength)
     */
    protected function _prepareColumns()
    {
        $this->addColumn(
            'brand',
            [
                'header' => __('Brand'),
                'index' => 'brand',
                'filter' => false
            ]
        );



                $this->addColumn(
                    'sku',
                    [
                        'header' => __('SKU'),
                        'index' => 'sku',
                        'filter' => false
                    ]
                );

                $this->addColumn(
                    'name',
                    [
                        'header' => __('Name'),
                        'index' => 'name',
                        'filter' => false
                    ]
                );

                $this->addColumn(
                    'sum_qty_ordered',
                    [
                        'header' => __('Sold Count'),
                        'type' => 'number',
                        'index' => 'sum_qty_ordered',
                        'filter' => false
                    ]
                );

                $this->addColumn(
                    'base_price',
                    [
                        'header' => __('Unit Price'),
                        'type' => 'currency',
                        'index' => 'base_price',
                        'filter' => false
                    ]
                );

                $this->addColumn(
                    'total_amount',
                    [
                        'header' => __('Total Amount'),
                        'type' => 'currency',
                        'index' => 'total_amount',
                        'filter' => false,
                        'format' => true
                    ]
                );

                $this->addColumn('action', array(
                    'header' => __('Action'),
                    'width' => '100',
                    'type' => 'action',
                    'getter' => 'getProductId',
                    'actions' => array(
                        array(
                            'caption' => __('View'),
                            'url' => array('base' => 'catalog/product/edit'),
                            'target'=>'_blank',
                            'field' => 'id'
                        )
                    ),
                    'renderer'  => 'Vendor\Module\Block\Adminhtml\Brandwisereport\Edit\Tab\Renderer\Brandwiseviewaction',
                    'filter' => false,
                    'sortable' => false,
                    'index' => 'id',
                    'is_system' => true,
                ));


           $this->addExportType($this->getUrl('module/*/exportCsv', ['_current' => true]),__('CSV'));
           $this->addExportType($this->getUrl('module/*/exportExcel', ['_current' => true]),__('Excel XML'));

        $block = $this->getLayout()->getBlock('grid.bottom.links');
        if ($block) {
            $this->setChild('grid.bottom.links', $block);
        }

        return parent::_prepareColumns();
    }



    /**
     * @return string
     */
    public function getGridUrl()
    {
        return $this->getUrl('module/*/index', ['_current' => true]);
    }

    /**
     * @param \Vendor\Module\Model\brandwisereport|\Magento\Framework\Object $row
     * @return string
     */
    public function getRowUrl($row)
    {
        return '#';
    }
}

I don't know whether it is best practice or not but it worked at my end. I hope this will help others though I am still looking for best practice in order to make custom report just like default magento report.

| improve this answer | |
  • hi @Mumammad, can you share the complete module, do we need to create any aggregate tables? – jafar pinjar Feb 18 at 9:05
  • 1
    Good For Collection getting!!! If you can add all thing by step it would be great help for others too!!! – Rutvee Sojitra Aug 15 at 15:42
  • Muhammad Hasham, if possible can you please share this module via git to better understand it all and how this works. I'm working on similar task and need to build report from my custom tables. – Shahzaib Hayat Khan Sep 1 at 11:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.