Skip to Content
avatar image
Former Member

SAP B1 QUERY ITEM COST NOT MATCH WITH GROSS PROFIT

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]

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    Oct 17, 2017 at 07:01 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      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

  • Oct 17, 2017 at 09:26 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 17, 2017 at 11:09 AM

    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
    10|10000 characters needed characters exceeded

    • 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