Skip to Content

Sales YTD by Sales Employee Query

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Apr 04, 2017 at 08:39 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 04, 2017 at 06:00 AM

    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

    Add comment
    10|10000 characters needed 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