How to create custom reports in Magento2?

Question

Now the real question I need to create custom reports based on conditions.

1. Sales Report
2. Marketing Report
3. Customer Report
4. Product Report

. Example:

Conditions:

1. How many products or list of products which sold more than 200 times.

2. I need to show it graphically in Charts (comparision,pie) e.g https://google-developers.appspot.com/chart/

I have already develop extension how can I use google charts ?

Answer 1

raw sql to get all product ids which were sold more than 200 times:

SELECT e.entity_id FROM magento2test.catalog_product_entity as e INNER JOIN magento2test.sales_order_item as soi 
ON e.entity_id = soi.product_id GROUP BY e.entity_id HAVING COUNT(soi.product_id) > 200;

Magento code to add needed changes to the product collection's select:

$reportCountVal = 200;
$collection = $this->productCollectionFactory->create();
$collection->getSelect()
     ->joinLeft(
                ['soi' => $collection->getResource()->getTable('sales_order_item')],
                'e.entity_id = soi.product_id'
            )
            ->group('e.entity_id')
            ->having('COUNT(soi.product_id) > ?', $reportCountVal);

Put into $this->productCollectionFactory product collection's factory auto generated magento 2 factory instance using automatic DI feature