Hi Experts,
I really need your help I've created a query that can generate item Sales Analysis Per Item Category with my clients Requirements but It seem I got a Problem with my query LineTotal - ItemCost is not match with the gross profit.But the summary of my gross profit query is match in the sales analysis. I think there's wrong with my RowTotal or ItemCost but I don't know where. hmmmmm Can you help me with it? Here's my Query Below thanks in advance.
SELECT DISTINCT '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.[StockPrice] ) AS 'Item Cost',SUM(T1.GrssProfit ) 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 '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],T2.[U_JTEC_SUB_PRINCIPAL]
UNION ALL
SELECT DISTINCT 'Return' As DocType,(T1.ItemCode), T1.Dscription,-SUM(T1.Quantity) AS 'QNTY',T2.[SalUnitMsr] AS 'UoM',-SUM(T1.LineTotal)as 'Amount',-SUM( T1.Quantity *T1.[StockPrice] ) as 'Item Cost',-SUM(T1.GrssProfit ) 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 '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],T2.[U_JTEC_SUB_PRINCIPAL]
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
Thanks for the query but it seem It didn't get it. Total Amount Less Item Cost = Gross Profit. But The Query Show same amount of gross profit with my sales analysis I don't know where is wrong In the total Amount or in the item Cost. Here a Screen Shot
Thanks
or its already net? bec. I have Discounts in the document aside from the row discount?
Hi,
INV1.LineTotal is the net total for the line (0% VAT). It takes line discount into account, but it does not take any separate document level discount into account.
Regards,
Johan
hello
then why is it my sales analysis Gross Profit by customer is same with my query item type? Should be the Gross Profit of item is higher than the customer bec. the document discount is not already less with the items only the row discount is less?
thanks
Hi,
Usually B1 will translate document discount in the sales order or delivery note, to line discounts in the invoice.
Are you sure, that you have both line discounts and document discounts in the same invoice? Please try this query to analyse:
SELECT 'Invoice' As DocType ,T0.DocNum ,T0.DiscPrcnt AS 'Document Discount %' ,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' ,T1.DiscPrcnt AS 'Line Discount %' ,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] ,T1.DiscPrcnt ,T0.DiscPrcnt
Please check the result, and see if there are any lines that have both document discount and line discount.
Regards,
Johan
Yes there is sir.
Like this Document sir
Hi,
T1.GrssProfit
This field does take both document and line discounts into account.
Regards,
Johan
ohh thank you It's all know clear to me know so I just need to make a formula in Crystal report Amount less ItemCost so I can get my gross Profit per item thanks
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
Hi Zal,
But our product is less vat already so we don't really use the tax set up.
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))
How can I insert It to my query?
1. If gross profit is not calculated by item cost, simply replace T1.StockPrice with T1.GrossBuyPr. That will get you the same amounts
2.Depends whether you want one column or two. If you want to swoh your net revenue in the column 'Amount', syntax would be
SUM(T1."LineTotal"*(1-(T0."DiscPrcnt"/100))) (modify it for SQL, this is copy/paste from HANA)
Also check if there are any correction invoices, they are included in sales analysis calculation so you should include them in your query