Ordered Products Report Not Grouping

Question

When I run the Ordered Products Report in Magento backend, the generated report lists all products ordered and their ordered quantity -- however, they don't seem to be grouped correctly by SKU. I have the same product showing up multiple times. I would like the report to generate correctly and group by SKU.

Here is an example report:

|----------|------------------------------------------|-------------|------------------|
| Interval | Product                                  | SKU         | Ordered Quantity | 
|----------|------------------------------------------|-------------|------------------|
| 8/12/18  | CBD Freeze Pain Relief 3oz Roller 1500mg | TFR-04-1500 | 8                |
|          | CBD Oil Tincture Drops 30mL Berry 300mg  | TIN-BR-0300 | 6                |
|          | CBD Oil Tincture Drops 30mL Berry 750mg  | TIN-BR-0750 | 5                |
|          | CBD Oil Tincture Drops 30mL Berry 300mg  | TIN-BR-0300 | 3                |
|----------|------------------------------------------|-------------|------------------|

Notice that SKU TIN-BR-0300 is listed twice for the same day/interval.

Answer 1

We found a solution so if anyone comes across this same issue

the group() query was removed when sku was added to the table array and the SUM() needed to be added to the ordered_qty.qty_ordered

Open File:

vendor/magento/module-reports/Model/ResourceModel/Product/Sold/Collection.php

Go to line 47 and replace

    public function addOrderedQty($from = '', $to = '')
{
    $connection = $this->getConnection();
    $orderTableAliasName = $connection->quoteIdentifier('order');

    $orderJoinCondition = [
        $orderTableAliasName . '.entity_id = order_items.order_id',
        $connection->quoteInto("{$orderTableAliasName}.state <> ?", \Magento\Sales\Model\Order::STATE_CANCELED),
    ];

    if ($from != '' && $to != '') {
        $fieldName = $orderTableAliasName . '.created_at';
        $orderJoinCondition[] = $this->prepareBetweenSql($fieldName, $from, $to);
    }

    $this->getSelect()->reset()->from(
        ['order_items' => $this->getTable('sales_order_item')],
        [
            'ordered_qty' => 'order_items.qty_ordered',
            'order_items_name' => 'order_items.name',
            'order_items_sku' => 'order_items.sku'
        ]
    )->joinInner(
        ['order' => $this->getTable('sales_order')],
        implode(' AND ', $orderJoinCondition),
        []
    )->where(
        'order_items.parent_item_id IS NULL'
    )->having(
        'order_items.qty_ordered > ?',
        0
    );
    return $this;
}

With this

    public function addOrderedQty($from = '', $to = '')
{
    $connection = $this->getConnection();
    $orderTableAliasName = $connection->quoteIdentifier('order');

    $orderJoinCondition = [
        $orderTableAliasName . '.entity_id = order_items.order_id',
        $connection->quoteInto("{$orderTableAliasName}.state <> ?", \Magento\Sales\Model\Order::STATE_CANCELED),
    ];

    if ($from != '' && $to != '') {
        $fieldName = $orderTableAliasName . '.created_at';
        $orderJoinCondition[] = $this->prepareBetweenSql($fieldName, $from, $to);
    }

    $this->getSelect()->reset()->from(
        ['order_items' => $this->getTable('sales_order_item')],
        [
            'ordered_qty' => 'SUM(order_items.qty_ordered)',
            'order_items_name' => 'order_items.name',
            'order_items_sku' => 'order_items.sku'
        ]
    )->joinInner(
        ['order' => $this->getTable('sales_order')],
        implode(' AND ', $orderJoinCondition),
        []
    )->where(
        'order_items.parent_item_id IS NULL'
    )->group(
        'order_items.product_id'
    )->having(
        'SUM(order_items.qty_ordered) > ?',
        0
    );
    return $this;
}

Answer 2

This is a known bug and will be fixed in the 2.2.10 and 2.3.3 release: https://github.com/magento/magento2/pull/22806