I have a test on your demo, just now.

I saw that when I create a sub category then I create an item with that sub category and made purchase then sale,
the answer is the Category report capture only purchase but not capture the sale and only capture the parrent category only.

so I think there is something wrong with category report.

I would want to clarify again, for my case in my system it doesn't capture both purchase or sale, parrent category or sub category.

hoping to seeing your feedback

Hello,

Categories and Products reports are shown once there is data both sales and purchases. It doesn't show subcategories yet.

Thank you

**[Mian Saleem](/u/saleem)** it does, my data has sale and purchase but it doesn't show up there.

and what about digital product? or service? it will not show up, right?

**[Mian Saleem](/u/saleem)** and I want to clarify, normally inventory item. without purchase you cannot sale also. so as I attached my screenshot of my sales bunch of them. they are inventory items, so we have purchased and sold but on category report does nothing.

Hello, Can I have the install link with owner account details to have look?

**[Mian Saleem](/u/saleem)** http://directexpress.psatmey.com/
and you can use the default owner account.

let's try with this new setup first. if you think it has no problem and if it works OK at your site. then I will send you the current system that we are working on with loads of data.

thank you

Hi , i want to follow this topic , this is bug and i think they will fix this bug in next version

Hello,

This is the query used to genererte report. If this get data then it will be displayed.

```sql
SELECT
sma_categories.id AS cid,
sma_categories.code,
sma_categories.name,
SUM(COALESCE(PCosts.purchasedQty, 0)) AS PurchasedQty,
SUM(COALESCE(PSales.soldQty, 0)) AS SoldQty,
SUM(COALESCE(PCosts.totalPurchase, 0)) AS TotalPurchase,
SUM(COALESCE(PSales.totalSale, 0)) AS TotalSales,
(SUM(COALESCE(PSales.totalSale, 0)) - SUM(COALESCE(PCosts.totalPurchase, 0))) AS Profit
FROM
`sma_categories`
LEFT JOIN (
SELECT
sp.category_id AS category,
SUM(si.quantity) soldQty,
SUM(si.subtotal) totalSale
FROM
sma_products sp
LEFT JOIN sma_sale_items si ON sp.id = si.product_id
LEFT JOIN sma_sales s ON s.id = si.sale_id
GROUP BY
sp.category_id) PSales ON `sma_categories`.`id` = `PSales`.`category`
LEFT JOIN (
SELECT
pp.category_id AS category,
SUM(pi.quantity) purchasedQty,
SUM(pi.subtotal) totalPurchase
FROM
sma_products pp
LEFT JOIN sma_purchase_items pi ON pp.id = pi.product_id
LEFT JOIN sma_purchases p ON p.id = pi.purchase_id
GROUP BY
pp.category_id) PCosts ON `sma_categories`.`id` = `PCosts`.`category`
WHERE
parent_id IS NULL
GROUP BY
`sma_categories`.`id`,
`sma_categories`.`code`,
`sma_categories`.`name`,
`PSales`.`SoldQty`,
`PSales`.`totalSale`,
`PCosts`.`purchasedQty`,
`PCosts`.`totalPurchase`
ORDER BY
`sma_categories`.`code` ASC
LIMIT 10
```

Hi , i check this query and we have mistake at "where parent_id = null" , i have fixed it with parent_id = 0 and it's working ,Please see my attach .

Hello,

I will update it to following in next update.

```sql
SELECT
sma_categories.id AS cid,
sma_categories.code,
sma_categories.name,
SUM(COALESCE(PCosts.purchasedQty, 0)) AS PurchasedQty,
SUM(COALESCE(PSales.soldQty, 0)) AS SoldQty,
SUM(COALESCE(PCosts.totalPurchase, 0)) AS TotalPurchase,
SUM(COALESCE(PSales.totalSale, 0)) AS TotalSales,
(SUM(COALESCE(PSales.totalSale, 0)) - SUM(COALESCE(PCosts.totalPurchase, 0))) AS Profit
FROM
`sma_categories`
LEFT JOIN (
SELECT
sp.category_id AS category,
SUM(si.quantity) soldQty,
SUM(si.subtotal) totalSale
FROM
sma_products sp
LEFT JOIN sma_sale_items si ON sp.id = si.product_id
LEFT JOIN sma_sales s ON s.id = si.sale_id
GROUP BY
sp.category_id) PSales ON `sma_categories`.`id` = `PSales`.`category`
LEFT JOIN (
SELECT
pp.category_id AS category,
SUM(pi.quantity) purchasedQty,
SUM(pi.subtotal) totalPurchase
FROM
sma_products pp
LEFT JOIN sma_purchase_items pi ON pp.id = pi.product_id
LEFT JOIN sma_purchases p ON p.id = pi.purchase_id
GROUP BY
pp.category_id) PCosts ON `sma_categories`.`id` = `PCosts`.`category`
WHERE (parent_id IS NULL
OR `parent_id` = 0)
GROUP BY
`sma_categories`.`id`,
`sma_categories`.`code`,
`sma_categories`.`name`,
`PSales`.`SoldQty`,
`PSales`.`totalSale`,
`PCosts`.`purchasedQty`,
`PCosts`.`totalPurchase`
ORDER BY
`sma_categories`.`code` ASC
LIMIT 10
```

[@Prom Vongchann](/u/prom.vongchann) Can you please run this query and let me know if you get data?

We can have both by replacing the `->where('parent_id is NULL', null, false)` with

```
->group_start()->where('parent_id is NULL', null, false)->or_where('parent_id', 0)->group_end()
```

[@Vinh Nguyen](/u/songlongvietnammedia) Thank you for sharing your findings :)