Skip to Content
avatar image
Former Member

SAP B1 Top Selling Product/Item Per Month Query

Hi Guys

How ca I make a query that can generate top selling products per month that is less returns from customers all ready (Credit memo),

Thanks in advance

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Aug 23, 2017 at 06:20 AM

    Hi,

    For Crystal Report purposes you may try this query:

    DECLARE @FROM AS DATETIME = CAST('2017/01/01' AS DATETIME)
    DECLARE @TO AS DATETIME = CAST('2017/12/31' AS DATETIME)
    SELECT i.ItemCode
         , i.ItemName
         , ISNULL(s.Quantity, 0) - ISNULL(c.Quantity, 0) AS Quantity
         , ISNULL(s.LineTotal, 0) - ISNULL(c.LineTotal, 0) AS TotalAmount
         , ISNULL(s.GrossProfit, 0) - ISNULL(c.GrossProfit, 0) AS GrossProfit
         , (ISNULL(s.GrossProfit, 0) - ISNULL(c.GrossProfit, 0))
           / CASE
              WHEN ISNULL(s.LineTotal, 0) - ISNULL(c.LineTotal, 0) = 0 THEN 1
              ELSE ISNULL(s.LineTotal, 0) - ISNULL(c.LineTotal, 0)
             END AS GrossProfitPercentage
    FROM OITM i
         LEFT OUTER JOIN (SELECT sr.ItemCode, SUM(sr.Quantity) AS Quantity,  SUM(sr.LineTotal) AS LineTotal
                              , (SUM(sr.LineTotal) - SUM(sr.GrossBuyPr * sr.Quantity)) AS GrossProfit
                          FROM INV1 sr
                               INNER JOIN OINV sh ON sr.DocEntry = sh.DocEntry
                          WHERE sh.DocDate BETWEEN @FROM AND @TO
                          GROUP BY sr.ItemCode) s ON i.ItemCode = s.ItemCode
         LEFT OUTER JOIN (SELECT cr.ItemCode, SUM(cr.Quantity) AS Quantity,  SUM(cr.LineTotal) AS LineTotal
                               , (SUM(cr.LineTotal) - SUM(cr.GrossBuyPr * cr.Quantity)) AS GrossProfit
                          FROM RIN1 cr
                               INNER JOIN ORIN ch ON cr.DocEntry = ch.DocEntry
                          WHERE ch.DocDate BETWEEN @FROM AND @TO
                          GROUP BY cr.ItemCode) c ON i.ItemCode = c.ItemCode
    ORDER BY GrossProfit DESC

    Please note that you can test this query as is, but that you will have to edit the first two lines before you can use it with Crystal Reports.

    Regards,

    Johan

    Add comment
    10|10000 characters needed characters exceeded

  • Aug 22, 2017 at 07:10 AM

    Hi,

    No need for a query. You may use the Sales Analysis report (Sales - A/R > Sales Reports > Sales Analysis).

    Choose the Items tab page, and run the report by invoices. This report you can sort either by sales or gross profit to see the top x selling items.

    In the report you may double click on the line number to get a detailed report of invoices and credit memos on which the line's information is based.

    Regards,

    Johan

    Add comment
    10|10000 characters needed characters exceeded