Skip to Content
0

How to create a formula for Grand total?

Mar 01, 2017 at 10:41 AM

156

avatar image

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?

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

4 Answers

Ian Waterman Mar 01, 2017 at 02:46 PM
1

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

Share
10 |10000 characters needed characters left characters exceeded
Rüdiger Frank Mar 01, 2017 at 04:58 PM
0

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

Show 1 Share
10 |10000 characters needed characters left characters exceeded

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.

0
Nagarajan K Mar 03, 2017 at 12:15 PM
0

Hi,

Please share your complete query here to add required fields.

Regards,

Nagarajan

Show 2 Share
10 |10000 characters needed characters left characters exceeded

SELECT T0.ItemCode, Max(T0.ItemName) 'Item Name', Max(T0.FrgnName) 'Model 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 INNER JOIN [dbo].[OITB] T3 ON T0.ItmsGrpCod = T3.ItmsGrpCod WHERE T2.[DocDate] >= [%1] and T2.[DocDate] <= [%2]

and T0.[ItmsGrpCod] IN ('119', '120', '121') GROUP BY T0.ItemCode

0

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.

0
Adam Tipping Apr 04, 2017 at 08:40 AM
0

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.

Share
10 |10000 characters needed characters left characters exceeded