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 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 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 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.

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.

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 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 Thank you for sharing your findings 😃