Skip to Content
0

Sales YTD by Sales Employee Query

Apr 03, 2017 at 03:06 PM

141

avatar image

I have been trying to write a query on SAP B1 in order to show customer sales YTD by Sales Employee, as Sales Analysis doesnt break this down into one coherent unit, and I have gotten a bit stuck.

It is returning data but not correctly as it isnt showing teh totals YTD correct (I know this by running SA to compare) and I would also like to show the profit as well, but cant seem to get my head around showing that - any help would be greatfully received as I need to sort for our end of year sales meeting:

SELECT T0.CardCode, T0.CardName, T2.SlpName, Sum(IsNull(T1.LineTotal,0)) 'YTD Total'
FROM OINV T0
Left JOIN INV1 T1 ON T1.DocEntry = T0.DocEntry
Left JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode

WHERE T2.SlpName = [%0]
And DateDiff(YY,T0.DocDate,GetDate()) = 0

GROUP BY T0.CardCode, T0.CardName, T2.SlpName
HAVING Sum(IsNull(T1.LineTotal,0))>0

Regards
Roy Bright

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

2 Answers

Best Answer
Johan Hakkesteegt Apr 04, 2017 at 08:39 AM
0

There's just no satisfying some people... ;-)

How about this:

SELECT T0.CardCode
     , T0.CardName
     , T2.SlpName
     ,SUM(T1.[LineTotal]) AS [YTD Total Netto]
     ,SUM(T1.[GrossBuyPr] * T1.[Quantity]) AS [YTD Gross Profit]
     ,SUM(T1.[GrossBuyPr] * T1.[Quantity]) / SUM(T1.[LineTotal]) AS [Gross Profit Percentage]
FROM OINV T0
     INNERJOIN INV1 T1 ON T0.DocEntry = T1.DocEntry
     INNERJOIN OSLP T2 ON T0.SlpCode = T2.SlpCode
WHERE T2.SlpName =[%0]
  AND YEAR(T0.DocDate)= YEAR(GetDate()) 
  AND T1.ItemCode NOT IN ('','',''/* etc.*/)/* <-- fill in any item codes you want to exclude */
GROUPBY T0.CardCode
       , T0.CardName
       , T2.SlpName
HAVING Sum(T0.DocTotal - T0.VatSum)>0

You'll have to adapt the date range thing again.

Regards,

Johan

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

hahahaha - I'm such a stinker, aint I :P
For some reason, bro the NOT IN clause is preventing the query from now running, this is how it looks for me with my additions, but fails to run at all (even an error):

SELECT T0.CardCode
, T0.CardName
, T2.SlpName
,SUM(T1.[LineTotal]) AS [YTD Total Netto]
,SUM(T1.[GrossBuyPr] * T1.[Quantity]) AS [YTD Gross Profit]
,SUM(T1.[GrossBuyPr] * T1.[Quantity]) / SUM(T1.[LineTotal]) AS [Gross Profit Percentage]
FROM OINV T0
INNERJOIN INV1 T1 ON T0.DocEntry = T1.DocEntry
INNERJOIN OSLP T2 ON T0.SlpCode = T2.SlpCode
WHERE T2.SlpName =[%0]
And T0.DocDate BETWEEN [%1] AND [%2]
AND T1.ItemCode NOT IN ('','',''/* Carriage*/)

GROUPBY T0.CardCode
, T0.CardName
, T2.SlpName
HAVING Sum(T0.DocTotal - T0.VatSum)>0

0

Ok, try changing this: AND T1.ItemCode NOT IN ('','',''/* Carriage*/)

to this: AND NOT T1.ItemCode = 'your item code here'

1

Mate, this is perfect - might I be totally cheeky and ask, one, last, thing :P - is there a way that i can also remove the credit notes for ORIN and RIN1? I have done a UNION ALL to add them into the report but realise I need some way of subtracting them from the OINV totals and I'm stumped :/

SELECT T0.CardCode
, T0.CardName
, T2.SlpName
,SUM(T1.[LineTotal]) AS [YTD Total Netto]
,SUM(T1.[GrossBuyPr] * T1.[Quantity]) AS [YTD Gross Profit]
,SUM(T1.[GrossBuyPr] * T1.[Quantity]) / SUM(T1.[LineTotal]) AS [Gross Profit Percentage]

FROM OINV T0
INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode

WHERE T2.SlpName = [%0] AND T0.DocDate BETWEEN [%1] AND [%2] AND NOT T1.ItemCode = 'Carriage'


GROUP BY T0.CardCode
, T0.CardName
, T2.SlpName
HAVING Sum(T0.DocTotal - T0.VatSum)>0

UNION ALL

SELECT T0.CardCode
, T0.CardName
, T2.SlpName
,SUM(T1.[LineTotal]) AS [YTD Total Netto]
,SUM(T1.[GrossBuyPr] * T1.[Quantity]) AS [YTD Gross Profit]
,SUM(T1.[GrossBuyPr] * T1.[Quantity]) / SUM(T1.[LineTotal]) AS [Gross Profit Percentage]

FROM ORIN T0
INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode

GROUP BY T0.CardCode
, T0.CardName
, T2.SlpName
HAVING Sum(T0.DocTotal - T0.VatSum)>0

0

It can be done. Encapsulate the whole thing in its own query. So something like this:

SELECT X.CardCode
, X.CardName
, X.SlpName
,SUM(X.[YTD Total Netto]) AS [YTD Total Netto]
,SUM(X.[YTD Gross Profit]) AS [YTD Gross Profit]
,SUM([Gross Profit Percentage]) AS [Gross Profit Percentage]
FROM (your query here) X GROUP BY X.CardCode , X.CardName , X.SlpName

remember that with this solution you need to make the ORIN / RIN1 values negative. So for example like this:

SUM(T1.[LineTotal]) * -1 AS [YTD Total Netto]
0

Sorry buddy, but I'm gonna sound a bit thick here :/
Do I replace the X's with the data I require or leave them? And do I need to do a UNION ALL?

My brain just inst grasping this one :(

0

You use your query as is, with the union alls and everything. All of that goodness goes inside the brackets. You replace only the text "your query here".

So simplified what happens is this:

SELECT SUM(X.LineTotal) AS LineTotal
FROM (select LineTotal from INV1
UNION ALL
select LineTotal * -1 from RIN1) X
1

Got it - thanks buddy :)

0
Johan Hakkesteegt Apr 04, 2017 at 06:00 AM
0

Hi Roy,

You were just overthinking it a bit, I think. Please give this version a try:

SELECT T0.CardCode
     , T0.CardName
     , T2.SlpName
     , Sum(T0.DocTotal - T0.VatSum) 'YTD Total Netto'
     , Sum(T0.GrosProfit) 'YTD Gross Profit'
FROM OINV T0
     INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode
WHERE T2.SlpName = [%0] 
  And YEAR(T0.DocDate)  = YEAR(GetDate())
GROUP BY T0.CardCode
       , T0.CardName
       , T2.SlpName
HAVING Sum(T0.DocTotal - T0.VatSum) > 0

Regards,

Johan

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

Hi Johan - thanks for this buddy - lifesaver as ever - this works great - I modified the report slightly so that I could report on a date range - but is there a way that I can have the report NOT report on a certain amount? We have a product code set up for CARRIAGE and I need to remove this from the query search. I tried to inner join INV1 and use a NotLike expression to remove the T1.ItemCode Carriage, but doing so, somehow doubled the end figures (which seemed to happen when I Inner Joined INV1 - any ideas buddy?

Regards

Roy

0