Zoho Analytics: Determine profits from invoices and purchase orders

: Joel Lipman; Zoho; 03 November 2022; Hits: 1630

What?
An article on the reasoning and steps to generate a dataset using Zoho's ANSI-SQL queries to generate the margins of each invoice in ZohoBooks.

Why?
We need to create a profits/losses dashboard in Zoho Analytics as we can't control the out-of-the-box PNL report that comes with Zoho Books. We needed a dataset that took into account "Delivery" as a separate line item on the purchase order.

I'm writing this article as it had gone through several partners before us and even Zoho themselves without a solution. This took my team the best part of two months to find a solution.

How?
Disclaimer! There may be many ways of doing this, this is just one way that we used that worked for 90% of the invoices. There are invoices that may be associated to sales orders changed after being confirmed or missing associated purchase orders altogether.

I had to break this down into several queries to generate the dataset, then use these as pseudo-subqueries. The first task was to determine the cost of each item purchased; including shipping and tax. The second is to associate these to the invoiced items and work out the profit. The third is to group by the sales person, date, and customer so as to generate meaningful reports.

The first dataset should be just the profit per item based on what was purchased. I'm including the Purchase Order ID so that I can refer to it later for shipping charges and other discrepancies. I'm calling this dataset "My Purchase Order Items"
copyrawstyler
```
SELECT DISTINCT
		 po."Purchase Order Date" AS "PO Date",
		 po."Purchase Order Number" AS "PO Ref",
		 poi."Product ID" AS "PO Product ID",
		 poi."Item ID" AS "PO Item ID",
		 poi."Item Name" AS "PO Item Name",
		 poi."Item Price (BCY)" AS "PO Item Price",
		 poi."FCY Tax Amount" / if(poi."Quantity"  = 0, 1, poi."Quantity") AS "PO Item Tax",
		 poi."Quantity" AS "PO Qty",
		 poi."FCY Tax Amount" AS "PO Line Tax",
		 if_null(pshipping."PO Delivery", 0) AS "PO Total Shipping",
		 if_null(pdiscrepancy."PO Discrepancy Total", 0) AS "PO Total Discrepancy",
		 if_null(pquantity."PO Total Qty", 1) AS "PO Total Qty",
		 (if_null(pshipping."PO Delivery", 0) / if(if_null(pquantity."PO Total Qty", 0)  = 0, 1, pquantity."PO Total Qty")) AS "PO Shipping Per Line",
		 (if_null(pdiscrepancy."PO Discrepancy Total", 0) / if(if_null(pquantity."PO Total Qty", 0)  = 0, 1, pquantity."PO Total Qty")) AS "PO Discrepancy Per Line",
		 poi."Quantity" * (poi."Item Price (BCY)" + (if_null(pshipping."PO Delivery", 0) / if(if_null(pquantity."PO Total Qty", 0)  = 0, 1, pquantity."PO Total Qty")) + (if_null(pdiscrepancy."PO Discrepancy Total", 0) / if(if_null(pquantity."PO Total Qty", 0)  = 0, 1, pquantity."PO Total Qty"))) AS "PO Subtotal excl Tax",
		 (poi."Quantity" * (poi."Item Price (BCY)" + (if_null(pshipping."PO Delivery", 0) / if(if_null(pquantity."PO Total Qty", 0)  = 0, 1, pquantity."PO Total Qty")) + (if_null(pdiscrepancy."PO Discrepancy Total", 0) / if(if_null(pquantity."PO Total Qty", 0)  = 0, 1, pquantity."PO Total Qty")))) * if(poi."FCY Tax Amount"  > 0, 0.2, 0) AS "PO Total Tax",
		 (poi."Quantity" * (poi."Item Price (BCY)" + (if_null(pshipping."PO Delivery", 0) / if(if_null(pquantity."PO Total Qty", 0)  = 0, 1, pquantity."PO Total Qty")) + (if_null(pdiscrepancy."PO Discrepancy Total", 0) / if(if_null(pquantity."PO Total Qty", 0)  = 0, 1, pquantity."PO Total Qty")))) * if(poi."FCY Tax Amount"  > 0, 1.2, 1) AS "PO Total incl Tax",
		 po."Reference number" AS "PO Custom Ref",
		 po."Sales order ID" AS "SO ID",
		 soi."Item ID" AS "SO Item ID"
FROM  "Purchase Order Items" poi
LEFT JOIN "Purchase Orders" po ON po."Purchase Order ID"  = poi."Purchase Order ID" 
LEFT OUTER JOIN "Sales Orders" so ON so."Sales order ID"  = po."Sales order ID" 
LEFT OUTER JOIN "Sales Order Items" soi ON soi."Sales order ID"  = so."Sales order ID"
	 AND	soi."Product ID"  = poi."Product ID" 
LEFT OUTER JOIN(	SELECT
			 po2."Purchase Order ID" AS "PO ID",
			 SUM(poi2."Total (FCY)") AS "PO Delivery"
	FROM  "Purchase Order Items" poi2
LEFT JOIN "Purchase Orders" po2 ON po2."Purchase Order ID"  = poi2."Purchase Order ID"  
	WHERE	 poi2."Item Name"  IN ( 'delivery'  , 'Delivery'  )
	GROUP BY  po2."Purchase Order ID" 
) pshipping ON pshipping."PO ID"  = po."Purchase Order ID" 
LEFT OUTER JOIN(	SELECT
			 po3."Purchase Order ID" AS "PO ID",
			 SUM(poi3."Quantity") AS "PO Total Qty"
	FROM  "Purchase Order Items" poi3
LEFT JOIN "Purchase Orders" po3 ON po3."Purchase Order ID"  = poi3."Purchase Order ID"  
	WHERE	 poi3."Item Name"  NOT IN ( 'delivery'  , 'Delivery'  , 'Price Disrepency'  )
	GROUP BY  po3."Purchase Order ID" 
) pquantity ON pquantity."PO ID"  = po."Purchase Order ID" 
LEFT OUTER JOIN(	SELECT
			 po4."Purchase Order ID" AS "PO ID",
			 SUM(poitm4."Total (FCY)") AS "PO Discrepancy Total"
	FROM  "Purchase Order Items" poitm4
LEFT JOIN "Purchase Orders" po4 ON po4."Purchase Order ID"  = poitm4."Purchase Order ID"  
	WHERE	 poitm4."Item Name"  = 'Price Disrepency'
	 OR	poitm4."Item Name"  = 'Price Discrepency'
	 OR	poitm4."Item Name"  LIKE '%Discrepancy%'
	GROUP BY  po4."Purchase Order ID" 
) pdiscrepancy ON pdiscrepancy."PO ID"  = po."Purchase Order ID"  
WHERE	 poi."Item Name"  NOT IN ( 'delivery'  , 'Delivery'  , 'Price Disrepency'  )
 AND	po."Purchase Order Status"  NOT IN ( 'Cancelled'  )
```
1. SELECT DISTINCT
2. po."Purchase Order Date" AS "PO Date",
3. po."Purchase Order Number" AS "PO Ref",
4. poi."Product ID" AS "PO Product ID",
5. poi."Item ID" AS "PO Item ID",
6. poi."Item Name" AS "PO Item Name",
7. poi."Item Price (BCY)" AS "PO Item Price",
8. poi."FCY Tax Amount" / if(poi."Quantity" = 0, 1, poi."Quantity") AS "PO Item Tax",
9. poi."Quantity" AS "PO Qty",
10. poi."FCY Tax Amount" AS "PO Line Tax",
11. if_null(pshipping."PO Delivery", 0) AS "PO Total Shipping",
12. if_null(pdiscrepancy."PO Discrepancy Total", 0) AS "PO Total Discrepancy",
13. if_null(pquantity."PO Total Qty", 1) AS "PO Total Qty",
14. (if_null(pshipping."PO Delivery", 0) / if(if_null(pquantity."PO Total Qty", 0) = 0, 1, pquantity."PO Total Qty")) AS "PO Shipping Per Line",
15. (if_null(pdiscrepancy."PO Discrepancy Total", 0) / if(if_null(pquantity."PO Total Qty", 0) = 0, 1, pquantity."PO Total Qty")) AS "PO Discrepancy Per Line",
16. poi."Quantity" * (poi."Item Price (BCY)" + (if_null(pshipping."PO Delivery", 0) / if(if_null(pquantity."PO Total Qty", 0) = 0, 1, pquantity."PO Total Qty")) + (if_null(pdiscrepancy."PO Discrepancy Total", 0) / if(if_null(pquantity."PO Total Qty", 0) = 0, 1, pquantity."PO Total Qty"))) AS "PO Subtotal excl Tax",
17. (poi."Quantity" * (poi."Item Price (BCY)" + (if_null(pshipping."PO Delivery", 0) / if(if_null(pquantity."PO Total Qty", 0) = 0, 1, pquantity."PO Total Qty")) + (if_null(pdiscrepancy."PO Discrepancy Total", 0) / if(if_null(pquantity."PO Total Qty", 0) = 0, 1, pquantity."PO Total Qty")))) * if(poi."FCY Tax Amount" > 0, 0.2, 0) AS "PO Total Tax",
18. (poi."Quantity" * (poi."Item Price (BCY)" + (if_null(pshipping."PO Delivery", 0) / if(if_null(pquantity."PO Total Qty", 0) = 0, 1, pquantity."PO Total Qty")) + (if_null(pdiscrepancy."PO Discrepancy Total", 0) / if(if_null(pquantity."PO Total Qty", 0) = 0, 1, pquantity."PO Total Qty")))) * if(poi."FCY Tax Amount" > 0, 1.2, 1) AS "PO Total incl Tax",
19. po."Reference number" AS "PO Custom Ref",
20. po."Sales order ID" AS "SO ID",
21. soi."Item ID" AS "SO Item ID"
22. FROM "Purchase Order Items" poi
23. LEFT JOIN "Purchase Orders" po ON po."Purchase Order ID" = poi."Purchase Order ID"
24. LEFT OUTER JOIN "Sales Orders" so ON so."Sales order ID" = po."Sales order ID"
25. LEFT OUTER JOIN "Sales Order Items" soi ON soi."Sales order ID" = so."Sales order ID"
26. AND soi."Product ID" = poi."Product ID"
27. LEFT OUTER JOIN( SELECT
28. po2."Purchase Order ID" AS "PO ID",
29. SUM(poi2."Total (FCY)") AS "PO Delivery"
30. FROM "Purchase Order Items" poi2
31. LEFT JOIN "Purchase Orders" po2 ON po2."Purchase Order ID" = poi2."Purchase Order ID"
32. WHERE poi2."Item Name" in ( 'delivery' , 'Delivery' )
33. GROUP BY po2."Purchase Order ID"
34. ) pshipping ON pshipping."PO ID" = po."Purchase Order ID"
35. LEFT OUTER JOIN( SELECT
36. po3."Purchase Order ID" AS "PO ID",
37. SUM(poi3."Quantity") AS "PO Total Qty"
38. FROM "Purchase Order Items" poi3
39. LEFT JOIN "Purchase Orders" po3 ON po3."Purchase Order ID" = poi3."Purchase Order ID"
40. WHERE poi3."Item Name" NOT in ( 'delivery' , 'Delivery' , 'Price Disrepency' )
41. GROUP BY po3."Purchase Order ID"
42. ) pquantity ON pquantity."PO ID" = po."Purchase Order ID"
43. LEFT OUTER JOIN( SELECT
44. po4."Purchase Order ID" AS "PO ID",
45. SUM(poitm4."Total (FCY)") AS "PO Discrepancy Total"
46. FROM "Purchase Order Items" poitm4
47. LEFT JOIN "Purchase Orders" po4 ON po4."Purchase Order ID" = poitm4."Purchase Order ID"
48. WHERE poitm4."Item Name" = 'Price Disrepency'
49. OR poitm4."Item Name" = 'Price Discrepency'
50. OR poitm4."Item Name" LIKE '%Discrepancy%'
51. GROUP BY po4."Purchase Order ID"
52. ) pdiscrepancy ON pdiscrepancy."PO ID" = po."Purchase Order ID"
53. WHERE poi."Item Name" NOT in ( 'delivery' , 'Delivery' , 'Price Disrepency' )
54. AND po."Purchase Order Status" NOT in ( 'Cancelled' )

The second dataset will be a grouping of the above, in addition to including the customer ID and invoice ID, I'm calling this one "Invoice Items vs SoPo Items":

copy raw

SELECT
		 MAX(invitm."Invoice ID") AS "Invoice ID",
		 MAX(inv."Invoice Date") AS "Invoice Date",
		 MAX(abs_month(inv."Invoice Date")) AS "Invoice Month Year",
		 MAX(inv."Invoice Number") AS "Invoice Ref",
		 MAX(inv."Invoice Status") AS "Invoice Status",
		 MAX(invitm."Buy Price") AS "Invoice Item Cost",
		 MAX(invitm."Discount Amount (BCY)") AS "Invoice Item Discount",
		 MAX(invitm."Item Name") AS "Invoice Item Name",
		 MAX(invitm."Is Discount Before Tax") AS "Discount Before Tax",
		 MAX(invitm."Item Price") AS "Invoice Item Sell",
		 MAX(invitm."Quantity") AS "Invoice Line Qty",
		 MAX(invitm."Sub Total (BCY)") AS "Invoice Line Subtotal",
		 MAX(invitm."Tax Amount") AS "Invoice Line Tax",
		 MAX(inv."Shipping Charge (BCY)" / if(iquantity."InvItm Line Count"  = 0, 1, iquantity."InvItm Line Count")) AS "Invoice Line Shipping excl Tax",
		 MAX(if_null(inv."Shipping Charge Tax Amount (BCY)", 0) / if(iquantity."InvItm Line Count"  = 0, 1, iquantity."InvItm Line Count")) AS "Invoice Shipping Tax",
		 MAX((inv."Shipping Charge (BCY)" / if(iquantity."InvItm Line Count"  = 0, 1, iquantity."InvItm Line Count")) + (if_null(inv."Shipping Charge Tax Amount (BCY)", 0) / if(iquantity."InvItm Line Count"  = 0, 1, iquantity."InvItm Line Count"))) AS "Invoice Shipping inc Tax",
		 MAX(invitm."Sub Total (BCY)" + (inv."Shipping Charge (BCY)" / if(iquantity."InvItm Line Count"  = 0, 1, iquantity."InvItm Line Count"))) AS "Invoice Line Total excl Tax",
		 MAX(invitm."Sub Total (BCY)" + invitm."Tax Amount" + (inv."Shipping Charge (BCY)" / if(iquantity."InvItm Line Count"  = 0, 1, iquantity."InvItm Line Count")) + (if_null(inv."Shipping Charge Tax Amount (BCY)", 0) / if(iquantity."InvItm Line Count"  = 0, 1, iquantity."InvItm Line Count"))) AS "Invoice Line Total inc Tax",
		 GROUP_CONCAT(DISTINCT t1."PO Ref") AS "PO Refs",
		 AVG(if_null(t1."PO Item Cost", 0)) AS "PO Item Cost",
		 SUM(if_null(t1."PO Item Shipping", 0)) AS "PO Shipping",
		 SUM(invitm."Quantity" * if_null(t1."PO Item Discrepancy", 0)) AS "PO Discrepancy",
		 SUM(if_null(t1."PO Item Cost", 0) * invitm."Quantity")+ SUM(if_null(t1."PO Item Shipping", 0)) AS "PO Total excl Tax",
		 (SUM(if_null(t1."PO Item Cost", 0) * invitm."Quantity")+ SUM(if_null(t1."PO Item Shipping", 0))) * 1.2 AS "PO Total incl Tax",
		 GROUP_CONCAT(DISTINCT so."Sales Order#") AS "SO Refs",
		 MAX(invitm."Sub Total (BCY)" + (inv."Shipping Charge (BCY)" / if(iquantity."InvItm Line Count"  = 0, 1, iquantity."InvItm Line Count"))) -(SUM(if_null(t1."PO Item Cost", 0) * invitm."Quantity")+ SUM(if_null(t1."PO Item Shipping", 0))) AS "Profit excl Tax",
		 (MAX(invitm."Sub Total (BCY)" + (inv."Shipping Charge (BCY)" / if(iquantity."InvItm Line Count"  = 0, 1, iquantity."InvItm Line Count"))) -(SUM(if_null(t1."PO Item Cost", 0) * invitm."Quantity")+ SUM(if_null(t1."PO Item Shipping", 0)))) * 1.2 AS "Profit incl Tax",
		 MAX(sp."Name") AS "Sales Person",
		 MAX(sp."Sales Person ID") AS "Sales Person ID"
FROM  "Invoices" inv
LEFT JOIN "Invoice Items" invitm ON invitm."Invoice ID"  = inv."Invoice ID" 
LEFT JOIN "Sales Persons" sp ON sp."Sales Person ID"  = inv."Sales Person ID" 
LEFT OUTER JOIN "Sales Order Invoice" soinv ON soinv."Invoice ID"  = inv."Invoice ID" 
LEFT OUTER JOIN "Sales Orders" so ON so."Sales order ID"  = soinv."Sales order ID" 
LEFT OUTER JOIN(	SELECT 
			 ab1."SO Item ID" AS "SO Item ID",
			 ab1."SO ID" AS "SO ID",
			 ab1."PO Product ID" AS "Product ID",
			 ab1."PO Qty" AS "PO Qty",
			 MAX(ab1."PO Ref") AS "PO Ref",
			 AVG(ab1."PO Item Price") AS "PO Item Cost",
			 SUM(ab1."PO Shipping Per Line") AS "PO Item Shipping",
			 SUM(ab1."PO Discrepancy Per Line") AS "PO Item Discrepancy",
			 MAX(ab1."PO Subtotal excl Tax") AS "Cost excl Tax",
			 SUM(ab1."PO Total incl Tax") AS "Cost incl Tax"
	FROM  "My Purchase Order Items" ab1 
	GROUP BY ab1."SO ID",
		 ab1."PO Product ID",
		 ab1."SO Item ID",
		 ab1."PO Qty" 
) t1 ON t1."SO ID"  = soinv."Sales order ID"
	 AND	(t1."Product ID"  = invitm."Product ID"
	 AND	t1."PO Qty"  = invitm."Quantity")
	 OR	invitm."SO ItemID"  = t1."SO Item ID" 
LEFT OUTER JOIN(	SELECT
			 i2."Invoice ID" AS "Invoice ID",
			 COUNT(ii2."Quantity") AS "InvItm Line Count"
	FROM  "Invoice Items" ii2
LEFT JOIN "Invoices" i2 ON i2."Invoice ID"  = ii2."Invoice ID"  
	GROUP BY  i2."Invoice ID" 
) iquantity ON iquantity."Invoice ID"  = inv."Invoice ID"  
WHERE	 inv."Invoice Status"  NOT IN ( 'Draft'  , 'Void'  )
GROUP BY invitm."Item ID",
	  invitm."Quantity"

SELECT
MAX(invitm."Invoice ID") AS "Invoice ID",
MAX(inv."Invoice Date") AS "Invoice Date",
MAX(abs_month(inv."Invoice Date")) AS "Invoice Month Year",
MAX(inv."Invoice Number") AS "Invoice Ref",
MAX(inv."Invoice Status") AS "Invoice Status",
MAX(invitm."Buy Price") AS "Invoice Item Cost",
MAX(invitm."Discount Amount (BCY)") AS "Invoice Item Discount",
MAX(invitm."Item Name") AS "Invoice Item Name",
MAX(invitm."Is Discount Before Tax") AS "Discount Before Tax",
MAX(invitm."Item Price") AS "Invoice Item Sell",
MAX(invitm."Quantity") AS "Invoice Line Qty",
MAX(invitm."Sub Total (BCY)") AS "Invoice Line Subtotal",
MAX(invitm."Tax Amount") AS "Invoice Line Tax",
MAX(inv."Shipping Charge (BCY)" / if(iquantity."InvItm Line Count" = 0, 1, iquantity."InvItm Line Count")) AS "Invoice Line Shipping excl Tax",
MAX(if_null(inv."Shipping Charge Tax Amount (BCY)", 0) / if(iquantity."InvItm Line Count" = 0, 1, iquantity."InvItm Line Count")) AS "Invoice Shipping Tax",
MAX((inv."Shipping Charge (BCY)" / if(iquantity."InvItm Line Count" = 0, 1, iquantity."InvItm Line Count")) + (if_null(inv."Shipping Charge Tax Amount (BCY)", 0) / if(iquantity."InvItm Line Count" = 0, 1, iquantity."InvItm Line Count"))) AS "Invoice Shipping inc Tax",
MAX(invitm."Sub Total (BCY)" + (inv."Shipping Charge (BCY)" / if(iquantity."InvItm Line Count" = 0, 1, iquantity."InvItm Line Count"))) AS "Invoice Line Total excl Tax",
MAX(invitm."Sub Total (BCY)" + invitm."Tax Amount" + (inv."Shipping Charge (BCY)" / if(iquantity."InvItm Line Count" = 0, 1, iquantity."InvItm Line Count")) + (if_null(inv."Shipping Charge Tax Amount (BCY)", 0) / if(iquantity."InvItm Line Count" = 0, 1, iquantity."InvItm Line Count"))) AS "Invoice Line Total inc Tax",
GROUP_CONCAT(DISTINCT t1."PO Ref") AS "PO Refs",
AVG(if_null(t1."PO Item Cost", 0)) AS "PO Item Cost",
SUM(if_null(t1."PO Item Shipping", 0)) AS "PO Shipping",
SUM(invitm."Quantity" * if_null(t1."PO Item Discrepancy", 0)) AS "PO Discrepancy",
SUM(if_null(t1."PO Item Cost", 0) * invitm."Quantity")+ SUM(if_null(t1."PO Item Shipping", 0)) AS "PO Total excl Tax",
(SUM(if_null(t1."PO Item Cost", 0) * invitm."Quantity")+ SUM(if_null(t1."PO Item Shipping", 0))) * 1.2 AS "PO Total incl Tax",
GROUP_CONCAT(DISTINCT so."Sales Order#") AS "SO Refs",
MAX(invitm."Sub Total (BCY)" + (inv."Shipping Charge (BCY)" / if(iquantity."InvItm Line Count" = 0, 1, iquantity."InvItm Line Count"))) -(SUM(if_null(t1."PO Item Cost", 0) * invitm."Quantity")+ SUM(if_null(t1."PO Item Shipping", 0))) AS "Profit excl Tax",
(MAX(invitm."Sub Total (BCY)" + (inv."Shipping Charge (BCY)" / if(iquantity."InvItm Line Count" = 0, 1, iquantity."InvItm Line Count"))) -(SUM(if_null(t1."PO Item Cost", 0) * invitm."Quantity")+ SUM(if_null(t1."PO Item Shipping", 0)))) * 1.2 AS "Profit incl Tax",
MAX(sp."Name") AS "Sales Person",
MAX(sp."Sales Person ID") AS "Sales Person ID"
FROM "Invoices" inv
LEFT JOIN "Invoice Items" invitm ON invitm."Invoice ID" = inv."Invoice ID"
LEFT JOIN "Sales Persons" sp ON sp."Sales Person ID" = inv."Sales Person ID"
LEFT OUTER JOIN "Sales Order Invoice" soinv ON soinv."Invoice ID" = inv."Invoice ID"
LEFT OUTER JOIN "Sales Orders" so ON so."Sales order ID" = soinv."Sales order ID"
LEFT OUTER JOIN( SELECT
ab1."SO Item ID" AS "SO Item ID",
ab1."SO ID" AS "SO ID",
ab1."PO Product ID" AS "Product ID",
ab1."PO Qty" AS "PO Qty",
MAX(ab1."PO Ref") AS "PO Ref",
AVG(ab1."PO Item Price") AS "PO Item Cost",
SUM(ab1."PO Shipping Per Line") AS "PO Item Shipping",
SUM(ab1."PO Discrepancy Per Line") AS "PO Item Discrepancy",
MAX(ab1."PO Subtotal excl Tax") AS "Cost excl Tax",
SUM(ab1."PO Total incl Tax") AS "Cost incl Tax"
FROM "My Purchase Order Items" ab1
GROUP BY ab1."SO ID",
ab1."PO Product ID",
ab1."SO Item ID",
ab1."PO Qty"
) t1 ON t1."SO ID" = soinv."Sales order ID"
AND (t1."Product ID" = invitm."Product ID"
AND t1."PO Qty" = invitm."Quantity")
OR invitm."SO ItemID" = t1."SO Item ID"
LEFT OUTER JOIN( SELECT
i2."Invoice ID" AS "Invoice ID",
COUNT(ii2."Quantity") AS "InvItm Line Count"
FROM "Invoice Items" ii2
LEFT JOIN "Invoices" i2 ON i2."Invoice ID" = ii2."Invoice ID"
GROUP BY i2."Invoice ID"
) iquantity ON iquantity."Invoice ID" = inv."Invoice ID"
WHERE inv."Invoice Status" NOT in ( 'Draft' , 'Void' )
GROUP BY invitm."Item ID",
invitm."Quantity"

And lastly we can group by invoices in the month and sum up the profits as well as include the customer name and other invoice references for the client's report:
copyrawstyler
```
SELECT
		 MAX(invsopoitm."Invoice Date") AS "Invoice Date",
		 MAX(invsopoitm."Invoice Ref") AS "Invoice Ref",
		 MAX(c."Customer Name") AS "Customer",
		 MAX(absp."Sales Person") AS "Sales Person",
		 SUM(invsopoitm."Invoice Line Subtotal") AS "Invoice Subtotal",
		 SUM(invsopoitm."Invoice Line Shipping excl Tax") AS "Invoice Shipping Subtotal",
		 SUM(invsopoitm."Invoice Line Total excl Tax") AS "Invoice Total excl Tax",
		 SUM(invsopoitm."Invoice Line Total inc Tax") AS "Invoice Total incl Tax",
		 SUM(invsopoitm."PO Item Cost" * invsopoitm."Invoice Line Qty") AS "PO Subtotal",
		 SUM(invsopoitm."PO Shipping") AS "PO Shipping",
		 SUM(invsopoitm."PO Discrepancy") AS "PO Discrepancy",
		 SUM(invsopoitm."PO Total excl Tax") AS "PO Total excl Tax",
		 SUM(invsopoitm."PO Total incl Tax") AS "PO Total incl Tax",
		 (MAX(if_null(t2."Credit Margin excl Tax", 0)) / MAX(t3."InvCount")) AS "Credit Margin excl Tax",
		 (MAX(if_null(t2."Credit Margin incl Tax", 0)) / MAX(t3."InvCount")) AS "Credit Margin incl Tax",
		 SUM(invsopoitm."Profit excl Tax") + (MAX(if_null(t2."Credit Margin excl Tax", 0)) / MAX(t3."InvCount")) AS "Profit excl Tax",
		 SUM(invsopoitm."Profit incl Tax") + (MAX(if_null(t2."Credit Margin incl Tax", 0)) / MAX(t3."InvCount")) AS "Profit incl Tax"
FROM  "Invoices" i
LEFT JOIN "Customers" c ON c."Customer ID"  = i."Customer ID" 
LEFT JOIN "Account Managers" absp ON absp."Customer ID"  = c."Customer ID" 
LEFT OUTER JOIN "Invoice Items vs SoPo Items" invsopoitm ON invsopoitm."Invoice ID"  = i."Invoice ID" 
LEFT OUTER JOIN(	SELECT
			 ab2."SalesPersonID" AS "SalesPersonID",
			 ab2."MonthYear" AS "MonthYear",
			 SUM(if_null(ab2."ItemMarginExclTax", 0)) AS "Credit Margin excl Tax",
			 SUM(if_null(ab2."ItemMarginInclTax", 0)) AS "Credit Margin incl Tax"
	FROM  "My Credit Note Items" ab2 
	WHERE	 ab2."Status"  IN ( 'Open'  , 'Closed'  )
	GROUP BY ab2."SalesPersonID",
		  ab2."MonthYear" 
) t2 ON absp."Sales Person ID"  = t2."SalesPersonID"
	 AND	abs_month(invsopoitm."Invoice Date")  = t2."MonthYear" 
LEFT OUTER JOIN(	SELECT
			 i3."Sales Person ID" AS "Sales Person ID",
			 abs_month(i3."Invoice Date") AS "MonthYear",
			 COUNT(*) AS "InvCount"
	FROM  "Invoices" i3 
	GROUP BY abs_month(i3."Invoice Date"),
		  i3."Sales Person ID" 
) t3 ON absp."Sales Person ID"  = t3."Sales Person ID"
	 AND	t3."MonthYear"  = abs_month(invsopoitm."Invoice Date")  
GROUP BY  invsopoitm."Invoice ID"
```
1. SELECT
2. MAX(invsopoitm."Invoice Date") AS "Invoice Date",
3. MAX(invsopoitm."Invoice Ref") AS "Invoice Ref",
4. MAX(c."Customer Name") AS "Customer",
5. MAX(absp."Sales Person") AS "Sales Person",
6. SUM(invsopoitm."Invoice Line Subtotal") AS "Invoice Subtotal",
7. SUM(invsopoitm."Invoice Line Shipping excl Tax") AS "Invoice Shipping Subtotal",
8. SUM(invsopoitm."Invoice Line Total excl Tax") AS "Invoice Total excl Tax",
9. SUM(invsopoitm."Invoice Line Total inc Tax") AS "Invoice Total incl Tax",
10. SUM(invsopoitm."PO Item Cost" * invsopoitm."Invoice Line Qty") AS "PO Subtotal",
11. SUM(invsopoitm."PO Shipping") AS "PO Shipping",
12. SUM(invsopoitm."PO Discrepancy") AS "PO Discrepancy",
13. SUM(invsopoitm."PO Total excl Tax") AS "PO Total excl Tax",
14. SUM(invsopoitm."PO Total incl Tax") AS "PO Total incl Tax",
15. (MAX(if_null(t2."Credit Margin excl Tax", 0)) / MAX(t3."InvCount")) AS "Credit Margin excl Tax",
16. (MAX(if_null(t2."Credit Margin incl Tax", 0)) / MAX(t3."InvCount")) AS "Credit Margin incl Tax",
17. SUM(invsopoitm."Profit excl Tax") + (MAX(if_null(t2."Credit Margin excl Tax", 0)) / MAX(t3."InvCount")) AS "Profit excl Tax",
18. SUM(invsopoitm."Profit incl Tax") + (MAX(if_null(t2."Credit Margin incl Tax", 0)) / MAX(t3."InvCount")) AS "Profit incl Tax"
19. FROM "Invoices" i
20. LEFT JOIN "Customers" c ON c."Customer ID" = i."Customer ID"
21. LEFT JOIN "Account Managers" absp ON absp."Customer ID" = c."Customer ID"
22. LEFT OUTER JOIN "Invoice Items vs SoPo Items" invsopoitm ON invsopoitm."Invoice ID" = i."Invoice ID"
23. LEFT OUTER JOIN( SELECT
24. ab2."SalesPersonID" AS "SalesPersonID",
25. ab2."MonthYear" AS "MonthYear",
26. SUM(if_null(ab2."ItemMarginExclTax", 0)) AS "Credit Margin excl Tax",
27. SUM(if_null(ab2."ItemMarginInclTax", 0)) AS "Credit Margin incl Tax"
28. FROM "My Credit Note Items" ab2
29. WHERE ab2."Status" in ( 'Open' , 'Closed' )
30. GROUP BY ab2."SalesPersonID",
31. ab2."MonthYear"
32. ) t2 ON absp."Sales Person ID" = t2."SalesPersonID"
33. AND abs_month(invsopoitm."Invoice Date") = t2."MonthYear"
34. LEFT OUTER JOIN( SELECT
35. i3."Sales Person ID" AS "Sales Person ID",
36. abs_month(i3."Invoice Date") AS "MonthYear",
37. COUNT(*) AS "InvCount"
38. FROM "Invoices" i3
39. GROUP BY abs_month(i3."Invoice Date"),
40. i3."Sales Person ID"
41. ) t3 ON absp."Sales Person ID" = t3."Sales Person ID"
42. AND t3."MonthYear" = abs_month(invsopoitm."Invoice Date")
43. GROUP BY invsopoitm."Invoice ID"

Additional
Here are some other queries that are required for the above but you may want to use the out-of-the-box modules instead:

Sometimes, a salesperson or user might be generating an invoice on behalf of the account manager (person who manages the customer's account), so let's get the account manager against the customer and if that isn't populated then the first estimate, sales order, invoice created by someone in relation to that customer; else take the sales person against the record:
copyrawstyler
```
SELECT DISTINCT
		 c."Customer ID" AS "Customer ID",
		 c."Customer Name" AS "Customer Name",
		 c."Last Modified Time" AS "Customer Last Modified",
		 c."Customer Sub Type" AS "Customer Type",
		 if_null(c."Account Owner", c."Created By") AS "Account Manager ID",
		 u."UserName" AS "Account Manager",
		 if_null(sp."Name", if_null(sp4."Name", if_null(sp3."Name", if_null(sp2."Name", '-')))) AS "Sales Person",
		 if_null(sp."Sales Person ID", if_null(i."Sales Person ID", if_null(so."Sales Person ID", if_null(e."Sales Person ID", '-')))) AS "Sales Person ID"
FROM  "Customers" c
LEFT JOIN "Accounts" a ON a."Account ID"  = c."Customer ID" 
LEFT JOIN "Users" u ON u."User ID"  = if_null(c."Account Owner", c."Created By") 
LEFT JOIN "Sales Persons" sp ON sp."Name"  LIKE CONCAT(u."UserName", '%') 
LEFT OUTER JOIN(	SELECT TOP 100
			 "Customer ID",
			 "Sales Person ID"
	FROM  "Estimates" 
ORDER BY "Estimate ID" DESC 
) e ON e."Customer ID"  = c."Customer ID" 
LEFT JOIN "Sales Persons" sp2 ON sp2."Sales Person ID"  = e."Sales Person ID" 
LEFT OUTER JOIN(	SELECT TOP 100
			 "Customer ID",
			 "Sales Person ID"
	FROM  "Sales Orders" 
ORDER BY "Sales Person ID" DESC 
) so ON so."Customer ID"  = c."Customer ID" 
LEFT JOIN "Sales Persons" sp3 ON sp3."Sales Person ID"  = so."Sales Person ID" 
LEFT OUTER JOIN(	SELECT TOP 100
			 "Customer ID",
			 "Sales Person ID"
	FROM  "Invoices" 
	WHERE	 "Customer ID"  IS NOT NULL
	 AND	"Sales Person ID"  IS NOT NULL
ORDER BY "Invoice ID" DESC 
) i ON i."Customer ID"  = c."Customer ID" 
LEFT JOIN "Sales Persons" sp4 ON sp4."Sales Person ID"  = i."Sales Person ID"
```
1. SELECT DISTINCT
2. c."Customer ID" AS "Customer ID",
3. c."Customer Name" AS "Customer Name",
4. c."Last Modified Time" AS "Customer Last Modified",
5. c."Customer Sub Type" AS "Customer Type",
6. if_null(c."Account Owner", c."Created By") AS "Account Manager ID",
7. u."UserName" AS "Account Manager",
8. if_null(sp."Name", if_null(sp4."Name", if_null(sp3."Name", if_null(sp2."Name", '-')))) AS "Sales Person",
9. if_null(sp."Sales Person ID", if_null(i."Sales Person ID", if_null(so."Sales Person ID", if_null(e."Sales Person ID", '-')))) AS "Sales Person ID"
10. FROM "Customers" c
11. LEFT JOIN "Accounts" a ON a."Account ID" = c."Customer ID"
12. LEFT JOIN "Users" u ON u."User ID" = if_null(c."Account Owner", c."Created By")
13. LEFT JOIN "Sales Persons" sp ON sp."Name" LIKE CONCAT(u."UserName", '%')
14. LEFT OUTER JOIN( SELECT TOP 100
15. "Customer ID",
16. "Sales Person ID"
17. FROM "Estimates"
18. ORDER BY "Estimate ID" DESC
19. ) e ON e."Customer ID" = c."Customer ID"
20. LEFT JOIN "Sales Persons" sp2 ON sp2."Sales Person ID" = e."Sales Person ID"
21. LEFT OUTER JOIN( SELECT TOP 100
22. "Customer ID",
23. "Sales Person ID"
24. FROM "Sales Orders"
25. ORDER BY "Sales Person ID" DESC
26. ) so ON so."Customer ID" = c."Customer ID"
27. LEFT JOIN "Sales Persons" sp3 ON sp3."Sales Person ID" = so."Sales Person ID"
28. LEFT OUTER JOIN( SELECT TOP 100
29. "Customer ID",
30. "Sales Person ID"
31. FROM "Invoices"
32. WHERE "Customer ID" IS NOT NULL
33. AND "Sales Person ID" IS NOT NULL
34. ORDER BY "Invoice ID" DESC
35. ) i ON i."Customer ID" = c."Customer ID"
36. LEFT JOIN "Sales Persons" sp4 ON sp4."Sales Person ID" = i."Sales Person ID"
And for true profits, we need to include those credit notes for refunds, etc. This tweak is if we want to calculate credit notes issued in a particular month rather than the one-to-one with an invoice as profits in the past would change. So this table is created so that if a credit note was issued in January for an invoice issued prior to this, and we want to work out profits in January, we're including the credit notes that were issued in January. I'm calling this one "My Credit Note Items":
copyrawstyler
```
SELECT
		 cn."Created Time" AS "Created",
		 abs_month(cn."Created Time") AS "MonthYear",
		 cn."CreditNotes ID" AS "CnId",
		 cni."Product ID" AS "ProductID",
		 cn."Credit Note Number" as "CnRef",
		 cn."Credit Note Status" AS "Status",
		 cn."Sales Person ID" AS "SalesPersonId",
		 sp."Name" AS "Sales Person",
		 cn."Reference Number" AS "Reference",
		 cn."Shipping Charge (BCY)" AS "Shipping",
		 if_null(cn."Shipping Charge Tax Amount (BCY)", 0) AS "Shipping Tax",
		 cn."Sub Total (BCY)" AS "CnSubTotal",
		 cn."Is Inclusive Tax" AS "Includes Tax?",
		 cn."Total (BCY)" AS "CnTotal",
		 cni."Buy Price" AS "ItemBuyPrice",
		 cni."Discount Amount (BCY)" AS "ItemDiscount",
		 cni."Item Name" AS "ItemName",
		 cni."Item Price" AS "ItemPrice",
		 cni."Quantity" AS "ItemQty",
		 cni."Sub Total" AS "ItemSubtotal",
		 cni."Tax Amount" AS "ItemTax",
		 cni."Total" AS "ItemTotalExclTax",
		 cni."Quantity" * (cni."Buy Price" -cni."Item Price") AS "ItemMarginExclTax",
		 (cni."Quantity" * (cni."Buy Price" -cni."Item Price")) * 1.2 AS "ItemMarginInclTax"
FROM  "Credit Notes" cn
LEFT JOIN "Credit Note Items" cni ON cni."CreditNotes ID"  = cn."CreditNotes ID" 
LEFT JOIN "Sales Persons" sp ON sp."Sales Person ID"  = cn."Sales Person ID" 
LEFT JOIN "Items" itm ON itm."Item ID"  = cni."Product ID"  
WHERE	 cn."Credit Note Status"  IN ( 'Closed'  , 'Open'  )
```
1. SELECT
2. cn."Created Time" AS "Created",
3. abs_month(cn."Created Time") AS "MonthYear",
4. cn."CreditNotes ID" AS "CnId",
5. cni."Product ID" AS "ProductID",
6. cn."Credit Note Number" as "CnRef",
7. cn."Credit Note Status" AS "Status",
8. cn."Sales Person ID" AS "SalesPersonId",
9. sp."Name" AS "Sales Person",
10. cn."Reference Number" AS "Reference",
11. cn."Shipping Charge (BCY)" AS "Shipping",
12. if_null(cn."Shipping Charge Tax Amount (BCY)", 0) AS "Shipping Tax",
13. cn."Sub Total (BCY)" AS "CnSubTotal",
14. cn."Is Inclusive Tax" AS "Includes Tax?",
15. cn."Total (BCY)" AS "CnTotal",
16. cni."Buy Price" AS "ItemBuyPrice",
17. cni."Discount Amount (BCY)" AS "ItemDiscount",
18. cni."Item Name" AS "ItemName",
19. cni."Item Price" AS "ItemPrice",
20. cni."Quantity" AS "ItemQty",
21. cni."Sub Total" AS "ItemSubtotal",
22. cni."Tax Amount" AS "ItemTax",
23. cni."Total" AS "ItemTotalExclTax",
24. cni."Quantity" * (cni."Buy Price" -cni."Item Price") AS "ItemMarginExclTax",
25. (cni."Quantity" * (cni."Buy Price" -cni."Item Price")) * 1.2 AS "ItemMarginInclTax"
26. FROM "Credit Notes" cn
27. LEFT JOIN "Credit Note Items" cni ON cni."CreditNotes ID" = cn."CreditNotes ID"
28. LEFT JOIN "Sales Persons" sp ON sp."Sales Person ID" = cn."Sales Person ID"
29. LEFT JOIN "Items" itm ON itm."Item ID" = cni."Product ID"
30. WHERE cn."Credit Note Status" in ( 'Closed' , 'Open' )