Sales analyses from multiple groups (article tab)

[jeroenw]

Hello,

Can someone please help me make a query. Or maybe link to another thread (I already searched but couldn't find any).

The standard sales analysis report allows only to choose one group or all groups. I need a sales report for multiple groups from the tab items.

When the query starts I need to fill in the following:

Date: sales results start date - sales results end date (example: 01-01-20 - 31-12-20)

Groups: Select the multiple item groups (example: vegetables - bread - candy - meat)

The results must be a follow:

Show ALL customers, also who haven't bought anything - so I gues LEFT JOIN shoud be used

Customer Code

Customer Name

Total sales from the selected groups (vegetables - bread - candy - meat)

Thanks for the help!

[PierreBrothier]

Hi,

i use cross join between customer and item groups to get all lines, and join with subquery
get sales group by customer/item group

Not sure that you can get multiple value from user defined query. May be a crystal report would be the solution.

SELECT o."CardCode",o."CardName",a."ItmsGrpCod",a."ItmsGrpNam",sales."SumQty",sales."LineTotal" FROM OCRD o 
CROSS JOIN OITB a
LEFT JOIN
(
	SELECT o."CardCode",o."CardName",i2."ItmsGrpCod",Sum(o2."Quantity") as "SumQty",SUm(o2."LineTotal") as "LineTotal" FROM ORDR o
	INNER JOIN RDR1 o2
	on o."DocEntry" = o2."DocEntry"
	INNER JOIN OITM i
	ON o2."ItemCode" = i."ItemCode"
	INNER JOIN OITB i2
	ON i."ItmsGrpCod" =  i2."ItmsGrpCod"
	WHERE o."DocDate" Between '2020-09-01' and '2021-01-01'
	group by o."CardCode",o."CardName",i2."ItmsGrpCod"
) as sales


on o."CardCode" = sales."CardCode"
AND a."ItmsGrpCod" = sales."ItmsGrpCod"
WHERE o."CardType"='C'
and a."ItmsGrpNam" in ('Grp1','Grp2')