Skip to Content
0
Former Member
Jun 28, 2016 at 10:53 AM

Adding Gross Profit to the attached report

29 Views

Hi all

We use the report below to track what our customer have paid for products in the past. We would like to add a gross profit % column if possible. Would someone be able to help me with writing this into the report please? To access the g/p we usually use the Ctlr + G in sale order to get the information

Thank you and Kind Regards


Matt Downing

SELECT 'Invoice' 'Type', T0.[CardCode], T0.[DocDate], T0.[DocNum], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T1.[PriceBefDi] as "Price B4 Dis", T1.[DiscPrcnt] as "Dis %", T1.[LineTotal]

FROM OINV T0

INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry

INNER JOIN OCRD T3 ON T0.CardCode = T3.CardCode

INNER JOIN OCRG T4 ON T3.GroupCode = T4.GroupCode

WHERE T0.[DocDate] <= (CASE WHEN [%0] = ' ' THEN '01.01.2099' ELSE [%0] END)

AND

T0.[CardCode] LIKE '%[%1]%'

AND

T1.[ItemCode] Like '[%2]%'

AND

T4.[GroupName] like '%[%3]%'

UNION ALL

SELECT 'Quote', T0.[CardCode], T0.[DocDate], T0.[DocNum], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T1.[PriceBefDi], T1.[DiscPrcnt], T1.[LineTotal]

FROM OQUT T0

INNER JOIN QUT1 T1 ON T0.DocEntry = T1.DocEntry

INNER JOIN OCRD T3 ON T0.CardCode = T3.CardCode

INNER JOIN OCRG T4 ON T3.GroupCode = T4.GroupCode

WHERE T0.[DocDate] <= (CASE WHEN [%0] = ' ' THEN '01.01.2099' ELSE [%0] END)

AND

T0.[CardCode] LIKE '%[%1]%'

AND

T1.[ItemCode] Like '[%2]%'

AND

T4.[GroupName] like '%[%3]%'