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

Improve this question
8
  • Did you find any solution on this? Commented Apr 16, 2019 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. Commented Apr 16, 2019 at 9:30
  • Can I start bounty on this question? Commented Apr 16, 2019 at 9:31
  • Yes you can if you want Commented Apr 16, 2019 at 9:33
  • Hi I am working on same type of reports. I can provide you the extension. Commented Apr 23, 2019 at 7:38

2 Answers 2

Reset to default
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
11
  • let me check this then I will get back to you Commented Apr 19, 2019 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. Commented Apr 22, 2019 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. Commented Apr 22, 2019 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? Commented Apr 22, 2019 at 13:02
  • Thanks Yash Shah for the time your have taken in this question. +1 for that Commented Apr 23, 2019 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
4
  • hi @Mumammad, can you share the complete module, do we need to create any aggregate tables? Commented Feb 18, 2020 at 9:05
  • 1
    Good For Collection getting!!! If you can add all thing by step it would be great help for others too!!! Commented Aug 15, 2020 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. Commented Sep 1, 2020 at 11:52
  • May i get full code for this module Commented May 9, 2022 at 4:49

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.