Skip to Content
0

How to create a formula for Grand total?

Mar 01, 2017 at 10:41 AM

178

avatar image
Former Member

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

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
Former Member

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

0
avatar image
Former Member 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