Skip to Content
avatar image
Former Member

SAP B1 TOP SELLING ITEMS

Hi Guys I'm trying to make a query that can generate top selling Items per month by Customer Group Name how can i get it when I run my query it say invalid column here my query below.

SELECT TOP 20 T1.ItemCode, T2.ItemName, cast(sum(T1.Quantity) as numeric) as 'Quantity Sold', sum(T1.LineTotal - T1.VatSum) as 'Total Net Value', sum(T1.LineTotal) as 'Total Gross Value', T1.Currency

FROM dbo.INV1 T1 inner join OINV T3 on T1.docentry = t3.docentry
LEFT OUTER JOIN OITM T2 on T1.ItemCode = T2.ItemCode INNER JOIN OCRD T4 ON T2.CardCode = T4.CardCode INNER JOIN OCRG T5 ON T4.GroupCode = T5.GroupCode

WHERE T1.ItemCode is not null and t3.docdate between [%0] and [%1] and t3.series = '229' and T5.GroupName = [%2]

GROUP BY T1.ItemCode, T2.ItemName, T1.Currency

order by sum(T1.Quantity) desc

thank you for helping

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    Apr 04, 2017 at 06:54 AM

    Hi,

    I found your mistake :

    ----------------------------------------------------------------------------------------------------------------------

    SELECT TOP 20 T1.ItemCode, T2.ItemName, cast(sum(T1.Quantity) as numeric) as 'Quantity Sold', sum(T1.LineTotal - T1.VatSum) as 'Total Net Value', sum(T1.LineTotal) as 'Total Gross Value', T1.Currency

    FROM dbo.INV1 T1

    INNER JOIN OINV T3 on T1.docentry = t3.docentry
    LEFT OUTER JOIN OITM T2 on T1.ItemCode = T2.ItemCode

    NNER JOIN OCRD T4 ON T3.CardCode = T4.CardCode

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

    WHERE T1.ItemCode is not null and (t3.docdate between [%0] and [%1]) and t3.series = 229 and T5.GroupName = '[%2]'

    GROUP BY T1.ItemCode, T2.ItemName, T1.Currency

    order by sum(T1.Quantity) desc

    ----------------------------------------------------------------------------------------------------------------------

    It should T3.CardCode = T4.CardCode not T2.CardCode = T4.CardCode

    Hope this helps :)

    Regards,

    Rizki

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Thank you! It's Working!

      But I think It didn't less the cancelled transaction(ar credit memo)

      how can I do that any Idea?

      sorry I'm Just new to SAP B1 I barely not know yet the flow of the database of it.

      can you help me thanks in advance.

  • Apr 04, 2017 at 04:17 AM

    Hi,

    You just need to make revision in where clause :

    SELECT TOP 20 T1.ItemCode, T2.ItemName, cast(sum(T1.Quantity) as numeric) as 'Quantity Sold', sum(T1.LineTotal - T1.VatSum) as 'Total Net Value', sum(T1.LineTotal) as 'Total Gross Value', T1.Currency

    FROM dbo.INV1 T1 inner join OINV T3 on T1.docentry = t3.docentry
    LEFT OUTER JOIN OITM T2 on T1.ItemCode = T2.ItemCode INNER JOIN OCRD T4 ON T2.CardCode = T4.CardCode INNER JOIN OCRG T5 ON T4.GroupCode = T5.GroupCode

    WHERE T1.ItemCode is not null and (t3.docdate between [%0] and [%1]) and t3.series = 229 and T5.GroupName = '[%2]'

    GROUP BY T1.ItemCode, T2.ItemName, T1.Currency

    order by sum(T1.Quantity) desc

    Hope this helps :)

    Regards,

    Rizki

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 05, 2017 at 03:51 AM

    Hi,

    Just Add this in where clause :

    -----------------------------------------------------------------------------------------------------------------------------

    Where .... and T3.Canceled='N' and T1.TargetType<>14

    -----------------------------------------------------------------------------------------------------------------------------

    T3.Canceled = 'N' --> Show Invoice that is not canceled only

    T1.TargetType<>14 --> Target type 14 for AR Credit Memo, so we need to remove it.

    Hope this helps :)

    Regards,

    Rizki W

    Add comment
    10|10000 characters needed characters exceeded