Profit and Loss Statement

[Former Member]

Hi all,

I am trying to recreate the results of the Profit and Loss Statement so I can customize them for a user as they need to sort sales by customer group and territory. I have created a query but the trouble is that it doesn't seem to return the same value as the P&L statement. Are there any conditions I'm missing here?

This is my query:

SELECT GroupName [Group], Descript [Territory], SUM(Total) [Invoiced] FROM (

--Invoices

SELECT T2.[GroupName], T3.[descript], SUM(T0.[DocTotal]) [Total] FROM OINV T0 LEFT JOIN OCRD T1 ON T0.CardCode = T1.CardCode LEFT JOIN OCRG T2 ON T1.GroupCode = T2.GroupCode LEFT JOIN OTER T3 ON T1.Territory = T3.territryID WHERE T0.DocDate >= '20130701' AND T0.DocDate <= '20140630' GROUP BY T2.[GroupName], T3.[descript]

UNION ALL

--Credits

SELECT T2.[GroupName], T3.[descript], SUM(T0.[DocTotal]) * -1 [Total] FROM ORIN T0 LEFT JOIN OCRD T1 ON T0.CardCode = T1.CardCode LEFT JOIN OCRG T2 ON T1.GroupCode = T2.GroupCode LEFT JOIN OTER T3 ON T1.Territory = T3.territryID WHERE T0.DocDate >= '20130701' AND T0.DocDate <= '20140630' GROUP BY T2.[GroupName], T3.[descript]

) T GROUP BY GroupName, Descript ORDER BY GroupName, Descript

[Matt_Fraser]

Is there a reason you're trying to do this in SQL, and not using the reports and transactions that already exist in the system?  There are a bunch of out-of-the-box reports and queries in the Controlling module for profitability analysis and profit center accounting.  Failing that, there are likely lots of flexible information structures in the SAP Query system (SQ01, SQ02, etc).  You can probably get much better advice if you post your question in one of those application area forums, such as , or perhaps another like that.

Regards,

Matt

[Former Member]

Hi Matt,

Thanks for your reply. I have explained the reasons for this query in my initial post but I'm thinking I may have posted it in the wrong forum as I am using SAP Business One. In any case I found a solution so this post is no longer needed. I'm unsure how to delete it though.

[Matt_Fraser]

Click the link for "Assumed Answered" near the header, and that will "close" it out.  You can't really delete it, although a moderator could.