Skip to Content
avatar image
Former Member

How to create a formula for Grand total?

I have created a Report, using tables OITM, OINV and INV1,

I need to include Grand Total for the following:

- Quantity (Total quantity of item invoiced) and
- Invoice Total (Total amount of item invoiced)
- Gross Profit

This is for every item in SAP

Any advice on how to get these totals (detail Line)

I also need Gross Profit % adding to the report.

can anyone assist?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Mar 01, 2017 at 02:46 PM

    Hi Adam

    If you have a simple listing report then add a group by Item Code and then add a count summary of item code and a sum summary of invoice amount to the item code group footer.

    Suppress detail line and item code group header and add/move item code and name fields to the footer.

    Ian

    Add comment
    10|10000 characters needed characters exceeded

  • Mar 01, 2017 at 04:58 PM

    Hi Adam,

    invoices are not stored in tables OSPP and SPP1 but in OINV and INV1!

    If you really want to have a correct quantity you should also consider credit notes - stored in tables ORIN and RIN1.

    Please also be aware that in RIN1 there might be records for only "price credits" which means "NO INVENTORY TRANSACTION".

    Best regards

    Rüdiger

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Rüdiger,

      I realised, I was meant to put OINV and INV1 in the question rather than OSPP and SPP1, I'm working on a few reports,

      I do need advice on how to minus credit notes from that invoiced quantity and total.

  • Mar 03, 2017 at 12:15 PM

    Hi,

    Please share your complete query here to add required fields.

    Regards,

    Nagarajan

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      A more complex query i am working on to get this information and more:

      SELECT * FROM ( SELECT T0.ItemCode, T2.DocEntry, Max(T0.ItemName) 'Item Name', Max(IsNull(T0.OnHand,0)) 'In Stock', SUM(IsNull(T1.Quantity,0)) 'Quantity', Sum(IsNull(T1.LineTotal,0)) 'Sales Amount', SUM(IsNull(T1.GrssProfit,0)) 'Gross Profit', 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 dbo.OITM T0 LEFT JOIN dbo.INV1 T1 ON T1.ItemCode=T0.ItemCode LEFT JOIN dbo.OINV T2 ON T2.DocEntry=T1.DocEntry WHERE T2.DocDate >= [%0] and T2.Docdate <= [%1] GROUP BY T0.ItemCode, T0.DocEntry ) T5 INNER JOIN dbo.OJDT T3 ON T3.BaseRef = T2.DocEntry INNER JOIN dbo.JDT1 T4 ON T4.TransID = T3.TransID LEFT JOIN ( SELECT T10.ItemCode, T10.DocEntry, Max(T10.ItemName) 'Item Name', Max(IsNull(T10.OnHand,0)) 'In Stock', SUM(IsNull(T11.Quantity,0)) 'Quantity', Sum(IsNull(T11.LineTotal,0)) 'Sales Amount', SUM(IsNull(T11.GrssProfit,0)) 'Gross Profit', Case WHEN Sum(Isnull(T11.LineTotal,0)) = 0 THEN 0 ELSE SUM(IsNUll(T11.GrssProfit,0))/Sum(Isnull(T11.LineTotal,0)) * 100 END 'Gross Profit %' FROM dbo.OITM T10 LEFT JOIN dbo.RIN1 T11 ON T11.ItemCode=T10.ItemCode LEFT JOIN dbo.ORIN T12 ON T12.DocEntry=T11.DocEntry WHERE T12.DocDate >= [%0] and T12.Docdate<=[%1] GROUP BY T10.ItemCode, T11.DocEntry ) T15 ON T3.BaseRef = T12.DocEntry

      However, I cant manage to minus 'Sales Amount' from the bottom table to the top table.

  • avatar image
    Former Member
    Apr 04, 2017 at 08:40 AM

    Here is the query I am using below:

    SELECT S.FrgnName,
    SUM(S.Quantity) as 'Quantity',
    SUM(S.Linetotal) as 'Amount(LC)',
    SUM(S.GrssProfit) as 'GrssProfit'
    FROM (SELECT T3.FrgnName AS 'FrgnName',
    T1.Quantity AS 'Quantity',
    T1.Linetotal - (T0.DiscPrcnt * (T1.LineTotal)/100) AS 'LineTotal',
    T1.GrssProfit AS 'GrssProfit'
    FROM dbo.OINV
    T0 INNER JOIN dbo.INV1 T1 ON T1.DocEntry = T0.DocEntry
    LEFT JOIN OCRD T2 ON T0.Cardcode = T2.CardCode
    LEFT JOIN OITM T3 ON T1.ItemCode = T3.ItemCode
    WHERE T0.docdate >= [%0] and T0.docdate <= [%1]

    UNION ALL

    SELECT T3.FrgnName AS 'FrgnName', -T1.Quantity AS 'Quantity',
    -T1.Linetotal - (T0.DiscPrcnt * (T1.LineTotal)/100) AS 'LineTotal',
    -T1.GrssProfit AS 'GrssProfit'
    FROM dbo.ORIN T0
    INNER JOIN dbo.RIN1 T1 ON T1.DocEntry = T0.DocEntry
    LEFT JOIN OCRD T2 ON T0.Cardcode = T2.CardCode
    LEFT JOIN OITM T3 ON T1.ItemCode = T3.ItemCode
    WHERE T0.docdate >= [%0] and T0.docdate <= [%1]
    ) S
    GROUP BY S.FrgnName
    ORDER BY SUM(S.LineTotal) DESC

    -- I need Grand Totals at the bottom of this report and gross profit % column adding to it.

    Add comment
    10|10000 characters needed characters exceeded