SAP B1 QUERY ITEM COST NOT MATCH WITH GROSS PROFIT

[Former Member]

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]

[Johan_H · Accepted solution]

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

[Former Member · Accepted solution]

Hi Johan Hakkesteegt,

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

[Former Member · Accepted solution]

or its already net? bec. I have Discounts in the document aside from the row discount?

[Johan_H · Accepted solution]

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

[Former Member · Accepted solution]

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

[Johan_H · Accepted solution]

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

[Former Member · Accepted solution]

Yes there is sir.

Like this Document sir

[Johan_H · Accepted solution]

Hi,

T1.GrssProfit

This field does take both document and line discounts into account.

Regards,

Johan

[Former Member · Accepted solution]

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

[hdolenec]

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

[Former Member]

How can I insert It to my query?

[hdolenec]

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

[zal_parchem2]

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

[Former Member]

Hi Zal,

But our product is less vat already so we don't really use the tax set up.