Hi,
Please try this:
SELECT 'Invoice' As DocType ,T1.ItemCode ,T1.Dscription ,SUM(T1.Quantity) AS 'QNTY' ,T2.[SalUnitMsr] AS 'UoM' ,SUM(T1.LineTotal) as 'Amount' ,SUM(T1.Quantity * T1.[GrossBuyPr]) AS 'Item Cost' ,SUM(IsNUll(T1.GrssProfit ,0)) as 'Gross Profit' ,T2.[U_JTEC_SUB_PRINCIPAL] ,CASE WHEN Sum(Isnull(T1.LineTotal,0)) = 0 THEN 0 ELSE SUM(IsNUll(T1.GrssProfit ,0)) / Sum(isnull(T1.LineTotal,0)) * 100 END as 'Gross Profit %' FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode] WHERE T0.[CANCELED] = 'N' AND T0.[DocDate] BETWEEN [%0] AND [%1] GROUP BY T1.ItemCode ,T1.Dscription ,T2.[SalUnitMsr] UNION ALL SELECT 'Invoice' As DocType ,T1.ItemCode ,T1.Dscription ,SUM(T1.Quantity) AS 'QNTY' ,T2.[SalUnitMsr] AS 'UoM' ,SUM(T1.LineTotal) as 'Amount' ,SUM(T1.Quantity * T1.[GrossBuyPr]) AS 'Item Cost' ,SUM(IsNUll(T1.GrssProfit ,0)) as 'Gross Profit' ,T2.[U_JTEC_SUB_PRINCIPAL] ,CASE WHEN Sum(Isnull(T1.LineTotal,0)) = 0 THEN 0 ELSE SUM(IsNUll(T1.GrssProfit ,0)) / Sum(isnull(T1.LineTotal,0)) * 100 END as 'Gross Profit %' FROM ORIN T0 INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode] WHERE T0.[CANCELED] = 'N' AND T0.[DocDate] BETWEEN [%0] AND [%1] GROUP BY T1.ItemCode ,T1.Dscription ,T2.[SalUnitMsr]
Regards,
Johan
Hello Socrates - you might want to check the field called VatSum in the INV1 and RIN1 tables. LineTotal minus VatSum.
Just a thought as it really depends upon whether you have tax set up. Regards, Zal
Hello,
Check two things:
1. Base Price Origin for Gross Profit Calculation (document settings, General tab) - maybe another price list is set for gross profit calculation
2. Is there any discount per document ("DiscPrcnt" field in OINV table)? If so, your revenue calculation shoud be
"LineTotal" x (1-("DiscPrcnt"/100))
Add comment